SQL Server 2012 Database Backups in Azure

SQL Server 2012 inlcudes new & updated features to SQL Server Management Studio (SSMS) that provide out-of-the-box integration into the Windows Azure platform-as-a-service Cloud service from Microsoft. It goes beyond just SQL Azure integration as you can now also connect into Windows Azure storage accounts where you can store files and blobs, including DACPAC & BACPAC files, essentially providing DBAs with out-of-the-box backup-to-cloud capabilities.

From a DBA’s perspective, this can be very beneficial because this would allow you to take your SQL Server backups and post them into the Azure cloud where files are automatically protected and replicated for high availability. This will also eliminate the need for you to maintain infrastructure for backups locally on your site. You would utilize Azure Storage for maintenance, retrieval and disaster recovery of your database backups. Here is a link with more details on Azure Storage.

Here are the steps to backup from SSMS 2012 to Windows Azure:

  1. First thing to note is that you will need to sign-up for a Windows Azure account and configure a storage container. You can click here for a free trial.
  2. Now, on SSMS, choose a database to backup. But instead of the normal Backup task, select “Export Data-Tier Application”. This is going to walk you through the process of exporting the schema with the data as a “BACPAC” file output.

3. On the next screen in the wizard, you will select the Azure storage account and container where SQL Server will store the export. Note that it will first backup the database (schema & data) to a local file and then upload it to the cloud for you.

4. Once the process is complete, you will see your exported backup as a BACPAC in your storage container. To restore a BACPAC, you right-click on the file from your container and select “Import Data-Tier Application”.

BTW, this process is identical to the way that you can export & import databases in SQL Azure. You can also easily now move your databases (schema and/or data) to and from SQL Server and SQL Azure with these BACPACs. This is not a full-service TLOG and data file backup like SQL Server native backups. This is more of a database export/import mechanism. But that is pretty much the most interactive that a DBA will get with SQL Azure anyway because you do not perform any TLOG maintenance on SQL Azure database. Microsoft takes care of that maintenance for you.

Nice New SQL Azure Integrations in SQL Server 2012

Have you downloaded the newest RTM (release to manufacturing) version of SQL Server 2012 yet? If not, download it here.

And if you are a big Cloud Computing enthusiast like I am, you will definitely want this latest version of SQL Server. The SQL Server 2012 RC0 is missing some of the newly-released integrations to Azure that I am going to highlight below. These are only available in the new RTM version of SQL Server 2012:

  1. Connect directly to your Azure Storage from the Azure Storage connector on SSMS Object Explorer. Here you can use Azure blob storage for your database backups to eliminate the need to use tape libraries or eliminate local storage for database backups. Azure Storage is automatically redundant with 3 copies of your data kept safe in Microsoft data centers.
  2. It is now super-easy to move database schemas from on-premises to Azure cloud in SQL Azure using the updated Data-Tier Application or DACPAC capabilities. And this function is also much less sensitive to database objects than in the previous SQL Server 2008 R2 first version of DAC along with ability to single-click deploy your database to SQL Azure.
  3. SQL Azure has introduced a new built-in programmatic capability to design scale-out OLTP SQL Server solutions using a sharding technology known as Federations. These are now manageable and visible natively in SSMS.