Windows Azure – Migration of on premise SQL Server 2014 database to Azure – Part 2

In my previous post (On-Premise SQL Server Database Migration to Windows Azure), I had detailed migration of the database by connecting directly from SSMS (supported by SQL Server 2012 and 2014). Today, I will go through alternate migration method using data-tier application files (.dacpac files. For additional reference click here )

Prerequisite: This article also refers to usage of Blobs container in Azure Storage account. The steps explained here requires you to have an Azure Storage account configured with valid access keys for connectivity. If you aren’t familiar with Azure Storage account, go through my post (Windows Azure – Configuring Storage Accounts)

There are two stages in the migration. The first stage is to generate the .bacpac file of your target on-premise database for migration. The option in SQL Server 2014 is shown below:



For the destination, you have to connect to your Azure Storage account. Establish the connectivity using your Account Key.


Once connected to your Azure storage, select the container (here, I have selected my Blob Container )


Few moments of waiting; and your .bacpac is uploaded to cloud storage.


The second stage is performed at Azure portal. Login to your azure account and select the SQL database server, and select the option “Import Database”


Select the Azure Storage and the container  as shown below


Select the .bacpac file


Specify the Pricing tier desired, database name and user account for login


Few moments of waiting… your database is getting created on the target server.


And the success notification. The database is online !


For verifying the database, let us try connecting from your local SSMS 2014 to new database in Azure and probably fire up some queries & procedures.


Whohaa ! All looking good here


For connectivity from your application, remember to use the connection strings as specified in Azure DB server. For example, connection strings format for this Sales DB to be used in your applications are specified below:-


Server=tcp:{servername},1433;Database=SalesDB;User ID={username_here}@{servername_here};Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;





Windows Azure – Configure Firewall for SQL Server Connectivity

Facing connectivity issues while connecting from client to Azure SQL Server database? Security settings in Windows Azure mandates that the firewall must be configured with a range of IP addresses to allow connectivity to server. The steps below demonstrates how to set this from Azure portal.

Login to your Azure account at

SQL databases ->  Select your database -> Settings


Under Firewall settings, you can specify specific IP addresses or range of IP address from which you want to allow connections to your SQL server database. Even though the time delay to effect this is specified as 5 minutes, most of the time the changes are reflected instantly.



Windows Azure – Migration of on premise SQL Server 2014 database to Azure

While planning to move your existing applications to Azure, one of the key considerations is how to move your existing on premise SQL server database to cloud. There is more than one way to accomplish this based on the specific requirement. For example, if you cannot afford down time between migration, the way forward is to use transactional replication setting your on premise database as publisher and your Azure SQL database as subscriber.

If real time migration isn’t a concern, you can go for exporting export your data (as .bacpac file that encapsulates a database’s schema and data) and importing it in your Azure database. Another solution is to deploy your on premise database directly to Azure as described below.

The feature to deploy database from SSMS to Azure was introduced in SQL Server 2012 and is available in SQL Server 2014 as well.

Deploy Database to Windows Azure SQL database


Note that the firewall settings for your Azure database server should be allowed to accept connections from your client IP.


The deployment wizard will let you to specify the connection and migration options as below.



Click on Finish to let the deployment/migration to complete