Description of two approaches for a disaster recovery plan for transactional replication (886839)

MORE INFORMATION

"Strategies for backing up and restoring transactional replication" topic in SQL Server Books Online

In SQL Server Books Online, the "Strategies for backing up and restoring transactional replication" topic describes the disaster recovery plan for transactional replication. The following list briefly describes the steps in the approach in SQL Server Books Online:

1. Synchronize the publication database to a backup. For more information, see the "Backing up and restoring the publication database" section of the "Strategies for backing up and restoring transactional replication" in SQL Server Books Online. Synchronizing the publication database to a backup may cause replication latency. Replication latency is the time that the replication takes for the changes at the publisher to be delivered to the subscriber. Replication latency can frequently be as little as several seconds and is now constrained to the frequency of backups at the publisher.
2. Synchronize the distribution database to a backup. For more information, see the "Backing up and restoring the distribution database" section of the "Strategies for backing up and restoring transactional replication" in SQL Server Books Online. The disadvantage of this synchronization is that setting the "sync with backup" option on the distribution database does not affect replication latency. However, the synchronization will delay the truncation of the transaction log on the publication database until the corresponding transactions in the distribution database have been backed up.
3. Set the minimum transaction retention period at the distributor. For more information, see the "Backing up and restoring a subscription database" section of the "Strategies for backing up and restoring transactional replication" topic in SQL Server Books Online.
   
   Note When you set this minimum transaction retention period at the distributor, make sure that this period is greater than the frequency of the backup interval at the subscriber.
   
   When you increase the minimum transaction retention period at the distributor, the size of the distribution database is larger because the distribution database maintains records for the minimum transaction retention value that is specified.

Therefore, the problem with the approach that is mentioned in SQL Server Books Online is the increase in the latency of the commands to reach the subscriber.

back to the top

Alternative approach 1

This approach does not synchronize the publication database to the backup as mentioned in SQL Server Books Online. To set up a disaster recovery plan for transactional replication by using this approach, follow these steps:

1. Schedule the transaction log backups of the publication database with the full database backups. Schedule the transaction log backups so that the transaction log backups occur every hour and the full database backups occur one time each day.
2. Synchronize the distribution database to a backup. For more information, see the "Backing up and restoring the distribution database" section of the "Strategies for backing up and restoring transactional replication" topic in SQL Server Books Online.
3. Set the minimum transaction retention period at the distributor. For more information, see the "Backing up and restoring a subscription database" section of the "Strategies for backing up and restoring transactional replication" topic in SQL Server Books Online.
   
   Note When you set this minimum transaction retention period at the distributor, make sure that this period is greater than the frequency of the backup interval at the subscriber.

back to the top

Advantage

Because you do not synchronize the publication database to a backup, the records are processed almost immediately after the records are inserted in the publisher database. Therefore, you do not notice an added latency.

Note To obtain this advantage, use the full database backups and the transaction log backups instead of synchronizing the publication database to a backup.

back to the top

Steps to recover from a failure

In this approach, the steps to recover the replication databases from a failure depend on the failure scenario:

• If the publisher fails, follow these steps to recover the replication databases.
  
  Note You can use this plan to recover the replication databases if you do not require data synchronization between the data at the publisher and the data at the subscriber.
  1. Restore the publication database by using the full database backups and the transaction log backups.
  2. Run the sp_replrestart system stored procedure in the publication database without any parameters. This procedure forces replication to continue even if the distributor and some subscribers have data that the publisher no longer has.
     
     Note Make sure that there is no incoming traffic to the publisher before you perform this step.
     
     When you run the sp_replrestart system stored procedure, SQL Server 2000 fills the transaction log of the publication database with "No Operation" commands until the log sequence number (LSN) in the transaction log is greater than the value of the transaction sequence number (max xact_seqno) that was sent to the distribution database before the publisher failed.
  3. Enable new incoming traffic in the publisher.
  4. Restart the Log Reader Agent and the Distribution Agents.
