Question

Photo of Brandon Gamache

0

Azure SQL Server - Updating Database Compatibility Level

3

Was hoping to use a sql command in a query I am working on and it turns out it is only available at compatibility level 130. The SQL Server engine is up to 150 but the Rock database instance is at 120.

For those that have their Rock instance hosted in Azure SQL Server, and if you have updated your compatibility level before, do you have any tips, tricks, gotchas, or warning regarding updating the compatibility level? Or is it pretty straight forward without any issues?

I will probably make a copy of the database and treat it as a sandbox and attempt to update its compatibility level but wanted to see if anyone in the community has any suggestions or comments as it relates to SQL Server changes and Rock compatibility/adherence.

Thanks

  • Photo of Brandon Gamache

    1

    We updated the compatibility level and have been running our production environment without issue for the last month. 

    If you are using Azure SQL, updating the compatibility level is very easy if you are using SSMS. If you are using some other SQL Server Manager software, the SQL code to use is also pretty simple and straightforward.

    If you are able, I would run a sandbox and test the update the sandbox first to make sure there aren't any issues with your Rock instance or any custom code you may have implemented.

    If you have SSMS
    • Open SSMS and navigate to your Rock database.
    • Right-click on the database and select Properties. A new window will display.
    • Under the Select a Page menu on the left, select Options.
    • Click on the Compatibility Level dropdown and select the level you would like.
    • Click the OK button and you're done.
    If using SQL query
    • Open a new query window.
    • This will display current database compatibility levels:
      SELECT name, compatibility_level FROM sys.databases;
    • This will update the compatibility for the desired database (you'll need to enter the database name and the desired compatibility level). 
      ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }

    If you would like more information about compatibility levels and SQL versions, and further explanations about the code above, you can visit Microsoft's documentation page: Database Compatibility Level

  • Photo of Nick Airdo

    1

    Brandon, good post!  Although I don't have an answer to your question, with many people moving to Microsoft SQL Azure, we are also making a small change to the System Information's Diagnostics tab detail to show the "Database Compatibility Version: _____" in order to make this more clear to the administrator which compatibility level they are running.

    This will appear as a new field right under the "Database Friendly Version:", but for those on Azure SQL, we're also going to hide the "Database Friendly Version:" since it is mostly meaningless for them since Microsoft states Azure SQL is "always running on the latest stable version of the SQL Server database engine..." But as you point out, that is different than the compatibility level (which can be controlled by the database administrator).

    DatabaseCompatibilityVersion.png


    • Brandon Gamache

      Brandon Gamache

      Perfect. When I was looking into this, and after I updated the compatibility level, Rock was still seeing it as SQL 2014 and I was thinking of submitting an idea to add what you mentioned you guys were working on. Thanks for adding the additional info in future versions of Rock!

    • Photo of Marcie Rempel

      0

      We've just updated our compatibility level in our sandbox to 160, but the diagnostics are now showing the Compatibility Version as Unknown. The SQL Server Management studio is only giving an option to update the compatibility level to 140 but when we run the query to show the current compatibility level - it is showing as 160 - so why does it say 'Unkown' in the diagnostics?

      Screenshot_2023-11-15_at_11.00.50 AM.png

      • Nick Airdo

        Nick Airdo

        Hi Marcie, we noticed that not too long ago and have resolved that in an upcoming version of Rock. Thanks for helping make Rock "better together"!