大容量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
猜你喜欢
- 张同学 10.4号开始发视频,视频的点赞量一直很高,11.17 号的视频达到了顶峰,收获 250w 个赞,之后关注量也开启了暴涨。所以挖掘
- python之Pyperclip模块下面介绍一下,python中的Pyperclip模块,它的简单又实用,主要用法就2点:1.用于复制剪贴板
- 词频统计是指在文本中计算每个词出现的次数。在 Python 中,可以使用一些第三方库(如 jieba)来分词,然后使用字典等数据结构记录每个
- 一:操作session1:session配置Session 的配置文件存储在config/session.php中,配置参数有:(1):配置
- 前言:在使用pycharm学习python的时候,经常需要第三方库,没有第三方库程序就会报错,pycharm也会提醒你要安装所需要的库,安装
- Cookie 对象是一种以文件(Cookie文件)的形式保存在客户端硬盘的Cookies文件夹中的数据信息(Cookie数据)。Cookie
- mysql中用命令行复制表结构的方法主要有一下几种: 1.只复制表结构到新表CREATE TABLE 新表 SELECT * FR
- 兼容主流浏览器,独立样式表,可以很方便的进行视觉效果自定义截图:模拟下拉菜单演示代码:<!DOCTYPE html PUBLIC &q
- 一、为什么要配置文件1、容易编辑,配置比较集中,方便修改,在大业务量的系统里面,通过配置会方便后人理解整个系统的架构2、做到业务代码和环境解
- 本文实例讲述了Python读写及备份oracle数据库操作。分享给大家供大家参考,具体如下:最近项目中需要用到Python调用oracle实
- 有一个优秀的库可以使用————demjson示范链接http
- 废话不多说,先给大家展示一下效果图,如果大家感觉不错,请参考实现代码。效果图如下所示:代码如下所示:<!DOCTYPE html PU
- 以下针对Ubuntu系统,Windows系统没有测试过。Ubuntu中默认就安装有Python 2.x和Python 3.x,默认情况下py
- mysql截取字符串的6个函数1、LEFT(str,len)从左边开始截取,str:被截取字符串;len:截取长度示例:2、RIGHT(st
- 1 包简介1.1 工作空间go语言的工作空间必须由 bin、pkg、src三个目录组成,可以在GOPATH环境变量中添加多个工作空间,但不能
- 在预测商品销量时,如果预测多了(预测值比真实销量大),商家损失的是生产商品的成本;而如果预测少了(预测值比真实销量小),损失的则是商品的利润
- 在 Web 编辑器领域,CKEditor – 七年的专注,赢取的是王者风范。TinyMCE – 五年前的小家碧玉,如今已成长为大家闺秀。Go
- 代码如下:ALTER proc [dbo].[sp_common_paypal_AddInfo] ( @paypalsql va
- Python的matplotlib模块中的errorbar函数可以绘制误差棒图,本次主要绘制不带折线的误差棒图。1.基本参数errorbar
- 在开始做mysql分页功能组件前,便设定的是要有一定可复用性。先在项目里Views文件夹下右键新建名为_PaginationComponen