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

Author:chaoqun.guocreatetime:2019-03-26

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 2012 中已经引入了"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、步骤目录

3.2、实操

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

USEmaster;
go

CREATECERTIFICATE SQLSVR1_certWITHSUBJECT ='SQLSVR1 certificate for database mirroring', 

start_date='2019-03-01',expiry_date='2030-01-01';

GO

--3、 使用主服务器实例的证书 SQLSVR1_cert 为主服务器 SQLSVR1 创建端点。

Use master;
go

CREATEENDPOINT [默认的镜像端点]

STATE =STARTED ASTCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

) 
FORDATABASE_MIRRORING ( 

AUTHENTICATION =CERTIFICATE SQLSVR1_cert

, ENCRYPTION =REQUIRED ALGORITHM AES

, ROLE =ALL

);

--4、备份主体服务器 SQLSVR1 的加密证书。一定要保证证书安全无损。

BACKUPCERTIFICATE SQLSVR1_cert TO FILE='d:	emp_bakSQLSVR1.cer';

--5、在主服务器实例的 master 数据库中为镜像服务器创建一个登录名

USEmaster;
go

CREATELOGIN SQLSVR2_login WITHPASSWORD='Sample@#';
GO

--6、为5中新创建的登录名创建一个用户

USEmaster;
go

CREATEUSERSQLSVR2_user FORLOGIN SQLSVR2_login;
GO

--7、将用户与镜像服务器的证书相关联。

--已经将从服务器的证书备份SQLSVR2.CER拷贝到D:	emp_bak

Use master;
go
CREATECERTIFICATE SQLSVR2_cert AUTHORIZATION SQLSVR2_user FROM FILE = 'D:	emp_bakSQLSVR2.cer'
GO

--8、授予其6中登录名对数据库镜像端点的 CONNECT 权限。

GRANTconnect onendpoint::[默认的镜像端点] TO [SQLSVR2_login];

GO

--9、设置SQLSVR1中主库为完整恢复模式，并备份到从服务器SQLSVR2

Alter databases db_name set recovery full with no_wait

Backup databases db_name to disk='URL'

/*

【1】先关闭事务备份作业。

【2】注意：模式的区别
（1）简单模式：那么设置为完整模式后全备到SQLSVR2即可
（2）大容量模式：那么设置为完整模式后，要利用之前的大容量模式下的全备，则需要顾及好日志链；（即要把主备+事务备+设置好完整模式后的事务备）
（3）完整模式：
如要利用以前的备份，则需要全备+全备之后的所有事务备
如果库小可以直接全备，再传输到SQLSVR2
（4）我的业务：主库搭建最佳实践：

*/

--10、（后做）设置镜像主从

Alter database db_name set partner='TCP//192.168.1.2:5022'

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

USEmaster;
go

CREATECERTIFICATE SQLSVR2_certWITHSUBJECT ='SQLSVR2 certificate for database mirroring',

start_date='2019-03-01',expiry_date='2030-01-01';

GO

--3、 使用从服务器实例的证书 SQLSVR2_cert 为从服务器 SQLSVR2 创建端点。

--4、备份从服务器 SQLSVR2 的加密证书。一定要保证证书安全无损。

BACKUPCERTIFICATE SQLSVR2_cert TO FILE='d:	emp_bakSQLSVR2.cer';

--5、在从服务器实例的 master 数据库中为主体服务器创建一个登录名

USEmaster;
go

CREATELOGIN SQLSVR1_login WITHPASSWORD='Sample@#';
GO

--6、为5中新创建的登录名创建一个用户

USEmaster;
go

CREATEUSERSQLSVR1_user FORLOGIN SQLSVR1_login;

GO

--7、将用户与镜像服务器的证书相关联。

--已经将主服务器证书的证书备份SQLSVR1.CER拷贝到D:	emp_bak

Use master;
go
CREATECERTIFICATE SQLSVR1_certAUTHORIZATIONSQLSVR1_user FROM FILE='D:	emp_bakSQLSVR1.cer'

GO

--8、授予其6中登录名对数据库镜像端点的 CONNECT 权限。

