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.

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?

share|improve this question
    
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 '12 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 '12 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 '12 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 '13 at 17:28
add comment

2 Answers

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.
share|improve this answer
add comment

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.)

share|improve this answer
add comment

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.