【sql server镜像】SQL SERVER 基于数据库镜像的主从同步(数据库镜像实践汇总)

本文详细介绍了SQL SERVER数据库镜像的概念,包括服务器角色、模式优缺点,以及实施前提和步骤。通过实例展示了如何进行主从同步、故障排查和监控,强调了自动故障转移和见证服务器的角色。此外,还探讨了数据库镜像在高安全性、高性能模式下的运行模式,并提供了常见故障解决方案和镜像监控方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

SQL SERVER 基于数据库镜像的主从同步

Author:chaoqun.guo    createtime:2019-03-26

 

 

目录

SQL SERVER 基于数据库镜像的主从同步... 1

1、概念... 2

1.1、服务器概念... 2

1.2、模式概念... 2

1.3、数据库镜像的优势... 3

1.4、数据库镜像的不足... 3

2、实施前提(基于证书访问实现)... 4

3、实施步骤(基于证书访问实现)... 4

3.1、步骤目录... 4

3.2、实操... 5

3.3、主备切换... 7

4、数据库镜像监控... 9

4.1、数据库镜像监视器... 9

4.2、系统存储过程监视(联机丛书—监视数据库镜像)... 9

5、常见故障排查... 11

【5.1】服务器网络地址 “TCP://xxx:5022″ 无法访问或不存在。... 11

【5.2】TCP://xxx:5022 的数据库镜像连接错误... 11

【5.3】程序如何在主从切换时自动切换连接?... 11

【5.4】孤立用户问题... 11

【5.5】自增键在镜像中的影响... 11

【5.6】镜像服务器与主服务器断开... 11

【5.7】其他相关... 12

附录1:数据库镜像主从+见证中断情况... 13

1.1、1个点中断的情况... 13

1.2、2个点中断的情况... 13

1.3、镜像集群监控概述... 13

附录2:镜像相关信息查阅... 14

2.1、查看master中的证书... 14

2.2、查看镜像端点状态(联机丛书视图参考)... 14

2.3、查看端点连接权限... 14

2.4、常见删除语句... 14

2.5、更多查阅... 14

附录3:加入见证服务器,实现自动故障转移(基于证书)... 15

附录4:基于域账户的镜像搭建... 18

4.1、操作步骤... 18

4.2、操作实践... 18

参考文献... 25

 

 

1、概念

1.1、服务器概念

◆ 主体服务器(Principal Server)

  其中一个实例为客户端提供服务,这个实例称为"主体服务器"。该服务器"扮演"主体角色",其数据库副本为当前的"主体数据库"。

 

◆ 镜像服务器(Mirror Server)

  另一个实例则充当备用服务器,这个实例称为"镜像服务器"(Mirror Server)。该服务器扮演"镜像角色",其数据库副本为当前的"镜像数据库"。镜像数据库不能供客户端访问,但是可以为镜像数据库创建一个快照,让客户端访问这个快照。

 

◆ 见证服务器(Witness Server)

  见证服务器并不能用于数据库,只是用来支持自动故障转移。见证服务器验证主体服务器是否保持运行,当见证服务器与主体服务器断开连接之后,如果此时镜像服务器和见证服务器保持相互连接,则镜像服务器启动自动故障转移,成为新的主体服务器。

 

 

1.2、模式概念

数据库镜像会话以同步操作或异步操作运行。

  在同步操作下,事务将在伙伴双方处提交。由于主体数据库需要等待镜像数据库将日志写入磁盘后返回的确认消息,因此会延长事务滞后时间。在异步操作下,事务不需要等待镜像服务器将日志写入磁盘便可提交,这样可最大程度地提高性能。

在SQL SERVER 2008之后,主库和镜像库之间的日志流传送会默认使用压缩,压缩一方面降低了网络压力,另一方面增大了镜像两端的CPU压力。 可以打开 TF 1462 来关闭日志流压缩,SQL SERVER 2005 上日志传送没有使用压缩。

 

