大容量SQL Server数据库迁移偏方
来源：Asp之家 时间：2011-05-05 08:18:00
一、目的
之前在博文SQL Server数据库最小宕机迁移方案中提到了使用了完全备份+差异备份的功能完成了数据库的转移，但是这个方法在遇到了700多G的数据时显然不适用，所以这篇中我是如何迁移700G的数据库到新的服务器的。
二、分析与设计思路
（一） 环境描述
我们的数据库使用了SQL Server 2005的，部署在Windows Server 2003 x86位操作系统上，有一个表占了这个数据库大部分的空间。
面对上面的情况，我们的数据库压力比较大了，所以我们打算在同一个集群中找另外一台机器，转移这个数据库的数据过去，通过设置新服务器的一些参数来达到优化这个数据库的目的。
（二） 数据分析
在拿到一个数据库的时候，我们应该查看这个数据库相关的信息，在了解了数据库的情况和参数之后再做出初步的评估，比如我们需要知道这个700G的数据库中那些表占用了多少空间，索引占了多少空间（有一个SQL可以直接查看到这些信息），是否做了表分区。
了解参数的时候可以看看服务器硬件信息，比如内存、硬盘、是否做了RAID策略、什么操作系统、数据库的版本、内存的压力、CPU的压力等等信息。了解这些信息是我们决定是否迁移到新的服务器的重要因素。
如果决定了进行数据迁移，那么为了不影响我们的生产的数据库，让生产数据库还能进数据，我们一次要搬多少条记录才是合适的，这个我们也是需要计算的。（搬迁的Job尽量让时间间隔大点，如果前一个Job还没有执行完的话，后一个Job即使到了时间也是不会执行的。）
（三） 设计思路
创建一个表。这个表用来保存我们一次需要转移的多少数据的ID值；（这个ID是我们要迁移表的主键，自增字段）。那我们需要一次性迁移多少数据呢？这个我们可以通过计算比如1000条记录有多少M，一次传输对局域网的压力大嘛？最好让ID是一个整千或者整万的整数，这样方便记录和查看。
创建一个服务器对象-链接服务器。这样就可以读取到其它服务器上的数据库了，可以进行数据搬迁了（注意这里需要设置链接服务器的帐号和密码）
创建一个存储过程。用于读取、控制转移数据，这存储过程需要比较智能一点，它需要解决下面缺陷中提到的几个问题。
创建一个Job。这个Job就调用这个存储过程，不过需要尝试多几次调用的频率问题。
三、参考脚本
下面列出一些重点的sql，供参考。
--1.1，创建表
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]
--1.2插入数据
declare @i int
set @i=1
while @i < 50000000
begin
insert into dbo.Temp_MoveManage values(@i,0)
set @i = @i + 50000
end
--1.3测试
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）就可以了）
五、注意
对磁盘做RAID0（看具体情况而定）之后的创建分区时需要设置64K的分配单元大小；
64位操作系统和64位数据库系统；
搬迁完之后需要创建这表必要的索引，迁移的时候没有创建索引是因为频繁的插入会影响索引，这些索引需要进行存储位置对齐；
因为把数据库搬迁到新的服务器了，程序链接的IP地址就需要修改，如果很多程序需要修改链接地址，那我们又没办法解决呢？可以通过修改服务器的IP；
测试相关的应用程序，测试数据库的运行情况；
六、其它
这是一些朋友的建议，这里还没有尝试，因为环境的限制，比如数据库是简单模式了等情况。这里记录下，期待适合环境的童鞋拿去用。
用Mirror迁移
考虑Log Shipping
先完整备份并在目标服务器还原，迁移前先进行事务日志备份并还原，最后将原库所有数据库账号改为只读，然后再进行一次事务日志备份并还原，这样宕机时间会进一步减少，而且如果相关应用不需要写库，那么在宕机时间段里对应用也不会有太大影响。
原文链接：http://www.cnblogs.com/gaizai/archive/2011/04/28/2032031.html
0
投稿猜你喜欢
Javascript 小游戏，“是男人坚持 100 次”
asp如何做一个树状展开视图来显示自己的记录结构？
给展示性图片增加提示工具条(黑白效果)
5个款MySQL常用维护管理工具
javascript 获取硬盘信息代码
Discuz7 的提示效果如何实现
如何解决SQLServer占内存过多的问题
asp如何在页面中实现对电子信箱的访问？
Css 清除浮动
分享216色网页拾色器（调色板）
语义、标准和样式
利用不同样式改变相同xhtml结构的布局
asp 随机字符串函数
SQL Server元数据的管理与应用
asp实现树型结构
Quickwork For Asp －实战之后台管理
可输入的select代码
日式酒店电梯面板设计
前后端分离开发模式初体验
