Quiesce your Replication topology

SQL Server

An interesting question came up during my recent SQL Server 2008 class. When we were covering Replication, one student asked when stopping replication which do you stop first, the publisher or the subscribers?

I must admit, the answer did not immediately come to mind. His thought was to shutdown the Publisher first but what if the Subscribers had outstanding updates that had not replicated? Conversely, if you shut down the Subscribers first, they may not have the updates from the Publisher.

I remembered from my Sybase days that there was a QUIESCE DATABASE statement that would gracefully shutdown a database allowing outstanding updates to complete without letting new transactions to start. But such a statement does not exist in Microsoft SQL Server.

So I Googled Quiesce and found out that Wiktionary has a definition - to "become quiet". But Wikipedia has a good article on the word as it relates to Database technology. What a pleasant surprise. It turns out DB2 supports a Quiesce statement. So does Oracle. Even Sharepoint does. But not Microsoft SQL Server.

Then I found a great article on MSDN entitled "How to: Quiesce a Replication Topology". That was it. Even though there is no Quiesce statement this document describes the steps to synchronize your Publisher with its Subscribers. The steps are different depending on the type of Replication, but the document spells it out for you.

So which do you shut down first, the Publisher or the Subscribers? The answer is: it really doesn't matter as long as you have successfully quiesced your replication topology first.

Now here's the real challenge: go use the word Quiesce in a normal conversation today. "I wish my kids would quiesce..."

cheers
Brian

"How to: Quiesce a Replication Topology":
http://msdn.microsoft.com/en-us/library/ms147385(SQL.90).aspx

• Microsoft
• quiesce
• Replication
• SQL Server
• SQL Server 2008
• Sybase
• transactional replication