Partager via

Move your SQL Server Databases to Windows Azure SQL

  • Article

Would you like to move SQL Server databases out of your datacenter and into a reliable, scalable, and secure solution? How about taking those databases that are rarely accessed or even idle and storing them somewhere else?

Windows Azure SQL Database can help you with solve those issues and more!

What is Windows Azure SQL Database?

Windows Azure SQL Database is a feature-rich, fully managed relational database service that offers a highly productive experience, incorporates proven SQL Server technology, and delivers business-class capabilities.

SQL Database allows customers to scale business apps for burst and global reach by removing the high costs of building an infrastructure which would accommodate occasional peak loads. Customers can also remove the security risks and hassles associated with hosting public-facing apps & websites from within a datacenter by quickly & cost-effectively building websites and mobile & social apps directly on SQL Database. Finally, customers with strict data policies can get the best of both worlds by keeping sensitive, policy-constrained data on-premises while extending parts of their application to the cloud for added innovation, scale and agility.

Source: https://www.windowsazure.com/en-US/services/data-management/

Let’s take a look at SQL server in Windows Azure and walk through how to upload a Windows SQL database into a Windows Azure SQL Database.

Compatibility:

  • SQL Server Management Studio 2012 (connecting to SQL Server instances)
  • Export from SQL 2000 Database Server and up

Other Prerequisites

To work with a DAC BACPAC, you must have installed the client DAC software, known as the DAC Framework. The DAC Framework is included with SQL Server Data Tools and the SQL Server utilities such as SQL Server Management Studio. When working with SQL Database, the recommended version of the DAC Framework to use is the one included in SQL Server Data Tools and SQL Server 2012. You can also upgrade earlier versions of the DAC framework by installing these three packages from the SQL Server 2012 Feature Pack:

  • Microsoft System CLR Types for Microsoft SQL Server 2012
  • Microsoft SQL Server 2012 Transact-SQL Script DOM
  • Microsoft SQL Server 2012 Data-tier Application Framework

For information about compatibility between versions of the DAC Framework and versions of SQL Server, see DAC Support For SQL Server Objects and Versions.

Source: https://msdn.microsoft.com/en-us/library/windowsazure/jj156148.aspx#LimitationsRestrictions

WALK-THROUGH

EXPORT A WINDOWS SQL DATABASE TO WINDOWS AZURE SQL DATABASE

In the Microsoft SQL Server 2012 Management Studio select and right click on the database you’d like to export:

clip_image002

 

Select “Tasks” the select “Deploy Database to SQL Azure… ”

clip_image004

Click next on the Welcome screen

On the Export Settings screen there are two options: Save to disk or save to a Windows Azure storage account (i.e. blob). For this walk-through I will choose “Save to Windows Azure” and select “Connect… ”

clip_image006

Note: If you chose to save to a local drive you’ll need to upload the .bacpac file to a Windows Azure storage account manually. For more information on how to upload data to Windows Azure please read my post on Windows Azure Storage there are a couple of free tools toward the bottom of the post that allow uploading data to Windows Azure fast and easy.

After selecting “Connect… ” you’ll need to populate some fields.

· Storage account: the name of your Windows Azure Storage Account.

· Account key: the access key associated with the Window Azure Storage Account.

clip_image007

LOCATING THE “STORAGE ACCOUNT” NAME AND “ACCOUNT KEY”

1. Navigate to https://manage.windowsazure.com/ and either log into your account or sign up for a trial account.

Note: If you’re new to Windows Azure and sign up for a trial account you’ll need to set up a storage account. Instructions may be found here: https://www.windowsazure.com/en-us/manage/services/storage/how-to-create-a-storage-account/

2. Once you’re logged into Windows Azure you’ll see a variety of options on the left hand navigation. Go ahead and select “STORAGE” from the list:

clip_image008

3. Select a storage account and then select “MANAGE ACCESS KEYS” at the bottom of the page:

clip_image010

4. You will see the following:

clip_image012

5. Copy and paste the STORAGE ACCOUNT NAME and PRIMARY ACCESS KEY from the Manage Access Keys page into the open fields and select “Connect”:

clip_image013

6. Once you’re connected to a Windows Azure storage account select a container and select “Next”:

clip_image015

7. Review the summary information and select “Finish”:

clip_image017

Once the export is complete review the summary of export tasks and select “Close”:

clip_image019

Congratulations, you’ve exported a database (.bacpac) from your SQL Server into a Windows Azure Storage account.

IMPORTING A .bacpac INTO A WINDOWS AZURE SQL DATABASE

First, let’s make sure the upload from the previous steps succeeded.

I’m using CloudXplorer to view my storage account:

clip_image021

Now that I verified the .bacpac was uploaded, let’s import the database into a Windows Azure SQL Database:

Navigate to https://manage.windowsazure.com/ and either log into your account.

Once you’re logged into Windows Azure you’ll see a variety of options on the left hand navigation. Go ahead and select SQL DATABASES from the list:

clip_image022

Select the “IMPORT” at the bottom of the page:

clip_image024

Select the .bacpac file from your storage account:

clip_image026

Navigate the storage account folder structure and select the .bacpac you uploaded in previous steps:

clip_image028

Select “Open”

· Type in a “NAME” or keep the default

· Select an existing “SERVER” or elect to create a new SQL database server.

· Fill in the login information

· Check the “CONFIGURE ADVANCED DATABASE SETTINGS” check box

clip_image030

 

On the “Advanced database settings” page there are two database options:

1. WEB: where you have database size limits of 1 GB and 5 GB

2. BUSINESS: where you have database size limits ranging from 10 GB – 150 GB

WEB

clip_image032

BUSINESS

clip_image034

I selected WEB 1 GB option. However you’ll need to size your database accordingly.

Note: if you need to expand the size of a database you can do this using the SCALE option in the Windows Azure database management portal.

Take a look at the status of the import at the bottom of the page:

clip_image036

The database will show up in your database list:

clip_image038

Select the database to view more information about it:

clip_image040

Congratulations, you’ve exported a SQL database from your SQL Server and imported it into Windows Azure!

Did you know you can export a SQL database from Windows Azure SQL as well? It’s available at the bottom of the Windows Azure SQL Database page.

ADDITIONAL RESOURCES

Here’s another tool that exports SQL Server databases and imports into Windows Azure SQL: https://sqlazuremw.codeplex.com/

 

Prefer command line options? https://social.technet.microsoft.com/wiki/contents/articles/2639.how-to-use-data-tier-application-import-and-export-with-windows-azure-sql-database-en-us.aspx

Comments

  • Anonymous
    January 01, 2003
    Excellent article.

  • Anonymous
    September 11, 2013
    Love the write up, Courtenay!  Will share w/ my network as well.

  • Anonymous
    November 05, 2014
    very simple writing but excellent

  • Anonymous
    March 17, 2015
    That is what I am looking for now, and will try for merge with local db to azure.