This week our team at BlueGranite decided that the sample relational database called ContosoRetailDW would be best located for our demo & training purposes in Azure as a SQL Database. Below are the steps taken to get this sample database in a state where it could be deployed as an Azure SQL DB. I will list my process from beginning to end, although I won't bother detailing out all of the early steps.

Download the Sample Database

First, I downloaded the Contoso database backup file, extracted it, and restored the backup to a SQL Server 2014 instance. Then I verified it can be queried in SQL Server Management Studio.

Azure Management Portal

In the Azure management portal, I created a small Azure SQL DB just for testing purposes. Since this was the first Azure SQL DB created in this Azure subscription, I wanted to do a small test to obtain a server name, set the server firewall, and check connectivity.

You can find the server name on the Servers page. To set the server firewall, click the Manage button, and respond Yes to the first prompt.

Connect to the Azure server from SSMS for a quick verification of connectivity to the test database created as an Azure SQL DB.

Good. Now that I know I can connect ok to Azure, I'm ready to move onto the task at hand with migrating the Contoso sample database.

First Attempt to Deploy to Azure SQL DB from SSMS

From within SSMS, connect to the instance where the database to be migrated exists. Right-click the database > Tasks > Deploy Database to Windows Azure SQL Database.

Well, if the story ended here it wouldn't have any drama, right? With my first attempt I received a list of errors due to unsupported features. I initially tried to rectify them in SSMS but it's just not the best tool for the job so I'll skip that party of the story. Enter Visual Studio.

Removing Unsupported Features in Visual Studio

Open Visual Studio (SQL Server Data Tools). Connect to your SQL Server instance in SQL Server Object Explorer. We will need a project in order to alter the schema to remove unsupported features. So, right-click the Contoso database and choose Create New Project.

I named mine as shown below since it's purpose was to convert the original database schema to where it's supported in Azure SQL DB.

Now that we have a project that is based on the Contoso Retail DW database, the first thing to do is change the target platform to Microsoft Azure SQL Database. This is done in the project properties.

Now that the project understands its destination will be an Azure SQL DB, let's do a build and see what happens.

Same list of errors that I got previously in SSMS. However, now we're in a lot better position to address them quickly.

There's an MSDN blog entry which discusses a quicker way to address the issues. I went ahead and handled them individually. It didn't take very long at all.

Double-click on each error in the Error List and it'll take you to the DDL where the unsupported syntax exists.

One of our two main issues is data compression. That required the "WITH (DATA_COMPRESSION = PAGE) syntax to be removed from some of the tables.

Another one of the issues was extended properties which aren't supported in Azure SQL DB at this time. So, even though they are nice for documenting a database, I went ahead and removed the extended properties from each object.

Let's try another build (a rebuild actually, to make sure everything gets evaluated). And now that all of the errors are rectified we are happy and ready to move on.

Schema Comparison in Visual Studio

Now it's time to get the real database sync'd up with the changes made to the project schema. So let's create a schema comparison under Tools > SQL Server > New Schema Comparison.

For the left side I used the project (i.e., the "fixed" one) and for the right side I used the database (i.e., the "wrong" one that needs to be "fixed").

Looking down the list, I see some deletes and some changes. They all look reasonable. Click on the Script button on the SqlSchemaCompare toolbar.

Now I have a script that I can run against the database to get its DDL updated. Scrolling down through the script, everything looks reasonable. Next step is to Connect to the database server using the icon on the toolbar.

Once connected, we can execute the script.

Mine took 18 minutes to run on a laptop.

Deploy from SSMS to Azure SQL Database

Now that the unsupported features have been addressed, let's try the deployment again. In SSMS, right-click the database > Tasks > Deploy Database to Windows Azure SQL Database.

We are presented with a wizard.

Connect to the server in Azure. The server name from Azure for a SQL DB is appended with ".database.windows.net". Be sure to use SQL Authentication.

I changed the maximum database size from the default of 1 GB to 5 GB, since my version of the Contoso Retail DW is about 3.9 GB.

You're presented with a summary page before choosing Finish. Double check your Source Database is less than the Maximum Database Size selected for the target with some room to spare. If the source is larger, it'll still deploy what it can until it runs out of space.