SQL Server 2014 Management Studio provides a wizard to deploy your on-premise database to a Windows Azure SQL Database server. This wizard can be used to export your on-premise database into a .bacpac file and upload it to your Azure SQL Database server. This wizard will help you to deploy your database to Windows Azure SQL Database. You may also use this wizard to deploy a Windows Azure SQL Database to a local instance of SQL Server, or to move a database from one instance of Windows Azure SQL Database to another. One advantage is that the wizard does a pre-validation check to determine if any unsupported object is present in the database which is not supported on Azure SQL Database.
This wizard can be used to deploy a database between an instance of the on-premise Database Engine and a Azure SQL Database server, or between two Azure SQL Database servers. An instance of the Database Engine must be running SQL Server 2005 Service Pack 4 (SP4) or later to work with the wizard. If a database on an instance of the Database Engine contains objects not supported on Azure SQL Database, you cannot use the wizard to deploy the database to Azure SQL Database. If a database on Azure SQL Database contains objects not supported by SQL Server, you cannot use the wizard to deploy the database to instances of SQL Server.
The first step is to launch the wizard. This is done by right clicking on a database and selecting Tasks and Deploy Database to Windows Azure SQL Database (see Screenshot 1).
In the Deployment Settings page (see Screenshot 2), you will need to provide the connection to your Azure SQL Database server which would be of the form <alpha numeric name>.database.windows.net. Before you are able to connect, you will need to an exception to the firewall from the Azure management portal to ensure that your on-premise machine is able to connect to your Azure SQL Database server. I had explained about this in a previous post.
Once you have connected to the database, you will need to specify the Azure SQL Database settings like the edition and maximum size. Ensure that you do this correctly because you are billed for your database usage on Azure.
Additionally, you will need to provide a temporary file name which will be the .bacpac file. In case you want to change the database name, you can choose to do so as well.
Once this is done, you are done with the Wizard and it will do it’s magic to export the database into a .bacpac file and import the same into an Azure SQL Database created with the same name as the one provided in the wizard.
The heavy lifting for all this activity is done by DLLs the present in C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin folder. If you want to automate this activity using command line operations, then one option is to use the SqlPackage utility which is available in the same folder.
This can be done using the following commands:
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Export /SourceServerName:<server name> /SourceDatabaseName:AzureTest /TargetFile:"G:\Tempdb\Azure\AzureTest_SqlPackageExport.bacpac"
The command line output is shown below:
Now that we have the exported file, we need to send to the Azure SQL Database server. This can be done using the command below:
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /TargetServerName:<server name>.database.windows.net /TargetDatabaseName:AzureTest2 /TargetUser:troubleshootingsql /TargetPassword:<password> /SourceFile:"G:\Tempdb\Azure\AzureTest_SqlPackageExport.bacpac"
The output of the command is shown in the screenshot below which is similar to what you will see in the Management Studio UI:
To summarize the long post, we saw two ways of sending your on-premise database to an Azure SQL Database server using:
a. SQL Server Management Studio
b. SqlPackage utility
Deploy a Database By Using a DAC