Aligning Compatibility Version Strategy

  • I have joined a new project, where the business uses Azure SQL Hyperscale for production. I have noticed that some Prod environments have compatibility levels set to 150, but some - 140. Same applies to Dev / Test SQL 2022 environments. I have been given a task to come up with a strategy on how to align both dev/test as well as the prod environments.

    I presume I am not the first one to face such an issue and rather than re-invent the wheel, is there any already available documentation / steps / strategy on how to implement the alignment with a minimum impact to the business, please?

    P.S. I think Azure SQL Managed Instance has 150 by default. Any reason not to set it to 160, please?

    Many thanks

    • This topic was modified 3 weeks, 2 days ago by  BOR15K.
  • Start by referencing this KB article, also use DMA to highlight issues when moving from one sql platform\version to another

    https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#differences-between-compatibility-levels

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • That was my first step. Done already. But I presume there are challenges on the actual update, especially in Prod, with 100's GB of data.

  • Case by case scenario, running DMA pre migration  will pinpoint issues

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I read you've already prepared it using DMA.

    You'll have to address your devs or software vendor(s) to have them test their apps against your target db level.

    I would aim to match DEV and PROD settings for the same database names!

    Then have application builders/testers test with your DEV system set to the target db level.

    After their "GO", plan for an implementation point-in-time, so they know what so search for when there arrise issues after the prod dba modifies the db level to the traget db level.

    If you can, create a query plan cache baseline - if you have monitoring software - so you - as a dba - can compare before and after query plans.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank you for the comments. I am more concerned with Prod update. Providing Dev / Test passed with no issues, what are the steps to amend the compatibility in Prod with 100s of GB and working 24/7.

  • Not relative to the amount of data, features support is primarily your concern

    if you’ve successfully tested in dev and test then apply to prod

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle wrote:

    Not relative to the amount of data, features support is primarily your concern

    if you’ve successfully tested in dev and test then apply to prod

    It is a bit scary to simply change the level in Prod, even after all the thorough, performance tests have been successfully completed. Or it is as simple as that?

  • If you’ve referenced DMA report and successfully changed and tested in dev and test then you have good level of assurance for prod change. You can always set it back to the previous level but thorough dev and testing should negate the need to do this

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Don't just change the DBLevel !

    Coordinate date and time of your action to the given software teams, so in case they should get alarms, they know about this all.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 10 posts - 1 through 9 (of 9 total)

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