It’s upgrade season.
Here at Straight Path, we have more than a couple of clients in the process of upgrading their SQL Environments to a more modern version. With the support door slamming shut soon for SQL 2008 (waaaay overdue), and others on 2012 (really not in a much better position – support wise) clients are asking us to help them with their upgrade plans. We had a really great 4-part series on SQL Server upgrades. One question we often receive is, Will my database actually upgrade? Is it compatible with SQL 2017? Do we have deprecated features? It has been running for so long, those of us left just keep the lights on.
There is a very simple and easy way to answer those types of questions – Microsoft has provided a decent tool to replace the Upgrade Advisor called the Microsoft Data Migration Assistant (DMA). This tool is quite powerful and is used for analyzing databases as well as, the name implies, migrating the data between environments eg. On-prem to Azure SQL DB. Today I will focus on the assessment feature of this tool.
One of the very first steps I like to perform during an upgrade engagement is to check the existing database for compatibility at the target version – otherwise known as how high can you go? Will this SQL 2008 DB have any compatibility issues running SQL 2016, SQL 2017, or soon higher?
To get started download the DMA here. (it doesn’t come on any Database Media and is a separate download). The installation process creates a desktop icon. Double click the icon to get started. Click on the “+” sign to add a new assessment project.
I chose assessment as the goal of this article is to check the compatibility of the DB on the target SQL version. Name the project and chose the Target Server Type. The client was not migrating to the cloud so the server type is SQL Server (on-prem).
I chose SQL 2017 as the target version for this assessment.
Choose the source server and connect to choose which database for the assessment.
In this example, I will perform the assessment on the StraightPath Database. Click “Start Assessment”.
The assessment will take several minutes depending on the DB size.
The Compatibility assessment results are clearly presented. The source SQL 2008 DB is in Compactivity level 80 (SQL 2005) and the assessment reviewed each of SQL 2017 valid Compatibility mode options (100,110,120,130,140). In this case, 4 highest modes are identical.
This client was very pleased there were so few compatibility issues identified for this upgrade. There is only 1 breaking change for this database.
NOTE: Keep in mind this tool only can assess the database objects. It cannot assess the application code. That will require testing to determine what compatibility mode will be acceptable on the new database version.
Happy Upgrading.
Stay tuned for the next Blog Using DMA to migrate your data to Azure SQL.