GRANTconnect onendpoint::[默认的镜像端点] TO[SQLSVR1_login];

GO

--9、还原SQLSVR1服务器上传过来的主数据库备份文件

Restore database db_name from disk='URL' with norecovery,stats=10,

Move N'logic_name'to 'URL', Move N'logic_name'to 'URL'

/*
【1】注意

（1）还原数据库：一定要和主库的数据库名一摸一样

（2）还原模式：一定要选择norecovery模式，否则将会失败

（3）还原库文件的存放路径：尽量和主库的盘符及目录及名字一摸一样，否则不支持新增文件同步（我们这里没用到，暂时不用管）。

如果机器盘符目录不一致，那么需要在还原时用move选项来进行位置移动。
（4）我的业务：从库搭建最佳实践：

*/

--10、（先做）设置镜像主从

Alter database db_name set partner='TCP//192.168.1.1:5022'

11、核验—基于SSMS

【11.1】主服务器/从服务器，查看数据库状态

右击数据库，刷新一下，看是否有出现这种情况

主服务器从服务器

【11.2】查看数据库属性，镜像选项卡

12、核验—基于T-SQL

--创建快照

createdatabase dbtank_demo1

on(name='db_tank_data',filename='d: emp_bakdb_tank20190325.snap')

asSNAPSHOTof db_tank

go

--查看表数据最早时间（可以多弄几个表之类的看看）

selecttop(5) gettime from dbtank_demo1..sys_users_runegoods

where gettime >='20190320'

orderby gettime desc

--删除快照

drop databases dbtank_demo1

3.3、主备切换

（额外注意事项：（1）系统库的存储过程　　（2）孤立用户　　（3）作业）

【1】.在高安全模式下：

在主机执行:

use master;

alter database Demo1 set partner failover;

即完成主备切换

【2】.在高性能模式下，需要先切换到高安全模式下再执行切换

use master;

alter database Demo1 set partner safety full;

alter database Demo1 set partner failover;

【3】.在主机（SQLSVR1）宕机的情况下在备机（SQLSVR2）进行强制切换：

use master;

alter database Demo1 set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

当主机(SQLSVR1)重新开机后，在SQLSVR2机器上执行

use master;

alter database Demo1 set partner resume;

此时SQLSVR1成为了备机，而SQLSVR2成为了主机。

再到SQLSVR2机器上执行

alter database Demo1 set partner failover;

就成了SQLSVR1成为主机，SQLSVR2成为备机

【4】切换镜像在高性能模式下(慎用，可能会丢失数据)

use master;

alter database Demo1 set partner safety off;

【5】.关闭数据库镜像

ALTER DATABASE Demo1 SET PARTNER OFF

【6】.暂停与恢复数据库镜像

在主体镜像服务器上，若是不小心日志过大，可以进行暂停来设置日志上限

（1）暂停：ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;

（2）恢复：ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

【7】移除见证服务器

USE [master]

GO

ALTER DATABASE Demo1 SET WITNESS OFF

GO

4、数据库镜像监控

4.1、数据库镜像监视器

【1】启动数据库镜像监视器

1. 打开数据库镜像监视器

4.2、系统存储过程监视（联机丛书—监视数据库镜像）

重点查看

Use msdb

Go

--查看2个小时以内的同步数据记录

EXEC sp_dbmmonitorresults db_tank, 2, 0;

【5】常见故障排查

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

请检查网络地址名称，并检查本地和远程端点的端口是否正常运行。(Microsoft SQL Server，错误: 1418)

后来在SQLServer日志中看到了如下错误: Database mirroring connection error 4 'An error occurred while receiving data: '10054(远程主机强迫关闭了一个现有的连接。)'.' for 'TCP://xxx:5022′.

通过这个错误找到了问题，c:ProgramDataMicrosoftCryptoRSAMachineKeys没有读写权限，一看真的是这样，加入管理员(即sqlserver的运行用户)的读写权限后一切正常！（如果还不行，配置权限后，再重新配置镜像）

具体见：http://dba.stackexchange.com/questions/6222/mirroring-problems-after-removing-domain

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

