RO-AG With Mirror On Different Compat Level

  • Good Morning Gentlemen,

    We have a  2014 SQL Server that is in a read only availability group with another 2014 sql server.  We recently discovered that one of the databases on the primary is set for 2008 (100).  We would like to update just the secondary database compatibility to 2014, I was wondering if anyone has any experience with this type of set up or knows if that would cause any issues.

  • You cannot change the compatibility level of a database on the secondary only - it must be changed on the primary.  I am assuming the reason for changing the compatibility level is because you want to be able to use new features in code and that isn't working.

    To solve that problem, create a new database on the secondary with the latest compatibility - and then run your code from that database context using 3-part naming (or better yet, setup synonyms).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • we were hoping to use a different cardinality estimator on the mirror vs prod and putting queries that performed poorly against the mirror as opposed to prod.  Shame.

  • Have you tried the ideas outlined here: https://www.brentozar.com/archive/2015/06/careful-testing-the-2014-cardinality-estimator-with-trace-flags/

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Fortunately, we are doing a major upgrade on the app that is on that system in July and we are mid testing a copy of it with 2017 SQL Server, I went ahead and put the database in question on 2017 in that environment and also ran the database migration assistant to point out things that might be issues.  No breaking changes, some old joins that aren't properly declared are really the only issues.  I was just hoping to get ahead of it since recently we tried to implement a clustered index on a box and the 2008 server basically just spun when accessing it. Just have to wade through the next couple months I spose 🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply