Friday, August 2, 2013

What is the Easiest way to copy a database to/from SQL Azure?

The answer: use SQL Server 2012

Moving a SQL database to and from your PC to SQL Azure used to be a horrendous nightmare.  With the new functionality in SQL Server 2012 and the Azure portal, this is now a breeze with just a few clicks.  You are not required to learn or understand data-tier applications or BACPAC, the underlying method used in the import/export.

This article assumes you already have an Azure subscription up and running.

The functionality in SQL Server is actually the SQL Server Data Tools. However, it is totally invisible once it is installed. Download it here.

Deploying An On-Premise SQL Database to SQL Azure 


Let's start with moving a database from your PC to Azure.

First of all, your on-premise database can use only those features supported by SQL Azure.

To deploy a database in SQL Server 2012 is pretty easy and fast.  In SSMS Object Explorer, right click your database and select Deploy Database to Sql Azure... under Tasks:

Figure 1 - Deploy to SQL Azure

After skipping the first page of the deployment wizard, you come to Deployment Settings.  Click Connect... and you will see the standard SSMS database Connect to Server dialog.  Enter your Azure SQL Server name (somedatabaseserver.database.windows.net), the Login has to be master, and the New database name has to be a new database on your Azure server.

Figure 2 Connect to SQL Azure

Next, very importantly, you must go to the Connection Properties tab and ensure that the database connected to is MASTER.  If you have been using your SSMS to connect to other databases on the Azure server, this setting might have defaulted to something else.

Figure 3 Must connect to MASTER database

Unless you are a totally newbie using SQL Azure, you would already have configured your Azure firewall to allow connections from your PC's IP address.  If you don't know what this is, read this.

After connecting, the wizard then does everything for you, and gives you the typical SSMS report card on the successful (and failed) steps that have taken place.

Storage Account/Container


Copying a SQL Azure database to your PC requires you to first export the database to a location your PC can access.  This holding area used is Azure Storage, another of the growing list of Azure services.  If you are already familiar with Azure Storage, you can skip this section.

In the Azure Management Portal, click STORAGE on the left menu.

Figure 4 Azure Storage

If you don't have an existing Storage account, you have to click CREATE STORAGE ACCOUNT next. You have to supply a globally unique Url to identify your Storage account.

Figure 5 - Create a new Storage account
Azure Storage uses this organization hierarchy:
  1. An Azure subscription can have 0 or more Storage accounts.
  2. A Storage account can contain 0 or more Containers.
  3. A Container holds files.
With a Storage account, you can now select it and create a Container (if you don't already have one) to hold your SQL files needed for the transfer.  The Azure Portal provides intuitive menus and options for you to create your container.  You should use a Container that is located in the same data center as your SQL Azure database to avoid clocking traffic.

While at the portal in the Storage section, click Manage Access Keys at the bottom and copy the PRIMARY KEY.  Your local SQL Server will need this later to access the Storage container.

Figure 6 - Storage Account Keys

Copying a SQL Azure Database to Your PC


First you have to Export your database to a Storage Container.  The Container can be in any Storage Account of any subscription of which you are the administrator.

From the Azure Portal with your database in focus, click Export at the bottom of the screen.
Figure 7 Exporting a SQL Azure Database

Remember the bacpac file name to which you have exported.

Now, go to your PC's SSMS Object Explorer, and in the destination server's Databases node, right click and select Import Data-tier Application....

After the wizard introduction page, in the Import Settings dialog, select Import from Windows Azure and click Connect...
Figure 8 Importing a SQL Azure Database

If you paste in the correct Storage Account key (see previous section), you will be able to connect and select the Storage Container and the exported bacpac file by selecting from the drop down lists.

The wizard will then step you through to import the database into your on-premise SQL Server.

It's that easy!