SQL Server:: Log Shipping and Replication

Log shipping involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients. This copy is known as the primary database. Updates made by clients to the primary database are propagated by means of log shipping to the other copy of the database, known as the secondary database. Log shipping involves applying the transaction log from every insertion, update, or deletion made on the primary database onto the secondary database.

Log shipping can be used in conjunction with replication, with the following behavior:

• Replication does not continue after a log shipping failover. If a failover occurs, replication agents do not connect to the secondary, so transactions are not replicated to Subscribers. If a failback to the primary occurs, replication resumes. All transactions that log shipping copies from the secondary back to the primary are replicated to Subscribers.
• If the primary is permanently lost, the secondary can be renamed so that replication can continue. The remainder of this topic describes the requirements and procedures for handling this case. The example given is the publication database, which is the most common database to log ship, but a similar process can also be applied to subscription and distribution databases.

Note:- We recommend using database mirroring, rather than log shipping, to provide availability for the publication database.

Requirements and Procedures for Replicating from the Secondary If the Primary Is Lost

Be aware of the following requirements and considerations:

• If a primary contains more than one publication database, log ship all of the publication databases to the same secondary.
• The installation path for the secondary server instance must be the same as the primary. User database locations on the secondary server must be the same as on the primary.
• Back up the service master key at the primary. This key will be restored at the secondary. For more information.
• Log shipping does not guarantee against data loss. A failure on the primary database can result in the loss of data that has not yet been backed up or for backups that are lost during the failure.

Log Shipping with Transactional Replication

For transactional replication, the behavior of log shipping depends on the sync with backup option. This option can be set on the publication database and distribution database; in log shipping for the Publisher, only the setting on the publication database is relevant.

Setting this option on the publication database ensures that transactions are not delivered to the distribution database until they are backed up at the publication database. The last publication database backup can then be restored at the secondary server without any possibility of the distribution database having transactions that the restored publication database does not have. This option guarantees that if the Publisher fails over to a secondary server, consistency is maintained between the Publisher, Distributor, and Subscribers. Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher; if your application can tolerate this latency, we recommend that you set this option on the publication database. If the sync with backup option is not set, Subscribers might receive changes that are no longer included in the recovered database at the secondary server. 

To configure transactional replication and log shipping with the sync with backup option

1. If the sync with backup option is not set on the publication database, execute sp_replicationdboption '<publicationdatabasename>', 'sync with backup', 'true'. For more information.
2. Configure log shipping for the publication database.
3. If the Publisher fails, restore the last log of the database to the secondary server, using the KEEP_REPLICATION option of RESTORE LOG. This retains all replication settings for the database. For more information.
4. Restore the msdb database and master databases from the primary to the secondary. For more information, If the primary was also a Distributor, restore the distribution database from the primary to the secondary.
   These databases must be consistent with the publication database at the primary in terms of replication configuration and settings.
5. At the secondary server, rename the computer and then rename the SQL Server instance to match the primary server name. For information about renaming the computer, see the Windows documentation. For information about renaming the server.
6. At the secondary server, restore the service master key that was backed up from the primary. 

To configure transactional replication and log shipping without the sync with backup option

1. Configure log shipping for the publication database. For more information.
2. If the Publisher fails, restore the last log of the database to the secondary server, using the KEEP_REPLICATION option of RESTORE LOG. This retains all replication settings for the database. 
3. Restore the msdb database and master databases from the primary to the secondary. If the primary was also a Distributor, restore the distribution database from the primary to the secondary.
   These databases must be consistent with the publication database at the primary in terms of replication configuration and settings.
4. At the secondary server, rename the computer and then rename the SQL Server instance to match the primary server name. For information about renaming the computer, see the Windows documentation. For information about renaming the server.
   You might receive an error message from the Log Reader Agent that the publication database and the distribution database are not synchronized.
5. At the secondary server, restore the service master key that was backed up from the primary. 
6. Execute sp_replrestart. This stored procedure can be used to force the Log Reader Agent to ignore all the previous replicated transactions in the publication database log. Transactions applied after the completion of the stored procedure are processed by the Log Reader Agent. 
7. Restart the Log Reader Agent after the stored procedure executes successfully. 
8. Transactions that have already been distributed to Subscriber might be applied at the Publisher. To ensure that the Distribution Agent does not fail with an error when attempting to reapply these transactions at a Subscriber, specify the agent profile titled Continue On Data Consistency Errors.

Log Shipping with Merge Replication

Follow the steps in the procedure below to configure merge replication and log shipping.

To configure merge replication and log shipping

1. Configure log shipping for the publication database. For more information.
2. If the Publisher fails, at the secondary server, rename the computer and then rename the SQL Server instance to match the primary server name. For information about renaming the computer, see the Windows documentation. For information about renaming the server.
3. Restore the last log of the database to the secondary server, using the KEEP_REPLICATION option of RESTORE LOG. This retains all replication settings for the database. 
4. Restore the msdb database and master databases from the primary to the secondary.  If the primary was also a Distributor, restore the distribution database from the primary to the secondary.
   These databases must be consistent with the publication database at the primary in terms of replication configuration and settings.
5. At the secondary server, restore the service master key that was backed up from the primary. For more information.
6. Synchronize the publication database with one or more subscription databases. This allows you to upload those changes made previously in the publication database, but not represented in the restored backup. The data that can be uploaded depends on the way in which a publication is filtered:
   • If the publication is not filtered, you should be able to bring the publication database up-to-date by synchronizing with the most up-to-date Subscriber.
   • If the publication is filtered, you might not be able to bring the publication database up-to-date. Consider a table that is partitioned such that each subscription receives customer data only for a single region: North, East, South, and West. If there is at least one Subscriber for each partition of data, synchronizing with a Subscriber for each partition should bring the publication database up-to-date. However, if data in the West partition, for example, was not replicated to any Subscribers, this data at the Publisher cannot be brought up-to-date. In this case, we recommend reinitializing all subscriptions so that the data at the Publisher and Subscribers converges. For more information.
   If you synchronize with a Subscriber that is running a version of SQL Server prior to SQL Server 2005, the subscription cannot be anonymous; it must be a client subscription or server subscription (referred to as local subscriptions and global subscriptions in previous releases). For more information.