5 'Connection handshake failed. The login 'ZBIANAdministrator' does not have CONNECT permission on the endpoint. State 84.'。

在日志中看到如上这个错误,发现是因为镜像数据库实例没有权限

对主数据库与镜像数据库进行了调整,主数据库与镜像数据库使用相同的实例账户,并重新启动数据库服务SQL Server (MSSQLSERVER)

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

（1）标准办法的连接字符串示例（参考：联机丛书—镜像数据库的连接字符串）

一般形式："Server=Partner_A; Failover Partner=Partner_B; Database=AdventureWorks; "

举例演示："Server=250.65.43.21,4734; Failover Partner=Partner_B; Database=AdventureWorks; "

（2）程序控制：

直接做IP判断即可，断线重试，重试几次后进行伙伴服务器连接尝试。

【5.4】孤立用户问题

当一个数据库从一个实例迁移（恢复）到另外一个实例时，登录名数据并没有随之一起迁移。即使重新新建与数据库用户相同的登录名，却因为SID不同，也成了孤立的用户/孤立的登录名。即-》只有数据库用户。没有登录名，无法登陆实例，则数据库用户无用。 即-》有登录名与同名数据库用户，但是两者没有关联起来，则登录名登录到实例无法访问数据库。解决方案如下：

1. 当一个库刚迁移（恢复）到另外一台实例时，新建同名登录名-》进行用户与登录名更新关联
2. 在主库上查询得到需要同步的用户sid，上在从库上创建登录并设定该登录的sid为查询到的sid
3. 推荐做法：直接重构一遍，删除所有的数据库用户与同名登录名，重新建立登录名与用户映射关系

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

1. 在对表TB1插入两条数据后，表中自增列当期值为 2 发生故障转移
2. 故障转移后，对表TB1插入一条数据，发现表中自增列当期值变成1002

结论：假设故障转移前自增列的当前值为 X ,在故障转移后，自增列的初始值会变成:((X/1000)+1)*1000+1

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

（1）. 因为主库或镜像库存在内存压力，导致无法完成镜像日志传送和重做

解决办法：设置数据库最小内存，保证数据库有足够内存完成镜像操作

（2） 因为主库和镜像库断开连接，导致镜像失败

解决办法：

1.使用TELNET IP 5022来检查双方之间的网络和端口是否打开

2.重启主库和镜像库的镜像端口

ALTER ENDPOINT [Endpoint_Mirroring] STATE=STOPPED

ALTER ENDPOINT [Endpoint_Mirroring] STATE=STARTED

连接上以后，如果主库与镜像没有自动开始继续同步，则使用如下进行尝试

（1）暂停：ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;

（2）恢复：ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

3. 检查证书是否过期和被更换

usemaster;

select*fromsys.certificates

--过期了就新建一个新的证书，然后alter endpoint endpoint_name来应用新的证书。然后从新关联用户与证书。

--查看镜像端点与证书的关系，是否被更换了

4.检查主库是否有非法关机的情况，如果存在，优先运行DBCC CHECKDB和检查备份

如果主库上备份还原失败，则需要使用备份从新搭建镜像

【5.7】其他相关

1. 更换证书：过期或不安全时使用
2. 如何在主库上增加文件：磁盘不足或需要做文件组规划时使用

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

1.1、1个点中断的情况

1. principal 与 witness 连接中断:

（1）状态：主库会变成Disconected 状态，表示失去与mirror连接，切断所有客户端连接， 停止读写服务，等待故障切换（默认超时为10S）

（2）结果：如果mirror与witness正常连接，mirror成为新的principal，开始对外提供读写服务。

1. mirror 与 witness 连接中断

（1）状态：principal与witness连接正常，principal状态变为Disconected，表示终止与mirror连接，mirror状态变为suspend; principal不再向mirror发送事务日志，等待mirror重新建立到witness链接后，principal才会恢复与mirror进行数据同步；

1. principal 与 mirror 连接中断：

