SQLServerWiki

“The Only Thing That Is Constant Is Change”

Automating Transactional Replication? Really?

Posted by database-wiki on October 23, 2011

Problem Description:
===============

I am using Transactional replication, initialized with backup, I have written some scripts, called from a batch file to,

1. Initially create the publication

2. Query the backup tables and get the latest full and subsequent Transaction logs, with this information it then generates a restore script.

3. Run the restore script generated in step 2 on the target server to provide us with a database to replicate to.

4. Generates a subscription to the publication created in step 1, using Extended properties where I have held the criteria as to whether the table is replicated, the script adds the articles based on this info.

The scripts also disable and re enable  the Distribution clean up job and Log Agent job as recommended in BOL.

I was running this initially from a pre-existing publication and it more of less was working fine, I added in the step to create the publication automatically and it has not worked since, the error I am getting is,

Msg 21397, Level 16, State 1, Server ATT1-SQL20\DAXOLTP, Procedure sp_MSaddauton
osyncsubscription, Line 279
The transactions required for synchronizing the no sync subscription created from
the specified backup are unavailable at the Distributor. Retry the operation ag
ain with a more up-to-date log, differential, or full database backup.

I have tried doing a log backup before creating the publication and many other combinations but I keep getting this error.

This is important as it will be the replication solution for our Dynamics AX installation and it needs to be automated, also this is why we have to use a backup rather than a snapshot, we cannot have the locks on the production system as it will be pretty much in 24/7 global use.

Would like some pointers as to where I may be going wrong with the ultimate aim of being able to run one batch file to recreate the entire replication process.

Research:
=======

We have collected all the scripts to reproduce the issue in house.

=> Our LAB setup just in case.

Publisher Details:

Server name: BALA24029220
SQL Server 2008 R2 x64 default instance.
Operating System: Windows Server 2008 – Enterprise Edition x64 – SP2

Subscriber Details:

Server name: BALA24029221
QL Server 2008 R2 x64 default instance.
Operating System: Windows Server 2008 – Enterprise Edition x64 – SP2

————————————————————————————————————–

Stored procedure: usp_Create_MDAX_Publication

————————————————————————————————————–
USE [MDAX]
GO
/****** Object:  StoredProcedure [dbo].[usp_Create_MDAX_Publication]    Script Date: 10/19/2011 17:24:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Create_MDAX_Publication]
AS
DECLARE @article_name nvarchar(100)
DECLARE article_cursor CURSOR
FOR SELECT  OBJECT_NAME(major_id) FROM   sys.extended_properties where name in (‘REPLICATED’,’REPLICATION_DETAILS’) and class = 1 and value = ‘YES’

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N’MDAX’;
SET @publication = N’MDAX’;
— Windows account used to run the Log Reader and Snapshot Agents.
SET @login = ‘DOM240292\administrator’;
— This should be passed at runtime.
SET @password = ‘Balajimalab!’;

— Enable transactional or snapshot replication on the publication database.
EXEC sp_replicationdboption
                @dbname=@publicationDB,
                @optname=N’publish’,
                @value = N’true’;

— Execute sp_addlogreader_agent to create the agent job.
EXEC sp_addlogreader_agent
                @job_login = @login,
                @job_password = @password,
                — Explicitly specify the use of Windows Integrated Authentication (default)
                — when connecting to the Publisher.
                @publisher_security_mode = 1;

— Create a new transactional publication with the required properties.
EXEC sp_addpublication
                @publication = @publication,
                @status = N’active’,
                @allow_push = N’true’,
                @allow_pull = N’true’,
                @independent_agent = N’true’;

— Create a new snapshot job for the publication, using a default schedule.

EXEC sp_changepublication    
     @Publication= @publication,
     @property = N’allow_initialize_from_backup’,  
     @value = True

OPEN article_cursor
FETCH NEXT FROM article_cursor into @article_name
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_addarticle
                @publication = @publication, — Publication to add the article to
                @article = @article_name, — Name of the article
                @source_object = @article_name,  –@table,
                @source_owner = ‘dbo’,
                @schema_option = 0x80030F3,
                @type = N’logbased’

    FETCH NEXT FROM article_cursor into @article_name

END
CLOSE article_cursor;
DEALLOCATE article_cursor;

————————————————————————————————————–

Stored procedure: usp_GenerateRestoreScripts

————————————————————————————————————–
USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[usp_GenerateRestoreScripts]    Script Date: 10/19/2011 17:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER
PROCEDURE
[dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)
AS

SET
NOCOUNT
ON– required because we’re going to print T-SQL for the restores in the messages ‘tab’ of SSMS

/* 
Script creates the T-SQL to restore a database with info from MSDB
It helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup
and all the required TRANSACTION LOG backups after this.
Neat when you have a high frequency of differential or log backups

The variable @DBName should be set to the name of the database you want to restore.

!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!
I usually include MSDB in the log backup schedule (set the db to full recovery mode)

*/
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
DECLARE @i INT, @logBackupPath VARCHAR(1000)
DECLARE @my_job_id UNIQUEIDENTIFIER
DECLARE @Logagent_job_id UNIQUEIDENTIFIER

