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

2018-01-31 560

一.本文所涉及的内容（Contents）

二.背景（Contexts）

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

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

（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的错误信息；

（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'广州')

（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) 在发布服务器上创建一个发布，具体步骤如下图所示：

（Figure3：新建发布）

（Figure4：选择数据Barfoo.TestPublish）

（Figure5：选择事务发布）

（Figure6：选择需要发布的字段）

（Figure7：不勾选）

（Figure8：设置快照代理）

（Figure9：设置代理安全性）

（Figure10：设置日志读取器代理）

（Figure11：设置帐号密码）

（Figure12：创建发布）

（Figure13：发布名称）

（Figure14：创建成功）

（Figure15：创建的发布）

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

（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脚本执行成功，数据库会返回下面的提示信息：

（Figure17：创建订阅返回信息）

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

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

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

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

（Figure18：订阅服务器属性）

（Figure19：设置安全性）

（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) 检查发布服务器和订阅服务器的订阅状态；

（Figure21：启动复制监视器）

（Figure22：订阅状态）

（Figure23：订阅服务器的本地订阅）

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

（Figure24：UserInfo表数据）

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

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

（Figure25：发布服务器上UserInfo表数据）

（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；

（Figure27：数据库只读）

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

（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

（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（原文）

微信关注我们

原文链接：https://yq.aliyun.com/articles/485423

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Ubuntu 16.04下安装 VSCode

官网下载安装地址：https://code.visualstudio.com/ Linux下的vscode文档地址：https://code.visualstudio.com/docs/setup/linux image.png 绑定vim键映射 image.png 安装插件 image.png 支持python image.png 2、为VSCode安装扩展插件用VSCode编程是需要依赖扩展的。写python需要安装python的扩展，写C++需要安装C++的扩展。刚打开编辑器的时候，它一般会推荐一些扩展。在python的学习中我们需要安装的几个插件：其中 vscode - icons插件可以不安装，他就是一个图标美化插件而已 image.png image.png

2018-02-01

760

pandas.DataFrame.groupby

pandas提供了一个灵活高效的groupby功能，它使你能以一种自然的方式对数据集进行切片、切块、摘要等操作。根据一个或多个键（可以是函数、数组或DataFrame列名）拆分pandas对象。计算分组摘要统计，如计数、平均值、标准差，或用户自定义函数。对DataFrame的列应用各种各样的函数。应用组内转换或其他运算，如规格化、线性回归、排名或选取子集等。计算透视表或交叉表。执行分位数分析以及其他分组分析。 DataFrame. groupby ( by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs ) [source] Group series using mapper (dict or key function, apply given function to group, return result as series) or by a series of columns. Parameters: by: mapping, function...

2018-02-01

568

发表评论

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。