Upgrading Compatibility Level of Database | With Migration Manager Checks
Here we go through upgrading the compatibility level of a database and the checks you need to before upgrading. Its really interesting!
Content
00:00 Check compatibility level of database
00:30 Upgrading compatibility level
01:18 Microsoft Migration Assistant download
01:50 Database Upgrade Checks
04:35 Upgrade compatibility level 150
What is a Database Compatibility Level?
We cover in this tutorial we cover how to change and how to check a SQL Server Database compatibility level.
When Microsoft release a new version of SQL Server, they also release a tonne of new features (usually) or improvements. These features would be included in the compatibility level. For example; Microsoft may introduce a new and improved execution plan in SQL Server 2019, if your SQL Server instance is SQL Server 2019 but your database is in compatibility level 2016 then you won't see the benefits of it.
A large chunk of these benefits in newer versions are 'activated' by the compatibility level of your database.
The opposite is true also, if this new improved execution plan causes problems with some of your applications then you can downgrade the compat level to be 2016 again so SQL Server handles your queries differently.
It is a juggling act, but as a rule of thumb, its better to upgrade!
How do you check the compatibility level of all databases?
In this tutorial I go through how to check the compatibility level of the database that you are going to upgrade, but also how to check the level of ALL databses. Its really easy as you can just query the sys.databases dmv and it gives you all you need to know.
I show you how at the start of the video.
Microsoft Migration Manager
Microsoft Migration Manager is a crucial free tool to check your databases prior to upgrade. It highlights any breaking changes that you may have that WILL need fixing before upgrading.
The reason why this is important is because upgrading versions, can stop legacy code from working. So get this stuff changed if its flagged up
Link to the download is below
https://www.microsoft.com/en-us/downl...
