azure sql. data and structure migration road map
DESCRIPTION
Microsoft describes Azure SQL as an ordinary MS SQL Server hosted in the cloud. If it’s true, all the functionality of the standalone MS SQL Server should be available on Azure SQL. Let’s investigate the functionality of Azure SQL based on the first task which should be solved on the Cloud Project Lifecycle.TRANSCRIPT
©2012 by EliNext Group
Azure SQL. Data and Structure Migration Road Map
Microsoft describes
Azure SQL as an
ordinary MS SQL Server
hosted in the cloud. If
it’s true, all the
functionality of the
standalone MS SQL Server should be available on Azure
SQL. Let’s investigate the functionality of Azure SQL based
on the first task which should be solved on the Cloud Project
Lifecycle. We’ll make an assumption, that we already have a
standalone application that will be migrated to the cloud. And
we have a database (we will describe the methods of
migration of database structure and database with data
separately) and we need to move it to the cloud server.
When we discussed this problem in our developers’ team, all of us
©2012 by EliNext Group
had the same vision of data migration (which is available in a usual
MS SQL Server). We make a backup of our local database, upload
this file using the cloud management console and restore the database
from file to Azure SQL Server. But our dreams were not brought to
life by Microsoft. There is no possibility of database restoring from
file in the Azure Server Management console. So we had to find the
other workflow to tire up our project database on the cloud SQL
Server.
©2012 by EliNext Group
©2012 by EliNext Group
The next method of data movement that we’ve tried was database
structure serializing to SQL initial script:
1. First of all we create an SQL Script using the Object explorer >>
Database >> Tasks >> Generate Script
©2012 by EliNext Group
2. Create new database on the Azure server
3. Run the script, using Azure Management Portal and …
©2012 by EliNext Group
get a lot of errors like: ‘Keyword or statement option 'pad_index'
is not supported in this version of SQL Server’.
We deleted the first part of them and got another list of errors. After
that we finally found full Azure SQL Specification on the MSDN:
http://msdn.microsoft.com/en-us/library/ee336281.aspx.
As you can see from the link, there are a lot of functions that are
partially supported or not supported at all. You should manually
change the initial SQL Script to fit SQL Azure Syntax before applying
it.
So we’ve continued searching for an easier way to move database
structure and data for local MS SQL Server to Azure SQL.
©2012 by EliNext Group
In the Azure Server documentation, I’ve found information about the
Data Tier Application (DAC). It is an application that could be
extracted from the real database structure (or constructed using Visual
Studio), uploaded to Azure BLOB Storage and imported to Azure
SQL. Full Specification of Data Tier Application you can find in
MSDN: http://msdn.microsoft.com/en-us/library/hh324978.aspx.
First we have tried to create the Data Tier Application using Visual
Studio. There were few errors that were easy to fix. And the Data Tier
Application was build successfully.
Another way to create it is to use SQL Server Management Studio
2008 R2. You could easily extract DAC package using a simple
export wizard.
©2012 by EliNext Group
After creating the DAC there is a problem with uploading files to
Azure Storage. Azure Storage doesn’t have an upload form for files.
To do this we’ve used Windows Azure Platform Management Tool
that could be downloaded from Codeplex:
http://wapmmc.codeplex.com
Windows Azure 1.4 SDK is a prerequirement for it.
http://www.microsoft.com/download/en/details.aspx?id=15658
After DAC Package Upload you should create DAC Import Task. Fill
the data to the form and …
©2012 by EliNext Group
view the status of DAC import on the status screen. As you can see,
both Visual Studio 2010 and MS SQL Management Studio 2008 R2
DAC Packages import failed because of the DAC file format.
©2012 by EliNext Group
Finally and unexpectedly the easiest solution for database structure
deployment was found:
1. You should login to the Azure SQL Server Management Console
without specifying the Database Name
2. Choose the Deploy Data Tier Application option
3. Select the DAC package from the local disk (no need to upload it
©2012 by EliNext Group
to the storage!)
4. Choose the database name and other options
5. Click Submit and in a few moments you’ll get the database you
need on Azure Server
WARNING: We got good results only with the DAC made on MS
SQL Server 2008R2. Visual Studio DAC file gave us a syntax
©2012 by EliNext Group
error on import.
So we have an empty database and we need to move data to it.
The first possibility of data migration we’ve found is SQL Server
Integration Services (SSIS). Using a visual development tool based
on Microsoft Visual Studio called the SQL Server Business
Intelligence Development Studio (BIDS). It allows users to edit SSIS
packages using drag-and-drop user interface.
©2012 by EliNext Group
It is a very easy to use tool, but we’ve got a problem using it. The
target database (on Azure SQL Server) should not have the primary
keys set on the tables, because data is migrated using all the columns.
And in case of migration to table which has a primary key set we will
get the Primary Key Violation Error. So this way of migration is
good, but not ideal.
Here are some other variants:
• Use a third party tool to create the SQL script with INERT
Statements and run it on the Azure SQL side. However, you
might experience problems with blog, image fields, etc. Also
take into account the large size of the script in case of huge
amount of data .
©2012 by EliNext Group
• Write a custom Data Migration Tool. We could easily connect
to the remote Azure SQL and push the data to it from our
application.
• You could also use Data Export/Import Tool from the
Management Studio. But there is also a problem. You cannot
connect to the Remote Azure server like to a usual MS SQL,
you will have to use .Net Framework Data Provider. This could
cause migration problems, because it doesn’t provide full data
migration functionality and errors can appear during migration.
• You could also use the Data Sync service, provided by Azure
Management Portal. You will need to have a special Agent
installed on the source MS SQL Server; and be careful to
configure the source and destination servers, sync directions,
©2012 by EliNext Group
etc. Remember, you could always turn off the sync if you don’t
need it anymore.
As a conclusion, I’d like to say that for now Azure SQL doesn’t have a
common (standard) way for simultaneous structure & data migration.
So, you will have to use some tricks to migrate them. In case of
structure migration the most convenient way is Data Tier
Deployment. However, the way of data migration should be chosen by
you. All the ways described above have both positive and negative
sides. And you have to think which of them will resolve all your
project’s needs in Azure SQL database deployment from scratch.
©2012 by EliNext Group
Industries and Technology Areas:
Industries: software development
Technology Areas: SQL Azure, Microsoft SQL Server, MS SQL
Server, Windows Azure Platform, Windows Azure, database
development, cloud computing
©2012 by EliNext Group
ELINEXT GROUP
Website: http://www.elinext.com
Email: [email protected]
Blog: http://software-topics.com
Facebook: http://www.facebook.com/elinext
Twitter: https://twitter.com/elinext
Delicious: http://delicious.com/elinextgroup
Ireland officeMarina House, Adelphi QuayWaterford, Republic of Ireland
Phone: +353 (51) 347 477
Belarus Development Center 155b Bogdanovich St.220040 Minsk, BelarusPhone: +375 (17) 237 53 65
Vietnam Development Center37A Phan Xich Long St.Ward 3, Phu Nhuan DistrictHo Chi Minh City, VietnamPhone: +84 (8) 3995 6849
Suggested citation: EliNext Group.2012. “Azure SQL. Data and Structure Migration Road Map”<http://www.elinext.com/azure-sql-data-and-structure-migration-road-map>