Technical Blog

Scripting Pull Subscriptions (SQL Server Replication)

Scripting Pull Subscriptions (SQL Server Replication)

Phil Grayson

Published

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 Local Subscriptions and Generate Publisher Scripts

USE [Database];

--Execute this batch at the Subscriber.
DECLARE @iPublisher AS sysname;
DECLARE @iPublication AS sysname;
DECLARE @iPublicationDB AS sysname;
DECLARE @iSubscriber AS sysname;
DECLARE @iSubscriptionDB AS sysname;

SET @iPublisher = N'PublisherName';
SET @iPublication = N'PublicationName';
SET @iPublicationDB = N'PublisherDatabase';
SET @iSubscriber = @SERVERNAME;
SELECT @iSubscriptionDB = DB_NAME();

--At the subscriber database, create a pull subscription to a transactional or snapshot publication.
EXEC sys.sp_addpullsubscription @publisher = @iPublisher,
                                @publication = @iPublication,
                                @publisher_db = @iPublicationDB;

--Execute the outputed query at the Publisher to register the subscriber
SELECT 'EXEC sys.sp_addsubscription @publication = ''' + @iPublication + ''', @subscriber = ''' + @iSubscriber
       + ''', @destination_db = ''' + @iSubscriptionDB
       + ''', @subscription_type = N''pull'', @status = N''subscribed'';' AS 'Run on publisher';
GO

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

Create and Start the Replication Agent Jobs

USE [msdb];
GO
DECLARE @iPublisher AS sysname;
DECLARE @iDistributor AS sysname;
DECLARE @iSubscriber AS sysname;
DECLARE @iPublication AS sysname;
DECLARE @iPublicationDB AS sysname;
DECLARE @iSubscriptionDB AS sysname;
DECLARE @job_name NVARCHAR(400);
DECLARE @cmd NVARCHAR(4000);
DECLARE @alt_snap_path NVARCHAR(512);
DECLARE @jobId BINARY(16);
DECLARE @programPath NVARCHAR(512);

SET @iPublisher = N'PublisherName';
SET @iPublication = N'PublicationName';
SET @iPublicationDB = N'PublisherDatabase';
SET @iSubscriptionDB = 'SubscriptionDatabase';
SET @iSubscriber = @@SERVERNAME;
SET @iDistributor = 'DistributorName';
SET @programPath = N'C:\Program Files\Microsoft SQL Server\120\COM\DISTRIB.EXE'; --This will differ depending on version and installation preference.
SET @alt_snap_path = N''; --'"\\unc\snapshot\repldata"'


SET @cmd
    = N'"' + @programPath + N'" -Subscriber [' + @iSubscriber + N'] -SubscriberDB [' + @iSubscriptionDB
      + N'] -SubscriberSecurityMode 1 -Publication [' + @iPublication + N'] -Publisher [' + @iPublisher
      + N'] -PublisherDB [' + @iPublicationDB + N'] -Distributor [' + @iDistributor
      + N'] -DistributorSecurityMode 1 -Continuous -SubscriptionType 1 '
      + CASE
            WHEN ISNULL(@alt_snap_path, '') <> '' THEN
                '-AltSnapshotFolder ' + @alt_snap_path
            ELSE
                ''
        END + N'';
SET @job_name = N'Replication: ' + @iPublisher + N'-' + @iPublicationDB + N'-' + @iPublication;


IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @job_name)
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_name = @job_name;
END;

BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;

IF NOT EXISTS
(
    SELECT name
    FROM msdb.dbo.syscategories
    WHERE name = N'REPL-Distribution'
          AND category_class = 1
)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',
                                                @type = N'LOCAL',
                                                @name = N'REPL-Distribution';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
        GOTO QuitWithRollback;

END;


EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @job_name,
                                       @enabled = 0,
                                       @notify_level_eventlog = 0,
                                       @notify_level_email = 0,
                                       @notify_level_netsend = 0,
                                       @notify_level_page = 0,
                                       @delete_level = 0,
                                       @description = N'Manually created job for syncing replication. Has to be manual because SQL Server doesn''t create the jobs for SQL Web servers.',
                                       @category_name = N'REPL-Distribution',
                                       @owner_login_name = N'sa',
                                       @job_id = @jobId OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
                                           @step_name = N'Run Agent',
                                           @step_id = 1,
                                           @cmdexec_success_code = 0,
                                           @on_success_action = 1,
                                           @on_success_step_id = 0,
                                           @on_fail_action = 2,
                                           @on_fail_step_id = 0,
                                           @retry_attempts = 2147483647,
                                           @retry_interval = 1,
                                           @os_run_priority = 0,
                                           @subsystem = N'CmdExec',
                                           @command = @cmd,
                                           @flags = 0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId,
                                          @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,
                                               @name = N'Replication agent schedule.',
                                               @enabled = 1,
                                               @freq_type = 64,
                                               @freq_interval = 0,
                                               @freq_subday_type = 0,
                                               @freq_subday_interval = 0,
                                               @freq_relative_interval = 0,
                                               @freq_recurrence_factor = 0,
                                               @active_start_date = 20150114,
                                               @active_end_date = 99991231,
                                               @active_start_time = 30000,
                                               @active_end_time = 235959,
                                               @schedule_uid = N'9f053472-701e-4de3-ba90-f620e2970def';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
                                             @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF (@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:


--Enable and start the jobs
EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 1;

EXEC msdb.dbo.sp_start_job @job_name = @job_name;
GO

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.

Get Started With

Start Now
icon_bluestone98