代码改变世界

SQL Server 通过备份文件初始化复制

2013-09-09 11:39 听风吹雨 阅读(...) 评论(...) 编辑 收藏

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 搭建过程(Process)
  4. 注意事项(Attention)
  5. 疑问(Questions)
  6. 参考文献(References)

二.背景(Contexts)

  MySQL在对有历史数据的数据库进行搭建复制(Master/Slave)的时候,可以通过在Master服务器备份历史数据,利用这个备份文件在Slave进行还原;这样做的好处是可以更加快速的搭建好环境,因为可以对备份文件进行压缩、分包,并且可以使用FTP等工具保证传输过程的安全与快捷;详情可参考:Windows下搭建MySQL Master Slave

  当SQL Server遇到同样需要对历史数据库搭建复制,通常的做法是在本地发布快照,再由订阅传输数据,那SQL Server应该如何实现备份历史数据搭建复制(发布/订阅)呢?下图是备份文件初始化订阅的基本逻辑结构图:

clip_image002

(Figure0:备份文件初始化订阅逻辑结构图)

三.搭建过程(Process)

(一) 环境信息

系统环境:Windows Server 2008 + SQL Server 2008

发布服务器:192.168.1.105,服务器名称:QuZhoushiwei105

分发服务器:与发布服务器同一台机器

订阅服务器:192.168.1.106,服务器名称:QuZhoushiwei106

发布数据库:Barfoo.TestPublish

订阅数据库:Barfoo.TestSubscribe

数据库帐号:ReplicationUser/ ReplicationPassword

说明:发布服务器与订阅服务器是在同一内网的机器,如果你的环境是跨网段(跨机房)的请参考:SQL Server 跨网段(跨机房)部署

 

(二) 搭建步骤

1) 在发布服务器上以QuZhoushiwei105服务器名称登陆发布服务器,如果你以localhost或者IP形式登陆服务器,在创建发布的时候会出现下图Figure1的错误信息;

clip_image004

(Figure1:错误信息)

登陆服务器之后使用下面的SQL脚本创建一个测试数据库:Barfoo.TestPublish,创建一个测试表:UserInfo,并插入一条数据,用于模拟历史数据;

--创建测试数据库
USE MASTER
GO
CREATE DATABASE [Barfoo.TestPublish]
GO

--创建测试表
USE [Barfoo.TestPublish]
GO
CREATE TABLE [dbo].[UserInfo](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [names] [nvarchar](50) NULL,
    [address] [nvarchar](50) NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

--插入测试数据
INSERT [dbo].[UserInfo] ([names],[address]) VALUES (N'gaizai', N'广州')

clip_image005

(Figure2:UserInfo表记录)

 

2) 在发布数据库和订阅服务器上分别执行下面的SQL脚本创建帐号和密码(ReplicationUser/ ReplicationPassword);

--发布服务器创建帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'
GO
USE [Barfoo.TestPublish]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [Barfoo.TestPublish]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

--订阅服务器创建帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'
GO

 

3) 在发布服务器上创建一个发布,具体步骤如下图所示:

clip_image006

(Figure3:新建发布)

clip_image007

(Figure4:选择数据Barfoo.TestPublish)

clip_image008

(Figure5:选择事务发布)

clip_image009

(Figure6:选择需要发布的字段)

clip_image010

(Figure7:不勾选)

clip_image011

(Figure8:设置快照代理)

clip_image012

(Figure9:设置代理安全性)

clip_image013

(Figure10:设置日志读取器代理)

clip_image014

(Figure11:设置帐号密码)

clip_image015

(Figure12:创建发布)

clip_image016

(Figure13:发布名称)

clip_image017

(Figure14:创建成功)

clip_image018

(Figure15:创建的发布)

 

4) 设置发布属性中的订阅选项,把允许从备份文件初始化的默认值false设置为true;也可以使用下面的SQL脚本进行修改;

clip_image019

(Figure16:Allow initialization from backup files)

--修改发表允许从备份中初始化
USE [Barfoo.TestPublish]
GO
DECLARE @publication AS sysname
SET @publication = N'testpub'
EXEC sp_changepublication
    @publication = @publication, 
    @property = N'allow_initialize_from_backup', 
    @value = true
GO

 

5) 使用下面的SQL脚本备份数据库Barfoo.TestPublish,保留备份文件,在后面创建订阅的时候需要用到;

--备份数据
BACKUP DATABASE [Barfoo.TestPublish] 
TO  DISK = N'G:\DBBackup\Barfoo.TestPublish_20130822.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Barfoo.TestPublish-完整数据库备份', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

6) 在订阅服务器:192.168.1.106上使用下面的SQL脚本还原刚刚的备份文件;

