I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).

The subscription databases are also SQL Server 2008 R2, however the destination databases are set to compatibility level 100 and replication is working fine.

If I change the compatibility level for the Published databases, will it affect replication in any way, or will it just be a case of reinitializing all the subscriptions and restarting replication?

I suspect that changing the published database compatibility level may change how the replication stored procedures function slightly, but I'm not 100% sure.

Is this the case?

  • Interesting question, but is there a reason these databases need to have their compatibility level changed? If things are working fine, I'd probably just leave it alone...
    – Jon Seigel
    Sep 28, 2012 at 17:14
  • The main reason I'd like to change them is that the DB and the servers they sit on have just been upgraded from a single-node 2005 box to a multi-node 2008R2 cluster. Additionally, there are a few other bits of functionality (such as MERGE etc.) that I'd like to be able to start making use of.
    – Bob
    Oct 1, 2012 at 9:03
  • 1
    You may want to read this question regarding what the compatibility level does: dba.stackexchange.com/questions/5166/…
    – Jon Seigel
    Oct 1, 2012 at 14:41
  • The end of civilization. Cats and dogs living together. Mass hysteria. I should also mention that it may cause a government shutdown. However, you seem to be in the clear on that one.
    – swasheck
    Oct 8, 2013 at 17:28

2 Answers 2


You can follow below steps :

  • Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
  • Stop user activity on published tables.
  • Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
  • Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
  • Execute sp_replflush to close the connection from sp_replcmds.
  • Change the compatibility level of the database.
  • Start the Log Reader Agent.
  • I have a very similar setup and want to do the same thing but my distribution database is also in 90 compatibility mode. From what I understand, the distributor can't operate on a lower compatibility mode as the publisher. So would the order of operations be to simply insert "Change compatibility level of distributor" before "Change the compatibility level of the (published) database" - after the log reader has been turned off?
    – Queue Mann
    Apr 16, 2015 at 14:43
  • @QueueMann "Change compatibility level of distributor" before "Change the compatibility level of the (published) database" that is correct. Distribution database should be on same or higher compatibility level than Publisher. A publisher can be on same or lower level than a distributor.
    – Kin Shah
    Apr 16, 2015 at 15:48

We did this, just to be safe we stopped replicating that database, changed compatibility level, then reinitialized. We incurred no issues. It was a pretty small publication and there was only one database subscribed. I think it could possibly be more difficult as your replication scenarios get more complex. (i.e., subscribing db getting publications from many dbs, etc.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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