MS SQL Server migration to SQL Azure

SQL Server data is currently present on the corporate server and needs to be migrated to SQL Azure

There are two types of migration

1. Database structure replication
2. Database structure replication and data migration to SQL Azure

The 1th approach is to create a new library from an existing database to a new server by generating a structure script on the original server and executing it on the new server

The 2nd approach is for a full migration of an existing database to SQL Azure by using the SQL Azure Migration Wizard tool to migrate the database structure and data to Azure at the same time

One: Database structure replication

Use SSMS to generate DB scripts for Azure SQL Server run

First step : Find the original database in SSMs, right click on the task (Task), and select Generate Script (Generate Scripts)

The second step : Select the database object to generate the script, if you are building a new library, you need to select all

Step three : In the SQL Script wizard window that pops up, the Database window appears, select the target database we need to migrate, and select a directory to save the generated script file

"Table/View Options"

Script to write data compression options: set to False because we cannot write data to the generated script

General

Write the Use database script: set to False,sql Azure temporarily does not support this option

Write extended property script: Set to False,sql Azure temporarily does not support extended properties

Convert UDDT to base type: set to True,sql Azure temporarily does not support user-Baoyi data types, and the data types that are available are the types listed in the Azure site

Fourth Step : After the third click is completed, SSMS will automatically generate the script to the specified directory

Fifth Step : Run the fourth step generated script file in SQL Azure

II: Database structure replication and data migration to SQL Azure

First you need to download the tool SQL Database Migration Wizard

The SQL Database migration Wizard is a third-party tool that solves SQL Server-to-SQL Azure migration Issues, resolves SQL Server-to-SQL Azure compatibility issues, and generates SQL Azure-compatible scripts. And you can remotely execute a script to implement a database migration The first step: Download SQL Database Migration Wizard and extract to local directory

Step two : Open the tool and select "Analyze and migrate the database" and select "SQL Database" in the directory server

Step three : Enter the server's IP and username/password on the database landing page, select the target database, and select the original target database in the list of server databases that pops up.

Fourth Step : Select the object to generate the script, here we select all the objects, because, the database migration, you need to include all the corresponding configuration, including stored procedures, tables, user-defined functions, views, do not directly select the next

Fifth Step : In the Select Generated Script Object dialog box, select Advanced Options and select the table structure and data option in the Generate table/data in table/view options to migrate the table structure and the data in the table to the new database

Sixth step : in the pop-up script check window to see the object to be generated, if all the objects are in it, you can skip to the next step, if the object is not selected, you should return to the fourth step, select the object you want to select

Seventh Step : Waiting for the generation of scripts and data files, data files are data table data in a single file, the default location in C:\SQLAzureMW\BCPData, export takes a little time, depending on the data table data size and network conditions

Eighth step : After you have generated all the structure scripts and table data files, you need to select the connection for the new directory database server and select the new directory database

nineth Step : Start the execution of the script, the first execution of the database structure script, after creating a good structure, the data file will be inserted into the data table

finally : use SSMs to connect to the SQL Azure database to see if the database was successfully migrated fully

Note : An error occurred during the migration that caused the migration process to be interrupted and a purged db script needed to be generated in SSMS

Generate the following script file, you need to put the generated script into SQL Azure to run once

After executing the SQL Azure database is empty, you can perform the migration step again to ensure that two databases are consistent

Add : When the SQL Server database is successfully migrated to SQL Azure, the backup and restore work is relatively straightforward and is exported in the Azure management system. Bacpac package, can be backed up and downloaded to local, restore is very simple

MS SQL Server migration to SQL Azure