SET @my_job_id = (SELECT job_id FROM msdb.dbo.sysjobs where name = ‘Agent history clean up: distribution’ )
EXEC dbo.sp_update_job @job_id = @my_job_id , @enabled = 0
— remove temp object that might exist

SET @Logagent_job_id = (SELECT job_id FROM msdb.dbo.sysjobs where name like ‘BALA24029220-MDAX%’ )
–EXEC dbo.sp_update_job @job_id = Logagent_job_id , @enabled = 1
EXEC dbo.sp_update_job @job_id = @Logagent_job_id , @enabled = 0

IF
OBJECT_ID(‘tempdb..#MSDBBackupHistory’)
IS
NOT
NULL
    DROP
TABLE #MSDBBackupHistory

CREATE
TABLE #MSDBBackupHistory (
    id INT
IDENTITY(1,1),
    backup_start_date DATETIME,
    backup_type CHAR(1),
    physical_device_name VARCHAR(1000))

INSERT
INTO #MSDBBackupHistory (backup_start_date,  backup_type, physical_device_name)
    SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name)
    FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id
    WHERE BS.database_name
= @DBName
    ORDER
BY BS.backup_start_date
— dump the last backup first in table

–Insert Drop Command for target database
PRINT ‘IF (SELECT CONVERT(sysname, SERVERPROPERTY(”servername”))) = ”BALA24029221”’
PRINT ‘BEGIN’
PRINT ‘PRINT ”–OK WE ARE ON THE RIGHT SERVER”’
PRINT ‘USE MASTER’
PRINT ‘ALTER DATABASE MDAX SET SINGLE_USER WITH ROLLBACK IMMEDIATE’
PRINT ”
PRINT ‘DROP DATABASE ‘ + @DBName
PRINT ”

— get the last Full backup info.
SET @lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type=’D’)
SET @lastFullBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)

— Restore the Full backup
PRINT
‘RESTORE DATABASE ‘
+ @DBName
PRINT
‘FROM DISK=”’
+ @lastFullBackupPath +
””

— IF it’s there’s no backup (differential or log) after it, we set to ‘with recovery’
IF
(@lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
    PRINT
‘WITH RECOVERY’
ELSE
PRINT
‘WITH NORECOVERY’

–PRINT
–‘GO’
PRINT

— get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type=’I’
AND id > @lastFullBackup)
SET @lastDifferentialBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)

— when there’s a differential backup after the last full backup create the restore T-SQL commands
IF
(@lastDifferentialBackup IS
NOT
NULL)
BEGIN
    — Restore last diff. backup
    PRINT
‘RESTORE DATABASE ‘
+ @DBName
    PRINT
‘FROM DISK=”’
+ @lastDifferentialBackupPath +
””

    — If no backup made (differential or log) after it, set to ‘with recovery’
    IF
