(初稿)SQL Server 复制(Replication)系列(2)——事务复制搭建

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文: (初稿)SQL Server 复制(Replication)系列(2)——事务复制搭建 本文演示如何搭建最基本的事务复制。环境准备:虚拟机2台:服务器名分别为RepA和RepB,RepA为发布服务器,RepB为订阅服务器。
原文: (初稿)SQL Server 复制(Replication)系列(2)——事务复制搭建

本文演示如何搭建最基本的事务复制。

环境准备:

虚拟机2台:

服务器名分别为RepA和RepB,RepA为发布服务器,RepB为订阅服务器。均安装WindowsServer 2008R2英文版(在外企工作的原因)。并确保两台服务器能互访。

SQL Server:

在上面的虚拟机中分别安装SQLServer 2008 R2 x64 英文企业版。

演示数据库:

微软示例数据库AdventureWorks2008 R2

 

配置事务复制

复制的配置可以使用T-SQL来实现,但是所需的T-SQL及存储过程加起来可能上百个,所以这里以SSMS操作来演示。

事务复制的配置需要对发布服务器、分发服务器和订阅服务器都进行配置。本例使用发布和分发服务器均在一个实例上的情景:

下面是拓扑图:


步骤一:检查数据库是否已经参与了复制环节,这一步为了保证避免重复对一个数据库进行复制配置:

USE master
go
SELECT  name ,
        CASEis_published
          WHEN0 THEN N'未发布'
          ELSE N'已发布'
        END N'是否发布' ,
        CASEis_subscribed
          WHEN0 THEN N'未订阅'
          ELSE N'已订阅'
        END N'是否订阅'
FROM    sys.databases
WHERE   name = 'AdventureWorks2008R2'



步骤二:配置分发服务器:

分发服务器是事务复制的核心。其他组件的运行都必须以分发服务器的可用为前提。所以是首要配置项。首先登录SSMS,这里是RepA。然后右键下图:




这是实例级别的配置,可以单独在独立的实例上配置,但是为了成本考虑,很多实际环境都不会这样用,所以我也把分发和发布部署在同一个实例上。配置步骤很简单:

步骤2.1:配置分发向导:


这步直接跳过也是没有问题的,简单说一下,如果选择了分发发布同一实例,则选择上面的选项,如果是独立的分发实例,就选择下面的选项。由于本例使用分发与发布相同实例,所以这里点击下一步。

步骤2.2:配置分发代理:



从描述中得知,用于配置SQLServeragent是否在机器启动后自动运行,作为正式应用,这里几乎没有理由选择手动启动,所以直接下一步。大家是否好奇为什么这里就有一个可点击的【Finish】?因为微软系列的产品大部分都已经比较自动化,所以实际上都已经默认了一些配置,读者可以直接点【Finish】看结果,如果没有进一步的配置,SQLServer会默认使用下面的路径来存放分发数据:



我们先回到上一步。

步骤2.3.快照文件夹:


前面一文说过,所有类型的复制都以快照为基础,事务复制中的快照用于初始化之用,所以所有的复制类型都要配置快照文件夹,每次初始化,复制会把发布服务器中配置为发布项的数据进行快照,然后存放到这个文件夹中,根据pull还是push模式,再进行对这个文件夹数据的处理。

需要说明的是这里是【共享文件夹】,由于订阅服务器需要从这个文件夹读取数据,所以这个文件夹最起码要让复制所涉及的账号有读的权限,对于发布服务器,还需要有写的权限。

这个快照文件夹可以是本机的任意路径或者网络路径,通常来说,这个路径应该创建在分发服务器上的网络共享盘,本例中就是在发布服务器上。比如使用下面截图中的路径,然后赋予合适的权限。在例子中,我使用在C盘下创建一个叫做ReplicationSnaeshotFolder的文件夹,然后赋权,这里创建一个叫做【授权用户】(AuthenticatedUsers)的用户组,然后赋予写的权限,添加【Everyone】,授予读的权限。



然后我们用这里的路径作为快照的共享文件夹路径:



步骤2.4.配置分发数据库:


这一步主要是配置分发数据库存放在哪个地方,由于演示环境的限制,都放在C盘,如果可以,建议独立分开,最起码不要放在C盘,系统盘,你懂的。

