DBA > Interview Resource

Replication FAQ (MS SQL Server 7.0 Enterprise Manager)

Part:   1  2  3  4  5  6  7  8  9 

(Continued from previous part...)

How to find out whether a change to the data is made by replication or by other users/applications?

Use the APP_NAME function. This function returns the name of the application that is modifying the data. First, find out the output of APP_NAME function when replication is modifying the data. The output of this function will be the distribution agent's name, when replication is modifying the data. Once you know this name, you can write a trigger which uses APP_NAME function to detrmine if replication is making any changes, and proceed with your custom processing.


How to change the replication working directory on the distributor?

By default every publisher stores it snapshot data in the MSSQL7\Repldate folder on the distributor. If you are running out of disk space on that particular you need to change the working directory to another drive. You need to change this path for each publisher, as it is not a global setting on the distributor.

There are two ways to do this:

Method #1:
- Open Enterprise Manager
- Connect to the Distribution server
- Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution...
- Go to Publishers tab
- Click on the properties button (...) against the publisher's name
- Enter the new path for the Snapshot folder

Method #2:
Run sp_changedistpublisher from query analyzer.


How to maintain IDENTITY property of a column on both publishing and subscribing databases in transactional replication?

If you maintain IDENTITY property on the subscribing table, distribution agent fails with the following error even after creating the publisher's IDENTITY property with NOT FOR REPLICATION option:

An explicit value for the identity column in table 't1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Category: Data source
Source: <Server Name>
Number: 8101

Follow these steps to maintain IDENTITY property of a column on both publishing and subscribing databases:
1) Before subscribing, you must create the table at the subscriber (by default, replication will not transfer the IDENTITY property to the subscribers) and don't forget to create the IDENTITY property with NOT FOR REPLICATION option.
2) After creating the publication, go to publication properties window and choose the Articles tab. Click on the properties button (...) against the table that has IDENTITY property. Go to 'Snapshot' tab. Choose 'Keep the existing table unchanged'.
3) By default SQL Server 7.0 applies the transactions to the subscriber using stored procedures (these procedures will get created during the application of initial snapshot). Before you subscribe, you can choose to continue using stored procedures to apply the transactions on the subscriber OR you can choose to apply the transactions to the subscriber using SQL statements.
If you choose to use SQL statements, jump to step 6. If you choose to go by the default behavior (stored procedures) follow steps 4 and 5.
4) Go to the publication properties, choose 'Articles' tab, click on the properties button (...) against the table that has IDENTITY property, click on the 'Commands' tab and uncheck 'Create the stored procedures specified above during the initial synchronization of the subscribers'. Using sp_scriptinsproc, sp_scriptmappedupdproc, sp_scriptdelproc (See SQL Server 7.0 Books Online for more information) generate the scripts for these procedures.
Add a column list to the INSERT statement in the INSERT stored procedure script. In the UPDATE stored procedure script, remove the line in the UPDATE statement which tries to update the IDENTITY column. Apply these scripts on the subscriber.
5) Push or pull the subscription.

6) If you choose to use SQL statements to apply transactions to the subscriber, go to the publication properties window, choose articles tab, click on the properties button (...) against the table that has IDENTITY property, choose 'Commands' tab. Uncheck the check marks against the procedure calls and check the box against 'Use column names in the SQL statements'.
7) Push or pull the subscription.

(Continued on next part...)

Part:   1  2  3  4  5  6  7  8  9