目的

实现MS SQL数据库镜像标准化操作，实现数据库高可用，提升数据库运维操作能力。

适用范围

MS SQL 2008及以上版本

责任人员

网络管理员/网络工程师

说明

"数据库镜像"是一种提高 SQL Server 数据库的可用性的解决方案。 镜像基于每个数据库实现，并且只适用于使用完整恢复模式的数据库。

数据库镜像维护一个数据库的两个副本，这两个副本必须驻留在不同的 SQL Server 数据库引擎 服务器实例上。 通常，这些服务器实例驻留在不同位置的计算机上。

启动数据库上的数据库镜像操作时，在这些服务器实例之间形成一种关系，称为"数据库镜像会话"。其中一个服务器实例使数据库服务于客户端（"主体服务器"）， 另一个服务器实例则根据镜像会话的配置和状态，充当热备用或温备用服务器（"镜像服务器"）。

master数据库。记录 SQL Server 实例的所有系统级信息。

msdb数据库。用于 SQL Server 代理计划警报和作业。

model数据库。用作 SQL Server 实例上创建的所有数据库的模板。 对 model 数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。

Resource 数据库。一个只读数据库，包含 SQL Server 包括的系统对象。 系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中。

tempdb 数据库。一个工作空间，用于保存临时对象或中间结果集。

具有自动故障转移功能的高安全性模式要求使用第三个服务器实例，称为"见证服务器"。

主体服务器 (principal server)。在数据库镜像中，是指当前作为主体数据库的数据库所属于的伙伴。

镜像服务器 (mirror server)。在数据库镜像配置中，镜像数据库所在的服务器实例。

见证服务器 (Witness)。仅用于高安全性模式，SQL Server 的一个可选实例，它能使镜像服务器识别何时要启动自动故障转移。 与这两个故障转移伙伴不同的是，见证服务器并不能用于数据库。 见证服务器的唯一角色是支持自动故障转移。

伙伴服务器，Partner：相对于镜像环境而言，镜像服务器就是主体服务器的伙伴服务器，而主体服务器也是镜像服务器的伙伴服务器。

端点，Endpoint：绑定到网络协议中的对象，允许SQL Server通过端点在网络间交互。

会话，Session：活动于镜像环境中，用于维护服务器之间的状态信息和关系。简单来说就是镜像环境中各个伙伴服务器之间信息的传递者。

运行模式，Operating Mode：表示镜像环境的安全级别，镜像的运行模式有三种：带有自动故障转移的高安全性模式（带有见证服务器的同步模式），不带有自动故障转移的高安全性模式（没有见证服务器的同步模式），高性能模式（没有见证服务器的异步同步）。

角色，Role：在镜像环境中的功能，同一时刻，一个特定的服务器只能是三种角色中的其中一种：主体、镜像或见证。

【日志传送】

原理是利用作业定时备份还原（所以为数据库级别），可将备份的日志还原到任意多个副本（可本地或异地），副本一般不作为可读。延迟性主要是备份还原的频繁度，因为数据库日志备份间隔都不会设置的很频繁。

主要应用场景是：有意让副本延迟，以防主库误操作丢失数据时，副本可很快恢复到一段时间前的状态。可当做冗余的备库。
缺点：副本延迟最大；副本不可用占用资源；主副本损坏不会自动切换，且所有副本都得重新配置（因为日志还原的事务序列不一样）。

【事务复制】

复制有多种，主要讲事务复制。事务复制是数据库中对象级别的同步，原理是读取事务日志，找出某个数据库某对象的事务日志，生产命令同步到中间库（分发服务器数据库），通过分发可将数据同步到任意多个副本（可本地或异地）。同步操作主要为存储过程的操作。

主要应用场景是：2012 以前，主要使用的高可用之一，副本的索引可以重新定义。主要用于报表服务器查询、开发人员有时需要的数据查询、也可作为一主多从的读写分离。事务复制应用很灵活。

缺点：表必须有主键；对象粒度小，管理麻烦；事务是顺序处理的，网络IO依赖较大；发布有问题，则都需要重新配置；

【镜像】

在数据库级别上产生一个镜像副本。原理是读取事务日志，将日志块实时同步（或同步或异步）到镜像库中。可在域环境或非域环境配置。

主要应用场景是：对当前数据库的一个冗余副本，副本可自动切换。如当前数据库损坏，副本可恢复使用。

缺点：只能镜像一个副本；副本不可读；