• If the distributor fails, follow these steps to recover the replication databases:
  1. Restore the distribution database by using the full database backups and the transaction log backups.
  2. Restart the Log Reader Agent and the Distribution Agents.
  Because the "Sync with backup" option has been turned on at the distributor, make sure that the publisher still has the part of transaction log for the Log Reader Agent to reapply the appropriate commands.
• If the subscriber fails, follow these steps to recover the replication database:
  1. Restore the subscription database by using the full database backups and the transaction log backups.
  2. Restart the Distribution Agent.
• If the publisher and the distributor fail, follow these steps to recover the replication databases:
  1. Restore the distribution database by using the full database backups and the transaction log backups.
  2. Restore the publication database by using the full database backups and the transaction log backups.
  3. Run the sp_replrestart system stored procedure in the publication database without any parameters. This procedure forces replication to continue even if the distributor and some subscribers have data that the publisher no longer has.
     
     Note Make sure that there is no incoming traffic to the publisher before you perform this step.
  4. Enable new incoming traffic in the publisher.
  5. Restart the Log Reader Agent and the Distribution Agents.

back to the top

Alternative approach 2

This approach describes how to recover from a failure scenario when the failure occurs at the publisher.

Note This approach cannot recover data that is not published.

Using this approach, you synchronize the distribution database to a backup to set up a disaster recovery plan for transactional replication.

For more information about how to synchronize the distribution database to a backup, see the "Backing up and restoring the distribution database" section of the "Strategies for backing up and restoring transactional replication" topic in SQL Server Books Online.

The basic strategy in this approach is to recover the publisher from a backup, and then replay the commands from the distributor in the publisher. The commands are replayed from the time after the backup of the publication database was taken by SQL Server.

back to the top

Steps to recover the publisher from a failure

To recover a publisher from a backup, and then make the publisher consistent with the distributor and subscribers so that the data in the published tables will match the related tables at the subscriber, follow these steps. The replication can continue after you complete these steps.

1. Run all the Distribution Agents on your instance of SQL Server until SQL Server finishes processing all the commands that are not delivered from the distribution database. To verify the commands that are not delivered from the distribution database, run the following Transact-SQL statement in SQL Query Analyzer:

   SELECT * FROM distribution.dbo.MSdistribution_status

   The output in the Results pane is similar to the following:
   

   article_id  agent_id    UndelivCmdsInDistDB DelivCmdsInDistDB
   ----------- ----------- ------------------- -----------------
   1    		3           0                   22
   1    		5           0                   22
   (2 row(s) affected)

2. To retrieve the backup header information of the publisher database backup, run the RESTORE HEADERONLY Transact-SQL statement, and convert the lastlsn value by using the following steps as a reference:

   RESTORE HEADERONLY
   FROM
   Disk='FullPathtoPublisherDBBackupFile\PublisherDBBackupFileName'

   The output is similar to the following:
   

   BackupName                                BackupDescription     BackupType ExpirationDate   Compressed Position DeviceType UserName     ServerName      DatabaseName   DatabaseVersion DatabaseCreationDate      BackupSize    FirstLsn            LastLsn              CheckpointLsn       DifferentialBaseLsn         BackupStartDate          BackupFinishDate            SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName      Flags       BindingId                              RecoveryForkId                         Collation                                                                                                                         
   ----------------------------------------- --------------------- ---------- ---------------- ---------- -------- ---------- ------------ --------------- -------------- --------------- ------------------------- ------------- ------------------- -------------------- ------------------- --------------------------- ------------------------ --------------------------- --------- -------- --------------- ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- ---------------- ----------- -------------------------------------- -------------------------------------- -------------------------------
   PublisherDB-Full Database Backup        NULL                  1          NULL             0          1        2          UserName   ServerName    PublisherDB  570             2003-11-13 17:21:18.000   2838016       85000000025700001   85000000025900001    85000000025700003   0                           2003-11-20 09:28:17.000  2003-11-20 09:28:19.000     52        0        1033            196609                 90                 4608             9                    0                    694                  MachineName     0           85858a96-07d7-484f-a229-73be1f595e51   85858a96-07d7-484f-a229-73be1f595e51   SQL_Latin1_General_CP1_CI_AS   
   (1 row(s) affected)

   
   The lastlsn value from this output is 85000000025900001.
   
   For more information about the RESTORE HEADERONLY Transact-SQL statement, see the "RESTORE HEADERONLY" topic in SQL Server Books Online.