--还原数据库
RESTORE DATABASE [Barfoo.TestSubscribe] 
FROM  DISK = N'G:\DBBackup\Barfoo.TestPublish_20130822.bak' 
WITH  FILE = 1,  
MOVE N'Barfoo.TestPublish' TO N'G:\DataBase\Barfoo.TestSubscribe.mdf',  
MOVE N'Barfoo.TestPublish_log' TO N'G:\DataBase\Barfoo.TestSubscribe_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

 

7) 在订阅服务器行修改帐号ReplicationUser,SQL脚本如下:

--修改帐号ReplicationUser
USE [Barfoo.TestSubscribe]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

 

8) 在发布服务器上执行sp_addsubscription存储过程添加订阅,SQL脚本如下:

--在发布服务器创建订阅
EXEC sp_addsubscription
    @publication = N'testpub',
    @subscriber ='QuZhoushiwei106',
    @destination_db = N'Barfoo.TestSubscribe',
    @subscription_type = N'Push',
    @sync_type = N'initialize with backup',
    @backupdevicetype='disk',
    @backupdevicename='G:\DBBackup\Barfoo.TestPublish_20130822.bak'

如果上面的SQL脚本执行成功,数据库会返回下面的提示信息:

clip_image021

(Figure17:创建订阅返回信息)

注意:如果备份文件之后相隔太长时间,执行上面的脚本有可能会出现下面的错误信息,如果遇到这个问题,可以参考下面【疑问】的内容:

消息21397,级别16,状态1,过程sp_MSaddautonosyncsubscription,第271 行

对从指定备份创建的非同步订阅进行同步时需要一些事务,但这些事务在分发服务器上不可用。请使用更新的日志以及差异或完整数据库备份再试此操作。

 

9) 检查新添加的订阅属性中的安全性->订阅服务器连接,确认正确的帐号和密码,默认是使用代理帐号;

clip_image022

(Figure18:订阅服务器属性)

clip_image024

(Figure19:设置安全性)

clip_image026

(Figure20:设置登录名密码)

也可以通过下面的SQL脚本设置订阅服务器连接的帐号密码:

--设置订阅服务器帐号密码
EXEC sp_addpushsubscription_agent 
    @publication = N'testpub', 
    @subscriber = N'QUZHOUSHIWEI106', 
    @subscriber_db = N'Barfoo.TestSubscribe', 
    @subscriber_security_mode = 0, 
    @subscriber_login = N'ReplicationUser', 
    @subscriber_password = N'ReplicationPassword'

 

10) 检查发布服务器和订阅服务器的订阅状态;

clip_image027

(Figure21:启动复制监视器)

clip_image029

(Figure22:订阅状态)

clip_image030

(Figure23:订阅服务器的本地订阅)

 

11) 查看订阅服务器QuZhoushiwei106的数据库Barfoo.TestSubscribe的UserInfo表的数据;

clip_image031

(Figure24:UserInfo表数据)

 

12) 使用下面的SQL脚本在发布服务器上UserInfo表插入新数据,测试复制,分别查看发布服务器与订阅服务器的数据;

--测试订阅数据
INSERT [dbo].[UserInfo] ([names],[address]) VALUES (N'viajar', N'北京')

clip_image032

(Figure25:发布服务器上UserInfo表数据)

clip_image033

(Figure26:订阅服务器上UserInfo表数据)

四.注意事项(Attention)

1. 在SQL SERVER下实现发布服务器和订阅服务器的通信正常(即可以互访),打开1433端口,在防火墙中设置入站规则;

2. 发布服务器与订阅服务器的SQL Server Agent代理帐号必须设置的一样,否则不能互访;

3. 后期添加新的表需要手动在订阅服务器创建表结构,主要先在发布属性的项目中勾选新表,再通过表的右键菜单创建表结构脚本。

五.疑问(Questions)

(一) 怎么确保在发布服务器持续进数据的情况下,如何保证在拷贝历史数据备份之后还能知道订阅从哪个LSN开始读取?

解答:如果备份文件之后相隔太长时间,执行上面的脚本有可能会出现下面的错误信息:

消息21397,级别16,状态1,过程sp_MSaddautonosyncsubscription,第271 行

对从指定备份创建的非同步订阅进行同步时需要一些事务,但这些事务在分发服务器上不可用。请使用更新的日志以及差异或完整数据库备份再试此操作。

如果遇到这个问题,有3种解决办法:

A. 按照上面的提示,对Barfoo.TestPublish数据库做一个差异备份,再在Barfoo.TestSubscribe数据库做差异还原,需要注意的是在使用sp_addsubscription的时候应该指定差异备份的文件;

