SQL Server 复制:事务发布

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文:SQL Server 复制:事务发布一、背景   在复制的运用场景中,事务发布是使用最为广泛的,我遇到这样一个场景:在Task数据库中有Basic与Group两个表,需要提供这两个表的部分字段给其它程序读取放入缓存,程序需要比较及时的获取到这些数据,作为DBA你需要从权限和性能控制的角度出...
原文: SQL Server 复制:事务发布

一、背景

  在复制的运用场景中,事务发布是使用最为广泛的,我遇到这样一个场景:在Task数据库中有Basic与Group两个表,需要提供这两个表的部分字段给其它程序读取放入缓存,程序需要比较及时的获取到这些数据,作为DBA你需要从权限和性能控制的角度出发,我采用了SQL Server的事务复制技术和timestamp,下面只讲述事务复制的搭建过程;

 

二、实现过程

(一) 环境信息

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

发布服务器:192.168.1.151,服务器名称:USER-H2B2A89PEK

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

订阅服务器:192.168.1.152,服务器名称:USER-FJMO8L052U

发布数据库:Task

订阅数据库:TaskSiteInfo

数据库帐号:ReplicationUser/ ReplicationPassword

 

(二) 搭建步骤

A. 发布服务器配置

  首先在发布数据库和订阅数据库上创建相同的帐号和密码(ReplicationUser/ ReplicationPassword),并且设置Task数据库的安全对象,设置这样的帐号的目的就是为了和程序连接到数据库的帐号区分开,可以做权限上的控制,方便问题的排查;

--更改安全对象的所有权

ALTER AUTHORIZATION ON DATABASE::[Task] TO [ReplicationUser]

在E盘目录下创建文件夹:E:\ReplData,并设置这个文件夹为共享目录,共享用户为barefootadmin;

image

(Figure1_1:文件夹权限)

这里需要设置SQL Server Agent登陆帐号为上面文件夹访问用户barefootadmin;

image

(Figure1_2:SQL Server Agent登陆帐号)

image

(Figure2:分发服务器)

  如果你设置快照文件夹路径为:E:\ReplData,即使你的发布服务器本身就是分发服务器,如果订阅服务器是另外一台机器,那么在请求(Pull)订阅(如果是推送(Push)订阅就没有这个限制)模式下订阅代理是无法访问到这个快照文件的;除非你发布服务器、分发服务器和订阅服务器都是同一台机器;你应该设置快照文件夹路径为:\\USER-H2B2A89PEK\ ReplData;

image

(Figure3:快照文件夹)

image

(Figure4:数据库)

  • 快照发布:隔一段时间会覆盖订阅服务器的数据库,在订阅服务器上做的修改同样被覆盖;
  • 事务发布:是一种接近实时地从源到目标分发数据的方法;
  • 具有可更新订阅的事务发布:订阅服务器可更新发布服务器的数据;
  • 合并发布:发布服务器和订阅服务器的更新都会同步到对方,注意ID在合并发布上的冲突

image

(Figure5:事务发布)

注意表必须有主键才能进行复制,选择你必要的字段,这样可以减轻快照文件的大小和传输时间,而且在业务逻辑上更加安全,如果有需要,你还可以对记录进行过滤;

image

(Figure6_1:表字段)

image

(Figure6_2:表字段)

image

(Figure6_3:表字段)

image

(Figure7:快照代理)

image

(Figure8:安全设置)

使用上面创建好的ReplicationUser帐号作为连接到发布服务器的帐号和密码;

image

(Figure9:使用刚刚创建的帐号密码)

image

(Figure10:创建发布)

image

(Figure11:发布名称)

image

(Figure12:查看复制情况)

 

B. 订阅服务器配置

创建完发布服务器(分发服务器也一起创建了),接下来就可以创建订阅服务器了,下面是具体的步骤:

image

(Figure13:查找发布服务器)

image

(Figure14:查找发布服务器)

image

(Figure15:选择发布)

image

(Figure16:请求订阅)

image

(Figure17:订阅数据库)

image

(Figure18:订阅连接)

image

(Figure19:帐号密码)

image

(Figure20:代理计划)

image

(Figure21:初始化订阅)

image

(Figure22:创建订阅)

image

(Figure23:订阅)

image

(Figure24:发布服务器上的订阅)

image

(Figure25:复制监视器)

image

(Figure26:订阅数据库新增的表)

image

(Figure27:表数据)

 

三、注意事项

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

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

3. 如果你希望在复制的过程中一并复制非聚集索引,可以对发布属性-项目进行如下设置,修改完之后需要重新生成快照;

image

(Figure28:非聚集索引复制)

4. 复制代理:快照代理(snapshot agent) 分布式代理(Distribution agent)日志读代理(log Reader agent) 合并代理(Merge agent) 队列读代理(Queue Reader Agent)

5. 适合使用复制的一些场景包括:

1) 负载均衡:通过将数据复制到其它数据库服务器来减少当前服务器的负载,比如说最典型的应用就是分发数据来分离OLTP和OLAP环境;

2) 分区:将经常使用的数据和历史数据隔离,将历史数据复制到其它数据库中;

3) 授权:将一部分数据提供给需要使用数据的人,以供其使用;

4) 数据合并:每个区域都有其各自的数据,将其数据进行合并。比如一个大公司,每个地区都有其各自的销售数据,总部需要汇总这些数据;

5) 故障转移:复制所有数据,以便故障时进行转移;

6. 快照复制或事务复制生成快照文件的类型有:

架构 (.sch)、数据 (.bcp)、约束和索引 (.dri)、约束 (.idx)、触发器 (.trg)(只用于更新订阅服务器)、压缩的快照文件 (.cab)。

 

四、疑问

1. SQL Server 只有在完整日志模式下才能使用复制嘛?

解惑:在简单模式下一样可以使用复制;

2. 如果是跨网段(跨机房)的发布与订阅,有没办法实现?需要注意什么?

解惑:可以通过修改host文件的方式搭建复制,请参考:SQL Server跨网段(跨机房)复制

3. 如果说上面的情况可以在host设置,但是如果有端口映射的,host也无法设置吧?

解惑:在SQL Server配置管理器里建立别名,同样可参考:SQL Server跨网段(跨机房)复制

4. 订阅的形式可以选择推送订阅或者请求订阅,请求订阅降低分发服务器处理工作的开销,这个开销有多大呢?怎么计算影响?

解惑:只有在有很多订阅服务器的时候才比较明显,推送订阅与请求订阅更大的区别是在管理方面的不同;

 

五、参考文献

快照复制

SQL Server 复制

timestamp (Transact-SQL)

SQL Server 复制订阅与发布

SQL Server复制入门(一)----复制简介

SQL Server复制入门(二)----复制的几种模式

使用快照初始化订阅

复制

相关实践学习
使用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中,事务复制延迟会影响数据同步性。并行复制可通过多线程处理优化这一问题,提高复制效率。主要优化方法包括:配置分发代理参数、优化网络带宽、调整系统资源、优化数据库设计及定期监控维护。合理实施这些措施可提升数据同步的及时性和可靠性。
251 0
|
9月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
256 13
|
9月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
159 9
|
9月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
116 6
|
7月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
399 0
|
8月前
|
SQL 监控 供应链
|
9月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
733 1

热门文章

最新文章

下一篇
oss创建bucket