Scripting Pull Subscriptions (SQL Server Replication)

Simple scripts for creating subscriptions and their pull jobs on single or multiple subscribers

Phil Grayson
Phil Grayson

Published Apr 2017

Share:

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


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


    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 = 'C:\Program Files\Microsoft SQL Server\120\COM\DISTRIB.EXE' —This will differ depending on version and installation preference.
    SET @alt_snap_path = ''; —\\unc\snapshot\


    SET @cmd
    = N'"' + @programPath + '" -Subscriber [' + @iSubscriber + '] -SubscriberDB ['
    + @iSubscriptionDB + '] -SubscriberSecurityMode 1 -Publication [' + @iPublication + '] -Publisher ['
    + @iPublisher + '] -PublisherDB [' + @iPublicationDB + '] -Distributor [' + @iDistributor
    + '] -DistributorSecurityMode 1 -Continuous -SubscriptionType 1 ' + @alt_snap_path + '';
    SET @job_name = N'Replication: ' + @iPublisher + '-' + @iPublicationDB + '-' + @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.

Phil Grayson
Phil Grayson

Published Apr 2017

Share: