SQLServer数据库迁移偏方

一、目的

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：空间域名、网站空间、营销软件、网站建设、伽师网站维护、网站推广。

之前在博文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，供参考。

--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:      
  
  
  
 
  
  
  
-- 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. 先完整备份并在目标服务器还原，迁移前先进行事务日志备份并还原，最后将原库所有数据库账号改为只读，然后再进行一次事务日志备份并还原，这样宕机时间会进一步减少，而且如果相关应用不需要写库，那么在宕机时间段里对应用也不会有太大影响。

 原文链接：http://www.cnblogs.com/gaizai/archive/2011/04/28/2032031.html

【编辑推荐】

1. SQL Server数据库最小宕机迁移方案
2. 数据访问由SQL Server迁移向Oracle数据库的问题整理
3. SQL Server数据迁移至云端应用技巧谈
4. SQL Server数据库迁移方法
5. 迁移SQL数据库五招

名称栏目：SQLServer数据库迁移偏方
URL网址：http://www.gawzjz.com/qtweb2/news3/9753.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联