SQL Server 数据库镜像操作指南

1.简介

数据库高可用环境的搭建可以参考以下文章

SQL Server 高可用（always on）配置指南之域(AD)环境搭建       ​​https://blog.51cto.com/waringid/5851856​​

SQL Server 高可用（always on）配置指南之节点配置      ​​https://blog.51cto.com/waringid/5851970​​

SQL Server 高可用（always on）配置指南之搭建故障转移群集     ​​https://blog.51cto.com/waringid/5854694​​

SQL Server 高可用（always on）配置指南之数据库安装    ​​https://blog.51cto.com/waringid/5854730​​

SQL Server 高可用（always on）配置指南之数据库侦听器及高可用   ​​https://blog.51cto.com/waringid/5859821​​

alwayson故障模拟及处理方案  ​​https://blog.51cto.com/waringid/5868824​​

2.数据库相关名词

• 主体服务器，Principal：在镜像环境中，包含活动库的源服务器，可以理解为主服务器。
• 镜像服务器，Mirror：在镜像环境中，包含目标数据库的服务器，即镜像环境中的目标服务器。
• 见证服务器，Witness：可选的一个服务器，用于监控主体服务器和镜像服务器，最主要的作用是进行自动故障转移（automatic Failover）。
• 伙伴服务器，Partner：相对于镜像环境而言，镜像服务器就是主体服务器的伙伴服务器，而主体服务器也是镜像服务器的伙伴服务器。
• 端点，Endpoint：绑定到网络协议中的对象，允许SQL Server通过端点在网络间交互。
• 会话，Session：活动于镜像环境中，用于维护服务器之间的状态信息和关系。简单来说就是镜像环境中各个伙伴服务器之间信息的传递者。
• 运行模式，Operating Mode：表示镜像环境的安全级别，镜像的运行模式有三种：带有自动故障转移的高安全性模式（带有见证服务器的同步模式），不带有自动故障转移的高安全性模式（没有见证服务器的同步模式），高性能模式（没有见证服务器的异步同步）。
• 角色，Role：在镜像环境中的功能，同一时刻，一个特定的服务器只能是三种角色中的其中一种：主体、镜像或见证。

3.数据库镜像模式

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

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

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

4. 数据库镜像操作

4.1. 数据库镜像操作简介

如果服务器使用Local System作为SQL Server服务账号，就需要使用证书授权。

证书授权同时也可以在你的服务器不能通过其他服务器的账号访问对方服务器或者你不想授权给Windows登录时使用。

使用证书搭建镜像的步骤如下：

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

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

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

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

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

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

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

8、在端点上授权。

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

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

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

4.2. 系统环境简介

主数据库

操作系统：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)

4.3. 配置前环境检查

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

4.4. 主数据库上创建证书

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(authenticatinotallow=certificate mirror_m_cert,encryptinotallow=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;

4.5. 镜像数据库上创建证书

操作方式及步骤参考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(authenticatinotallow=certificate mirror_s_cert,encryptinotallow=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;

4.6. 使用证书授权用户

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;

4.7. 主数据库完整备份

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

46.8. 镜像数据库恢复数据库

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

4.9. 添加镜像伙伴

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';

4.10. 检查确认

5. 常见操作

5.1. 主机与镜像转换

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

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

7.2. 测试主数据库和镜像切换

主机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;