SQL Server 數據庫遷移偏方
一、目的
之前在博文SQL Server 數據庫最小宕機遷移方案中提到了使用了完全備份+差異備份的功能完成了數據庫的轉移，但是這個方法在遇到了700多G的數據時顯然不適用，所以這篇中我是如何遷移700G的數據庫到新的服務器的。
二、分析與設計思路
(一) 環境描述
我們的數據庫使用了SQL Server 2005的，部署在Windows Server 2003 x86位操作系統上，有一個表占了這個數據庫大部分的空間。
面對上面的情況，我們的數據庫壓力比較大了，所以我們打算在同一個集群中找另外一臺機器，轉移這個數據庫的數據過去，通過設置新服務器的一些參數來達到優化這個數據庫的目的。
(二) 數據分析
在拿到一個數據庫的時候，我們應該查看這個數據庫相關的信息，在了解了數據庫的情況和參數之后再做出初步的評估，比如我們需要知道這個700G的數據庫中那些表占用了多少空間，索引占了多少空間（有一個SQL可以直接查看到這些信息），是否做了表分區。
了解參數的時候可以看看服務器硬件信息，比如內存、硬盤、是否做了RAID策略、什么操作系統、數據庫的版本、內存的壓力、CPU的壓力等等信息。了解這些信息是我們決定是否遷移到新的服務器的重要因素。
如果決定了進行數據遷移，那么為了不影響我們的生產的數據庫，讓生產數據庫還能進數據，我們一次要搬多少條記錄才是合適的，這個我們也是需要計算的。（搬遷的Job盡量讓時間間隔大點，如果前一個Job還沒有執行完的話，后一個Job即使到了時間也是不會執行的。）
(三) 設計思路
1. 創建一個表。這個表用來保存我們一次需要轉移的多少數據的ID值；（這個ID是我們要遷移表的主鍵，自增字段）。那我們需要一次性遷移多少數據呢？這個我們可以通過計算比如1000條記錄有多少M，一次傳輸對局域網的壓力大嘛？最好讓ID是一個整千或者整萬的整數，這樣方便記錄和查看。
2. 創建一個服務器對象-鏈接服務器。這樣就可以讀取到其它服務器上的數據庫了，可以進行數據搬遷了（注意這里需要設置鏈接服務器的帳號和密碼）
3. 創建一個存儲過程。用于讀取、控制轉移數據，這存儲過程需要比較智能一點，它需要解決下面缺陷中提到的幾個問題。
4. 創建一個Job。這個Job就調用這個存儲過程，不過需要嘗試多幾次調用的頻率問題。
三、參考腳本
下面列出一些重點的sql，供參考。
CREATE TABLE [dbo].[Temp_MoveManage](
[Id] [int] NOT NULL,
[IsDone] [bit] NOT NULL,
[UpdateTime] [datetime] NULL,
CONSTRAINT [PK_Temp_MoveManage] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--插入數據
declare @i int
set @i=1
while @i < 50000000
begin
insert into dbo.Temp_MoveManage values(@i,0)
set @i = @i + 50000
end
--測試
select * from Temp_MoveManage
--2,鏈接服務器(省略)
--3,存儲過程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Viajar>
-- Create date: <2011.04.14>
-- Description: <轉移數據>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetMoveData]
AS
BEGIN
DECLARE @Id1 INT,@Id2 INT
DECLARE @MaxId INT--原表的最大值
SET @Id1 = 0
SET @Id2 = 0
SELECT TOP 1 @Id1 = Id FROM Temp_MoveManage WHERE IsDone = 0 ORDER BY Id
SELECT TOP 1 @Id2 = Id FROM Temp_MoveManage WHERE IsDone = 0 AND Id > @Id1 ORDER BY Id
SELECT @MaxId = MAX(Id) FROM [dbo].[ClassifyResult]
IF(@Id1 != 0 AND @Id2 != 0 AND @MaxId>=@Id2)
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SET IDENTITY_INSERT [ClassifyResult_T] ON
INSERT INTO [dbo].[ClassifyResult_T](
[Id]
,[ClassId]
,[ArchiveId])
SELECT
[Id]
,[ClassId]
,[ArchiveId]
FROM [dbo].[ClassifyResult]
WHERE Id >= '+ CONVERT(VARCHAR(100),@Id1) + ' and Id < '+ CONVERT(VARCHAR(100),@Id2) + '
ORDER BY Id
SET IDENTITY_INSERT [ClassifyResult_T] OFF '
EXEC (@sql)
UPDATE Temp_MoveManage SET IsDone = 1 WHERE Id = @Id1
END
END
--4,Job(省略)
四、缺陷
缺陷1：在CreateTable生成的表中，最后一條記錄無法執行，因為最后一個Id是使用<，沒有用=，所以在轉移的表中最后一條記錄是沒有轉移過分區表的；
缺陷2：如果轉移表的記錄同時在不斷的增長，那么數據就無法把最新的數據轉移到分區表了；針對這個缺陷，本來的想法是為搬遷輔助表的Id分段加多一些記錄，這樣就可以執行最新數據；
缺陷3：對于上面的那個問題，也是有缺陷的，例如現在Id分段是100和200，當新數據Id>100的某段時間，這兩個分段值的IsDone就會給更新為1，這樣就會造成缺失了很多數據；針對這個缺陷，也是可以解決的，先去判斷當新數據的Id>200的時候，才執行導Id為100和200分段的腳本；（在存儲過程中判斷Max(Id)就可以了）
五、注意
1. 對磁盤做RAID0（看具體情況而定）之后的創建分區時需要設置64K的分配單元大小；
2. 64位操作系統和64位數據庫系統；
3. 搬遷完之后需要創建這表必要的索引，遷移的時候沒有創建索引是因為頻繁的插入會影響索引，這些索引需要進行存儲位置對齊；
4. 因為把數據庫搬遷到新的服務器了，程序鏈接的IP地址就需要修改，如果很多程序需要修改鏈接地址，那我們又沒辦法解決呢？可以通過修改服務器的IP；
5. 測試相關的應用程序，測試數據庫的運行情況；
六、其它
這是一些朋友的建議，這里還沒有嘗試，因為環境的限制，比如數據庫是簡單模式了等情況。這里記錄下，期待適合環境的童鞋拿去用。
1) 用Mirror遷移
2) 考慮Log Shipping
3) 先完整備份并在目標服務器還原，遷移前先進行事務日志備份并還原，最后將原庫所有數據庫賬號改為只讀，然后再進行一次事務日志備份并還原，這樣宕機時間會進一步減少，而且如果相關應用不需要寫庫，那么在宕機時間段里對應用也不會有太大影響。
留言列表
