none
Change database compatibility for published database (merge replication)

    Question

  • Hello

    I have 3x Sql Server 2008 R2 servers running. The first is publisher and distributor. The other 2 are subscribers. Some databases have a merge replication setup. For one of those db's I would like to change the compatibility level from 'Sql Server 2000(80)' to 'Sql Server 2008(100)'. But I'm not sure if I can do that without any problems. Not from an application point of view. Application is our own. I just want to be sure on SQL level replication doesn't brake. The publication already has the compatibility level 'Sql Server 2008 or later'.

    Anybody here who has already done that?

    Kind regards

    Koen

    Wednesday, September 19, 2018 9:17 AM

All replies

  • I forgot to mention that on the subscription servers, the target database already has the 'Sql Server 2008(100)' compatibility level.
    Wednesday, September 19, 2018 9:26 AM
  • There should not be any issue atleast I have not faced any. You need to make sure you do it correctly and properly.

    You need to make sure there is no activity on publisher, or very least activity.

    You then need to make sure all transactions are moved from publisher to subscriber and there is no pending transactions. You must run sp_replcmd command to make sure no replicated transactions are there. if you find one wait for it to move to subscriber or use sp_replflush to move transaction to subscriber

    Stop the log reader agent and change compatibility level. Start the log reader agent after compatibility level is changed.

    If you see any issue follow same process and change compatibility level back to 80 but I doubt there would be issue



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 19, 2018 9:32 AM
  • Hello Shashank

    Thanks for your reply. But it seems there is no log reader agent in a merge subscription. If I go to 'Replication Monitor' none is found. Confirmed here: https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/start-and-stop-a-replication-agent-sql-server-management-studio?view=sql-server-2014


    Kind regards

    Koen

    Wednesday, September 19, 2018 12:02 PM
  • Sorry missed that, it should be done via, see same example here, below is dummy example

    DECLARE @publication AS sysname;  
    SET @publication = N'AdvWorksSalesOrdersMerge' ;  
    
    -- Change the publication compatibility level to   
    -- SQL Server 2008 or later.  
    EXEC sp_changemergepublication   
    @publication = @publication,   
    @property = N'publication_compatibility_level',   
    @value = N'100RTM';  
    GO  


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, September 19, 2018 12:51 PM
  • Shashank

    That's not my question. The example you give is how to change the publication compatibility level. That one is already 'Sql Server 2008(100)'. I just want to change the compatibility level of the database that is being published. And I need to be sure replication won't break.

    Kind regards

    Koen

    Wednesday, September 19, 2018 1:58 PM
  • Shashank

    That's not my question. The example you give is how to change the publication compatibility level. That one is already 'Sql Server 2008(100)'. I just want to change the compatibility level of the database that is being published. And I need to be sure replication won't break.

    Kind regards

    Koen

    In that case you just need to make sure none of the subscriber is updating publisher, basically when databases are being changed and there is almost no activity. You need to run 

    alter database db_name set compatibility_level=100
    I cannot say this with surety that nothing will break the best way is to test it, by breaking I mean the replication would work but some DB functionalities might change. You change DB compatibility level and if you see issue change it back.



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 19, 2018 4:08 PM