根据是否同步操作以及是否支持自动故障转移功能,数据库镜像有以下三种运行模式。

(1) 高安全性模式:主库把事务日志数据信息发给从库,从库返回事务日志持久化确认信息,确认同步后,事务将在主从库一起提交。

(2) 高性能模式:主库把事务日志数据信息发给从库,发完后无需等待从库返回确认信息。

(3) 自动故障转移模式(高可用模式):在高安全模式运行时,可以添加见证服务器,从而实现自动故障转移。

 

 

1.3、数据库镜像的优势

数据库镜像技术有以下优势:

 

(1)消除存储方面的单一故障点:不用共享磁盘

(2)提高数据库可用性:快速自动/手动主从切换

(3)增强的数据保护:提供完整的数据冗余

(4)自动页修复:2008企业版在某些类型的错误导致页损坏,使其无法读取后,在 SQL Server 2008 企业版或更高版本上运行的数据库镜像伙伴(主体或镜像)将尝试自动修复该页。无法读取该页的伙伴将从其伙伴请求该页的新副本。如果此请求成功,则将以可读副本替换不可读的页,并且这通常会解决该错误。

 

1.4、数据库镜像的不足

镜像数据库技术有以下不足之处:

 

(1)版本限制

  对于标准版的 SQL Server 实例,只可以使用"高安全模式",即主体数据库与镜像数据库必须实现同步操作。在这种运行模式时,如果任何一个伙伴遇到性能问题,都将使同步操作带来较大的延时。通常标准版的 SQL Server 受到一些技术限制导致性能不能提升,从而使同步操作的延时更加明显。

 

(2)镜像数据库的访问限制

  镜像数据库甚至不可以提供只读访问,只有通过创建快照才能访问,因此镜像数据库的利用率不高。

由于数据库镜像技术存在上述不足,SQL Server 后续产品将删除此项功能,建议改用 AlwaysOn 可用性组。SQL Server 2016 标准版中已经引入了"AlwaysOn 基本可用性组",用来替代数据库镜像技术。

  

2、实施前提(基于证书访问实现)

(1)实例版本:必须是2005 SP1及以上(且兼容级别也要在这个版本及以上),且主从版本一致。

(2)数据库版本:必须是标准版及更高的开发/评估/企业,且只有企业版/开发版才能实现高性能模式。且主从一致。

(3)通信:确认网络能ping通,确定端点端口(默认一般是5022)与实例端口(默认1433)可以telnet 通实现访问。

(4)磁盘:足够的磁盘空间(全备复制+事务备复制+还原空间+预留空间)。最好主从是相同的目录(不同会造成无法加文件)。

(5)限制:不支持 FILESTREAM。不能在主体上创建它。不能为包含 FILESTREAM 文件组的数据库配置数据库镜像。

(6)系统:32位系统下,单实例最多支持10个数据库做镜像。

(7)主数据库:镜像的数据库对象不能是系统数据库。主库必须是完整恢复模式。且主从库必须是相同的数据库名。

(8)从数据库:利用主数据库的相关备份进行还原,必须以norecovery模式(RESTORING 状态)

(9)权限:登录名具有实施步骤权限,最好sysadmin。可能还需要实例账户拥有一定程度的windows权限,最好是admin组;

(10)端点:镜像两端的加密算法必须保持一致,否则无法搭建。(因为不同版本默认加密算法不一样)小版本没关系,但前提是从库可以还原主库。

 

 

3、实施步骤(基于证书访问实现)

3.1、步骤目录

主服务器host_A

从服务器host_B

【1】在master中,创建数据库秘钥

【1】创建master数据库秘钥

【2】基于【1】中秘钥,创建服务器实例加密证书的出站证书

【2】基于【1】中秘钥,创建服务器实例加密证书的出站证书

【3】使用服务器实例的证书为该服务器实例创建端点。

【3】使用服务器实例的证书为该服务器实例创建端点。

【4】将证书备份到文件,并将其安全地复制到从服务器。

【4】将证书备份到文件,并将其安全地复制到主服务器。

