New Disaster Recovery option for Peer-2-Peer (P2P) Replication in SQL 2008
Problem:
Following a severe systems/replication failure, SQL 2005 requires database updates to be frozen until the peer is re-established.
Peer-to-Peer Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151196(SQL.90).aspxMaintenance Considerations
The following actions require the system to be quiesced (stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes):
· Adding a node to an existing topology
· Adding an article to an existing publication
· Making schema changes
· Restoring a node from a backup
We realized this was not practical for a 24×7 datacenter. Therefore, this feature was enhanced in the SQL 2008 product. The SQL 2008 Books Online describe how a new Peer node can be configured while data on the 1st Peer is still be updated. We still require a full backup, but data changes are queued in the 1st Peer’s Distribution database then applied to the 2nd Peer after it is initialized.
SQL Server 2008 Books Online (August 2008)
New Peer Initialization (Peer-to-Peer Replication)
http://msdn.microsoft.com/en-us/library/ms177292.aspxSpecify how the new peer database(s) was initialized
When a publication is enabled for peer-to-peer replication, the allow_initialize_from_backup publication property is set. Replication immediately starts to track changes in the first publication database. Therefore, these changes can be delivered to a restored database at one or more peers if the initialize with backup option is selected. . . . All changes in the first publication database that have a higher LSN will be delivered to each peer.
How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms152536.aspxTo add the new node to the topology (creating subscriptions between Server A and Server D)
STEP 6:
———-
Replication immediately started to track changes in the publication database at Server A so that other peers could be brought up-to-date after a restore.Click the Browse button to navigate to the backup that is used, and replication will read the log sequence number (LSN) from the backup. All changes in the publication database at Server A that have a higher LSN will be delivered to Server D.
The “Maintenance Considerations” topic was also updated showing updates only need to be stopped when adding a 2005 node but not when adding another SQL 2008 node.
Peer-to-Peer Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151196.aspxMaintenance Considerations
The following actions require the system to be quiesced. This means stopping activity on published tables at all nodes and making sure that each node has received all changes from all other nodes.
· Adding a SQL Server 2005 node to an existing topology
· Adding an article to an existing publication
· Making schema changes
· Restoring a node from a backup
We recommend moving to SQL Server 2008 to take advantage of the new Peer-2-Peer features in SQL Server 2008.
