SQLServer－最佳實踐－SSMS配合BCP遷移SQLServer資料庫上阿里雲
本文討論的主題是使用SSMS（SQL Server Management Studio）配合BCP命令列的方式來遷移SQL Server資料庫。使用SSMS做資料庫結構遷移，使用BCP命令做全量資料遷移，此方案是以本地SQL Server資料庫遷移到阿里雲RDS SQL Server 2012為例。
如果你覺得讀取文章不夠直觀，請點選觀看Youku視訊，近25分鐘的視訊詳細介紹來如何使用SSMS ＋ BCP遷移SQL Server資料庫上阿里雲RDS SQL Server。使用SSMS＋BCP遷移SQL Server資料庫上阿里雲RDS SQL Server
背景資訊
本方法適用於SQL Server資料庫結構遷移和全量資料遷移
本方法僅適用於資料全量遷移，不支援資料增量遷移
本方法適用於本地資料到本地資料庫、本地資料庫上雲到RDS SQL Server資料庫、RDS SQL Server資料庫到RDS SQL Server資料庫三種場景的資料庫全量遷移
本文以遷移本地SQL Server 2012資料庫AdventureWorks2012到阿里雲RDS SQL Server 2012為例，講述詳細的遷移步驟
總體步驟
本文件詳細的操作步驟和具體實施過程稍顯繁瑣，在詳細的操作步驟之前，我們梳理下總體的思路和方法。這樣可以，從大處著眼，小處著手，條理清楚，思路清晰。我們的目標是SQL Server本地資料庫全量遷移或者是本地資料庫上雲RDS SQL Server，我們必須完成三大任務：
準備工作
物件結構遷移
資料全量遷移
準備工作
準備工作包括源端資料的準備工作和目標資料庫的準備工作。
源端資料庫：建立使用者和斷開客戶端連線。
目標資料庫：建立資料庫、確保排序規則一致和建立使用者。
物件結構遷移
微軟SQL Server Management Studio（簡稱：SSMS）工具提供了資料庫物件結構建立指令碼的生成功能。所以，我們直接使用這個功能來生成源端資料庫物件結構的建立指令碼，然後在目標資料庫中去執行。為了不遺漏任何的資料庫物件，我們需要對比源端資料庫和目標資料庫的物件資訊，確保一致。具體的步驟可以分解為以下幾個小的步驟：
源端資料庫獲取物件建立指令碼
目標資料庫執行物件建立指令碼
源端資料庫和目標資料庫獲取物件資訊
物件資訊對比
注意：
這裡需要特別提醒，為了避免資料全量遷移過程報錯和提高資料匯入效率，我們需要在“目標資料庫”中先禁用外來鍵、索引和觸發器，資料匯入完畢後，再啟用這三類物件。
資料全量遷移
我們使用BCP命令列來做資料庫資料的匯出匯入功能。為了確保資料全量遷移成功，我們需要對比源端資料庫和目標資料庫中表記錄數是一致的。具體的操作步驟分解為：
BCP從源端資料庫匯出資料
BCP匯入資料到目標資料庫
表記錄數對比
目標資料庫啟用外來鍵約束、索引和觸發器
準備工作
源端資料庫
源端資料庫的準備工作包括使用者建立和斷開客戶端連線。
使用者建立
在源端資料庫建立使用者登入，如果已經存在使用者，並具備讀寫該資料庫許可權，請跳過該步驟。建立使用者程式碼如下：
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斷開客戶端連線
由於本文討論的方法僅支援全量遷移，為了確保源端資料庫遷移前後的資料是完全一致的，所以在開始遷移之前一定要確保源端資料庫資料沒有任資料變更操作。我們可以採用斷開源端資料庫所有的客戶端連線的方式來達到這個目的：
停止相關的所有應用程式
停止源端資料庫SQL Agent服務
停掉Service Broker埠（如果部署有Service Broker）
當然，你也可以採用其他方法來確保源端資料庫沒有資料變更操作。
目標資料庫
目標資料庫的準備工作包括：檢查儲存空間、建立資料庫、確保排序規則一致和建立使用者。
檢查儲存空間
目標資料庫主機需要有充足的儲存空間來存放匯入的資料和因此而帶來的日誌檔案增長，兩者加起來的空間增長大概是源端資料庫大小的2-3倍（如果是資料庫是Full模式）。如果目標資料庫是在本地自建環境，請確保宿主機有足夠的儲存空間，如果是阿里雲RDS SQL Server，請確保已經購買了充足的儲存空間。
建立資料庫
建立資料庫的步驟很簡單，如果是本地資料庫或者阿里雲RDS SQL Server 2012，請參見CREATE DATABASE語句。如果是阿里雲RDS SQL Server 2008R2，可以使用使用者控制檯建立新的資料庫。這裡需要確保新建資料庫排序規則與源端資料庫保持一致。
確保排序規則和源端資料庫一致
我們需要確保目標資料庫排序規則和源端資料庫排序規則一致，以免資料匯入失敗。修改排序規則的方法如下：
-- 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使用者建立
如果是本地環境資料庫或者RDS SQL Server 2012，請參考“源端資料庫中的使用者建立”部分來建立使用者；如果是RDS SQL Server 2008R2，請使用使用者控制檯來建立具有讀寫許可權的使用者。
操作步驟
以下是SQL Server資料庫結構資訊和全量資料遷移的詳細實現步驟。
源端資料庫獲取物件建立指令碼
這一步是生成源端資料庫物件資訊建立指令碼，我們使用SSMS自帶的指令碼生成工具。方法是
展開Databases => 右鍵點選相應資料庫 => 選擇Tasks => Generate Scripts。如下圖所示：
一般性介紹頁面
選擇要匯出指令碼的物件
生成指令碼選項設定，這一步是最關鍵的地方，比如：
Script for Server Version：可以選擇生成指令碼適用的SQL Server版本（目標資料庫版本），這個選項使得不同版本間資料遷移成為可能；
Types of data to script：這裡請選擇Schema only，否則會生成INSERT插入資料的語句；
Table/View Options：建議全部選擇True。
彙總資訊，這裡關注下生成的指令碼檔案目錄。
最後完成指令碼生成工作。
目標資料庫建立物件並禁用外來鍵、索引和觸發器
在上一步，我們已經將源端資料庫所有物件生成了建立的指令碼檔案，接下來，需要在目標資料庫中執行該指令碼檔案來建立這些物件到目標資料庫中。使用SSMS連線目標資料庫例項，然後開啟我們之前生成的指令碼檔案，並執行。
目標資料庫完成物件建立以後，一個非常重要的步驟是需要禁用表外來鍵約束、索引和觸發器。因為，表外來鍵約束的存在會導致資料匯入失敗，而表索引和觸發器的存在會導致資料匯入效率降低。為了達到這個目的，請在目標資料庫中執行以下指令碼。注意輸入引數@is_disable = 1表示禁用外來鍵約束、索引和觸發器；@is_disable = 0表示啟用外來鍵約束、索引和觸發器。
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源端和目標資料庫獲取物件資訊並比較
物件資訊是指資料庫下存在的各種物件資訊，包含但不僅限於表、檢視、函式、觸發器、約束和索引等。請在源端和目標資料庫中分別執行以下程式碼，獲取資料庫的物件彙總資訊。
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源端和目標資料庫物件資訊獲取完畢後，接下來是比較彙總資訊。為了方便對比和避免人眼觀察帶來的人為錯誤，我們建議使用對比工具來對比物件彙總資訊，推薦使用的是：Araxis Merge 2001 v6.0 Professional這款對比工具。
首先，我們將源端資料庫獲取到的物件彙總資訊複製到對比工具左側視窗中，複製方法如下圖所示：
然後，將目標資料庫物件彙總資訊複製到對比工具右側視窗中。
接下來，由於阿里雲RDS SQL Server目標資料庫名稱僅支援小寫字母，而源資料庫名可能含有大寫字母，所以我們需要修改對比工具的設定，忽略字母大小寫。方法如下：
View => Options => 選中”Ignore differences in character case”
修改設定之前，對比工具認為左右兩邊視窗的資訊是不一樣的，對比工具已經高亮顯示了不同的地方；修改設定忽略大小寫之後，對比工具顯示左右視窗中的資訊已經完全一樣了。因此，我們可以認為所有的物件資訊已經從源資料庫遷移到了目標資料庫。這是我們在下一步匯入資料到目標資料庫開始之前一定要確保成功的。
使用BCP做資料全量遷移
上一個步驟，我們已經確保了源端資料庫和目標資料庫物件資訊已經保持一致了。接下來需要將源端資料庫中所有表的所有資料匯入到目標資料庫對應的表中。這個動作我們使用SQL Server自帶的BCP命令列工具，方法如下：
SQL 指令碼生成BCP OUT和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)在源端資料庫執行上面的指令碼
請參照下面截圖中的說明修改兩個紅色框中部分。
儲存執行結果中BCP_OUT列所有內容到檔案BCPOUT.bat
儲存執行結果中BCP_IN列所有內容到檔案BCPIN.bat
執行BCPOUT.bat檔案
檢查BCPOUT.bat執行的日誌檔案
BCPOUT.bat批處理檔案執行後會生成一個日誌檔案，日誌檔案的命名格式是：BCPOUT_YYYYMMDDHHMMSS.txt，比如：BCPOUT_20170123113408.txt。
執行BCPIN.bat檔案
檢查BCPIN.bat執行的日誌檔案
BCPIN.bat批處理檔案執行後會生成一個日誌檔案，日誌檔案的命名格式是：BCPIN_YYYYMMDDHHMMSS.txt，比如：BCPIN_20170123113408.txt。
刪除BCP匯出的中間臨時檔案
如果確保資料已經從源端資料庫匯入到目標資料，磁碟上的這些中間臨時檔案就可以刪除了。
表記錄數對比
在源端資料庫和目的端資料庫分別執行下面的語句，統計每個表總共存在的記錄總數。然後參照“源端和目標資料庫獲取物件資訊並比較”中對比工具使用方法，對比執行結果，最終來確保源端資料庫和目標資料庫資料保持一致。
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源端資料庫和目標資料庫所有表記錄總數相同，對比結果如下圖所示：
目標資料庫啟用表外來鍵、索引和觸發器
在確保目標資料庫表的所有資料已經匯入完畢以後，最後一個步驟是啟動目標資料庫中所有表的外來鍵約束、索引和觸發器。這個步驟操作非常簡單，只需要將“目標資料庫建立物件並禁用外來鍵、索引和觸發器”中的指令碼輸入引數設定為@is_disable = 0，執行指令碼即可。這個指令碼可能會執行比較長的時間，請不要驚慌或者手動終止，因為指令碼會做表索引的重建工作，具體執行時間視資料量大小而定，你可以通過Messages視窗檢視當前進度。
注意
排序規則的一致性
在目標資料庫建立的時候，一定要確保目標資料庫排序規則和源端資料庫保持一致，否則很可能會導致全量資料遷移失敗。
注意外來鍵、索引和觸發器
為了防止資料全量遷移過程報錯，需要在“目標資料庫”中禁用外來鍵、索引和觸發器，然後再啟用，以此來避免錯誤發生和提高資料匯入效率。
時間戳列和計算列
BCP匯出的資料檔案中針對計算列或 timestamp 列，BCP匯入時這些列值將被忽略，SQL Server 將自動分配該列的值。
如果遭遇錯誤
因為難免考慮不周，如果您在參照這個過程遇到任何問題或者錯誤，請聯絡阿里雲，以便我們及時糾正錯誤並竭誠為您服務。
相關文章
- PgSQL·最佳實踐·雲上的資料遷移SQL
- MSSQL·最佳實踐·RDSSDK實現資料庫遷移上阿里雲RDSSQLServerSQL資料庫阿里Server
- Hadoop資料遷移MaxCompute最佳實踐Hadoop
- cassandra百億級資料庫遷移實踐資料庫
- 資料庫上雲實踐：使用Ora2pg進行資料庫遷移資料庫
- kafka資料遷移實踐Kafka
- 資料庫平滑遷移方案與實踐分享資料庫
- 阿里雲王林平：一站式資料庫上雲最佳實踐阿里資料庫
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- SQLServer·最佳實踐·透明資料加密TDE在SQLServer的應用SQLServer加密
- 【遷移】使用rman遷移資料庫資料庫
- ASM的資料庫遷移回到DISK上ASM資料庫
- 資料庫遷移資料庫
- Jenkins搭建與資料遷移實踐Jenkins
- Datapump資料遷移的實踐總結
- 遷移sqlserver資料到MongoDbSQLServerMongoDB
- 資料庫遷移之資料泵實驗資料庫
- SqlServer-觸發器SQLServer觸發器
- 阿里雲資料庫遷移方案-不間斷業務阿里資料庫
- ORM實操之資料庫遷移ORM資料庫
- GaussDB技術解讀系列：資料庫遷移創新實踐資料庫
- PHP最佳實踐之資料庫PHP資料庫
- 線上資料遷移，數字化時代的必修課 —— 京東雲資料遷移實踐
- 最佳實踐 | 資料庫遷雲解決方案選型 & 流程全解析資料庫
- 阿里雲NAS檔案遷移專案實踐阿里
- 攜程MySQL遷移OceanBase最佳實踐｜分享MySql
- 資料庫遷移 ：理解資料庫
- laravel資料庫遷移Laravel資料庫
- Odoo遷移資料庫Odoo資料庫
- redis資料庫遷移Redis資料庫
- Cacti 遷移資料庫資料庫
- 資料庫遷移方案資料庫
- ORACLE資料庫遷移Oracle資料庫
- 遷移資料庫成功！資料庫
- 建立資料庫遷移資料庫
- MSSQL·最佳實踐·例項級別資料庫上雲RDSSQLServerSQL資料庫Server
- 動手為王 | Oracle 資料庫跨版本升級遷移實踐Oracle資料庫