欢迎投稿

今日深度:

SQL Server数据库迁移偏方

SQL Server数据库迁移偏方


一、目的

之前在博文SQL Server数据库最小宕机迁移方案中提到了使用了完全备份+差异备份的功能完成了数据库的转移,但是这个方法在遇到了700多G的数据时显然不适用,所以这篇中我是如何迁移700G的数据库到新的服务器的。

二、分析与设计思路

(一) 环境描述

我们的数据库使用了SQL Server 2005的,部署在Windows Server 2003 x86位操作系统上,有一个表占了这个数据库大部分的空间。

面对上面的情况,我们的数据库压力比较大了,所以我们打算在同一个集群中找另外一台机器,转移这个数据库的数据过去,通过设置新服务器的一些参数来达到优化这个数据库的目的。

(二) 数据分析

在拿到一个数据库的时候,我们应该查看这个数据库相关的信息,在了解了数据库的情况和参数之后再做出初步的评估,比如我们需要知道这个700G的数据库中那些表占用了多少空间,索引占了多少空间有一个SQL可以直接查看到这些信息),是否做了表分区。

了解参数的时候可以看看服务器硬件信息,比如内存、硬盘、是否做了RAID策略、什么操作系统、数据库的版本、内存的压力、CPU的压力等等信息。了解这些信息是我们决定是否迁移到新的服务器的重要因素。

如果决定了进行数据迁移,那么为了不影响我们的生产的数据库,让生产数据库还能进数据,我们一次要搬多少条记录才是合适的,这个我们也是需要计算的。搬迁的Job尽量让时间间隔大点,如果前一个Job还没有执行完的话,后一个Job即使到了时间也是不会执行的。)

(三) 设计思路

三、参考脚本

下面列出一些重点的sql,供参考。

--1.1,创建表

  1. CREATE TABLE [dbo].[Temp_MoveManage](  
  2.  
  3.     [Id] [intNOT NULL,  
  4.  
  5.     [IsDone] [bitNOT NULL,  
  6.  
  7.     [UpdateTime] [datetime] NULL,  
  8.  
  9.  CONSTRAINT [PK_Temp_MoveManage] PRIMARY KEY CLUSTERED   
  10.  
  11. (  
  12.  
  13.     [Id] ASC 
  14.  
  15. )WITH (IGNORE_DUP_KEY = OFFON [PRIMARY]  
  16.  
  17. ON [PRIMARY

--1.2插入数据

  1. declare @i int   
  2.  
  3. set @i=1   
  4.  
  5. while @i < 50000000  
  6.  
  7. begin   
  8.  
  9.     insert into dbo.Temp_MoveManage values(@i,0)  
  10.  
  11.     set @i = @i + 50000  
  12.  
  13. end 

--1.3测试

  1. select * from Temp_MoveManage 

--2,链接服务器(省略)

--3,存储过程

  1. SET ANSI_NULLS ON 
  2.  
  3. GO  
  4.  
  5. SET QUOTED_IDENTIFIER ON 
  6.  
  7. GO  
  8.  
  9. -- =============================================  
  10.  
  11. -- Author:    <Viajar>  
  12.  
  13. -- Create date: <2011.04.14>  
  14.  
  15. -- Description:   <转移数据>  
  16.  
  17. -- =============================================  
  18.  
  19. ALTER PROCEDURE [dbo].[sp_GetMoveData]  
  20.  
  21. AS 
  22.  
  23. BEGIN 
  24.  
  25.     DECLARE @Id1 INT,@Id2 INT 
  26.  
  27.     DECLARE @MaxId INT--原表的最大值  
  28.  
  29.     SET @Id1 = 0  
  30.  
  31.     SET @Id2 = 0  
  32.  
  33.     SELECT TOP 1 @Id1 = Id FROM Temp_MoveManage WHERE IsDone = 0 ORDER BY Id  
  34.  
  35.     SELECT TOP 1 @Id2 = Id FROM Temp_MoveManage WHERE IsDone = 0 AND Id > @Id1 ORDER BY Id  
  36.  
  37.     SELECT @MaxId = MAX(Id) FROM [dbo].[ClassifyResult]  
  38.  
  39.     IF(@Id1 != 0 AND @Id2 != 0 AND @MaxId>=@Id2)  
  40.  
  41.     BEGIN 
  42.  
  43.        DECLARE @sql VARCHAR(MAX)  
  44.  
  45.        SET @sql = '  
  46.  
  47.        SET IDENTITY_INSERT [ClassifyResult_T] ON   
  48.  
  49.        INSERT INTO [dbo].[ClassifyResult_T](  
  50.  
  51.            [Id]   
  52.  
  53.           ,[ClassId]  
  54.  
  55.            ,[ArchiveId])  
  56.  
  57.        SELECT   
  58.  
  59.           [Id]   
  60.  
  61.           ,[ClassId]  
  62.  
  63.            ,[ArchiveId]  
  64.  
  65.        FROM [dbo].[ClassifyResult]  
  66.  
  67.        WHERE Id >= '+ CONVERT(VARCHAR(100),@Id1) + ' and Id < '+ CONVERT(VARCHAR(100),@Id2) + ' 
  68.  
  69.        ORDER BY Id  
  70.  
  71.        SET IDENTITY_INSERT [ClassifyResult_T] OFF '  
  72.  
  73.        EXEC (@sql)  
  74.  
  75.        UPDATE Temp_MoveManage SET IsDone = 1 WHERE Id = @Id1  
  76.  
  77.     END 
  78.  
  79. END 

--4,Job(省略)

四、缺陷

缺陷1:在CreateTable生成的表中,最后一条记录无法执行,因为最后一个Id是使用<,没有用=,所以在转移的表中最后一条记录是没有转移过分区表的;

缺陷2:如果转移表的记录同时在不断的增长,那么数据就无法把最新的数据转移到分区表了;针对这个缺陷,本来的想法是为搬迁辅助表的Id分段加多一些记录,这样就可以执行最新数据;

缺陷3:对于上面的那个问题,也是有缺陷的,例如现在Id分段是100和200,当新数据Id>100的某段时间,这两个分段值的IsDone就会给更新为1,这样就会造成缺失了很多数据;针对这个缺陷,也是可以解决的,先去判断当新数据的Id>200的时候,才执行导Id为100和200分段的脚本;在存储过程中判断Max(Id)就可以了)

五、注意

六、其它

这是一些朋友的建议,这里还没有尝试,因为环境的限制,比如数据库是简单模式了等情况。这里记录下,期待适合环境的童鞋拿去用。

编辑推荐】

www.htsjk.Com true http://www.htsjk.com/shujukugl/17805.html NewsArticle SQL Server数据库迁移偏方 一、目的 之前在博文SQL Server数据库最小宕机迁移方案中提到了使用了完全备份+差异备份的功能完成了数据库的转移,但是这个方法在遇到了700多G的数据时显然...
评论暂时关闭