NOTE: Microsoft has since abandoned the SQL Server 2016 Upgrade Advisor and now calls the tool the Microsoft Data Migration Assistant (DMA). The concepts for using the tool are the same for using DMA as shown below. Please stay tuned for a new blog post that covers the capabilities of DMA.
SQL Server 2016 has been released, and it is already making waves as a big step up from previous versions. Naturally, upgrading to SQL Server 2016 has an appeal that is growing with each passing day. But how can you be sure that your databases are ready to make the move without breaking? Enter SQL Server 2016 Upgrade Advisor.
With SQL Server 2016 Upgrade Advisor, you can easily check for any issues in your databases that may interfere with the upgrade process so that you can pinpoint and fix them quickly. Then, it is a simple process to move your databases that aren’t using any high-availability or disaster-recovery features from their current instances to SQL Server 2016. Check out our five-minute video on how to use SQL Server 2016 Upgrade Advisor to check for upgrade issues, how to fix non-ANSI join syntax issues, and how to migrate a database to a newer version of SQL Server.
Here is a summary of what the video covers:
Downloading SQL Server 2016 Upgrade Advisor
The first step is to download SQL Server 2016 Upgrade Advisor. This link will lead you to the most current version. Once you have downloaded and installed the program, you can open it up and get to work.
Running your initial database analysis
Hopefully at this point you already know which databases you want to move. Click the button labeled “Analyze and Migrate to SQL Server” and connect to the server hosting the databases you are migrating. Now, you can select the databases on this server that you will be upgrading and click “Run” to begin the initial analysis.
At this point, Upgrade Advisor will analyze the schema and look through stored procedures, functions, triggers, and more for any issues that may arise when migrating these databases. Upon completion, you will see a compatibility assessment for each database that indicates how many compatibility issues each may have. Clicking on the database will allow you to view the results in detail.
Reviewing and exporting the flagged issues
Upgrade Advisor will display the compatibility issues next to their associated version within the database under three categories. The first category, which will be shown in red, denotes High Severity issues that must be resolved before the upgrade can be completed. The second category, shown in yellow, indicates Medium Severity issues that may become a larger problem with future SQL Server versions and may cause problems with this version. The third category, shown in blue, are Low Severity issues that can typically be ignored at this point. If you click on the issues, you can view them in more detail.
By clicking the save icon in the upper-right corner, you can also export the compatibility report and save it as a separate file.
The report generated by the export function can be created as either an HTML or a CSV file, and it will allow you to review the report outside of Upgrade Advisor.
Resolving High Severity issues
Upon viewing the issues, click the red issues marked as High Severity to get more details on the cause. One of the most common issues you will encounter is an ANSI join issue related to improper syntax. Fortunately, this can be fixed easily. First, go to Management Studio and find the Stored Procedure where the error occurred. Open the stored procedure, and highlight the query where the error is. Then, simply right-click, choose “Design Query in Editor,” and click “OK” to automatically convert to the ANSI join syntax.
Finalizing the upgrade process
After fixing all of the High Severity issues, run another analysis of the databases using Upgrade Advisor. Hopefully, the analysis should return no more High Severity issues. At this point, click the “Migrate” button. Now, choose your Destination Server and click “Connect.” Finally, click the second “Migrate” button to complete the process.
Now, your databases should be successfully migrated to your newer version of SQL Server. If need be, you can verify this by refreshing the server in Management Studio and locating the migrated databases on that server.
Reality check
There is much more to upgrading your application than just upgrading the database you need to be aware of. For example, what if the application is sending older syntax that is no longer supported by newer versions of SQL Server? You will need a way to identify the statements and the application so you can fix the application. How do you go about updating client drivers for the application so that they can connect to the newer versions of SQL Server? How can you best take advantages of the new versions of SQL Server 2016? In a future blog post, we will talk about our DB Best 12-step process for upgrading applications to modern platforms.
Next steps
We hope that you have found this introduction to using SQL Server 2016 Upgrade Advisor helpful. This guide should be fairly comprehensive in getting you through simple upgrades, but not all upgrades can be simple. If you are having trouble completing the process, DB Best can help. Check out our available services for modernizations and upgrades here. Drop us a line when you are ready to engage with Microsoft’s Global Partner of the Year for Modernizations.
