SQL Server Replication: Automatic Synchronization

Article
12/30/2010

The simplest transactional replication synchronization scenarios involve a single publication and one or more subscribers. In most cases the complexity changes very little whether or not a remote distributor is involved, so it's omitted in most diagrams.

Automatic Synchronization

This is the default scenario and the one you get if you create it using the UI.

Advantages

Can be done entirely using the GUI
Easiest to configure
Only copies data involved with the publication
Least amount of administrative effort

Disadvantages

Extremely slow, especially over long distances
Causes blocking on the publisher
Requires snapshot space on the distributor
Loss of permissions on subscriber tables

Best used when

Publication is small
Synchronization time is unimportant
Publisher, Distributor, and Subscriber in the same datacenter
No long-running transactions on published tables during snapshot generation

Notes

Blocking: Contrary to popular belief SQL server does take a Sch-M (Schema Modification) lock on published tables even with concurrent snapshot processing. What this means is that if you have some long-running selects on the table it's going to wait for those to finish before it gets its lock, and every statement behind it will be blocked as well.
Distributor Space: The main issues with large data sets involve generating the snapshot and storing it on the distributor. One thing that often happens is with multiple failed attempts the server might have several snapshots and fill the drive. If the distributor has sufficient space it might work reasonably well if the machines are geographically close together.
Latency: Long geographic distances have the most profound effect where subscriptions of only a few GB in size can take days to synchronize. Pull subscriptions generally perform better in this environment but it will still take a while to apply the snapshot and comes at the expense of having to manage replication jobs on multiple servers.
Permission Issues: Another concern with the snapshot process is permission loss. The default configuration for a publication is to drop and recreate the destination table which also removes any permissions that were on it. The most common solutions to this are to configure the published objects to truncate rather than drop and create, or configure a post snapshot script that applies any table-level permissions.

Initialization Process

The best part is that all work is done on the publisher and it's as simple as running sp_addsubscription. I still recommend a script even when the GUI is available especially when adding the second subscriber because there's less chance of misconfiguring, or mismatching the current subscription. Unless I'm trying to do something special with a new subscriber I create them all from a template.

The first two steps in this template are for manually deleting the existing subscription. If you do this step through the GUI, you can skip the hassle of switching connections to delete the subscriber record.

/*===============================================================================================*/
/*Add Transactional snapshot subscription */
/*Synchronize using snapshot */
/*SQL 2005 or SQL 2008 */
/* */
/*Use Ctrl-Shift-<M> to replace template parameters */

/*===============================================================================================*/
--1. Run on Publisher: <Publisher, varchar, PubServer>
--In the publisher DB: <PublisherDB, varchar, PubDB>
--
--Steps 1 and 2 manually delete the subscription from the publisher and subscriber.
--If you drop the subscription through the GUI, and select the option to connect to
--the subscriber and delete the record, you can skip steps 1 and 2.
--This way you don't have to bother with opening the new query window for step 2

USE <PublisherDB, varchar, PubDB>
GO

--Drop the existing subscription if present
--
IF EXISTS (
SELECT 1
FROM syspublications sp
JOIN sysarticles sa ON (sp.pubid = sa.pubid)
JOIN syssubscriptions ss ON (sa.artid = ss.artid)
WHERE sp.Name = N'<Publication, varchar, PubName>'
AND ss.srvname = N'<NewSubscriber, varchar, NewSubServer>'
AND ss.dest_db = N'<NewSubDB, varchar, PubDB>'
)
EXEC sp_dropsubscription
        @publication = N'<Publication, varchar, PubName>',
        @subscriber = N'<NewSubscriber, varchar, NewSubServer>',
        @destination_db = N'<NewSubDB, varchar, PubDB>',
        @article='all'

/*===============================================================================================*/
--2. Run on Subscriber: <NewSubscriber, varchar, NewSubServer>
--In the Subscription DB: <NewSubDB, varchar, PubDB>
--
--You can skip this step if you deleted the subscription record from the GUI
USE <NewSubDB, varchar, PubDB>
GO

EXEC sp_subscription_cleanup
        @publication = N'<Publication, varchar, PubName>',
        @publisher_db = N'<PublisherDB, varchar, PubDB>',
        @publisher = N'<Publisher, varchar, PubServer>'

/*===============================================================================================*/
--1. Run on Publisher: <Publisher, varchar, PubServer>
--In the publisher DB: <PublisherDB, varchar, PubDB>
USE <PublisherDB, varchar, PubDB>
GO

exec sp_addsubscription
@publication = N'<Publication, varchar, PubName>',
@subscriber = N'<NewSubscriber, varchar, NewSubServer>',
@destination_db = N'<NewSubDB, varchar, PubDB>',
@subscription_type = N'Push',
@sync_type = N'automatic',
@update_mode = N'read only',
@subscriber_type = 0

exec sp_addpushsubscription_agent
@publication = N'<Publication, varchar, PubName>',
@subscriber = N'<NewSubscriber, varchar, NewSubServer>',
@subscriber_db = N'<NewSubDB, varchar, PubDB>',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@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,
@dts_package_location = N'Distributor'

--Start the snapshot agent to start synchronizing the subscription
exec sp_startpublication_snapshot @publication = N'<Publication, varchar, PubName>'
/*===============================================================================================*/

Summary

Hopefully this helps automate some of the subscription setup process and avoid some of the more common pitfalls with using the Automatic synchronization method