There are many ways to migrate an on premise SQL Server database to Azure, for this article I want to show you how to import a BACPAC file and create a new Azure SQL Database, which is Microsoft’s Platform as a Service (PaaS) offering.
The following image is taken from BOL (Books on Line) at https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate. We will be completing the left route of the diagram.
To import a SQL Server database to Azure you will need the following:
- An Azure subscription.
- A .bacpac file (BACPAC) of the database you want to import. The BACPAC ultimately needs to be in an Azure storage blob container.
- The database that you want to migrate to Azure must be “Azure ready”. By this I mean that the database must be compatible.
At the time of writing this article there is currently a small set of features that are still not supported. More information can be found at this link: https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information
Creating a BACPAC
On your on premise database that you want to migrate to Azure, you will need to right click on it and work your way to the Export Data-Tier Application option. This is shown in the below screen shot.
The aim here is to actually create the BACPAC straight into an Azure storage container. To successfully do this you will need your Azure storage account details along with your storage key.
Once you have filled in the relevant information relating to your Azure account and clicked connect you will then be able to work through the rest of the wizard.
Once completed, Azure will now be holding your bacpac file. As you can see from the below image, my bacpac is called CloudApp.bacpac, which is located within my azuresqldatabasedisks container.
The Storage Account
Even though the bacpac file is within the storage container, for the purpose of this article I want to confirm its existence. To do this I will need to navigate to the storage account. The below image shows the Azure portal side navigation bar. For this example I will need to navigate to the Storage accounts (classic) section.
Then I select the Blob service then the storage container, which I have circled below in blue.
Now that we have confirmed the existence of the file, we will now use it to create our Azure SQL Database.
Creating an Azure SQL Database
Via the main navigation menu, you need to select SQL Servers. On the server you want to create the database, you will need to select the import database option as circled in red below.
Once you select this option you should then find the bacpac file that you wish to import. Naturally, for this article it is the CloudApp.bacpac file. Then the Azure portal will then prompt you to fill in the main screen.
This includes information such as pricing tier, name, SQL collation, username and password setup.
Once the main screen has been populated and you click submit the database import will take place. Below shows a screen shot on the progress details.
The completion of this import means that you have migrated successfully an on premise SQL database to Azure. To confirm this fact other further checks can be carried out. You could use a local SSMS (SQL Server Management Studio) to connect to your Azure account as indicated by the below screen shot or simply just check the portal as shown by the last screen shot.
I hope this write up has given you a more clear understanding on one of the possible techniques to move an on premise SQL database to Azure.