Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Im building my first transactional replication database for our Sharepoint team. The Publisher database is fairly large 330GB+ Because the Publisher database is in a different data center than the Distributor and Subscriber database I will be perfoming the initial seed using a database backup instead of using a snapshot.

I understand that the Publisher will know the last log sequence number (LSN) from when the backup was taken. The Subscriber will be populated from the back up and have it's own last LSN from the restore.

Im not sure how the process will work once I turn the synchronization on after everything has been initialized/restored.

  • Does the Publisher talk to the Subscriber through the Distributor to know how far out of sync they are? I thought the Publisher and Subscriber did not have any real direct communication?
  • How will the proper records be sent to the Distributor from the Publisher when the Distributor is unaware of the initialization from backup.

Extra Details:
Publisher: SQL Server 2008 R2
Distributor: SQL Server 2012
Subscriber: Sql Server 2012

share|improve this question
    
The case you're describing should be documented here: technet.microsoft.com/en-us/library/ms147834.aspx –  Jens W. Mar 7 at 0:02

1 Answer 1

up vote 1 down vote accepted

The link posted as a comment to your question explains the technical steps but may not have made clear how the setup really works.

When you create the subscription you inform the publisher that the subscriber has been restored from a backup (specifying the backup file used). The backup you use has to be taken after the publication was created. When you setup replication you tell the distributor how long to keep transactions around (Transaction Retention Period). The distributor then keeps these commands until they are delivered to all subscribers, or until the retention period has been reached. So when the subscription is created, the LSN of the backup is checked to see if it was taken from within the retention period. If so, then the backup and therefore the subscriber is valid. Only the undistributed transactions created after the backup will need to be sent to the subscriber.

In your case you would:

  • setup the publication
  • take a backup
  • copy that to the subscriber before the retention period expires
  • restore the backup to the subscriber
  • create the subscription (using the initialize from backup option)
  • watch as the undistributed commands then get sent to the subscriber
  • finally see that the entire thing works!

Initializing from backup makes the entire thing much easier than snapshots, because you generally have backups running anyhow and, more often than not, can use compression. This does mean that the entire DB is on the subscriber though, which may be desired or may not.

share|improve this answer
    
This is very helpful. When you create the publication, does it immediately start sending transactions to the distributor? Or does it wait until there is a subscription? –  ProfessionalAmateur Mar 17 at 15:15
    
The distributor will immediately start collecting commands (using the log reader agent) as soon as the publication is created so that any subscription created can receive any relevant changes after initialization. If you have high transaction volumes on the publishing database, you may experience a performance impact here. The log reader copies the relevant transactions from the publishing database into the distribution database. The distribution database can then grow quite large - watch the retention period and create the subscription as soon as possible to help here. –  sql_williamd Mar 18 at 12:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.