Three ways to migrate databases to SQL Azure: Alibaba Cloud Developer Community

For many SQL Azure users, the first step in using SQL Azure is to migrate existing databases to SQL Azure. The following describes three tools to help you migrate databases.

1. SQL Server Migration Assistant (SQL Server Migration Assistant)

first, SQL Server Migration Assistant (SQL Server Migration Assistant,SSMA) is introduced. It has several versions for Access, MySQL, Oracle, and Sybase. Here we 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 step of "Create a New Project" (Create New Project), change the Migration To (Migration To) To SQL Azure. This is a key step to note 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, queries, and other objects in the Access database for selection.   

4. After selecting the objects to be migrated, we need to provide SQL Azure server and database names and logon information. In general, we can create a blank database on our SQL Azure server.

5. Finally, the core of the migration starts. In this step, the Migration Assistant first converts the objects in Access (including tables, primary keys, foreign keys, and constraints) into the corresponding objects in the SQL Azure, and create these objects in the SQL Azure database, then convert all the data rows in the table and load them SQL Azure the database. The Migration Assistant generates a detailed report on the process of converting objects. We can click the Report button to browse.

2. Data layer applications (Data-tier Application,DAC)

through the Data-tier Application (DAC) Application, we can extract the Schema of the database in the SQL Server and easily deploy the Schema to the SQL Azure. The 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 schemas, but cannot extract and deploy data rows in tables. In the future, DAC will add this function.

DAC is suitable for database-based software development, especially when we integrate Visual Studio and Management Studio.

In this article, we take DAC in Management Studio as an example to introduce the extraction and deployment of data layer applications.

1. Start the wizard to extract data layer applications. Right-click a Object Explorer database in the SQL Server, select Tasks from the pop-up menu, and then select Extract Data-tier Application ". In this way, the Wizard for extracting data-layer applications pops up.

.

2. In the Set Properties (settings) page of the wizard, you can Set the name of the application and the path of the DAC Package.

3. Next, go to the Validation and Summary (verification and Summary) page. If the database selected in step 1 has features that are not supported by DAC, an error is reported in this step.  

4. Go to the Build Package page to generate a DAC Package. Next, we will deploy the generated DAC package to SQL Azure server.

5. In the Management Studio Object Explorer, right-click a SQL Azure server. In the displayed menu, select Deploy Data-tier Application (Deploy Data layer applications).  

6. In the Deploy Data-tier Application wizard that appears, enter the Select Package page. Here, we select the newly generated DAC package.

7. In the following "Update Configuration" page, we can modify the name of the target database for this deployment.

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

9. Finally, on the "Deploy DAC" page, the wizard will Deploy the DAC package to SQL Azure server and register the metadata of the DAC package on the server.

3. Script generation Wizard (Generate Script Wizard,GSW)

the principle of using the script generation wizard to migrate databases is to export all schemas and data rows of a database to Transact-SQL scripts using GSW, and then run these scripts in the SQL database. Specifically, follow these steps.

1. In the Management Studio of the Object Explorer, right-click the database of a SQL Server. In the displayed menu, select Tasks, and then select Generate Scripts from the displayed menu ".

2. The Generate and Publish Scripts Wizard appears, which 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 Tables, Stored Procedures, User-Defined Data Types, and Schemas to be migrated.

4. On the "Set Scripting Options" page, we need to click the "Advanced" button to change the Advanced Options.

5. We need to change 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. The second is to change "Types of data to script" to "Schema and data" to migrate data patterns and data rows together.

7. Then, we can avoid the "Summary" page and come to the "Save or Publish Scripts" page. Here, GSW will transcribe the database object we selected as a T-SQL script file.

8. Now, we can create a new database on the SQL Azure server, and then run the Management Studio script in the database through the T-SQL Script window. This completes the database migration.

This article is forwarded from liudao blog, original link: http://www.cnblogs.com/liudao/archive/2012/11/12/2766323.html,如需转载请自行联系原作者