SQL Server 复制:事务发布

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文: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
目录
相关文章
|
4天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
12天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
241 1
|
1月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
161 1
|
5天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
42 3
|
4天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
5天前
|
SQL 数据库 索引
SQL中如何实现事务?
【6月更文挑战第17天】SQL中如何实现事务?
14 2
|
5天前
|
SQL 弹性计算 API
云服务器 ECS产品使用问题之如何通过API调用阿里云服务器上SQL Server数据库中的数据
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
15天前
|
SQL 关系型数据库 MySQL
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
24 5
|
1天前
|
SQL 存储 关系型数据库
ArcGIS Engine连接ArcSDE SQL Server(获得所有SDE图层)
ArcGIS Engine连接ArcSDE SQL Server(获得所有SDE图层)