【alwayson 故障转移集群实例（FCI）】

在多台windows 服务器集群的基础上，创建sql server 故障转移集群，但 mssql 集群共享同一个存储，即数据库文件是放在共享存储上的，集群上的数据库实例都是访问同一个共享存储上的数据文件。alwayson FCI 是实例级别的。

主要应用场景是：为了防止单台服务器宕机情况，或是为了减轻单台服务器资源消耗。有虚拟IP，可自动切换到各个实例。

缺点：依赖windows域环境和windows集群；数据存储单节点，IO还是没有减少；集群实例资源没有有效利用（当然可创建A-A群集，一服务器多个实例）

【alwayson 可用性组（AG）】

2012的新功能，是sql server 的一种革命性功能。相当于结合了复制和镜像的特性，为数据库级别同步。数据库在windows群集上的服务器是独立安装的，数据存储在各个服务器上。 原理是读取主副本事务日志，将日志分别同步到各个服务本。有侦听器（虚拟IP / 名称），可切换到任意一个副本，副本同样保存同步。

主要应用场景是：满足镜像和复制的场景。

缺点：依赖windows域环境和windows集群；维护主要在主副本（备份可在其他副本）

2016 后，AlwaysOn AG 可不依赖于域环境，但仍依赖于windows 集群
2017后，AlwaysOn AG 可不依赖于域环境，也可不依赖与windows集群，即可进行跨平台（linux）搭建

数据库镜像简介

SQL Server镜像只有两种模式：高安全模式和高性能模式。两种模式的主要区别在于在事务提交后的操作。

在高性能模式下，主体服务器不需要等待镜像服务器响应即可提交事务。

在高安全性模式，需要把事务同步到镜像并得到响应后才最终提交主体服务器的事务。

数据库镜像操作

数据库镜像操作简介

如果服务器使用Local System作为SQL Server服务账号，就需要使用证书授权。证书授权同时也可以在你的服务器不能通过其他服务器的账号访问对方服务器或者你不想授权给Windows登录时使用。使用证书搭建镜像的步骤如下：

1、创建数据库主密钥（如果主密钥不存在）。

2、在Master数据库中创建证书并用主密钥加密。

3、使用证书授权创建端点（endpoint）。

4、备份证书成为证书文件。

5、在服务器上创建登录账号，用于提供其他实例访问。

6、在master库中创建用户，并映射到上一步的登录账号中。

7、把证书授权给这些用户。

8、在端点上授权。

9、设置主体服务器的镜像伙伴。

10、设置镜像服务器的主体伙伴。

11、配置见证服务器。（可选）

系统环境简介

主数据库

操作系统：windows 2012 R2 X64；

IP地址：192.168.111.19；

计算机名称：mirror-m；
数据库：SQL2012 X64 SP3 –11.0.6020.0 (X64)

镜像数据库

操作系统：windows 2012R2 X64；

IP地址：192.168.111.19；

计算机名称：mirror-s；

数据库：SQL2012 X64 SP3 –11.0.6020.0 (X64)

配置前环境检查

1、检查系统网络环境。确保2台服务器的网络是否可用。

2、检查数据库端口（1433端口）是否可用（使用 telnet 192.168.111.19 1433）。如果端口不可用，检查数据库协议设置并确保防火墙策略正常。注意检查已开启的协议内容和对应的开放端口。

3、检查服务器环境。确保数据库版本（包括补丁版本）一致；检查并确认数据库文件和日志文件所在的盘符和路径一致。要确保镜像服务器端的文件路径和主服务器端的一致。

USE master 
go 
SELECT physical_name--物理文件路径 
FROM sys.master_files 
WHERE database_id = DB_ID('sysauth')

4、检查数据库环境。检查数据库的恢复模式和兼容级别，确保数据库恢复模式为"完整"，兼容级别为"SQL2012"。

USE master 
go 
SELECT name [数据库名] ,recovery_model_desc [恢复模式] ,
CASE WHEN [compatibility_level] = 90 THEN '2005'
WHEN [compatibility_level] = 100 THEN '2008'
WHEN [compatibility_level] = 110 THEN '2012'
WHEN [compatibility_level] > 110 THEN '2012+'
ELSE '2000 or lower version'
END [兼容级别]
FROM sys.databases
WHERE name = 'sysauth'
USE master
GO
ALTER DATABASE sysauth SET RECOVERY FULL WITH NO_WAIT
GO

主数据库上创建证书

1、创建数据库主密钥

