This document talks about how to migrate SQL Server databases to Alibaba Cloud using SSMS (SQL Server Management Studio) combined with BCP command line. SSMS is used to migrate the database structure, while BCP command line is used to migrate all the data. In this document, we will use the migration of a local SQL Server database to Alibaba Cloud RDS SQL Server 2012 as an example.
If you find it not easy to understand, click to watch the YouKu video. The nearly 25-minute-long video will show you how to migrate SQL Server databases to Alibaba Cloud RDS SQL Server using SSMS and BCP.
Background This method applies to SQL Server database structure migration and full data migration
 The method only applies to full data migration and does not support incremental data migration
 The method applies to the full data migration of local data to a local database, local database to the online RDS SQL Server database, and RDS SQL Server database to RDS SQL Server database
 The document shows the detailed steps by taking the migration of local SQL Server 2012 database (AdventureWorks2012) to Alibaba Cloud RDS SQL Server 2012 as an example.
Overall stepsThe detailed operation procedures and specific practices of database migration might be a little complex. Before that, let me give you a brief introduction to the migration. Hopefully, this will make the complex procedures easy to understand. Our goal is to perform full data migration of a local SQL Server database or local database to an online RDS SQL Server, which requires the following three steps:
 Preparation
 Object structure migration
 Full data migration.
PreparationIt can be divided into the source database preparation and target database preparation.
 Source database: create a user account and disconnect the client.
 Target database: create a database and user account, and ensure the ordering rule consistency.
Object structure migrationMicrosoft SQL Server Management Studio (SSMS for short) can generate the creation script of the database object structure. Therefore, we can use this tool to generate the creation script of the source database object structure, which will be executed in the target database. We should compare the objects of the source database and target database to ensure that no object is missed. The detailed steps are as follows:
 Get the object creation script from the source database
 Execute the object creation script in the target database
 Get the object information from both the source database and target database
 Comparison of object information
Note:
One thing deserves special attention: in order to avoid faults and improve efficiency during the data import of the full data migration, the foreign keys, indexes and triggers of the “target database” should be disabled, and they can be re-enabled after data migration.
Full data migrationWe use BCP command line to export and import the data in the database. In order to guarantee the success of the full migration, we need to check whether the number of records in the source database and target database is consistent. The detailed steps are as follows:
 Use BCP to export data from the source database
 Use BCP to import data into the target database
 Comparison of the number of records
 Enable the foreign key constraint indexes and triggers of the target database
PreparationSource databaseThe preparation of the source database includes creating a user and disconnecting the client.
Create a user accountCreate a user account in the source database. If there is already a user account with the read-write permission, this step can be skipped. The user creation code is as follows:
USE MASTER
GO
CREATE LOGIN testdbo
WITH PASSWORD = N'XXXXXXXX',CHECK_POLICY = OFF
GO
USE AdventureWorks2012
GO

CREATE USER testdbo FOR LOGIN testdbo;

EXEC sys.sp_addrolemember 'db_owner','testdbo'
GODisconnect the clientIn this document, we only talk about the full migration. In order to ensure the data consistency before and after the migration, it is essential to make sure that there is no data operation in the source database during migration. To achieve this, all source database clients should be disconnected:
 Stop all relevant applications
 Stop the SQL Agent service of source database
 Disconnect the Service Broker port (if there is a Service Broker)
You can also take other measures to ensure that there is no data change operation in the source database.
Target databaseThe preparation of target database includes checking the storage space, creating a database, ensuring the ordering rule consistency, and creating a user.
Check the storage spaceThere should be enough storage space in the target database to import the data and log files. The total size of the two parts is about 2-3 times that of the source database (if the database is in Full mode). If the target database is in the local self-built environment, ensure that the host has enough storage space. If the database is on Alibaba Cloud RDS SQL Server, ensure that you have purchased enough storage space.
Create a databaseIt is easy to create a database. For local databases or Alibaba Cloud RDS SQL Server 2012, please refer to CREATE DATABASE statement. For Alibaba Cloud RDS SQL Server 2008R2, the new database can be created in the user console. It is essential that the ordering rule of the new database should be the same as that of the source database.
Ensure the ordering rule of the new database is the same as that of the source databaseThe ordering rule of the new database should be the same as that of the source database to avoid data import failures. The following shows how to change the ordering rule:
-- Check Collation name
SELECT name,collation_name
FROM sys.databases
WHERE name = 'adventureworks2012'

