Wednesday, October 8, 2014

How To: Export a SQL Server Database to Windows Azure

If you are beginning to work with Windows Azure and are ready to deploy an application or service, you may begin to wonder how to export that existing backend SQL Server database as well. 

The good news is it's quite trivial to do by using SQL Server Management Studio and the Windows Azure Management Portal. For this example I'm going to use local my 'BowlingStats' SQL Database that is used with my BowlingSPA application to export to Azure.

  • Obtain a Windows Azure Account

1. Create a Storage Account

Once logged onto the Azure Management Portal, select 'Storage' from the options on the left, and then from 'Data Services' -> 'Storage,' select the 'Quick Create' option. Enter a URL for the name of your storage account as well as a location and replication strategy. Normally Azure will pre-populate with the best default options but you can change them if desired.

You will see a message once the account has been successfully created:

Upon creating the account you will be presented with a screen containing a 'Primary' and 'Secondary' set of access keys for the Storage Account you just created. Store these keys as they will be needed to connect to Azure Storage later from SQL Server.

Don't worry if you quickly dismissed the dialog with the keys. You can always get back to them by selecting 'Manage Access Keys' at the bottom of the 'Storage' Azure option. You can also regenerate the keys if they have been compromised.

2. Export your SQL Database as a .bacpac file directly to Azure

Now that we have a storage account, we need to hop over to SQL Server Management Studio 2012 and export our database as a .bacpac file to Azure.

Right-click the database and select, 'Tasks' -> 'Export Data-tier Application...'

After selecting 'Next' from the Introduction screen, select the 'Save to Windows Azure' option and then press 'Connect'. Here you can enter the name of your Azure Storage Account created in step 1, as well as the 'Primary' key value provided when the Storage Account was created. Press the 'Connect' button once the information has been entered.

After the connect dialog has been dismissed add a 'Container' name that will contain the .bacpac file on Azure. Once the information is all correct, press the 'Next' button and being the export of data from your SQL Server database directly to Windows Azure! 

Once successfully exported, the bacpac file will be available for import back on the Azure Management Portal in storage.

3. Import your uploaded bacpac to a SQL Database on Azure

Finally, let's go back to the Windows Azure Management Portal and import the SQL Database bacpac file. Select 'SQL Databases' from the options on the left, and then from 'Data Services' -> 'SQL Database,' select the 'Import' option.

The next dialog will allow you to choose the database settings. To select the bacpac we already uploaded, press the folder icon to browse to the available data files in storage.

Expand your storage account and you should see the container name set when the .bacpac file was exported from SQL Server. Select the file and press 'Open' to automatically populate the bacpac URL.

Enter a name for the database, select your subscription, service tier, (do not select 'Web' or 'Business' from the screenshot as they will eventually be retired), performance, max size, and server. If you have previously created a SQL Server instance, you can choose it and provide the login information. If this is the 1st time, select  'New SQL database server' and press the next button. 

You will now need to create the SQL Server instance login information. Note that while sometimes Azure appears to be smart and pre-populate options (i.e. Region') with the one closest to you, I did not find this to be the case with this dialog. I believe 'East Asia' was pre-selected and provided me a warning that the storage account and database were not in the same region. Make sure to switch it to the same value as your storage account and the warning will be dismissed. Once you enter your login credentials, press the accept button to complete the process.

That's it! Your new SQL Database and instance once provisioned will display under 'SQL Databases' in Windows Azure for all of your cloud application and service needs.

1 comment: