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:
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
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.
