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

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