步骤2.5:配置发布者(发布服务器):


 

这里可以配置分发者使用的账号密码,及其他混合项,Impersonatethe agent process account是指用sqlagent的账号作为连接发布者的账号。


另外,还可以在这里指定是SQLServer的发布还是oracle的发布。

步骤2.6:配置完成


这里可以选择直接配置或者只生成脚本,建议两者都选择,这样一旦有问题,脚本还是可以很快重建。最后点【完成】,让SQLServer自己去配置。

最后可以看到系统数据库文件夹下出现了新数据库:


步骤三:配置发布

3.1. 发布(Publication):

这是一个名词,前面提到过,是一组项目(articles)的逻辑集合,每个发布可以包含一个或多个项目。发布中的配置项会影响它包含的所有项目,通过这种方式,可以减少管理对象所带来的影响。其中最重要的配置项就是复制类型。由于本文用微软示例数据库,所以这里不用再创建测试数据库。

3.2. 新建发布:

打开图中的部分,配置发布项:


3.3. 发布数据库:

这里是选择需要进行发布的数据库。


3.4. 复制类型:


这里我们选择单纯的事务发布,对于第三个选项,实际上就是【对等发布】,在后续文章中演示。

3.5. 选择发布对象(article)

我们随便找几个,但是这里注意,事务复制要求表上必须有主键,我随便创建了一个简单的表,没有主键,在选择的时候就能看到这种情况:


补充一下,这里选表的时候,可以指定某些列,也可以全表发布。但是即使你没勾选主键列,只要你选了表中的某些列,主键列也会被自动选上的。

3.6. 筛选表数据

上面一步是对列进行筛选,这一步是对行进行筛选,可以对已经选择的列中数据进行条件筛选,然后点下一步:


3.7. 配置快照引擎


这里可以选择在配置完毕之后马上创建快照,并在订阅初始化之前都保留这个快照,也可以选择安排特定时间运行,比如系统闲时。也可以都不勾选。很多例子上都会勾选第一项,但是本人建议全部不勾选,特别是对大数据库。这部分在后面的文章再介绍,这里只做演示。

3.8. 代理安全性


 

本文选择借用sqlagent来运行,出于安全性,通常会创建专用的域账号或者本地账号(非域环境)和安全的密码,这样不需要经常更改。

3.9. 完成发布:


这里和配置分发一样,可以保存脚本。

3.10. 定义发布项的名称:

这个名称是标识每个项目的名字,这里我使用FirstPublication作为发布项的名称


步骤4:配置订阅

订阅服务器实际上可以在同一台机器,不过在实践中,通常是分开服务器,所以这里也单独一台服务器,在完成发布之后,接下来就是配置订阅,接收来自发布的信息:

首先要保证两台机器能互联,另外,复制技术要求使用服务器名,如果使用IP地址或者别名,会报错,错误信息如下,所以你需要使用服务器名来登录,本例中使用RepB登录:


4.1. 配置订阅项

现在连到订阅服务器,本例使用RepB,这里的步骤基本上和配置发布项是一样的


4.2.

这里有个需要提醒的,如果两个服务器不在域中,由于DNS解析问题,可能需要修改host文件,路径在:

C:\Windows\System32\drivers\etc\host  把对方服务器的IP加进去即可:


另外防火墙等问题都是要预先处理的。


连接之后,就能看到发布项,如下图:


4.3. 选择分发代理位置

实际上就是选择订阅类型,在第一篇中已经提到它们的区别,这里选择pull模式:


4.4. 选择订阅者

实际上就是订阅数据库,这里下拉框可以选择新库,或者现有的库,如果选择新库,而且是库比较大的时候,初始化的时候会很久,所以对于第一次部署,并且是大库,建议先把发布库的备份还原到本地,这样就减少需要同步的数据量。由于是演示库,我这里新建一个不同名的库,库名也可以相同。


这里新建一个叫Adventureworks_Rep的库,注意图32中的红框部分,有时候复制中的报错是因为这里没有填写,如果权限要求不是很严格的话,可以填入sa。


 

4.5. 订阅代理安全性:

这里可以选择专用的账号,也可以使用SQLServer代理账号,为了简便,这里选择代理账号,但是对于正式环境,还是建议使用专用账号:


