Sql-server – Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

replicationsql serversql-server-2005sql-server-2008-r2

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?

Best Answer

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.
Related Question