How to migrate a local database to SQL Azure

SQL Azure for Windows Azure and SQL Server have different architectures, which can be said to be two different products. SQL Azure does not fully support or does not yet support some features of SQL Server, which makes it impossible to migrate the database as usual with Bak file restores, or to use the Data Import Export Wizard. Many of the features of SQL Server are not supported in SQL Azure.

So how can we migrate an existing database to SQL Azure?

One, "Deploy database to SQL Azure" Wizard

　　The first thing I thought about was the wizard, the right-click on the database, the task, and select "Deploy Database to SQL Azure". Follow the Deployment Wizard step-by-step to finally get a list of such long errors:

SQL Azure does not support extended properties [ms_description] (field descriptions) for SQL Server, as shown in the error message. Modifying an existing database to delete all the field descriptions is obviously not feasible, even if possible, there will be new compatibility issues waiting for you. Using the Data Import Export Wizard also does not solve the problem perfectly.

Ii. Migrating the database schema using a DAC

So I tried to just migrate the schema with the DAC package, which is a data-tier application that uses the extract data-tier Application wizard in SQL Server Management Studio (SSMS) to package the database's schema information into a. dacpac file:

Then open ssms and use the SQL Azure deploy data-tier Application wizard to migrate the database schema to SQL Azure by selecting the. dacpac file just now.

However, the DAC package only contains schema definition information for the database and does not contain data, so it is only possible to migrate the schema. If the database has a huge amount of data, how to migrate it?

third, the use of SQLAZUREMW perfect implementation of database migration

SQLAZUREMW is a third-party database migration Tool that is a good solution for SQL Server to SQL Azure migration issues.

it resolves SQL Server and SQL Azure compatibility conflicts, generates SQL Azure-compatible scripts, and then remotely executes scripts to implement database migrations.

The tool: http://sqlazuremw.codeplex.com/

I take the database school as an example, it contains 1 million data and complex foreign key relations, constraints. It also contains 1 stored procedures, 1 views.

The steps are as follows:

1. Select Analyze and migrate/database

2. Select the source database and take the local database school as an example

3. Select the database object to build

4. Turn on advanced options to ensure that the "Generate Table/Data" option is "table structure and data"

5. Check the database object that will generate the script

6. Script Generation succeeded

7. Link to the target database, I'll take the example of schoolazure on SQL Azure

8. Click Next to execute the script

9. Open SSMs to view the results, you can see that the tables, data, primary foreign key relationships, constraints, views, stored procedures are all in. Migration Success!

PostScript: Once the database is migrated to Azure, future backup and restore work is much simpler and is exported directly to the Windows Azure Management Portal. Bacpac package, which can be backed up or migrated locally.