【5】为从服务器创建登录名。

【5】为主服务器创建登录名。

【6】创建一个使用该登录名(【5】中的)的用户

【6】创建一个使用该登录名(【5】中的)的用户

【7】使用证书对【6】中用户授权

【7】使用证书对【6】中用户授权

【8】对【5】中登录名进行连接端点的授权

【8】对【5】中登录名进行连接端点的授权

【9】主数据库设置为完全恢复模式,并以全备传输到从服务器

【9】以norecovery模式还原需要镜像的库

【10】(后做)设置镜像伙伴为从库

【10】(先做)设置镜像伙伴为主库

【11】核验,基于SSMS与T-SQL

【11】核验,基于SSMS与T-SQL

 

 

 

 

 

 

3.2、实操

主服务器:SQLSVR1, 192.168.1.1

从服务器:SQLSVR2, 192.168.1.2

--1、创建 master 数据库主密钥

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO

 

--2、对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

 

USE master;
CREATE CERTIFICATE SQLSVR1_cert	WITH SUBJECT = 'SQLSVR1 certificate for database mirroring', 
   start_date='2019-03-01',expiry_date='2030-01-01';
GO

 

--3、 使用主服务器实例的证书 SQLSVR1_cert 为主服务器 SQLSVR1 创建端点。
Use master;
CREATE ENDPOINT [默认的镜像端点]
   STATE = STARTED AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   ) 

FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SQLSVR1_cert
### SQL Server 数据库主从同步配置最佳实践 #### 配置SQL Server 主从同步概述 为了实现高可用性和数据冗余,SQL Server 提供了多种复制方式来设置主从同步。最常用的方式是通过事务复制(Transactional Replication),它允许实时或接近实时的数据传输。 #### 准备工作 确保主数据库镜像数据库使用相同的实例账户,并重新启动数据库服务 `SQL Server (MSSQLSERVER)`[^2]。这一步骤对于确保两个环境的一致性至关重要。 #### 创建发布者(Publisher) 和订阅者(Subscriber) 1. **创建发布者** - 打开 SQL Server Management Studio 并连接到要作为发布者的服务器。 - 展开“复制”,右键单击“本地出版物(Local Publications)”并选择“新建出版物(New Publication)...”。 ```sql USE [distribution] GO EXEC sp_adddistributor @distributor = N'DIST_SERVER_NAME', @password = N'dist_password' GO ``` 2. **定义文章(Articles)** - 在向导中指定要发布的表或其他对象。可以选择整个表、视图或是查询结果集的一部分。 3. **设定快照代理(Snapshot Agent)属性** - 可以立即生成初始快照或将现有备份用于初始化目的。 #### 添加订阅者 - 连接到发布者所在的服务器,在“复制监视器(Copy Monitor)”下找到对应的出版物。 - 选择“新建推送订阅(New Push Subscription...)”或“新建拉取订阅(New Pull Subscription...)" 来添加新的订阅者节点。 ```sql -- 推送订阅示例 exec sp_addsubscription @publication = 'YourPublicationName', @subscriber = 'SUBSCRIBER_SERVER_NAME', @destination_db = 'TargetDatabase', @sync_type = 'automatic', @article = 'all', @update_mode = 'read only'; go ``` #### 日志传送(Log Shipping) 除了传统的复制机制外,还可以考虑采用日志传送技术来进行灾难恢复准备。此功能会定期将主服务器上的交易日志备份发送给次级站点,并应用这些更改保持两者之间尽可能一致的状态。 #### 故障排查常见问题 当遇到同步失败的情况时,可以从以下几个方面入手: - 检查网络连通性:确认源端和目标端之间的通信正常无阻塞; - 查看错误日志:查看 SQL Server 错误日志和服务管理控制台中的记录寻找线索; - 调整权限设置:验证登录账号是否拥有足够的权利执行必要的操作; - 更新统计信息:有时过期的统计数据可能导致性能下降甚至同步中断;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值