Automating Replication On Your Server

If you use replication, you have had the situation occur where you had to restore a replicated database.  You’ve have doubtless been paged to restore a replicated database. You have experienced the ineffable joy of being tearing down replication-dependent indexed views (if you have them), blowing away replication, doing the restore, putting replication and indexing back together again, and finally redeploying your indexed views.  I know I have.

In fact, I’ve done it enough times that I didn’t want to do it anymore. So, you may ask, did I go to a different modality of replicating my data?  Did I go to Availability Groups or mirroring instead?  No.  I actually like replication.  It’s invaluable when you need to write code around real-time data (especially from a third party database), but you aren’t able to index the original copy.  It’s been around for a long time and is well vetted, and pretty forgiving, once you understand how it works.  So, no need to reinvent the wheel. I decided to automate replication instead.

In my case, I had a setup where the publisher was on ServerA, the distributor was on ServerB, and the subscriber was on ServerC.  I had indexes on this table that would be dropped as a result of replication.  I also had indexed views that were dependent on replication, making this about as complex of a procedure as I could think of offhand.

I began…..well, at the beginning.  I knew my first step would be to drop the indexed views.  So I created a stored procedure to do just that and put it in a clearly labeled job (i.e., ‘DropIndexedViews_WhenNeeded’).  That went on ServerC (good luck trying to drop indexed views via a linked server from ServerA).

Then I needed to drop the existing replication.  This will do it dynamically.  Just plug in the appropriate values for your system:

USE PublisherDatabase;
GO

CREATE TABLE #SubscriptionNames
(
    publication sysname
);

INSERT INTO #SubscriptionNames
(
    publication
)
SELECT publication
FROM [ServerB].DistributorDB.dbo.MSpublications
WHERE publisher_db = 'PublisherDatabase'
ORDER BY publication;

DECLARE @dropSubName sysname;
DECLARE @dropSubCursor CURSOR;
DECLARE @dropSubSQL NVARCHAR(MAX);

SET @dropSubCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT publication
FROM #SubscriptionNames
ORDER BY publication;

OPEN @dropSubCursor;

