Friday, January 15, 2016

Deploy on-premises database to Azure SQL database

This is simple step by step guide to deploy your on-premises (or even cloud) Microsoft SQL database to Azure SQL database service with data.  Simply, this is a data migration to Azure SQL.

You have to make sure you have an Azure tenant with an active subscription to proceed with the next few steps. I have downloaded and installed AdventureWorks2014 database in my own machine for the demonstration.

One more prerequisite, you need to have a SQL database server in Azure. Your server name should look be similar to

Open Microsoft SQL Server Management Studio, right click on the database, select “Tasks” and select “Deploy Database to Microsoft Azure SQL Database…”

After the introduction screen, you will be asked to set the deployment settings. There you must set the server connection, the new database name and a temporary location for the bacpac file.

Once you click on the Connect, to connect to the Azure database server, you will be asked to enter the credentials.

You will then be asked to add a firewall rule where you have to sign in and provide access permissions to your ip address (or subnet) to access the azure database server.

Once the above steps are done, you will be allowed to set the pricing tier for the server. This is bit of a tricky thing as there are DTUs for each tier. The resources and power of each service tier and performance level for Microsoft's Azure SQL cloud databases are expressed in terms of Database Throughput Units (DTUs). The available resources will vary based on the DTUs. So till we complete the data migration, it’s better to stay within the standard tier.

Providing the necessary information, you can proceed with the data migration.

Once completed, we can see the DTU consumption and you can use your preferred tool to connect to Azure SQL Database.

No comments: