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 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 the file to Azure SQL Server. But our dreams were not brought to life by Microsoft. There is no possibility of database restoring from the file in the Azure Server Management console. So we had to find other workflows to tire up our project database on the cloud SQL Server.
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
2. Create new database on the Azure server
3. Run the script, using Azure Management Portal and
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: https://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 from local MS SQL Server to Azure SQL.
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: https://msdn.microsoft.com/en-us/library/hh324978.aspx.
First, we have tried to create the Data-Tier Application using Visual Studio. There were a 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 the DAC package using a simple export wizard.
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: https://wapmmc.codeplex.com
Windows Azure 1.4 SDK is a pre-requirement for it. https://azure.microsoft.com/en-us/downloads/
After DAC Package Upload you should create DAC Import Task. Fill the data into the form and
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.
Finally and unexpectedly the easiest solution for database structure deployment was found:
2. Choose the Deploy Data-Tier Application option
3. Select the DAC package from the local disk (no need to upload it 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 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 a drag-and-drop user interface.
It is very easy to use the 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 a table that 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 blogs, image fields, etc. Also, take into account the large size of the script in case of a huge amount of data.
- 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 the 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, etc. Remember, you could always turn off the sync if you don’t need it anymore.
In 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 the 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.
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