-- change the collate if need.
USE master;
GO
ALTER DATABASE adventureworks2012
COLLATE SQL_Latin1_General_CP1_CI_AS;
GOCreate a user accountFor local databases or RDS SQL Server 2012, please refer to the “Create a user in the source database” section. For RDS SQL Server 2008R2, the user account with read-write permissions will be created in the user console.
Operation stepsThe followings are the detailed operation steps to migrate the database structure information and full data of the SQL Server.
Get the object creation script of the source databaseThis step is to generate the object creation script of the source database by using the script generation tool provided with SSMS. Steps:
Expand Databases > right click the corresponding database > Tasks > Generate Scripts. As shown in the figure below:
General introduction page
Select the object of the script to be exported
Generate script options (this step is the key), such as:
Script for Server Version: select the version of SQL Server (target database version) suitable for generating the script. This option makes it possible for the migration between different database versions;
Types of Data to Script: select [Schema only] here. Otherwise an INSERT statement will be generated;
Table/View Options: we recommend to select True for all options.
Summary: pay attention to the script file catalog generated here.
Scripts are generated.
Create the target database objects and disable the foreign keys, indexes, and triggersIn the previous step, we have generated the scripts for all objects of the source database. Next we will execute these scripts in the target database to create these objects. Connect to the target database instance using SSMS, and open and execute the previously generated scripts.

After the creation of the target database objects, it is essential that the foreign key constraints, indexes and triggers are disabled. If foreign key constraints exist, it will cause the data import to fail, while the indexes and triggers will reduce the efficiency of the data import. For this reason, please execute the following script in the target database. Note: input @is_disable = 1 to disable the foreign key constraints, indexes and triggers, and input @is_disable = 0 to enable them.
USE [adventureworks2012]
GO

CLOSE cur_triggers;
DEALLOCATE cur_triggers;
GOObtain and compare the object information of the source and target databasesThe object information refers to various information in the database, including but not limited to tables, views, functions, triggers, constraints, and indexes. Please execute the code below in the source and target databases to obtain their object information.
USE AdventureWorks2012
USE AdventureWorks2012
GO
;WITH objs
AS(
-- check objects
SELECT
database_name = LOWER(DB_NAME())
, object_type = type
, objet_desc = type_desc
, object_count = COUNT(1)
FROM sys.all_objects WITH(NOLOCK)
WHERE is_ms_shipped = 0
GROUP BY type,type_desc
UNION ALL

--check indexes
SELECT
database_name = LOWER(DB_NAME())
, object_type = CAST(ix.type AS VARCHAR)
, objet_desc = ix.type_desc
, object_count = COUNT(1)
FROM sys.indexes as ix
INNER JOIN sys.objects as obj
ON ix.object_id = obj.object_id
WHERE obj.is_ms_shipped = 0
GROUP BY ix.type,ix.type_desc
)
SELECT * FROM objs
ORDER BY object_typeAfter obtaining the object information, compare the summary information. In order to improve efficiency and avoid human errors, we suggest you use a comparison tool for this task, and Araxis Merge 2001 v6.0 Professional is recommended.
First, we paste the obtained object information of the source database in the left window of the comparison tool. See the following picture for the specific method:
Then paste the object information of the target database into the right window of the comparison tool.
Since Alibaba Cloud only supports lower-case letters as database name, and the name of the source database may contain upper-case letters, we should change the comparison tool's settings to ignore differences in character case. The method is as follows:
View > Options > Select “Ignore differences in character case”
Before changing this setting, information in the windows on both sides is considered different by this comparison tool. Therefore, it highlights the differences. After selecting the option of “Ignore differences in character case”, the information in both windows is exactly the same. With this, we can affirm that the object information has been migrated from the source database to the target database. We must ensure the previous operation is successful before importing data to the target database.
Full data migration using BCPIn the previous step, we have ensured that the object information has been migrated from the source database to the target database successfully. Next we will import the data in all the tables of the source database into the corresponding tables of the target database. We use the BCP command line provided with SQL Server to implement this operation, and the specific steps are as follows:
SQL scripts generate BCP OUT and BCP IN.
USE AdventureWorks2012
GO

IF ISNULL(@transfer_table_list, '') = ''
BEGIN
INSERT INTO #tb_list
SELECT name
FROM sys.tables AS tb
WHERE tb.is_ms_shipped = 0
END
ELSE
BEGIN
INSERT INTO #tb_list
SELECT table_name = T.C.value('(./text())[1]','sysname')
FROM @transfer_table_list_xml.nodes('./V') AS T(C)
WHERE T.C.value('(./text())[1]','sysname') IS NOT NULL
END
;

