Here's a step-by-step (almost) instructions to set up Transactional Replication in SQL Server 2000 from a restored copy.
- Restore the publication database onto subscriber.
- Restore the last transaction log backup onto subscriber.
- Drop all foreign key constraints on the subscriber database.
Sample script 1:
SELECT DISTINCT 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
FROM SYSFOREIGNKEYS
GO
- If you have timestamp columns you need to change them to binary.
Sample script 2:
UPDATE syscolumns SET xtype=173, xusertype=173 WHERE xtype=189
--If updates not allowed use foll. script
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
- Set up Publication on the publisher database using the Replication -> Configure Publishing, Subscription and Distribution wizard.
- Get the code for sp_scriptpublicationcustomprocs from MASTER database. Make sure the width of the results window is set to 2000 in the Tools -> Options window for both Publisher and Subscriber. If you have procs with a large number of parameters, they could get truncated when being displayed in the Results window/file.
- Compile and create the stored proc code on the publication database.
- Execute the stored proc on the publication. The stored proc generates all the required Add/Update/Delete stored procedures for all the articles.
Sample Script 3:
exec sp_scriptpublicationcustomprocs @publication = N'Pub Name here'
--Note: If publisher DB is not enabled for 'Publish' use the following code to enable it first.
USE master
GO
EXEC sp_replicationdboption @dbname = N'CopyProd', @optname = N'publish', @value = N'true'
GO
- Compile the generated script on the subscriber database.
- Set up the Subscriber. Right click on the Publisher -> Properties -> "Push New". Dont forget to choose the "No, the Subscriber already has the schema and data" option on the Initialize Subscription screen.
Issues with updates on tables with IDENTITY Columns
If you are updating a table that has an identity column and if replication distribution agent fails with error: “Cannot update identity column ”, the following steps can help resolve the issue.
The advantage with this method compared to dropping and adding the article from subscription is that there is no need to generate the snapshot which means no need to transfer the millions of rows for large tables over the network to the subscriber.
- Get the script from MASTER db for the stored proc sp_scriptdynamicupdproc
- Compile the stored proc on the publisher DB
- Get the article-Id from sysarticles for the table in question.
- Execute the stored proc sp_scriptdynamicupdproc @artid = <artid>
- The stored proc will generate a script for “update” procedure.
- Compile the generated script on the subscriber database.
If you have many tables with IDENTITY columns, you can write up some dynamic SQL to generate the update stored procs as mentioned above to a file and compile all the stored procs at once on the subscriber.
Pretty easy..ha...
posted @ Monday, April 02, 2007 12:03 PM