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