Kenan
Assistant Engineer
Assistant Engineer
  • UID621
  • Fans0
  • Follows0
  • Posts55
Reads:394Replies:0

Migrate SQL Server database to Alibaba Cloud using SSMS combined with BCP

Created#
More Posted time:Apr 10, 2017 13:31 PM
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 steps
The 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.
Preparation
It 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 migration
Microsoft 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 migration
We 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
Preparation
Source database
The preparation of the source database includes creating a user and disconnecting the client.
Create a user account
Create 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'
GO

Disconnect the client
In 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 database
The preparation of target database includes checking the storage space, creating a database, ensuring the ordering rule consistency, and creating a user.
Check the storage space
There 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 database
It 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 database
The 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;  
GO

Create a user account
For 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 steps
The 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 database
This 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 triggers
In 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

--public variables: need init by users.
DECLARE
    @is_disable BIT = 1 -- 1: disalbe indexes, foreign keys and triggers;
                        -- 0: enable indexes, foreign keys and triggers;
;

--================ Private variables
DECLARE
    @sql NVARCHAR(MAX)
    , @sql_index NVARCHAR(MAX)
    , @tb_schema SYSNAME
    , @tb_object_name SYSNAME
    , @tr_schema SYSNAME
    , @tr_object_name SYSNAME
    , @ix_name SYSNAME
;

--================= Disable/Enable indexes on all tables
DECLARE
    cur_indexes CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT
    ix_name = ix.name
    , tb_schema = SCHEMA_NAME(obj.schema_id)
    , tb_object_name = obj.name
FROM sys.indexes as ix
    INNER JOIN sys.objects as obj
    ON ix.object_id = obj.object_id
WHERE ix.type >= 2
    AND obj.is_ms_shipped = 0
    AND ix.is_disabled = (1 - @is_disable)

OPEN cur_indexes;
FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;

WHILE @@FETCH_STATUS = 0
BEGIN  
    SET
        @sql_index = N'ALTER INDEX ' + QUOTENAME(@ix_name)
                    + N' ON ' + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
                    + CASE @is_disable
                        WHEN 1 THEN N' DISABLE;'
                        WHEN 0 THEN N' REBUILD; '
                        ELSE N''
                    END;
    RAISERROR(N'%s', 10, 1, @sql_index) WITH NOWAIT;
    EXEC sys.sp_executesql @sql_index
    FETCH NEXT FROM cur_indexes INTO @ix_name, @tb_schema, @tb_object_name;
END

CLOSE cur_indexes;
DEALLOCATE cur_indexes;

