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!

Comments

Post a Comment

Popular posts from this blog

Attack Lab - Level 5 explained

Image
Ning Wang has published a very good write-up (2022 update: web page is gone now) on his solutions to the Attack Lab from the CS:APP book. However, I thought his solution to Level 5 could do with a bit more elaboration for those who are struggling with this topic. First, it is necessary to understand what ROP requires. An essential component of ROP is good luck, lots of it. Without getting lucky, you may take more than 10 100  years to find a workable attack, if at all. This, the authors of CS:APP did not mention. I also could not comprehend the purpose this exercise is even given out as homework in CMU to be completed in a week (while there are tons of homework from other courses too), without an intensive course in Intel Assembler as a prerequisite. Level 5 requires the same general logic as Level 3, which can be summarized as (pseudo code): mov <cookiestring address>, %rdi //why? because touch3 is expecting it there call touch3 To achieve the above for Level 5, ...
Read more

Assembly version, File version, Product version

Image
If you are a .NET developer, Assembly and File version would be familiar to you. They can be set in the Designer UI (project Properties page, Application tab, Assembly I nformation button...) which basically updates appropriate attributes in AssemblyInfo.cs. Now, there is a less well known number - the Product version . It is not well documented and most developers ignore it altogether with no side-effects most of the time. However, it is an important number and it seems that, of the three, Product version is the most widely used. The three version numbers simply take the values you give them or: a. If Assembly version is not explictly specified, it takes the value of 0.0.0.0. b. If File version is not explicitly specified, it takes the value of Assembly version. c. If Product version is not explicitly specified, it takes the value of File version. In Windows Explorer of Windows XP and Server 2003, you can see from the properties of a file all three version numbers. In W...
Read more

Setting to English for the Xiaomi Wifi Router

Image
The Xiaomi Mi Wifi Router now is in English. If it boots up to a foreign language for you, this guide will help you to change it to English. It is best to connect to it via wired Ethernet for the first time. Otherwise, the default unsecured wifi SSID should be Xiaomi_C3C1 . Your IP should be in the subnet 192.168.31.x, and the router is at http://miwifi.com which the router will resolve to 192.168.31.1. The first screen you see will be the setting up of the wifi SSID and wifi password. The big blue button at the bottom is the Next button. The next screen sets up the router administrator password (second field). The first field is Home/Work/Custom. Click the big blue button to complete. At the main configuration page, look for the third item (Settings) on the top menu, and the orange option (System Status): The last option on the page is the language selection: Open the drop down to select English: The change will take place the moment you click English. Enj...
Read more