SELECT
BCP_OUT = N'BCP ' + @source_Database + '.' + sch.name + '.' + tb.name
+ N' Out '
+ QUOTENAME( REPLACE(@source_Instance, N'\', N'_')+ '.' + @source_Database + '.' + sch.name + '.' + tb.name, '"')
+ N' /N /U ' + @source_User +N' /P ' + @source_Passwd +N' /S ' + @source_Instance
+ N' >> BCPOUT_' + @timestamp +N'.txt'
,BCP_IN = N'BCP ' + @destination_Database + '.' + sch.name + '.' + tb.name
+ N' In '
+ QUOTENAME( REPLACE(@source_Instance, N'\', N'_')+ '.' + @source_Database + '.' + sch.name + '.' + tb.name, '"')
+ N' /N /E /q /k /U ' + @destination_User + N' /P ' + @destination_Passwd + N' /b '
+ CAST(@batch_Size as varchar) + N' /S ' + @destination_Instance
+ N' >> BCPIN_' + @timestamp + N'.txt'
--,*
FROM sys.tables as tb
LEFT JOIN sys.schemas as sch
ON tb.schema_id = sch.schema_id
WHERE tb.is_ms_shipped = 0
AND tb.name IN (SELECT Table_name FROM #tb_list) Execute the above script in the source database
Please refer to the instructions in the following screenshot to modify the information in the two red boxes.
 Save all the contents in the BCP_OUT column of the execution result to the file BCPOUT.bat
 Save all the contents in the BCP_IN column of the execution result to the file BCPIN.bat
 Execute the file BCPOUT.bat
 Check the log file after executing BCPOUT.bat
A log file will be generated after BCPOUT.bat is executed. The log file naming format is BCPOUT_YYYYMMDDHHMMSS.txt, such as BCPOUT_20170123113408.txt.
 Execute the file BCPOUT.bat
 Check the log file after executing BCPIN.bat
A log file will be generated after BCPIN.bat is executed. The log file naming format is BCPIN_YYYYMMDDHHMMSS.txt, such as BCPIN_20170123113408.txt.
 Delete the temporary files generated by BCP
If the data has been migrated from the source database to the target, the temporary files can be deleted.
Comparison of table record numbersExecute the following statement in both of the source and target databases and record the total number of records of each table. Then compare the number of records of the source and target databases to check whether they are consistent using the tool and method of “Obtain and compare the object information of the source and target databases”.
USE AdventureWorks2012
GO
SELECT
schema_name = SCHEMA_NAME(tb.schema_id)
,table_name = OBJECT_NAME(tb.object_id)
,row_count = SUM(CASE WHEN ps.index_id < 2 THEN ps.row_count ELSE 0 END)
FROM sys.dm_db_partition_stats as ps WITH(NOLOCK)
INNER JOIN sys.tables as tb WITH(NOLOCK)
ON ps.object_id = tb.object_id
WHERE tb.is_ms_shipped = 0
GROUP BY tb.object_id,tb.schema_id
ORDER BY schema_name,table_nameThe numbers of records of all the tables in the source and target databases are the same. See the following figure for the comparison results:
Enable the foreign keys, indexes, and triggers of the target databaseWhen all data has been migrated into the target database, the final step is to re-enable the foreign key constraints, indexes, and triggers of the target database. This operation is very easy. All you need to do is to change the parameter of “Create objects of the target database and disable the foreign keys, indexes, and triggers” to @is_disable = 0, and execute this script. The execution of this script will take a rather long time, because it reconstructs the table indexes. The specific executing period depends on the data size, and you can check the progress in the Messages window.
Notes Consistency of ordering rules
When creating the target database, the ordering rule of the target database must be consistent with that of the source database, or the full data migration may fail.
 Pay attention to the foreign keys, indexes, and triggers
In order to avoid faults and to improve the data import efficiency during full data migration, the foreign keys, indexes and triggers of the target database should be disabled and be re-enabled after migration.
 Timestamp column and computed column
BCP exports data files for the timestamp column or computed column. These column values will be ignored when BCP is imported, and the SQL Server will automatically assign these values.
 When failures occur
For any questions including failures during migration, please contact Alibaba Cloud. We are always here to help you.