Maintain SQL Server Replication using Source Control

Problem

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.

Solution

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:

using sqlcmd mode to connect to both the publisher and subscriber as part of creating the replication publication

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 " because this is a very nice way to make sure you are on the right server. If you are not on the correct server by accident, the script will not connect and fail to move on to the next step.

use connect to make sure you are on the right server

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.

setting up pull replication using sqlcmd mode

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

Next Steps

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.

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2011-06-16