SQL Server data is currently present on the corporate server and needs to be migrated to SQL Azure
There are two types of migration
- Database structure replication
- 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