Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2016-07-08   |   Comments (3)   |   Related Tips: More > Azure

Problem

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?

Solution

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;
GO

CREATE DATABASE UpgradeNightmare;
GO

ALTER DATABASE UpgradeNightmare SET COMPATIBILITY_LEVEL = 100;
GO

USE UpgradeNightmare;
GO

CREATE TABLE dbo.BadTable
(
   x INT PRIMARY KEY,
   y NTEXT,
   ts TIMESTAMP
);
GO

CREATE INDEX ix1 ON dbo.BadTable(ts);
GO

CREATE PROCEDURE dbo.BadProcedure;1
@x IMAGE
AS
BEGIN
   SET ANSI_NULLS OFF;
   SELECT name FROM syscolumns;
   EXEC sp_addtype N'EMail', N'nvarchar(320)';
END
GO
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


Last Updated: 2016-07-08


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Thank you Boj and Sri for your feedback :-)


Friday, July 08, 2016 - 5:58:09 PM - Bojanna Muruvanda Back To Top

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


Friday, July 08, 2016 - 5:47:46 PM - Srinath Back To Top

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


Learn more about SQL Server tools