4.6. 同步计划

这里可以选择是连续运行还是按需运行,因为是事务复制,我们实际上是希望能尽量实时同步,如果不是,其实合并复制也行,所以我们这里选择连续运行:


 

初始化订阅:

可以选择马上进行初始化或者在第一次同步时初始化,这里选择马上初始化,然后点下一步


由于在配置发布时没有初始化,所以目前可以看到订阅库中没有任何表


我们只需要初始化发布项即可。


 

 

Errormessages:

·         Theprocess could not execute 'sp_replcmds' on 'REPA'. (Source: MSSQL_REPL, Errornumber: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011

·         Cannotexecute as the database principal because the principal "dbo" doesnot exist, this type of principal cannot be impersonated, or you do not havepermission. (Source: MSSQLServer, Error number: 15517)
Get help: http://help/15517

The process could not execute'sp_replcmds' on 'REPA'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

 

 

完成后可以看到本地订阅中已经有订阅项:


在配置的过程中,我们可能会遇到各类问题,但是绝大部分问题都是可以解决的,而且很多问题都是因为一个根源导致的,比如下面的两个报错:

报错一:

英文描述:

Cannot execute as the database principal because theprincipal "dbo" does not exist, this type of principal cannot beimpersonated, or you do not have permission.

中文描述:

无法作为数据库主体执行,因为主体 "dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。

已将数据库上下文更改为 'AdventureWorks2008' (Microsoft SQL Server,错误: 15517)

问题2:

SQL Server 2008 replication failing with: process couldnot execute 'sp_replcmds'

这两个问题虽然会有其他原因引起,但是通常来说是因为发布的数据库所有者未填写,此时可以查一下数据库属性->文件->所有者如果没有话,填个sa再试一次就可以。

 


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
8月前
|
SQL 数据库 数据安全/隐私保护
SQL Server数据库Owner导致事务复制log reader job无法启动的解决办法
【8月更文挑战第14天】解决SQL Server事务复制Log Reader作业因数据库所有者问题无法启动的方法:首先验证数据库所有者是否有效并具足够权限;若非,使用`ALTER AUTHORIZATION`更改为有效登录名。其次,确认Log Reader使用的登录名拥有读取事务日志所需的角色权限。还需检查复制配置是否准确无误,并验证Log Reader代理的连接信息及参数。重启SQL Server Agent服务或手动启动Log Reader作业亦可能解决问题。最后,审查SQL Server错误日志及Windows事件查看器以获取更多线索。
|
5月前
|
SQL Oracle 关系型数据库
[SQL]事务
本文介绍了事务处理的基本概念,包括事务的四大特性(原子性、一致性、隔离性、持久性)及生命周期。文章还详细解释了事务的保存点、四种事务隔离级别及其异常读现象,并提供了设置事务隔离级别的方法。最后,作者建议读者深入学习相关理论以更好地理解事务隔离级别。
105 0
|
7月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
7月前
|
SQL 存储 监控
SQLServer事务复制延迟优化之并行(多线程)复制
【9月更文挑战第12天】在SQL Server中,事务复制延迟会影响数据同步性。并行复制可通过多线程处理优化这一问题,提高复制效率。主要优化方法包括:配置分发代理参数、优化网络带宽、调整系统资源、优化数据库设计及定期监控维护。合理实施这些措施可提升数据同步的及时性和可靠性。
252 0
|
9月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
257 13
|
9月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
160 9
|
9月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
116 6
|
7月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
400 0
|
8月前
|
SQL 监控 供应链
|
8月前
|
SQL 数据库连接 网络安全
SQLServer非默认端口下事务复制代理作业服务无法启动的处理
【8月更文挑战第14天】若SQL Server非默认端口下的事务复制代理作业无法启动,可按以下步骤解决:1) 确认服务器连接字符串正确无误,包括非默认端口号;2) 检查防火墙设置,确保允许非默认端口的连接;3) 核实SQL Server配置已启用非默认端口;4) 查阅代理作业日志寻找错误详情;5) 重启SQL Server与Agent服务;6) 使用工具测试非默认端口的连接性。如问题持续,请寻求专业支持。
118 0

热门文章

最新文章

下一篇
oss创建bucket