principal与mirror同时保持witness的连接会话，但是principal与mirror之间会话中断，witness会通知mirror，principal依然保持连接状态，不会触发故障切换；此时principal由于保持有witness的连接会话，服务正常；

1.2、2个点中断的情况

1. principal 与所有节点会话中断

只要mirror与witness会话正常，即可完成正常的故障转移；如果mirror与witness连接也中断，则无法完成，即便是后来mirror与witness的会话优先恢复，则也无法故障切换，因为已然不确定mirror是否拥有全部principal的数据，此时即便principal处于运行状态，也无法提供服务，等待principal与任意节点会话恢复正常，即可恢复读写服务；

【2】mirror 与 所有节点会话中断

不会触发故障切换，principal切入公开运行模式（异步），即不会再向mirror发送事务日志，也不再需要等待mirror的响应，直到mirror重新恢复会话。

【3】witness 与 所有节点会话中断

不会触发故障切换，principal继续提供读写服务，与mirror数据继续同步，镜像集群丧失自动故障转移能力，退化为不带故障转移的高安全模式；如果三方会话同时连接中断，则principal无法提供服务，直到principal与任意节点通信恢复正常。

1.3、镜像集群监控概述

镜像集群的监控可以通过SQL Server Management stdio启动镜像监视器，或者系统内置的存储过程来实现，监控的主要指标包括：

（1）未发送日志：principal上未发送的日志超过指定的阈值，会在principal上生成一个警告，在高性能模式下，强制服务时可以作为评估principal上事务丢失数量的依据，同样也适用于在高安全模式切换成异步模式状态下（mirror失去连接）

（2）未还原日志：重做队列中的未被应用的事务日志数量（KB），超过阈值，会在mirror上生成一个警告，该值可以作为评估故障转移时间的主要因素。

（3）最早未被发送的事务：principal发送队列中，最早未被发送的事务的至今的时间，单位时分钟，超过阈值，会在principal上生成警告，与未发送日志量一起，从时间维度，衡量高性能模式下和高安全异步模式下，数据丢失数量；

（4）镜像提交开销：高安全模式下，principal上事务从提交到等到mirror响应的时间开销的平均值，如果超过阈值，则在principal上生成一个警告，在同步模式下，该值可以i衡量同步开销；

附录2：镜像相关信息查阅

2.1、查看master中的证书

USE master;

SELECT * FROM sys.certificates;

2.2、查看镜像端点状态（联机丛书视图参考）

（1）数据库镜像端点

SELECTname, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc

FROMsys.database_mirroring_endpoints;

（2）查看所有端点

select*fromsys.endpoints

修改状态：

ALTER ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = <port_number>)

FOR database_mirroring (ROLE = ALL);

GO

2.3、查看端点连接权限

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

GO

2.4、常见删除语句

查看与修改：
更多查阅参考：联机丛书--镜像
删除：

【1】.    删除某终端点(终端点不带引号)   dropendpoint <endpoint_name>

【2】.    删除证书  在master |Security |Certificates (dropmaster key...) 

【3】.    删除用户  在master |User

【4】.    然后可以删除登录名  droplogin <login_name>

【5】.    修改master key:   altermaster keydropencryption byservice master key

【6】.    删除master key: dropmaster key

【7】.    删除镜像的命令: alterdatabse <dbname> setpartner off

2.5、查看镜像状态

参考：如何使用T-SQL查看镜像状态

--====================================================--查看镜像状态
SELECT
DB_NAME(database_id) ASDatabaseName,
dm.mirroring_role ASMirroringRole,
(CASEdm.mirroring_role
   WHEN 1 THEN '主体'
   WHEN 2 THEN '镜像'
   END) ASMirroringRoleDesc,