(@lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
        PRINT
‘WITH RECOVERY’
    ELSE
PRINT
‘WITH NORECOVERY’

—    PRINT
–‘GO’
    PRINT

— new line for readability
END

— construct the required TRANSACTION LOGs restores
IF
(@lastDifferentialBackup IS
NULL)
— no diff backup made?
    SET @i = @lastFullBackup +
1    — search for log dumps after the last full
ELSE
SET @i = @lastDifferentialBackup +
1
— search for log dumps after the last diff

— script T-SQL restore commands from the log backup history
WHILE
(@i <=
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
BEGIN

    SET @logBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)
    PRINT
‘RESTORE LOG ‘
+ @DBName
    PRINT
‘FROM DISK=”’
+ @logBackupPath +
””

    — it’s the last transaction log, set to ‘with recovery’
    IF
(@i =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
        PRINT
‘WITH RECOVERY’
    ELSE
PRINT
‘WITH NORECOVERY’   

—    PRINT
–‘GO’
    PRINT

— new line for readability

    SET @i = @i +
1
— try to find the next log entry
END
PRINT ‘USE ‘+ @DBName
–Disable all triggers on the subscriber database:
PRINT ‘EXEC sp_msforeachtable ”ALTER TABLE ? DISABLE TRIGGER all”’
–Disable all constraints on subscriber database:
PRINT ‘EXEC sp_msforeachtable ”ALTER TABLE ? NOCHECK CONSTRAINT ALL”’

PRINT ‘END’
PRINT ‘ELSE’
PRINT ‘BEGIN’

PRINT ‘PRINT ”–THIS SCRIPT CAN ONLY BE RUN AGAINST THE REPORTING REPLICA, OTHERWISE IT WILL BLOW AWAY PRODUCTION !!”’
PRINT ‘PRINT ”–FAILSAFE BUILT IN TO CHECK SERVER NAME AND EXIT OTHERWISE”’
PRINT ‘END’
— remove temp objects that exist
IF
OBJECT_ID(‘tempdb..#MSDBBackupHistory’)
IS
NOT
NULL

    DROP
TABLE #MSDBBackupHistory

— Add in a reference to the last file restored to be used later when the subscription is created, so we can use the same file, putting it at the very end so its easier
— to find, it reads the file in, finds the last line and uses it in the add subscription, not re-querying as may have been some time elapsed
— between kicking the restore off
PRINT ‘–‘+@logBackupPath

————————————————————————————————————–

RESTART_REP_RESTORE_COMMANDS.SQL

————————————————————————————————————–
IF (SELECT CONVERT(sysname, SERVERPROPERTY(‘servername’))) = ‘BALA24029221’
BEGIN
PRINT ‘–OK WE ARE ON THE RIGHT SERVER’
USE MASTER
ALTER DATABASE MDAX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE MDAX

RESTORE DATABASE MDAX
FROM DISK=’E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MDAX_FULL.BAK’
WITH NORECOVERY

RESTORE LOG MDAX
FROM DISK=’E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MDAX_TRAN.TRN’
WITH RECOVERY

USE MDAX
EXEC sp_msforeachtable ‘ALTER TABLE ? DISABLE TRIGGER all’
EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
END
ELSE
BEGIN
PRINT ‘–THIS SCRIPT CAN ONLY BE RUN AGAINST THE REPORTING REPLICA, OTHERWISE IT WILL BLOW AWAY PRODUCTION !!’
PRINT ‘–FAILSAFE BUILT IN TO CHECK SERVER NAME AND EXIT OTHERWISE’
END
–E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MDAX_TRAN.TRN

————————————————————————————————————–

CREATE_SUBSCRIPTION_COMMANDS.SQL

————————————————————————————————————–
USE MDAX
–Add a subscription.
EXEC sp_addsubscription
                                @publication = ‘MDAX’,
                                @subscriber = ‘BALA24029221.DOM240292.LOCAL’,
                                @destination_db =’MDAX’,
                                @sync_type = ‘initialize with backup’,
                                @backupdevicetype = ‘disk’,
                                @backupdevicename = ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MDAX_TRAN.TRN’
USE MDAX
–Add an agent job to synchronize the push subscription.
     EXEC sp_addpushsubscription_agent
                                @publication = ‘MDAX’,
                                @subscriber = ‘BALA24029221.DOM240292.LOCAL’,
                                @subscriber_db =’MDAX’,
                                @job_login = ‘DOM240292\administrator’,
                                @job_password = ‘Balajimalab!’;
USE MSDB
EXEC dbo.sp_update_job @job_id = ’78E67D13-19BC-423E-9AB9-0B7E3593A6CA’, @enabled = 1
EXEC dbo.sp_update_job @job_id = ‘5B07BFDB-3E67-44E8-95FF-029FDC154BCC’, @enabled = 1

————————————————————————————————————–

Tranrepl.bat

————————————————————————————————————–
REM – Run script to create the initial subscription and add the replication articles
sqlcmd.exe -S BALA24029220 -Q “Exec MDAX.[DBO].[usp_Create_MDAX_Publication]”

REM – Run script to generate restore commands and delete the target database on the reporting server
sqlcmd.exe -S BALA24029220 -Q “Exec MSDB.[dbo].[usp_GenerateRestoreScripts] ‘MDAX'” -o “E:\NEW FOLDER\RESTART_REP_RESTORE_COMMANDS.SQL”

REM – Run the script created in the previous step, i.e. drop the database and restore a new copy – MAY TAKE SOME TIME !
sqlcmd -S BALA24029221 -i “E:\NEW FOLDER\RESTART_REP_RESTORE_COMMANDS.SQL”

REM – Generate the command to create a subscription to the recently restored copy of the live database on the reporting server.
sqlcmd.exe -S BALA24029220 -Q “Exec MSDB.[dbo].[usp_GenerateSubscriptionScript] ‘MDAX'” -o “E:\NEW FOLDER\CREATE_SUBSCRIPTION_COMMANDS.SQL”

REM – Run the script created in the previous step, i.e. Create the subscription
sqlcmd.exe -S BALA24029220 -i “E:\NEW FOLDER\CREATE_SUBSCRIPTION_COMMANDS.SQL”

————————————————————————————————————–

We were able to reproduce the error which
we got when using a backup that was taken before the creating on publication:

Work arround:
==========

We managed to get the process to work but not end to end.

If we do it in this order,

Create the Publication

Disable Distribution

Backup the database

Restore the database

Create the subscription

My thinking is that we should be able to create the whole lot in one go, the point of using the backups was to avoid putting any additional load On the database server, i.e. that the backups are already done and we may as well use them, if we have to create them again it kind of defeats the objective.

We continued the investigation on a little while longer for our understanding why we need to take a fresh backup after the creation of the publication and why the previous backup wont work.

Was able to investigate the work arround,

seems like we need to take a fresh backup which is documented in http://msdn.microsoft.com/en-us/library/ms147834.aspx

1. Creating publication and add articles.
2. Disabling distribution agent job.
3. Take back up and move to subscriber.
4. Then run the batch.

CONCLUSION:
==========

Since above behavior is as per the design and is clearly documented in http://msdn.microsoft.com/en-us/library/ms147834.aspx the question why arises because taking a 1TB backup during the setting up of replication is cumbersome and might also complication the current backup strategy and need for an addition storage.

These are few things we can think of as to why backup taken even before creating of publication won’t work:

1. Considering the retention period of the distribution Agent. It is always going to clean up the old data whether it’s being synced with subscriber(s) or not.
2. Even if we find a way for the distribution agent to retain the data, Imagine a scenario if we have more than one subscriptions how will distribution agent know the data needs to be moved to the newly created subscription?
3. After creating the publication with all the articles the transaction log is going to make a mark in the transaction log to replication them after setting up the replication that’s how SQL server is going to identify what all the transactions that need to be replication from the publisher to the subscribers  that has been missed during the process of setting up the replication.

http://msdn.microsoft.com/en-us/library/ms151706.aspx

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.

3 Responses to “Automating Transactional Replication? Really?”

  1. Hitachi Kutochi said

    WoW… Nice piece of info… Great work!

    Can you post more of these kind information

  2. Hitachi Kutochi said

    So nice of you.. You look smart in the photograph… So you should be smart in SQL Server also…

    When I come to India, I owe a beer bottle of your choice.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: