Maintain SQL Server Replication using Source Control
My company relies on replication intensively and we have many different SQL Server versions and configurations to support. For example, some publications are replicating data every minute, some continuous and others on demand. In addition, we have several replication articles with different schema options due to the requirements on the subscribers. Therefore, we wanted to be able to manage the configuration in a Source Control system. Also, we want to have one single script for each publication so that when we need to setup or refresh a Dev\Test environment, we can simply get the latest scripts from Source Control. Then we can run the script to build the replication architecture from scratch. In our environment, we are using Pull Replication, so we need to connect to the publisher as well as the subscribers to run the script. Do you have any suggestions to accomplish these items? Check out this tip to learn more.
We are using one script per publisher and utilizing SQLCMD Mode to connect to both the publisher and subscriber as part of creating the replication publication. If you are not familiar with SQLCMD mode, please see the Using the SSMS Query Editor in SQLCMD Mode tip before jumping into this tip. Also, before I go any further, I would like to give credit to Ed Hellyer, my co-worker, who originally developed this technique.
Basic template as big picture
Here is a basic replication architecture with the associated SQL Server instance and database names:
The script below is a high level view of the Pull Replication process. You can also download the complete Replication Sample Script here.
Note: You need to pre-configure the replication distributor as a publisher, distributor and subscriber. I assume the distributor server is fully configured and running correctly because this script is only for setting up new a publication.
:CONNECT MYSERVER101\Publisher :ON ERROR EXIT if @@servername != 'MYSERVER101\Publisher' raiserror('Not connected to the right server.',16 ,1) GO use [ReplPlayground] exec sp_replicationdboption
GO -- Adding the transactional publication use [ReplPlayground] exec sp_addpublication GO exec sp_addpublication_snapshot GO use [ReplPlayground] exec sp_addarticle -- Adding the transactional subscriptions use [ReplPlayground] exec sp_addsubscription GO -----BEGIN: Script to be run at Subscriber 'MYSERVER103\Subscriber' :connect MYSERVER102\Subscriber if @@servername != 'MYSERVER103\Subscriber' raiserror('Not connected to the right server.',16 ,1) GO use [ReplPlaygroundSub] exec sp_addpullsubscription exec sp_addpullsubscription_agent -----END: Script to be run at Subscriber 'MYSERVER103\Subscriber'
Major points from the script above
As you can see from the screenshot below, I used ":CONNECT
At the end of the script, I am connecting to the subscriber server to add the pull subscription and the pull subscription agent as I am trying to setup Pull Replication. So, I have used the SQLCMD mode again to connect to the subscriber in the same script.
Depending on your configuration, you may have to run the "Snapshot Agent" and/or "Distribution Agent" to apply the snapshot, etc., after you run the script.
Script to delete publication (optional)
As an optional step, I have included a script to delete the publication and disable replication completely. This is a very similar concept as the code above and you can also download the code for this script here.
-- Drop Subscriber -- connect Subscriber Server and drop pull subscription :connect MYSERVER103\Subscriber use [ReplPlaygroundSub] exec sp_droppullsubscription
go -- Connect Publisher Server :connect MYSERVER101\Publisher -- Drop Subscription use [ReplPlayground] exec sp_dropsubscription go -- Drop publication use [ReplPlayground] exec sp_droppublication -- Drop use [ReplPlayground] exec sp_replicationdboption
- Now you have one script per publication so you can go ahead and build this useful way to refresh the databases in your Development or Test environment from a production backup for a replicated database.
- You will need to drop all of the replication configurations in the Development\Test environment and restore the database from Production without the replication configurations and just run the scripts from your source control system. These scripts will reconfigure replication.
- Since this script is server specific, you will need a script for each server and database. As a process improvement, it would be better to have a table driven configuration so you have one script for each database and a table with metadata that will contain all of the environment specific configurations so that you can get by with a one script solution.
- You can integrate this script with other deployment methods such as a Database Project to have a single process to deploy a database even if replication involved.
- Check out these other SQL Server Replication tips.
Last Updated: 2011-06-16
About the author
View all my tips