use master
go
create master key encryption by password='www.com';

2、创建证书，使用主密钥加密

create certificate mirror_m_cert with subject='mirror_m_certificate',expiry_date='2028-1-1'; -

3、创建端点

if not exists (select 1 from sys.database_mirroring_endpoints ) 
begin
create endpoint [databasemirroring\] state = started as tcp (listener_port=5022,listener_ip=all)
for database_mirroring(authentication=certificate mirror_m_cert,encryption=required algorithm AES,role=all);
end
backup certificate mirror_m_cert to file='c:\PerfLogs\mirror_m_cert.cer';
create login mirror_s_login with password='www.com';
create user mirror_s_user for login mirror_s_login;

镜像数据库上创建证书

操作方式及步骤参考6.4，需要注意的是证书的命名和用户账号名称不能和主数据库重名。

use master
go
create master key encryption by password='www.com';
create certificate mirror_s_cert with subject='mirror_s_certificate',expiry_date='2028-1-1';
if not exists (select 1 from sys.database_mirroring_endpoints ) 
begin
create endpoint [databasemirroring] state = started as tcp (listener_port=5022,listener_ip=all)
for database_mirroring(authentication=certificate mirror_s_cert,encryption=required algorithm AES,role=all);
end
backup certificate mirror_s_cert to file='c:\PerfLogs\mirror_s_cert.cer';
create login mirror_m_login with password='www.com';
create user mirror_m_user for login mirror_m_login;

使用证书授权用户

1、在主数据库中执行以下操作导入镜像服务器的证书并映射到新建的用户，同时完成登录账号对访问端口的授权。

create certificate mirror_s_cert authorization mirror_s_user from file ='c:\PerfLogs\mirror_s_cert.cer';
grant connect on endpoint::[databasemirroring] to mirror_s_login;
create certificate mirror_m_cert authorization mirror_m_user from file ='c:\PerfLogs\mirror_m_cert.cer';
grant connect on endpoint::[databasemirroring] to mirror_m_login;

主数据库完整备份

BACKUP DATABASE sysauth TO DISK = 'c:\PerfLogs\sysauth_full.bak'
BACKUP LOG sysauth TO DISK = 'c:\PerfLogs\sysauth_log.trn'

镜像数据库恢复数据库

RESTORE DATABASE sysauth FROM DISK = 'c:\PerfLogs\sysauth_full.bak' WITH NORECOVERY
RESTORE LOG sysauth FROM DISK = 'c:\PerfLogs\sysauth_log.trn' WITH NORECOVERY
GO

添加镜像伙伴

1、在主服务器上执行以下脚步，把mirror-s添加成为msrror-m的伙伴。

ALTER DATABASE sysauth SET PARTNER = 'TCP://mirror-s:5022';

2、在镜像服务器上执行以下脚步，把mirror-m添加成为msrror-s的伙伴。

ALTER DATABASE sysauth SET PARTNER = 'TCP://mirror-m:5022';

检查确认

常见操作

主机与镜像转换

执行成功后原主体数据库会显示正在还原，备机数据库显示主体正在同步字样。

--主机
use master;
alter database sysauth set partner failover;

测试主数据库和镜像切换

主机mirror-m崩溃，强制镜像服务器（mirror-s）当主机，原主机恢复后再切换回去。

1、在mirror-s上执行，数据库镜像手工切换。

use master;
alter database sysauth set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收

2、停止主机mirror-m的SQL SERVER 服务(比如断电)，此时备机上的数据库会显示正在恢复状态，大概持续几十秒，最后变成sysauth(主体，已断开链接),即现在的镜像mirror-s可以用来充当主机了。

3、假如现在有业务往数据库里插也是能成功的。

USE SysAuth
GO
CREATE TABLE MyTable ([Guid] [uniqueidentifier] NOT NULL,[SID] [varbinary](85) NOT NULL,[Name] [nvarchar](50) NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([Guid] ASC,[SID] ASC)ON [PRIMARY]) ON [PRIMARY]
GO
ALTER TABLE MyTable ADD CONSTRAINT [DF_MyTable_Guid] DEFAULT (newid()) FOR [Guid]
ALTER TABLE MyTable ADD CONSTRAINT [DF_MyTable_SID] DEFAULT (suser_sid()) FOR [SID]
INSERT INTO MyTable([Name]) SELECT 'KK'
GO 15000

use master;
alter database sysauth set partner resume;

alter database sysauth set partner failover;