SQL Server 2005 Transactional Replication: Tips & Tricks
One of the more intimating things in the database architecture for DBAs that I meet at events is replication. Especially, if the replication that you need to set up needs to have a little twist in it that is not handled necessarily by the GUI Wizards in SQL Server Management Studio. What most DBAs need to realize is that if they concentrate on learning the underpinnings of ‘how’ replication works within the SQL Server environment then manipulating that environment becomes fairly easy.
Using that line of thought, I thought it would be nice if I compiled some tips and tricks dealing with transactional replication. Hopefully, these hints will shed some light on the underpinning of the system and let you see just how easy replication can be.
Stored Procedures
A lot of what transactional replication does in most cases is done through the use of stored procedures. Now normally the GUI would take care of going out and populating the stored procedures for you but in some instances you may need to do this manually or you may just want to have a quick script of the objects in order to modify some things.
Luckily, the database has a very simple stored procedure in order to script out the stored procedure that will be used for the articles in a publication.
exec sp_scriptpublicationcustomprocs [Publication Name]
This stored procedure will create all of the stored procedures needed for your publication and are normally applied by the snapshot agent but now you have them.
Identity Fields
Identity fields are often one of the sticklers in setting up replication because in a lot of instances you are working with a database for which you cannot make changes, 3rd party or just strict policies. So in some instances you may need to keep the existing architecture with the identity columns.
The trick here is once you realize that all of the replication is handled through stored procedures on the subscriber then you can take the hint on stored procedures above and modify them. So in this instance you would simply be looking for the stored procedures dealing with the tables that have the identity columns..specifically the insert and update ones. One the insert stored procedure you have to simply add an IDENTITY INSERT statement. On the update statements you just simply modify the update statement to not update the identity column. Piece of cake!
Working with Very Large Databases(VLDBs)
Replication often works through the use of the snapshot agent applying an initial snapshot of the database onto the subscriber for an initial synch. However, if you are working with very large databases you may not have the ability to apply a snapshot . In this instance, you may need to do a backup and restore but how do you get the subscription to be ‘pre-initialized’ and skip the creation of that initial snapshot.
Very simply, you just manually create the subscription yourself without the GUI. There is one areas that control how the initialization is handled. Take a look at the code below:
exec sp_addsubscription @publication = N’AJPub’, @subscriber = N’NXDEVDSSQL03′, @destination_db = N’AJDB_Dest’, @subscription_type = N’Push’, @sync_type = N’none’, @article = N’all’, @update_mode = N’read only’, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @offloadagent = 0, @dts_package_location = N’Distributor’
GO
The @sync_type parameter here is what we use to tell the replication process that we don’t need to do an initial synch. No initial synch…no big snapshot file created.
Not Propagating DELETEs
Yes, I know ..not exactly best practices stuff here…but in some instances you may have tables that you are purging on one end of the replication setup and not on the other. So this goes back to realizing that the actual process of pushing the updates to the subscriber is handled by stored procedures. The replication distributor is just in the business of running a bunch of stored procedure calls and if the stored procedure executes successfully then that is good enough for the distributor. There is really no mystical process by which it double checks to make sure that the change was actually made like it was supposed to.
So in this instance all you need to do is to locate the delete stored procedure for the particular object you are wanting to not delete from on the subscriber. Once you find that just comment out the guts of the stored procedure(in case you need to reverse the decision some day) and replace it simply with ‘SELECT 1’ …the statement will always execute successfully….the distributor is happy ….and everybody else is happy.
When Replication Goes Wrong
Normally, replication goes on day after day with no hitches. Yet every once in a while you get that dreaded failure. Now SQL Server has a pretty tight way of handling your transactions in that it attempts to batch execute them over on the subscriber…if that fails then what it does is falls back and executes each entry separately for that particular batch. This is a pretty cool concept when you think about it because it is just verifying for you not only that it wasn’t just something screwy with the batch but also it ‘finds’ the offending transaction. Every transaction is sequenced so the next transaction in the bin when the failure occurs during round #2 is the bad one.
All transaction information is generally stored in these two tables…
Msrepl_commands
Msrepl_transactions
Now you can write your own routines to find the next one in the sequence and all but SQL Server provides some mechanisms for you to use.
sp_helpsubscriptionerrors
This stored procedure lets you actually view the errors involved in the transactional replication set and more importantly gives you the LSN of the offending line.
sp_replcmds @maxtrans=1
This stored procedure will show you which transaction is currently up in the queue and a little information about it such as the command type.
sp_replshowcmds @maxtrans=1
This stored procedure will actually return the command text of the next stored procedure in the queue so that you can see what is trying to execute on the distributor. Then you can pair this up with the information from the sp_helpsubscriptionerrors statement …fix the offending statement,execute it on the subscriber, and then use the next hint to actually skip the offending line.
Skipping a Transaction
sp_setsubscriptionxactseqno
This stored procedure is used to manually set the LSN that the replication is currently on. This should be the last successful transaction. So if you were to want to do something like skipping a bad replication command you would pair this with the sp_helpsubscriptionerrors command to get the offending LSN..plug it in to this command and then SQL Server will officially skip the offending statement.
Replicating Across Domains
Replicating across a domain is often viewed as a pretty complicated process when in actuality it is pretty simple. Just set up a point to point IP tunnel between the distributor and the subscriber. Once you verify that you can connect to the subscriber SQL Server instance the rest is fairly easy.
The trick to remember here is the replication doesn’t necessarily like IP addresses for the destination server. So you have two options….
- Bug your system administrator to create an entry in DNS for the IP
- Create an alias in SQL Server configuration manager to point to the IP
I always like to use #2 because it eliminates the need to go back to the SAs in order to change things. Additionally, you can use this step regardless of whether you are replicating across domains or not. If you use an alias on the SQL Server then it is your hedge if you ever have to move the subscriber database to another server. If your replication is set up with the alias name then you just change the alias in config manager and replication is happy still.
Using SQL Server Authentication
Out of the box the replication agent in a push scenario is going to use windows authentication in order to make the connection to the subscriber database. In some situations those this is not optimal as you may want to use SQL Server authentication(see replicating across domains above). This change is quite simple, once you realize that the replication agent for pushing the transactions is actually just a SQL Server job. Locate the job on the distributor, it will be named with a combination of the pub and subscriber dbs. Now open it up and look at the syntax…
-Subscriber [DestinationServer] -SubscriberDB [AJDB_Dest] -Publisher [AJDB-SOURCE] -Distributor [AJDB-DIST] -DistributorSecurityMode 1 -Publication [AJProd] -PublisherDB [AJDB_Pub] -Continuous
The syntax above is the usual default that is created and is windows authentication just by looking and seeing no SubscriberSecurityMode. By default it is 1 which mean windows. So it is going to use whatever windows account the replication agent is running under to execute this. In order to change it we just simply change the mode to 0 (SQL Server authentication) and provide a username and password.
-Subscriber [DestinationServer] -SubscriberDB [AJDB_Dest] -Publisher [AJDB-SOURCE] -Distributor [AJDB-DIST] -DistributorSecurityMode 1 -Publication [AJProd] -PublisherDB [AJDB_Pub] -SubscriberSecurityMode 0 -SubscriberLogin [AJDBUser] -SubscriberPassword [VerySecretPassword]
Now save the job updates and you are ready to roll.
I hope that you find these tips helpful in both possibly implementing your own brand of replication and understanding just how the process works. If you have your own unique requirements concerning replication then shoot me an email and I will see if I have run across it before. It may even end up being appended to this list.
Cheers,
AJ