FETCH NEXT FROM @dropSubCursor
INTO @dropSubName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @dropSubSQL
        = N'EXEC sys.sp_dropsubscription @publication = ''' + @dropSubName
          + N''', @article = ''all'', @subscriber = ''ServerC''; ';
    PRINT @dropSubSQL;
    EXEC sp_executesql @dropSubSQL;
    FETCH NEXT FROM @dropSubCursor
    INTO @dropSubName;
END;

CLOSE @dropSubCursor;
DEALLOCATE @dropSubCursor;

DROP TABLE #SubscriptionNames;


--/* Drop the publications */

CREATE TABLE #PublicationNames
(
    publication sysname
);

INSERT INTO #PublicationNames
(
    publication
)
SELECT publication
FROM [ServerB].DistributorDB.dbo.MSpublications
WHERE publisher_db = 'PublisherDatabase'
ORDER BY publication;

DECLARE @dropPublicationName sysname;
DECLARE @dropPublicationCursor CURSOR;
DECLARE @dropPublicationSQL NVARCHAR(MAX);

SET @dropPublicationCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT publication
FROM #PublicationNames
ORDER BY publication;

OPEN @dropPublicationCursor;

FETCH NEXT FROM @dropPublicationCursor
INTO @dropPublicationName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @dropPublicationSQL = N'EXEC sys.sp_droppublication @publication = ''' + @dropPublicationName + N''';';
    PRINT @dropPublicationSQL;
    EXEC sp_executesql @dropPublicationSQL;
    FETCH NEXT FROM @dropPublicationCursor
    INTO @dropPublicationName;
END;

CLOSE @dropPublicationCursor;
DEALLOCATE @dropPublicationCursor;

DROP TABLE #PublicationNames;

--Know that we know it is all gone

EXEC sys.sp_removedbreplication 'PublisherDatabase';

SELECT 'PublisherDatabase replication successfully dropped' AS StatusUpdate;

Here’s the part where you would do your database restore.  When that is finished, you’re now ready to put Humpty Dumpty back together again.  I tried to do this with as little hardcoding as possible.  This version does have the publications and indexes ultimately hardcoded in, but in a future version, I’d love to write some PowerShell to automatically execute all the scripts in a specified folder to put the publications and indexing on.

Now, we enable the database for publication (if it isn’t already) and queue the logreader agent:

SELECT 'Enabling the replication database' AS StatusUpdate;

-- Enabling the replication database
USE master;
EXEC sp_replicationdboption @dbname = N'PublisherDatabase',
                            @optname = N'publish',
                            @value = N'true';
GO

SELECT 'Queue the logreader agent' AS StatusUpdate;


IF
(
    SELECT name
    FROM msdb.dbo.sysjobs AS sj
        INNER JOIN msdb.dbo.sysjobsteps AS sjs
            ON sjs.job_id = sj.job_id
               AND subsystem = 'logreader'
) NOT LIKE 'ServerA-PublisherDatabase%'
BEGIN
    EXEC [PublisherDatabase].sys.sp_addlogreader_agent @job_login = NULL,
                                          @job_password = NULL,
                                          @publisher_security_mode = 1;

END;

ELSE

SELECT 'Adding publications' AS StatusUpdate;
GO

So far, so good.  Next, I generated scripts for all my publications (go to the Replication tab > Local Publications, right click on a publication and choose “Generate Scripts”).  I modified the permissions so that only the most needed individuals could get in there.  I am using a sample script below to give you the idea:

    -- Adding the transactional publication
    USE [PublisherDatabase];
EXEC sp_addpublication @publication = N'tPublisherDatabaseArticle1',
                       @description = N'Transactional publication of database ''PublisherDatabase'' from Publisher ''ServerA''.',
                       @sync_method = N'concurrent',
                       @retention = 0,
                       @allow_push = N'true',
                       @allow_pull = N'true',
                       @allow_anonymous = N'false',
                       @enabled_for_internet = N'false',
                       @snapshot_in_defaultfolder = N'false',
                       @alt_snapshot_folder = N'Folder location here',
                       @compress_snapshot = N'false',
                       @ftp_port = 21,
                       @ftp_login = N'anonymous',
                       @allow_subscription_copy = N'false',
                       @add_to_active_directory = N'false',
                       @repl_freq = N'continuous',
                       @status = N'active',
                       @independent_agent = N'true',
                       @immediate_sync = N'false',
                       @allow_sync_tran = N'false',
                       @autogen_sync_procs = N'false',
                       @allow_queued_tran = N'false',
                       @allow_dts = N'false',
                       @replicate_ddl = 1,
                       @allow_initialize_from_backup = N'false',
                       @enabled_for_p2p = N'false',
                       @enabled_for_het_sub = N'false';
GO


EXEC sp_addpublication_snapshot @publication = N'tPublisherDatabaseArticle1',
                                @frequency_type = 1,
                                @frequency_interval = 0,
                                @frequency_relative_interval = 0,
                                @frequency_recurrence_factor = 0,
                                @frequency_subday = 0,
                                @frequency_subday_interval = 0,
                                @active_start_time_of_day = 0,
                                @active_end_time_of_day = 235959,
                                @active_start_date = 0,
                                @active_end_date = 0,
                                @job_login = NULL,
                                @job_password = NULL,
                                @publisher_security_mode = 1;
EXEC sp_grant_publication_access @publication = N'tPublisherDatabaseArticle1',
                                 @login = N'sa';
GO

EXEC sp_grant_publication_access @publication = N'tPublisherDatabaseArticle1',
                                 @login = N'WhoeverIsNeeded';
GO


-- Adding the transactional articles
USE [PublisherDatabase];
EXEC sp_addarticle @publication = N'tPublisherDatabaseArticle1',
                   @article = N'Table1',
                   @source_owner = N'dbo',
                   @source_object = N'Table1',
                   @type = N'logbased',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x000000000803509F,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = N'Table1',
                   @destination_owner = N'dbo',
                   @status = 24,
                   @vertical_partition = N'false',
                   @ins_cmd = N'CALL [sp_MSins_dboTable1]',
                   @del_cmd = N'CALL [sp_MSdel_dboTable1]',
                   @upd_cmd = N'SCALL [sp_MSupd_dboTable1]';
GO
USE [PublisherDatabase];
EXEC sp_addarticle @publication = N'tPublisherDatabaseArticle1',
                   @article = N'Table2',
                   @source_owner = N'dbo',
                   @source_object = N'Table2',
                   @type = N'logbased',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x000000000803509F,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = N'Table2',
                   @destination_owner = N'dbo',
                   @status = 24,
                   @vertical_partition = N'false',
                   @ins_cmd = N'CALL [sp_MSins_dboTable2]',
                   @del_cmd = N'CALL [sp_MSdel_dboTable2]',
                   @upd_cmd = N'SCALL [sp_MSupd_dboTable2]';
GO
USE [PublisherDatabase];
EXEC sp_addarticle @publication = N'tPublisherDatabaseArticle1',
                   @article = N'Table3',
                   @source_owner = N'dbo',
                   @source_object = N'Table3',
                   @type = N'logbased',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x000000000803509F,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = N'Table3',
                   @destination_owner = N'dbo',
                   @status = 24,
                   @vertical_partition = N'false',
                   @ins_cmd = N'CALL [sp_MSins_dboTable3]',
                   @del_cmd = N'CALL [sp_MSdel_dboTable3]',
                   @upd_cmd = N'SCALL [sp_MSupd_dboTable3]';
GO

-- Adding the transactional subscriptions
USE [PublisherDatabase];
EXEC sp_addsubscription @publication = N'tPublisherDatabaseArticle1',
                        @subscriber = N'ServerC',
                        @destination_db = N'SubscriberDatabase',
                        @subscription_type = N'Push',
                        @sync_type = N'automatic',
                        @article = N'all',
                        @update_mode = N'read only',
                        @subscriber_type = 0;
EXEC sp_addpushsubscription_agent @publication = N'tPublisherDatabaseArticle1',
                                  @subscriber = N'ServerC',
                                  @subscriber_db = N'SubscriberDatabase',
                                  @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';
GO

Now we have a publication, but the replication process hasn’t started yet.  It needs to take a snapshot and then push the data through.  Let’s make that happen dynamically for all the publications in the database:

--Start the snapshots

USE PublisherDatabase;
GO

DECLARE @publicationName sysname;
DECLARE @publicationCursor CURSOR;
DECLARE @publicationSQL NVARCHAR(MAX);

SET @publicationCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT publication
FROM [ServerB].DistributorDB.dbo.MSpublications
WHERE publisher_db = 'PublisherDatabase';

OPEN @publicationCursor;

FETCH NEXT FROM @publicationCursor
INTO @publicationName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @publicationSQL = N'EXEC sys.sp_startpublication_snapshot @publication = ' + N'''' + @publicationName + N'''';
    PRINT @publicationSQL;
    EXECUTE sp_executesql @publicationSQL;
    FETCH NEXT FROM @publicationCursor
    INTO @publicationName;
END;

CLOSE @publicationCursor;
DEALLOCATE @publicationCursor;

SELECT 'Snapshots started...' AS StatusCheck;

--Check to ensure that snapshots go through.  If one or more of them fail, find them and restart them.

WHILE
(
    SELECT COUNT(*)
    FROM [ServerB].[DistributorDB].[dbo].[MSsnapshot_history]
    WHERE (
              start_time > DATEADD(HOUR, -1, GETDATE())
              AND runstatus = 2
          )
) <
(
    SELECT COUNT(*)
    FROM [ServerB].[DistributorDB].dbo.MSpublications
    WHERE publisher_db = 'PublisherDatabase'
)
BEGIN

    WAITFOR DELAY '00:01';

    IF
    (
        SELECT COUNT(*)
        FROM [ServerB].[DistributorDB].[dbo].[MSsnapshot_history]
        WHERE (
                  start_time > DATEADD(HOUR, -1, GETDATE())
                  AND runstatus = 6
              )
    ) > 0
    BEGIN

        IF OBJECT_ID('tempdb..#FailedPublications') IS NOT NULL
            DROP TABLE #FailedPublications;

        CREATE TABLE #FailedPublications
        (
            Publication sysname
        );

        INSERT INTO #FailedPublications
        (
            Publication
        )
        SELECT p.publication
        FROM [ServerB].[DistributorDB].[dbo].MSpublications AS p
            INNER JOIN [ServerB].[DistributorDB].[dbo].[MSsnapshot_history] AS s
                ON p.publication_id = s.agent_id
        WHERE (
                  s.start_time > DATEADD(HOUR, -1, GETDATE())
                  AND s.runstatus = 6
              );

        DECLARE @FailedSnapshotName sysname;
        DECLARE @FailedSnapshotCursor CURSOR;
        DECLARE @FailedSnapshotSQL NVARCHAR(MAX);

        SET @FailedSnapshotCursor = CURSOR FAST_FORWARD LOCAL FOR
        SELECT Publication
        FROM #FailedPublications
        ORDER BY Publication;

        OPEN @FailedSnapshotCursor;

        FETCH NEXT FROM @FailedSnapshotCursor
        INTO @FailedSnapshotName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @FailedSnapshotSQL
                = N'EXEC sys.sp_startpublication_snapshot @publication = ' + N'''' + @FailedSnapshotName + N'''';
            EXECUTE sp_executesql @FailedSnapshotSQL;
            FETCH NEXT FROM @FailedSnapshotCursor
            INTO @FailedSnapshotName;
        END;

        CLOSE @FailedSnapshotCursor;
        DEALLOCATE @FailedSnapshotCursor;

        DROP TABLE #FailedPublications;

    END;

    ELSE
        SELECT 'Snapshots are still busy' AS StatusCheck;


END;
--Next, we make sure the publication is all the way through and validated before we do anything else (like, say, try to index the tables):

SELECT 'Starting validation ' AS StatusCheck;

--Validate the publications

DECLARE @PublicationInfo TABLE
(
    Publisher sysname,
    Publication sysname,
    Article sysname,
    alert_error_text NVARCHAR(MAX),
    Time DATETIME2
);


WHILE
(
    SELECT COUNT(DISTINCT Publication)
    FROM @PublicationInfo
    WHERE [Time]
          BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
          AND alert_error_text LIKE '%passed data validation%'
) <>
(
    SELECT COUNT(*)
    FROM [ServerB].[DistributorDB].dbo.MSpublications
    WHERE publisher_db = 'PublisherDatabase'
          AND description NOT LIKE 'Snapshot publication %'
)
BEGIN

    DECLARE @lcPubName sysname;
    DECLARE @dbCursor CURSOR;
    DECLARE @lcSql NVARCHAR(MAX);

    SET @dbCursor = CURSOR FAST_FORWARD FOR
    SELECT name
    FROM dbo.syspublications
    WHERE description NOT LIKE 'Snapshot publication%'
    ORDER BY name;

    OPEN @dbCursor;

    FETCH NEXT FROM @dbCursor
    INTO @lcPubName;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        EXEC sys.sp_publication_validation @publication = @lcPubName,
                                           @rowcount_only = 1, -- 1 = don't do checksums
                                           @full_or_fast = 2;  -- 2 = fast unless fail                                                                                                                                                                                    then full
        FETCH NEXT FROM @dbCursor
        INTO @lcPubName;
    END;

    DELETE FROM @PublicationInfo;
    INSERT INTO @PublicationInfo
    (
        Publisher,
        Publication,
        Article,
        alert_error_text,
        Time
    )
    SELECT publisher,
           publication,
           article,
           alert_error_text,
           [time]
    FROM [ServerB].msdb.dbo.sysreplicationalerts
    WHERE [time]
          BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
          AND alert_error_text LIKE '%passed data validation%';


    CLOSE @dbCursor;
    DEALLOCATE @dbCursor;


    IF
    (
        SELECT COUNT(DISTINCT Publication) FROM @PublicationInfo
    ) <>
    (
        SELECT COUNT(*)
        FROM [ServerB].[DistributorDB].dbo.MSpublications
        WHERE publisher_db = 'PublisherDatabase'
              AND publication NOT LIKE 'sPub%'
    )
    BEGIN

        WAITFOR DELAY '00:01';

    END;

END;


SELECT 'Everything done successfully' AS StatusCheck;

Now that this has run successfully, I can apply my indexes.  I do this in a stored procedure that I call from the publisher (EXEC [ServerC].[SubscriberDatabase].[dbo].[PutTheIndexesBackOn]).  Finally, I used a stored procedure to put on the indexed views (EXEC [ServerC].[SubscriberDatabase].[dbo].[PutIndexedViewsBackOn].  This was tricky.  I had to put the code in dynamic SQL inside the stored procedure, which looks something like this:

DECLARE @IVSQL NVARCHAR(MAX) = 
N'CREATE VIEW [dbo].[IndexedView1]
WITH SCHEMABINDING
AS


    SELECT	d.1,
            d.2,
            3,
            COUNT_BIG(*) AS NumRec,
            SUM(ISNULL(4, 0)) AS 4,
            SUM(ISNULL(5, 0)) AS 5,
            SUM(ISNULL(6, 0)) AS 6,
            SUM(ISNULL(7, 0)) AS 7,
            8,
            9
    FROM    dbo.Article1 d
            INNER JOIN dbo.Table2 p ON d.1 = p.1
    WHERE   p.10 IN (''Col1'', ''Col2'')
			AND d.11 = 0    
    GROUP BY d.1,
            d.2,
            d.3,
            d.4,
            d.5'
PRINT @IVSQL;
EXECUTE sp_executesql @IVSQL;

All of this is put into three SQL Server Agent Jobs: One to drop the indexed views (stored on ServerC), one to drop the publications (stored on ServerA) and one to add the publications and redo the indexing and indexed views (also stored on ServerA).  When executed, it shaved about 30 minutes off the execution time, because (for instance) SQL Server wasn’t waiting on me to notice that a publication was probably ready to be validated.  Other things to consider:  putting the database in RESTRICTED_USER (or even SINGLE_USER) to expedite the process.  Make that decision as appropriate to your environment.  I hope it works as well for you.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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