--================= Disable/Enable foreign keys on all tables
--disable
IF @is_disable = 1
BEGIN
    SELECT
        @sql = N'
        RAISERROR(N''ALTER TABLE ? NOCHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
        ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
    ;
END
ELSE    --enable
BEGIN
    SELECT
        @sql = N'
        RAISERROR(N''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'', 10, 1) WITH NOWAIT
        ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'
    ;
END

EXEC sys.sp_MSforeachtable @sql

--================= Disable/Enable triggers on all tables

DECLARE
    cur_triggers CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT
    tb_schema = SCHEMA_NAME(tb.schema_id)
    ,tb_object_name = tb.name
    ,tr_schema = SCHEMA_NAME(obj.schema_id)
    ,tr_object_name = obj.name
FROM sys.objects as obj
    INNER JOIN sys.tables as tb
    ON obj.parent_object_id = tb.object_id
    INNER JOIN sys.triggers as tr
    ON obj.object_id = tr.object_id
WHERE obj.type = 'TR'
    AND obj.is_ms_shipped = 0
    AND tr.is_disabled =  (1 - @is_disable)
ORDER BY tb_schema, tb_object_name

OPEN cur_triggers;
FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = CASE @is_disable
                    WHEN 1 THEN N'DISABLE TRIGGER '
                    WHEN 0 THEN N'ENABLE TRIGGER '
                    ELSE N''
                END
                + QUOTENAME(@tr_schema) + N'.' + QUOTENAME(@tr_object_name)
                + N' ON '
                + QUOTENAME(@tb_schema) + N'.' + QUOTENAME(@tb_object_name)
    ;
    RAISERROR(N'%s', 10, 1, @sql) WITH NOWAIT;
    EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM cur_triggers INTO @tb_schema, @tb_object_name, @tr_schema, @tr_object_name;
END

CLOSE cur_triggers;
DEALLOCATE cur_triggers;
GO

Obtain and compare the object information of the source and target databases
The 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_type

After 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 BCP
In 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

-- declare public variables, need to init by user
DECLARE
    @source_Instance sysname
    , @source_Database sysname
    , @source_User sysname
    , @source_Passwd sysname

    , @destination_Instance sysname
    , @destination_Database sysname
    , @destination_User sysname
    , @destination_Passwd sysname

    , @batch_Size int

    , @transfer_table_list nvarchar(max)
;

-- Public variables init.
SELECT
    @source_Instance = @@SERVERNAME             -- Source Instance Name
    , @source_Database = DB_NAME()                  -- Source Database is current database.
    , @source_User = 'XXX'                          -- Source Instance Connect User Name
    , @source_Passwd = N'XXX'               -- Source Instance User Password

    , @destination_Instance = N'XXXX.sqlserver.rds.aliyuncs.com,3433'   -- Destination Instance Name
    , @destination_Database = N''                       -- Destination Database name: NULL/empty: Keep the same as source db
    , @destination_User = 'XXX'                     -- Destination Instance User Name
    , @destination_Passwd = N'XXX'          -- Destination Instance User Password

    , @transfer_table_list = N''                                --NULL/empty: ALL Tables are needed to be transfered.
    , @batch_Size = 50000                                   -- BCP IN Batch Size, by default, it is 50000. Must between 1 and 50000.
;

-- Private variables, there is no need to init.
DECLARE
    @transfer_table_list_xml xml
    , @timestamp char(14)
    ;

-- correct the variables init by user.
SELECT
    @source_Instance = RTRIM( LTRIM(@source_Instance) )
    , @source_User = RTRIM( LTRIM( @source_User ) )
    , @source_Passwd = RTRIM( LTRIM( @source_Passwd ) )

    , @destination_Instance = RTRIM( LTRIM( @destination_Instance ) )
    , @destination_Database =  CASE
                                                WHEN ISNULL(@destination_Database, N'') = N'' THEN @source_Database
                                                ELSE @destination_Database
                                            END
    , @destination_User = RTRIM( LTRIM( @destination_User ) )
    , @destination_Passwd = RTRIM( LTRIM( @destination_Passwd ) )

    , @batch_Size = CASE  
                                WHEN (@batch_Size>0 AND @batch_Size<=50000) THEN @batch_Size
                                ELSE 50000
                            END
    , @transfer_table_list_xml = '<V><![CDATA[' + REPLACE(
                                                    REPLACE(
                                                                REPLACE(
                                                                            @transfer_table_list,CHAR(10),']]></V><V><![CDATA['
                                                                        ),',',']]></V><V><![CDATA['
                                                            ),CHAR(13),']]></V><V><![CDATA['
                                                  ) + ']]></V>'
    , @timestamp =  
                    REPLACE(
                        REPLACE(
                                REPLACE(
                                            CONVERT(CHAR(19), GETDATE(), 120), N'-', '')
                                        , N':', N'')
                                    , CHAR(32), N'')
;

IF OBJECT_ID('tempdb..#tb_list', 'U') IS NOT NULL
    DROP TABLE #tb_list
CREATE TABLE #tb_list(
 RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
 ,Table_name SYSNAME NOT NULL
)

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 numbers
Execute 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_name

The 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 database
When 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.
[Kenan edited the post at Jul 13, 2017 15:28 PM]
Guest