Two secrets: SQL Server migration to SQL azure

Windows or Web applications developed using vs. net Program We often use SQL Server for application development and deployment. In cloud computing application development, we can use vs 2010 to develop applications. For data storage, we can use SQL azure. Existing applications and databases can also be deployed in the cloud. This article describes how to migrate an existing SQL database to SQL azure.

There are two ways to migrate an existing SQL database to SQL azure: one is to use SQL database management tools (SQL Server Management studio, SSMs) to generate scripts; the second is to use the SQL azure migration wizard (SQL azure migration wizard, sqlazuremw), an open-source tool provided by Microsoft. We will introduce it separately below.

Use SSMs for Migration

One of the features of Microsoft's cloud computing platform for developers is that developers can fully use existing skills and experience to use familiar development languages and tools, this is also evidenced by the use of SQL database management tools for migration. The following describes how to use SSMs for migration.

Applications and Cases

In this case, we will use SSMs to migrate the instance database northwnd provided by Microsoft, and finally generate a database script that can be deployed on SQL azure. The detailed steps are as follows:

Step 1: Open SSMs, find the northwnd database, right-click task, and select generate scripts in the pop-up window ...), 1:

Figure 1 select database generation script

Step 2: In the pop-up SQL script wizard window, click "Next", the Select Database window appears, select the Microsoft instance database northwnd, click "Next", and select the script option, on the script option page, we need to configure several items in the script option. The items to be configured have been marked with red lines, as shown in Figure 2:

 

 
Figure 2 set export script options

(1) Table/view options need to be configured

Data Writing Script: Set the data writing script to false, because we cannot write data to the generated script.

(2) regular configuration

Write the use database Script: Set to false because SQL azure does not support it.

Compile an extension property Script: Set to false because SQL azure does not support extension properties.

Convert uddts to the base type: Set to true because SQL azure does not support custom data types. The data types that can be used are listed in the portal.

Step 3: select the object to generate the script. Here you can select tables, views, and stored procedures, as shown in Figure 3:

 

 
Figure 3 select a table

Step 4: After selecting the table, click "Next". SSMs automatically generates the script, which is very familiar to developers or DBAs. The generated script is shown in Window 4:

 

 
Figure 4 exported successfully

In Figure 4, 37 public projects are successfully generated, including all tables, views, and stored procedures of the northwnd. In the following content, we will also use this instance database, but the migration method is replaced with the SQL azure migration wizard tool.

Use sqlazuremw for Migration

Sqlazuremw is a Project released by Microsoft on codeplex. It can migrate SQL 2005 and SQL 2008 databases to SQL azure. The latest version is 3.3, which is:

Http://sqlazuremw.codeplex.com/releases/view/32334

Sqlazuremw detects and corrects the incompatibility between SQL Server 2005/2008 and SQL azure. Some common problems that can be fixed by the wizard include replacing the text column with varchar (max) and converting user-defined types to the base type. Convert the text column to varchar (max), convert the image to varbinary (max), and unbind the XML schema because it is not supported by SQL azure. Because SQL azure lacks support for hierarchyid, it must be done manually. In addition to generating scripts on SQL azure to deploy the schema for migration, you can also use this tool to migrate table data through BCP.

Applications and Cases

In this case, we will use sqlazuremw to migrate the instance database northwnd provided by Microsoft, and finally generate a database script that can be deployed on SQL azure. The detailed steps are as follows:

Step 1: Download sqlazuremw. After downloading sqlazuremw, decompress the package and the package contains four files. There are two configuration files in the mid-term. One is not supported by SQL Azure, and the other is the SQL azure data migration configuration file, as shown in Figure 5.

 
Figure 5 enable sqlazuremw

Step 2: Run sqlazuremw. Double-click sqlazuremw.exe, as shown in window 6 in Figure 6. We can see:

 

Sqlazuremw supports the following data migration types:

(1) SQL Server to SQL azure

(2) SQL azure to SQL Server

(3) SQL azure to SQL azure

Sqlazuremw supports the following data migration functions:

(1) only analysis Database

(2) analyze and migrate Databases

(3) migrate databases only without Analysis

(4) direct operation of T-SQL for analysis

Here we chose to analyze and migrate the database | SQL database, and click "Next", as shown in 7:
 

 
Figure 7 connecting to a database

Step 3: connect to the database. Click Connect to server. In the displayed window, enter the name, user name, and password of the database to be connected, and click Connect. interface 8 after successful connection:

 
Figure 8 select a database

Step 4: select a database. After the connection is successful, select the database. Here we select the sample database northwnd and click Next, as shown in 9: 

 
Figure 9 selecting database object scripts

 

 

Step 5: select the generated step object. Here we select all the objects in the database to generate the script, and click Next, as shown in 10:
 

  
Figure 10 Export results

Step 6: Save the script file. In figure 10, we generated the backup file of the script, and the BCP command is called here. Due to the language version problem, some garbled characters appear in figure 10. However, we can still see that the generation is complete. Switch to the SQL script tab, as shown in Figure 11:

Figure 11 generated SQL script

In Figure 11, click "save" to save the generated script as a file. In this way, we get an SQL script that can be deployed on SQL azure. Our migration has been completed by 80%, and the rest is to execute this script on our cloud SQL azure.

Note that SQL Azure is built on SQL Server 2008 and its compatibility level reaches 100. Databases running at a lower compatibility level such as 80 or 90 may have problems. Although the migration tool can capture compatibility issues, we strongly recommend that you increase the database Compatibility Level to 100 before using the tool.

Whether using SSMs or sqlazuremw, migrating existing databases to SQL Azure is not as difficult as we think. The two methods have their own application scenarios, and SSMs is more suitable for DBAs, sqlazuremw is undoubtedly a beacon for developers and DBAs who do not even know the database. It also uses bcp to export data in the database, therefore, sqlazuremw is more powerful in terms of data migration.

Summary

There are two ways to migrate an existing SQL database to SQL azure: SSMs and SQL azure migration wizard. This article migrates the instance database northwnd using two methods, and finally generates an SQL script that can be executed on SQL Azure, so that we can directly deploy it on the cloud with the ready-made steps.

 

It168: http://tech.it168.com/a2010/0629/1071/000001071646_all.shtml