Migrate local SQL Server database to Windows Azure SQL database via the SSMs tool

Migrate local SQL Server database to Windows Azure SQL database via the SSMs tool

Microsoft's product updates are getting faster, almost every year there are product updates, today, we mainly introduce how to migrate the local SQL Server database to SQL Server on Windows Azure. Of course, when it comes to the migration of SQL Serrver databases, everyone will think of the most common and common approach, by backing up the database and then restoring it from the backed up database file. In fact, we work in IT operations, how many will have experience, the most common method is often the most effective method is also the safest method, but inefficient, because the times progress too fast, we can not too out, do not want to use this ancient method to do data migration, after all, SQL Both server and Windows Azure are Microsoft's products, and Microsoft certainly has other management tools to migrate or manage. We use the database management tool on a daily basis, and we prefer to use SQL Server Manager Studio (SSMS) for administrative operations. That SSMS does not support migrating data from a local SQL server database to Windows Azure SQL Server. The answer is affirmative support, then how to operate, that is the main content we introduced today, we will focus on two methods, the first is to migrate directly through SSMS to Windows Azure SQL database, there is a way to export through ssms need to migrate the schema information , and then import the schema information under Windows Azure SQL database.

About local SQL Server migration to Azure database, we first need to identify the differences between the two (such as supported SQL syntax and some constraints, etc.), and then make a migration plan based on the current database according to these differences. There are several important differences between Azure SQL database and traditional SQL Server databases:

1. Clustered index requirements: Azure SQL database does not support tables that do not have a clustered index, and the table must have a clustered index (this is important, more constraints reference: Azure SQL Database general guidelines and limitations).

2. Azure SQL Database Transact-SQL Reference

3. Non-supported Transact-SQL statements

For the specific migration implementation process, we can refer to Microsoft's official migration scenarios:

1. Migrating SQL Server databases to Azure SQL database

2. Use SQL Server Data Tools to migrate databases to Azure SQL database

Use SQL Server Database Import Azure

Prerequisites:

A. You need to install SQL Server SP1 CU2 or an updated version locally. If you have SQL Server SP1 installed, you can download and update SQL Server from the following connection.

SQL Server2012 SP1 CU2	Http://support.microsoft.com/kb/2790947/en-us
SQL Server2012 SP1 CU4	Http://support.microsoft.com/kb/2833645/en-us

B. Data objects for the local database need to meet the requirements of the DAC support, specifically to view DAC support for SQL Server objects and versions (http://technet.microsoft.com/zh-cn/library/ee210549.aspx)

In addition, we can also migrate through third-party tools.

Use SQLAZUREMW to migrate

SQLAZUREMW is a project published by Microsoft on CodePlex that 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 the wizard can fix are: replace the text column with varchar (max), and convert the user-defined type to a base type. Converts the text column to varchar (max), converts the image to varbinary (max), and also cancels the binding to the XML Schema because SQL Azure does not support it. Because SQL Azure lacks support for hierarchyid, it needs to be done manually. In addition to generating scripts on SQL Azure to deploy migrated schemas, users can also use the tool to migrate table data through BCP.

Nonsense not much to say, today we mainly introduce through SSMS if do data migration. See below for specific operations:

The premise is that we've created the database on Windows Azure and then managed to connect the database with 2008 of SQL Server Manager studio, and we connected to the local SQL Server and Windows through SSMS SQL Server Database on Azure. After we connected, we found that under the task of SQL Server Manager Studio Tools, there is no option to deploy data to Windows Azure database, after consulting the century interconnect, it is advised that the SSMS version must be more than 2012 versions, We are now using the 2008 version of SSMs, so we cannot export and import it in the following ways:

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image002" src= "http://s3.51cto.com/wyfs02/M02/53/BE/wKioL1Ruyy3BHnDsAAFCtljopeI460.jpg" height= "310"/>

In order to solve the problem, we download the latest database on the Microsoft MSDN website, currently Microsoft's latest database version is SQL Server 2014, so we downloaded the SQL Seraver Manager Studio management tool installation package.

SQL Server Manager Studio Management database

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image004" src= "http://s3.51cto.com/wyfs02/M00/53/BE/wKioL1Ruyy2zjZaWAADK1opoF90010.jpg" height= "271"/>

Start installing SQL Server Manager Studio2014

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image006" src= "http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyrShGdsnAADjDGHI9Cs631.jpg" height= "385"/>

After the installation is complete, we run the SQL Server Manager Studio2014 tool

650) this.width=650; "title=" clip_image008 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image008" src= "http://s3.51cto.com/wyfs02/M01/53/C0/wKiom1RuyrSji8HAAACyuDKBsFY062.jpg" height= "410"/>

or after you connect the local SQL Server database and the Windows Azure SQL Server database, and then we right-click the database that you want to migrate under the local database---Deploy to Windows Azure SQL D Atabase

We found a total of two options, and one is the deploy Database to Windows Azure VM, which is a SQL database migration running on a VM on Windows Azure

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image010" src= "http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyrTC326zAAGOXF_gk3k607.jpg" height= "381"/>

After we migrate the local data to Windows Azure SQL database, we default to the next

650) this.width=650; "title=" clip_image012 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image012" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1RuyrTB6DagAAFtmZ3xIzE164.jpg" height= "376"/>

