Three methods for migrating databases to SQL azure

For many SQL azure users, the first step in using SQL Azure is to migrate existing databases to SQL azure. Next I will introduce you to three tools to help you migrate your database.

I. SQL Server migration assistant)

First, we will introduce the SQL Server migration assistant (ssma ). It has several versions for access, MySQL, Oracle, and Sybase respectively. Take SQL Server migration assistant 2008 for access v4.2 as an example.

1. Open the migration wizard (Migration wizard) from the File menu ).

2. In the create new project step, modify the migration to SQL azure. This is a key step, because the default option is SQL Server.

3. Then, add the Access database to the Wizard. After a few seconds, the wizard will list all tables and query objects in the ACCESS database for selection.

4. After selecting the object to be migrated, we need to provide the SQL azure server and Database Name and login information for the next step. Generally, you can create a blank database on your SQL azure server.
 

5. Finally, the core of the migration is started. In this step, the migration assistant first converts the objects (including tables, primary keys, foreign keys, and constraints) in access to the objects in SQL azure, create these objects in the SQL azure database, and then convert all data rows in the table and load them into the SQL azure database. For the process of converting objects, the migration assistant generates a detailed report. We can click the report button to browse.

 

2. Data Layer applications Program (Data-tier application, DAC)

With the data-tier Application (DAC), we can extract the schema of the database in SQL Server and easily deploy the schema to SQL azure. Data-layer application DAC has been integrated into SQL Server Management Studio (2008 R2) and Visual Studio 2010. In front of this, DAC can only extract and deploy the schema, but cannot extract or deploy the data rows in the table. In the future, DAC will add this feature.

DAC is suitable for developing database-based software, especially when Visual Studio and Management studio are integrated.

In this article, we take the DAC in management studio as an example to introduce the data layer application extraction and deployment.

1. Start the wizard for extracting data layer applications. You only need to right-click an SQL Server database in object explorer, select "tasks" in the pop-up menu, and then select "extract data-tier application ". In this way, the wizard for extracting the data layer application pops up.

.

2. on the set properties page in this wizard, we can set the name of the application and the path of the DAC package.

 

3. Go to the validation and summary page. If the selected database in step 1 contains features not supported by DAC, an error is returned.
 

4. Go to the build package page to generate the DAC package. Next we will deploy the generated DAC package to the SQL azure server.

 

5. In object explorer of Management studio, right-click an SQL azure server. In the displayed menu, select deploy data-tier application ).
 

6. In the displayed "deploy data-tier Application" Wizard, go to the "select package" page. Here, we select the generated DAC package.

 

7. On the subsequent "Update Configuration" page, we can modify the name of the target database deployed this time.

8. On the summary page, you can see the summary of this deployment.

9. Finally, on the "deploy DAC" page, the wizard deploys the DAC package to the SQL azure server and registers the metadata of the DAC package on the server.

Iii. Script generation wizard (generate script wizard, GSW)

The principle of using the script generation Wizard to migrate the database is to export all the schemas and data rows of a database to scripts of transact-SQL, and then execute these scripts in the SQL database. Specifically, there are the following steps.

1. In object explorer of Management studio, right-click an SQL Server database. In the pop-up menu, select "Tasks", and then select "generate scripts" in the expanded menu ".

2. The generate and publish scripts wizard is displayed, that is, the generate and publish script wizard. On the introduction page of the wizard, click Next.

3. On the "choose objects" page, you can select the tables to be migrated, stored procedures, and user-defined data types) and schemas.

4. On the "set scripting options" page, click the "advanced" button to change the advanced options.

5. We need to change the two advanced options. One is to change "script for the database engine type" to "SQL azure Database ". This is because we want to migrate this database to SQL azure.

6. Change "types of data to script" to "schema and Data" to migrate the data mode with the data row.

7. Then, we can escape the "summary" Page and go to the "Save or publish scripts" page. Here GSW will write the transcript of the database object we selected into a T-SQL script file.

8. Now we can create a new database on the SQL azure server, and then execute the generated T-SQL script in this database through the script window of Management studio. This completes the database migration.