dm.mirroring_partner_name ASMirroringPartnerName,
(CASE WHEN dm.mirroring_witness_name IS NULL
   THEN '--'
   ELSEdm.mirroring_witness_name
END)ASMirroringWitnessName,
dm.mirroring_state ASMirroringState,
(CASEdm.mirroring_state
   WHEN 0 THEN '已挂起'
   WHEN 1 THEN '与其他伙伴断开'
   WHEN 2 THEN '正在同步'
   WHEN 3 THEN '挂起故障转移'
   WHEN 4 THEN '已同步'
   WHEN 5 THEN '伙伴未同步'
   WHEN 6 THEN '伙伴已同步'
   WHEN NULL THEN '无镜像'
END) ASMirroringStateDesc,
dm.mirroring_safety_level ASMirroringSafetyLevel,
(CASEdm.mirroring_safety_level
   WHEN 0 THEN '未知'
   WHEN 1 THEN '异步'
   WHEN 2 THEN '同步'
   WHEN NULL THEN '无镜像'
END) ASMirroringSafetyLevelDesc,
dm.mirroring_witness_state ASMirroringWitnessState,
(CASEdm.mirroring_witness_state
   WHEN 0 THEN '见证未知'
   WHEN 1 THEN '见证连接'
   WHEN 2 THEN '见证断开'
   WHEN NULL THEN '无见证'
END) ASMirroringWitnessStateDesc
FROMsys.database_mirroring dm
WHERE dm.mirroring_guid IS NOT NULL
 
--============================================================--查看镜像的日志传送
sp_dbmmonitorresults database_name
   , rows_to_return
    , update_status
 
database_name
指定返回其镜像状态的数据库。
rows_to_return
指定返回的行数：
0 =最后一行
1 =最后两小时的行
2 =最后四小时的行
3 =最后八小时的行
4 =最后一天的行
5 =最后两天的行
6 = 最后 100行
7 = 最后 500行
8 = 最后 1,000行
9 = 最后 1,000,000行
update_status
指定返回结果之前，过程：
0 =不更新数据库的状态。仅使用最后两行计算结果，其保留时间取决于何时刷新状态表。
1 =通过在计算结果之前调用 sp_dbmmonitorupdate
来更新数据库的状态。但是，如果在前 15秒内已更新状态表，或用户不是 sysadmin 固定服务器角色的成员，则 sp_dbmmonitorresults
将运行，而不更新状态。
 
--============================================================--创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像数据库的镜像状态。
sp_dbmmonitoraddmonitoring [update_period ]update_period：指定更新间隔（分钟）。此值可以是介于 1 到 120 分钟之间的值。默认值为 1分钟。
 
 
--============================================================--自定义查询--sp_dbmmonitoraddmonitoring 数据来源于dbm_monitor_data
 
WITH tmp AS( SELECT ROW_NUMBER()OVER(
PARTITION BYdm.database_id
ORDER BY dm.[local_time] DESC) ASRID,
 *
FROMmsdb.dbo.dbm_monitor_data dm)
SELECT * FROM tmp WHERE RID=1
 
 
----------------------------------------------------------------------------------补充资料--============================================--MSDN: http://technet.microsoft.com/zh-cn/library/ms173768.aspx--sp_dbmmonitorupdate 会插入镜像相关数据，并将超过天的历史数据删除。
复制代码
复制代码
--===================================================================--创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像数据库的镜像状态。--sp_dbmmonitoraddmonitoring [ update_period ]--update_period--指定更新间隔（分钟）。此值可以是介于1 到120 分钟之间的值。默认值为1 分钟。--要求具有sysadmin 固定服务器角色的成员身份运行,更新间隔过小会影响性能
 
EXEC msdb.sys.sp_dbmmonitoraddmonitoring 3;
 
 
--===================================================================--更改数据库镜像监视参数，设置监视器更新频率--sp_dbmmonitorchangemonitoring parameter, value--parameter--指定要更改的参数的标识符。当前，只有以下参数可用：--1 = 更新周期,数据库镜像状态表的更新间隔期的分钟数。默认间隔为1 分钟。--value--为正在更改的参数指定新值。范围在1 到120 的整数，用于指定新的更新周期（分钟）。
 
exec msdb.sys.sp_dbmmonitorchangemonitoring 1,2
 
 
--===================================================================--查看监视器更新频率--sp_dbmmonitorhelpmonitoring
 
execmsdb.sys.sp_dbmmonitorhelpmonitoring
 
 
--===================================================================--sp_dbmmonitorresults database_name , rows_to_return, update_status
 
