Migrate database to Azure SQL Database using SQL Server 2016 Upgrade Advisor

By:   |   Comments (3)   |   Related: > Azure SQL Database


The tip, Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor, described the new SQL Server 2016 upgrade advisor. Can we use this new upgrade advisor to migrate to Azure SQL database?


You can download the latest SQL Server 2016 Upgrade Advisor from this link: https://www.microsoft.com/en-us/download/details.aspx?id=48119. This is still a preview version, so make sure you don't install it directly on a production or important server as this product is subject to change. For more understanding regarding the terms like "beta" or "preview" version refer this link.

After you install, launch the SQL Server 2016 Upgrade Advisor.

Once you launch the Upgrade Advisor, you will see the below screen. The scenarios tab shows the options that are available. Note the options are named differently as compared to the ones described in this tip Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor

SQL Server 2016 Upgrade Advisor Scenarios

In the "Active" tab, you can see active workflows that you initiated. As we haven't started to use this tool yet, there are no workflows in this tab. See below screenshot.

SQL Server 2016 Active Workflows

Migrate to Azure SQL Database

In this tip, we will see how to use the "Migrate to Azure SQL Database" option. Use the below script to setup a test database on your local instance of SQL Server. Our goal in this tip is to migrate this newly created database "UpgradeNightmare" to Azure SQL Database.

USE master;

CREATE DATABASE UpgradeNightmare;


USE UpgradeNightmare;

   y NTEXT,

CREATE INDEX ix1 ON dbo.BadTable(ts);

CREATE PROCEDURE dbo.BadProcedure;1
   SELECT name FROM syscolumns;
   EXEC sp_addtype N'EMail', N'nvarchar(320)';
CREATE PROCEDURE dbo.BadProcedure;2 AS BEGIN SET FMTONLY ON; UPDATE b SET x += 1 FROM dbo.BadTable AS b WITH (NOLOCK); EXEC sp_configure 'allow updates', 1; END GO CREATE PROCEDURE dbo.WorseProcedure AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF; SELECT 'string alias' = 1; DROP INDEX dbo.BadTable.ix1; END GO

Once the database "UpgradeNightmare" is created, as a first step click on the option "Migrate to Azure SQL Database". Provide the SQL Server instance details where you created the "UpgradeNightmare" database and click on "Connect".

Configure Azure Migrator and connect to the SQL Instance

Once the connection is successful, you will be able to view all the databases on the instance as shown below.

Select the SQL Server Database to Migrate

Click on the database "UpgradeNightmare" and click "Select". You will see this option and then click "Run".

Click Run to begin the Migrate to Azure SQL Database

Once you click on "Run", the Upgrade Advisor analyses the selected database and presents the results as shown below. You can see there are no errors reported.

Analysis Results for the Database Migration to Azure SQL Database

Click on the "Select Target" option where you need to provide the details of the Azure Server name. You can get a Azure server name if you already have a SQL Azure database. However, if you don't have a Azure SQL Database already you can create a new server by following the steps on the Azure portal.

Configure the Server, Create a new server and setup credential for the Azure Server

Once you click on "Select", you will see this screen where you can create a test database. Enter the relevant details and click on "Create".

Azure Sample SQL Database Configuration

Once the Azure database is created, you can get the Azure Server name from the portal as shown below.

Azure Server Name

Now going back to our previous sequence of steps in the Upgrade Advisor, click on the option "Select Target" as shown above. Once you click on the "Select Target" option, you will see the below screen. Enter the credentials of the Azure SQL Server which you created earlier and click on "Connect". Once done, click on the "Select" option.

Select Server to setup the user name and password

You will then see this screen, where you will be able to select the option to "Migrate" the database.

Azure Migrator Analysis Results

Once migration is complete you can verify that the database is indeed migrated by connecting to the Azure SQL Server. I noticed that there is no confirmation or any type of message displayed on the Upgrade Advisor that the migration completed successfully.

Validate the migrated database in SQL Server Management Studio

If the migration was not successful, the Upgrade Advisor displays a message as "Failed" and there is another "Show Errors" option you can select to view the actual error messages. In this example, I tried to migrate the "UpgradeNightmare" database again to the same Azure SQL server.

Sample Migrated Error when moving the same database

With the new SQL Server Upgrade Advisor, we have a good tool to migrate to SQL Azure.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, July 10, 2016 - 4:54:17 PM - Mohammed Back To Top (41857)

Thank you Boj and Sri for your feedback :-)

Friday, July 8, 2016 - 5:58:09 PM - Bojanna Muruvanda Back To Top (41846)

Very good article Moinu. Thanks for detailed one with screenshots. Great job!! Keep writing.

Friday, July 8, 2016 - 5:47:46 PM - Srinath Back To Top (41845)

Very descriptive and yet another great post, Moinu. Looking forward to see more from you...

get free sql tips
agree to terms