Parsing comes to the point where we need to connect to the target service, that is, the destination of the migration, we are migrating to Windows Azure, so we need to connect to Windows Azure SQL Server database

650) this.width=650; "title=" clip_image014 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image014" src= "http://s3.51cto.com/wyfs02/M01/53/C0/wKiom1RuyrSQpVP9AAGVrPLIiF0641.jpg" height= "389"/>

Can be connected and configured according to your own options

650) this.width=650; "title=" clip_image016 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image016" src= "http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyrSB2Xg3AAFu2X9HBaQ895.jpg" height= "376"/>

Start exporting schema information for SQL Server

650) this.width=650; "title=" clip_image018 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image018" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1RuyrTRi0AnAAE-NvJwB0Y397.jpg" height= "391"/>

Start creating the corresponding database schema and packets on Windows Azure.

650) this.width=650; "title=" clip_image020 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image020" src= "http://s3.51cto.com/wyfs02/M01/53/C0/wKiom1RuyrXxIkPFAAFyORhnhOc867.jpg" height= "388"/>

Operation completed

650) this.width=650; "title=" clip_image022 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image022" src= "http://s3.51cto.com/wyfs02/M00/53/BE/wKioL1Ruyy_RWm6KAAHAXFumS1A260.jpg" height= "395"/>

We can see through ssms that Windows Azure SQL Server has more than one local database, and through query statements, data integrity is good.

650) this.width=650; "title=" clip_image024 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image024" src= "http://s3.51cto.com/wyfs02/M00/53/BE/wKioL1RuyzDR4EZ-AAFqilszSM8012.jpg" height= "347"/>

Method 2:

We also have a way to migrate the database, that is, export data-tier application to restore. This feature is similar to a backup machine restore of a database.

650) this.width=650; "title=" clip_image026 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image026" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1Ruyrbh1VLSAAGozRN6A64609.jpg" height= "401"/>

Our default Next

650) this.width=650; "title=" clip_image028 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image028" src= "http://s3.51cto.com/wyfs02/M01/53/C0/wKiom1RuyrbTm9KJAAFRuvEhTek237.jpg" height= "490"/>

Save the template file, the type of the template file is. bacpac

650) this.width=650; "title=" clip_image030 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image030" src= "http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyraR3-ZjAAGCZH3PiLk683.jpg" height= "501"/>

Save Information Tips

650) this.width=650; "title=" clip_image032 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image032" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1RuyrnA6PF2AAEKEsfDLP4361.jpg" height= "497"/>

The operation is complete. Export information such as databases and schemas.

650) this.width=650; "title=" clip_image034 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image034" src= "http://s3.51cto.com/wyfs02/M01/53/BE/wKioL1RuyzOC0Z5PAAF3h_Oodqk601.jpg" height= "497"/>

Exported schema files and data files

650) this.width=650; "title=" clip_image036 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image036" src= "http://s3.51cto.com/wyfs02/M02/53/BE/wKioL1RuyzTCATZrAAC4Pfr-uDk520.jpg" height= "290"/>

Next we're going to restore through the SSMs link to Windows Azure SQL Server database. After we right-click Windows Azure SQL Server database, right-click Import Data-their Application

650) this.width=650; "title=" clip_image038 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image038" src= "http://s3.51cto.com/wyfs02/M00/53/BE/wKioL1RuyzTjA2zvAAD7JSjLdEw584.jpg" height= "/>"

Prompt information.

650) this.width=650; "title=" clip_image040 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image040" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1RuyrqzpcKgAAFfLDQaAGk079.jpg" height= "501"/>

We chose the database schema file that we just backed up

650) this.width=650; "title=" clip_image042 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image042" src= "http://s3.51cto.com/wyfs02/M01/53/C0/wKiom1RuyrrBXZAbAAFw6mxgbpA551.jpg" height= "501"/>

After confirming the configuration information for the database, the next step

650) this.width=650; "title=" clip_image044 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image044" src= "http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyrqhAFO2AAFp3XbwJeY261.jpg" height= "517"/>

Confirmation information

650) this.width=650; "title=" clip_image046 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image046" src= "http://s3.51cto.com/wyfs02/M00/53/C0/wKiom1RuyrqDfQtvAAEXbOfcfjQ330.jpg" height= "506"/>

The operation is complete.

650) this.width=650; "title=" clip_image048 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image048" src= "http://s3.51cto.com/wyfs02/M01/53/BE/wKioL1RuyzThRUbMAAGbwj4iT9w507.jpg" height= "503"/>

We still look at SQL Server database information under Windows Azure through SSMS

650) this.width=650; "title=" clip_image050 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt = "clip_image050" src= "http://s3.51cto.com/wyfs02/M02/53/BE/wKioL1RuyzWxjXDIAAEjc5Qha_E886.jpg" height= "395"/>

We still use the SQL query statement to the database just migrated query machine judgment.

650) this.width=650; "title=" clip_image052 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image052 "src=" http://s3.51cto.com/wyfs02/M02/53/C0/wKiom1RuyruwpdgjAAF3GU14qaI808.jpg "height=" 411 "/>

This article is from the "Gao Wenrong" blog, make sure to keep this source http://gaowenlong.blog.51cto.com/451336/1580834

Migrate local SQL Server database to Windows Azure SQL database via the SSMs tool