--database_name--指定返回其镜像状态的数据库。
 
--rows_to_return--指定返回的行数：--0 = 最后一行--1 = 最后两小时的行--2 = 最后四小时的行--3 = 最后八小时的行--4 = 最后一天的行--5 = 最后两天的行--6 = 最后100 行--7 = 最后500 行--8 = 最后1,000 行--9 = 最后1,000,000 行
 
--update_status--指定返回结果之前，过程：--0 = 不更新数据库的状态。仅使用最后两行计算结果，其保留时间取决于何时刷新状态表。--1 = 通过在计算结果之前调用sp_dbmmonitorupdate 来更新数据库的状态。--但是，如果在前15 秒内已更新状态表，或用户不是sysadmin 固定服务器角色的成员，--则sp_dbmmonitorresults 将运行，而不更新状态。
 
EXEC msdb.sys.sp_dbmmonitorresults DB1, 2, 0;
 
 
 
--===================================================================--停止并删除服务器实例上所有数据库的镜像监视器作业。--要求具有sysadmin 固定服务器角色的成员身份。
 
EXECmsdb.sys.sp_dbmmonitordropmonitoring
 
--===================================================================--检查dbm_monitor_data中数据是否有超过阀值数据,如果有,则报警--status: 数据库的状态：= 已挂起1 = 已断开2 = 正在同步3 = 挂起故障转移4 = 已同步--send_queue_size:在主体的发送队列中未发送日志的大小(KB)。--redo_queue_size:镜像中重做队列的大小(KB)。--role：服务器实例的当前镜像角色：= 主体1 = 镜像，--witness_status：见证状态：= 未知1 = 已连接2 = 已断开
 
usemsdb;
GO
IF (OBJECT_ID('tempdb.dbo.#MirrorResult') IS NOT NULL)
BEGIN
DROP TABLE#MirrorResult
END
GO
WITH tmp AS(
SELECTROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) ASRID,
*
FROMmsdb.dbo.dbm_monitor_data
)
SELECT * INTO #MirrorResult FROMtmp
WHERE RID=1
AND(
([status]<>2 AND [status]<>4)
OR send_queue_size>30000
OR redo_queue_size>30000)
 
--如果表不为空，则镜像可能出现问题
IF EXISTS(SELECT 1 FROM#MirrorResult)
BEGIN
DECLARE @databaseNames NVARCHAR(MAX);
DECLARE @errorMessage NVARCHAR(MAX);
SET @databaseNames='';
SELECT @databaseNames=@databaseNames+DBS.name+'/' FROM#MirrorResult MR
INNER JOINmaster.sys.databases DBS
ON MR.database_id=DBS.database_id
 
set @errorMessage= '数据库：'+@databaseNames+'镜像断开或者存在大量日志为发送或重做'
 
--发送警告
PRINT @errorMessage
 
END
--===================================================================

复制代码
 

复制代码
--=============================================================================--查看当前挂起的镜像或有大量日志积压的镜像
WITH tmp AS(
SELECTROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) ASRID,
*
FROMmsdb.dbo.dbm_monitor_data
)
SELECT * INTO #MirrorResult FROMtmp
WHERE RID=1
AND(
([status]<>2 
AND [status]<>4) 
OR send_queue_size>30000 
OR redo_queue_size>30000)

附录3：加入见证服务器，实现自动故障转移（基于证书）

附录4：基于域账户的镜像搭建

4.1、操作步骤

4.2、操作实践

参考文献

基本故障问题排除请参考：联机丛书—镜像故障排除

证书方式配置与演示参考：联机丛书---使用证书设置数据库镜像

暂停、恢复、删除参考 ：联机丛书---暂停、恢复、删除数据库镜像

监事数据库镜像参考 ：联机丛书---监视数据库镜像

设置数据库镜像：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms190941(v%3dsql.105)

镜像的实现方式参考：http://www.mssqlmct.cn/dba/?post=51

数据库镜像原理：https://blog.csdn.net/jessicaiu/article/details/81906899