Scripting pull subscriptions (SQL Server replication)

I’ve created some simple scripts for creating subscriptions and their pull jobs on single or multiple subscribers. It makes life much easier to use scripts, especially if you’re bringing in multiple servers and / or the subscribers are running SQL Server Web edition as they don’t get created automatically via the GUI; which can be confusing.

There are various tools to run scripts across multiple servers, like SQL Server Central Management Servers or if you want something more advanced and easier to use, try the Script module in Aireforge Studio.

You can also use Aireforge Studio to compare the jobs afterwards, making sure they were created properly and are running. It’s also useful for flagging up other configuration inconsistencies such as missing database objects, users or database and instance configuration settings.

Create local subscriptions and generate publisher scripts

Create the local subscriptions and generate the script for adding the subscriber to the publisher (this will need to be copied out and run against the publisher database).

Create and start the replication agent jobs

Remember to locate and set the location of DISTRIB.EXE. I’ve also included an option to set an alternative snapshot location. Very useful for remote servers, especially if you distribute the snapshot files using DFS-R (search here until I blog about it).

Now check that the agent is working properly and either waiting for the initial snapshot to be created or propagating out any active snapshots.

Please let me know if you spot any issues or would like to improve the scripts.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.