--差异备份数据库
BACKUP DATABASE [Barfoo.TestPublish] 
TO  DISK = N'G:\DBBackup\Barfoo.TestPublish_Diff_20130822.bak' 
WITH NOFORMAT, NOINIT, DIFFERENTIAL,  NAME = N'Barfoo.TestPublish-差异数据库备份', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--完整还原数据库
RESTORE DATABASE [Barfoo.TestSubscribe] 
FROM  DISK = N'G:\DBBackup\Barfoo.TestPublish_20130822.bak' 
WITH  FILE = 1,  
MOVE N'Barfoo.TestPublish' TO N'G:\DataBase\Barfoo.TestSubscribe.mdf',  
MOVE N'Barfoo.TestPublish_log' TO N'G:\DataBase\Barfoo.TestSubscribe_log.ldf',  
NOUNLOAD,  REPLACE,  NORECOVERY,  STATS = 10
GO

--差异还原数据库
RESTORE DATABASE [Barfoo.TestSubscribe] 
FROM  DISK = N'G:\DBBackup\Barfoo.TestPublish_Diff_20130822.bak' 
WITH  FILE = 1,  
MOVE N'Barfoo.TestPublish' TO N'G:\DataBase\Barfoo.TestSubscribe.mdf',  
MOVE N'Barfoo.TestPublish_log' TO N'G:\DataBase\Barfoo.TestSubscribe_log.ldf',  
NOUNLOAD,   STATS = 10
GO

--在发布服务器创建订阅
EXEC sp_addsubscription
    @publication = N'testpub',
    @subscriber ='QuZhoushiwei106',
    @destination_db = N'Barfoo.TestSubscribe',
    @subscription_type = N'Push',
    @sync_type = N'initialize with backup',
    @backupdevicetype='disk',
@backupdevicename='G:\DBBackup\Barfoo.TestPublish_Diff_20130822.bak'

B. 如果你的数据库Barfoo.TestPublish可以接受短时间不写入数据,可以在做完整备份之前就先设置数据库为只读状态,在数据库【属性】-【选项】-【状态】-【数据库为只读】设置为True;

clip_image034

(Figure27:数据库只读)

C. 最后一种方式最常使用,是创建好发布之后马上关掉distributor cleanup这个JOB,可参考:Implementing NoSync Initializations (and variations) on SQL Server 2005/8(CareySon),关掉这个JOB,创建订阅之后数据会同步到订阅服务器上,但是不会对订阅执行dbo.sp_MSdistribution_cleanup,可以使用CHECKPOINT 1进行测试;

clip_image035

(Figure28:分发清除)

(二) 如果是transactional的replication,创建完毕之后是会产生对应的三个Job,下面3个是复制作业中的Job,他们的作用分别是什么呢?

A. QUZHOUSHIWEI105-Barfoo.TestPublish-12

B. QUZHOUSHIWEI105-Barfoo.TestPublish-testpub-12

C. QUZHOUSHIWEI105-Barfoo.TestPublish-testpub-QUZHOUSHIWEI106-22

QUZHOUSHIWEI105-Barfoo.TestPublish-12,这是REPL-LogReader类别的JOB,一个数据库只会有一个日志读取器作业,命名的格式是:ServerName-DBName-Num;

QUZHOUSHIWEI105-Barfoo.TestPublish-testpub-12,这是REPL-Snapshot类别,的JOB,一个发布(也叫做一条同步链)对应一个快照作业,命名的格式是:ServerName-DBName-PublishName-Num;

QUZHOUSHIWEI105-Barfoo.TestPublish-testpub-QUZHOUSHIWEI106-22,这是一个REPL-Distribution类别的JOB,一个订阅对应一个分发作业,命名的格式是:ServerName-DBName-PublishName-ServerName-Num;

可以通过下面的SQL脚本查询订阅JOB和订阅属性的相关信息:

--返回订阅信息
SELECT * FROM msdb.dbo.sysjobs WHERE category_id=10
--返回订阅属性信息
SELECT [description]
      ,[name]
      ,[allow_initialize_from_backup]
      ,[min_autonosync_lsn]
  FROM [Barfoo.TestPublish].[dbo].[syspublications]
GO

clip_image037

(Figure29:订阅配置信息)

六.参考文献(References)

初始化事务订阅(不使用快照)

如何从备份初始化事务订阅(复制 Transact-SQL 编程)

使用快照初始化订阅

Replication Without Creating a Snapshot(CareySon)

Implementing NoSync Initializations (and variations) on SQL Server 2005/8(CareySon)

SQL Server事务复制通过备份文件进行订阅初始化

SQL Server复制用备份文件初始化订阅

ALTER AUTHORIZATION (Transact-SQL)

Server2008+SQL2008 日志读取代理器未运行 进程无法在“WIN-XXX”上执行“sp_replcmds

SQLServer Replication 常见错误

sp_addsubscription (Transact-SQL)

sp_addpullsubscription_agent (Transact-SQL)

SQL Server数据库事务日志序列号(LSN)介绍(译文)

Log sequence numbers(原文)