3. Create a function to convert an LSN value to a binary value. To do this, run an SQL script that is similar to the following:

   CREATE FUNCTION dbo.fn_convertnumericlsntobinary(
   @numericlsn numeric(25,0)
   ) returns binary(10)
   AS
   BEGIN
   -- Declare components to be one step larger than the intended type
   -- to avoid sign overflow problems. For example, convert(smallint, convert(numeric(25,0),65535)) will fail but convert(binary(2), 
   -- convert(int,convert(numeric(25,0),65535))) will give the 
   -- intended result of 0xffff.
   declare @high4bytelsncomponent bigint,
   @mid4bytelsncomponent bigint,
   @low2bytelsncomponent int
   select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))
   select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000
   select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))
   select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000
   select @low2bytelsncomponent = convert(int, @numericlsn)
   return convert(binary(4), @high4bytelsncomponent) +
   convert(binary(4), @mid4bytelsncomponent) +
   convert(binary(2), @low2bytelsncomponent)
   END
   

4. Convert the last LSN value from step 2 to a binary value by using the function in step 3. To do this, run a Transact-SQL statement that is similar to the following in SQL Query Analyzer:

   SELECT dbo.fn_convertnumericlsntobinary(85000000025900001)

   The output in the Results pane is similar to the following:
   

   ---------------------- 
   0x00000055000001030001 
   
   (1 row(s) affected)
   

5. Restore the publication database from the backup.
6. For each publication in the publication database that you restored, create a push subscription at the publisher using the publisher as the subscriber. When you create the push subscriptions at the publisher, set the Parameter Value to @sync_type none @loopback_detection false.
7. Stop the Distribution Agents.
8. Create the replication procedures on the publisher if your subscriptions use these procedures to apply changes. To do this, follow these steps:
   1. Run the sp_scriptpublicationcustomprocs system stored procedure for each table in the publication database. The sp_scriptpublicationcustomprocs system stored procedure scripts the replication procedure. For more information about the sp_scriptpublicationcustomprocs system stored procedure, see the following MSDN Web site:http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_repl3_4e9h.asp
   2. Use the SQL scripts to create the replication procedures.
9. Update the subscription_seqno column, the publisher_seqno column, and the ss_cplt_seqno column for the new subscription rows in the distribution.dbo.MSsubscriptions table to the time of the last transaction in the restored publication database by using the value in step 4. For example, run the following UPDATE Transact-SQL statement in SQL Query Analyzer:

   UPDATE MSsubscriptions
   SET
   subscription_seqno=0x00000055000001030001,
   publisher_seqno=0x00000055000001030001,
   ss_cplt_seqno=0x00000055000001030001
   where publisher_id = 0 and --insert publisher id for your database here as returned from querying the table dbo.distribution.Mspublisher_databases
   publisher_db = 'PublisherDB' and --insert your publisher database here
   subscriber_db = 'PublisherDB' --insert your publisher database here
   

10. Update the timestamp column for the new subscription in the msreplication_subscriptions table of the publishing database by using the following code:

    Update MSreplication_subscriptions 
    Set transaction_timestamp = -- LSN as obtained in step 4 (Hex value) 
    Where publisher = -- publisher server name and publisher_db = -- publisher database name

11. Start the Distribution Agents for the new subscriptions on the publisher, and then wait until all the commands have been delivered and the publisher is resynchronized.
12. Generate a SQL script for the replication topology to an .sql file.
13. In the SQL script, modify all the calls to the sp_addsubscription system stored procedure to set the @sync_type parameter to none.
14. Drop all publications and subscriptions for the restored database, and then re-create the publications and subscriptions by using the script in step 11.

back to the top