SQLServer镜像部署

概述

镜像角色

主节点(principal)：具有完整的数据副本，对外提供读写服务
镜像节点(mirror)：具有完整的数据副本，不提供读写服务，通过接收principal节点的日志实现数据同步，允许创建数据库快照
见证节点(witness)：本身不存储数据，只在高安全允许模式下提供自动故障转移的功能，确保只有一个节点对外提供服务

在镜像群集中，principal和mirror的数据同步依靠事务日志来实现的，与Oracle和Mysql不同，SQL Server的事务日志是DataBase级别的，每个数据库都有单独的事务日志，因此镜像服务是可以基于数据库的。一个数据库只能有一个mirror节点，与Mysql的级联复制不太一样。

SQL Server的事务日志是物理级别的，记录对数据页的操作。principal创建镜像后，会启动一个日志发送线程，维护一个虚拟的发送队列，然后读取事务日志，进行压缩再发送到Mirror节点，Mirror节点接收到后，将其写入本地磁盘的重做队列中，通过异步线程从重做队列中获取事务日志分发给应用线程回放。

镜像运行模式

高性能模式：principal与mirror之间异步传输，无需等待mirror的确认，在principal宕机后可能会造成数据丢失，不支持自动故障转移，适合对数据可靠性要求不高，性能要求比较高的场景，类似与Oracle DataGurad中的最大性能模式
不带故障转移的高安全模式：principal上所有的事务提交，都必须确认该事务已经应用到mirror中，才可提交。可实现principal宕机数据零丢失，不支持故障转移，可手动转移。
带故障转移的高安全模式：与不带故障转移的高安全模式，增加了witness(见证服务器)，可以实现故障自动转移，确保只有一个节点成为principal对外提供数据库服务

带故障转移的高安全模式下自动故障转移服务有以下情况：

principal与witness连接断开此时witness与mirror连接正常，触发自动故障转移，principal状态标记为disconnected，切断客户端连接，停止读写服务，等待故障切换。为避免网络抖动的原因造成不必要的切换，连接超时时间为10S。等待mirror上的重做队列日志回放完成后，mirror成为新的principal对外提供服务。
mirror和witness连接断开此时principal与witness连接正常，principal状态变成disconnected，与mirror断开连接，mirror状态为suspend，不再向mirror发送日志，等待mirror重新连接witness才会恢复与mirror之间的同步。
principal和mirror连接断开 principal 与 mirror 同时保持 witness 的连接会话，但是 principal 与 mirror 之间会话中断，witness 会通知 mirror，principal 依然保持连接状态，不会触发故障切换；此时 principal 由于保持有 witness 的连接会话，服务正常
principal 与所有节点会话中断只要 mirror 与 witness 会话正常，即可完成正常的故障转移；如果 mirror 与 witness 连接也中断，则无法完成，即便是后来 mirror 与 witness 的会话优先恢复，则也无法故障切换，因为已然不确定 mirror 是否拥有全部 principal 的数据，此时即便 principal 处于运行状态，也无法提供服务，等待 principal 与任意节点会话恢复正常，即可恢复读写服务
mirror 与所有节点会话中断不会触发故障切换，principal 切入公开运行模式（异步），即不会再向 mirror 发送事务日志，也不再需要等待 mirror 的响应，直到 mirror 重新恢复会话
witness 与所有节点会话中断不会触发故障切换，principal 继续提供读写服务，与 mirror 数据继续同步，镜像集群丧失自动故障转移能力，退化为不带故障转移的高安全模式

镜像群集配置(AD域)

先决条件

数据库必须处于完全恢复模式，如果不是请用下列语句修改

1
2
3
4
5


USE master;  
GO  
ALTER DATABASE AdventureWorks   
SET RECOVERY FULL;  
GO  

主节点备份数据库

1
2
3
4


BACKUP DATABASE TEST 
TO DISK = N'D:\backup\test.bak' 
   WITH NOFORMAT, NOINIT,  NAME = N'TEST-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

镜像节点恢复数据库(NORECOVERY)

1
2
3
4
5
6


RESTORE DATABASE test  
   FROM disk='D:\backup\test.bak'  
   WITH NORECOVERY,  
   MOVE 'test_Data' TO 'D:\data\test_Data.mdf',   
   MOVE 'test_Log' TO 'F:\log\test_Log.ldf';  
GO 

主节点创建端点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


CREATE ENDPOINT Endpoint_Mirroring  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=PARTNER)  
GO  
--Partners under same domain user; login already exists in master.  
--Create a login for the witness server instance,  
--which is running as Somedomain\witnessuser:  
USE master ;  
GO  
CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;  
GO  
-- Grant connect permissions on endpoint to login account of witness.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];  
--Grant connect permissions on endpoint to login account of partners.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
GO  

镜像节点创建端点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


CREATE ENDPOINT Endpoint_Mirroring  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL)  
GO  
--Partners under same domain user; login already exists in master.  
--Create a login for the witness server instance,  
--which is running as Somedomain\witnessuser:  
USE master ;  
GO  
CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;  
GO  
--Grant connect permissions on endpoint to login account of witness.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];  
--Grant connect permissions on endpoint to login account of partners.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
GO  

见证节点创建端点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


CREATE ENDPOINT Endpoint_Mirroring  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=WITNESS)  
GO  
--Create a login for the partner server instances,  
--which are both running as Mydomain\dbousername:  
USE master ;  
GO  
CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;  
GO  
--Grant connect permissions on endpoint to login account of partners.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];  
GO  

在镜像节点设置主节点成为伙伴

1
2
3
4


ALTER DATABASE AdventureWorks   
    SET PARTNER =   
    'TCP://192.168.25.10:5022'  
GO

在主节点设置镜像节点成为伙伴

1
2
3


ALTER DATABASE AdventureWorks   
    SET PARTNER = 'TTCP://192.168.25.11:5022'  
GO  

在主节点设置见证节点

1
2
3
4


ALTER DATABASE AdventureWorks   
    SET WITNESS =   
    'TCP://192.168.25.12:5022'  
GO  

镜像群集配置(非域)

先决条件

数据库必须处于完全恢复模式，如果不是请用下列语句修改

1
2
3
4
5


USE master;  
GO  
ALTER DATABASE AdventureWorks   
SET RECOVERY FULL;  
GO  

主节点备份数据库

1
2
3
4


BACKUP DATABASE TEST 
TO DISK = N'D:\backup\test.bak' 
   WITH NOFORMAT, NOINIT,  NAME = N'TEST-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

镜像节点恢复数据库(NORECOVERY)

1
2
3
4
5
6


RESTORE DATABASE test  
   FROM disk='D:\backup\test.bak'  
   WITH NORECOVERY,  
   MOVE 'test_Data' TO 'D:\data\test_Data.mdf',   
   MOVE 'test_Log' TO 'F:\log\test_Log.ldf';  
GO 

主节点创建证书

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123';    --KEY密码
GO
CREATE CERTIFICATE CA_MIRROR_SQL01     --证书名称
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231';
GO

镜像节点创建证书

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123';    --KEY密码
GO
CREATE CERTIFICATE CA_MIRROR_SQL01     --证书名称
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231';
GO

见证节点创建证书

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123';    --KEY密码
GO
CREATE CERTIFICATE CA_MIRROR_SQL01     --证书名称
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231';
GO

备份主节点证书到其它节点

1
2


BACKUP CERTIFICATE CA_MIRROR_SQL01
TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'

备份镜像节点证书到其它节点

1
2


BACKUP CERTIFICATE CA_MIRROR_SQL01
TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'

备份见证节点证书到其它节点

1
2


BACKUP CERTIFICATE CA_MIRROR_SQL01
TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'

主节点创建端点

1
2
3
4
5
6
7
8
9


CREATE ENDPOINT EDP_Mirror
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
        FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CA_MIRROR_SQL01, -- 证书身份验证
ENCRYPTION = DISABLED,         -- 不对传输的数据加密,如果需要加密,可以配置为  SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)

镜像节点创建端点

1
2
3
4
5
6
7
8
9


CREATE ENDPOINT EDP_Mirror
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
        FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CA_MIRROR_SQL02, -- 证书身份验证
ENCRYPTION = DISABLED,           -- 不对传输的数据加密,如果需要加密,可以配置为  SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)

见证节点创建端点

1
2
3
4
5
6
7
8
9


CREATE ENDPOINT EDP_Mirror
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
        FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CA_Mirror_WITNESS, -- 证书身份验证
ENCRYPTION = DISABLED,    -- 不对传输的数据加密,如果需要加密,可以配置为  SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)

主节点创建镜像节点证书

1

CREATE LOGIN CA_MIRROR_SQL02 FROM FILE='C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL02.cer';

主节点创建见证节点证书

1
2


create CERTIFICATE CA_MIRROR_WITNESS
FROM FILE = 'C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';

镜像节点创建主节点证书

1
2


create CERTIFICATE CA_MIRROR_WITNESS
FROM FILE = 'C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';

镜像节点创建见证节点证书

1
2


create CERTIFICATE CA_MIRROR_WITNESS
FROM FILE = 'C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';

见证节点创建主节点证书

1
2


create CERTIFICATE CA_MIRROR_WITNESS
FROM FILE = 'C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';

见证节点创建镜像节点证书

1
2


CREATE CERTIFICATE CA_MIRROR_SQL02
FROM FILE = 'C:\Program Files\Microsoft SQL  Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL02.cer';

主节点创建登陆用户

1
2
3
4


CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02;
CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;

镜像节点创建登陆用户

1
2
3
4


CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02;
CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;

见证节点创建登陆用户

1
2
3
4


CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02;
CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS;
GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;

镜像节点设置主节点为伙伴

1

alter database mirror set PARTNER='TCP://192.168.25.10:5022';

主节点设置镜像节点为伙伴

1

alter database mirror set PARTNER='TCP://192.168.25.11:5022';

主节点设置见证节点为伙伴

1

ALTER DATABASE mirror SET WITNESS = 'TCP://192.168.25.13:5022'

同步验证

查看镜像服务状态

1
2
3
4
5
6
7


SELECT 
mirroring_role_desc,           -- 数据库在镜像会话中当前的角色
mirroring_state_desc,          -- 镜像当前状态
mirroring_safety_level_desc,   -- 镜像运行模式
mirroring_witness_state_desc   -- 与见证服务器的连接情况
FROM sys.database_mirroring
WHERE database_id = DB_ID(N'mirror');

镜像暂停与恢复

1
2
3
4


--暂停
ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;  
--恢复
ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME; 

镜像切换

1
2


ALTER DATABASE [MirrorDB] SET PARTNER FAILOVER;
Go

Tips：应用程序在连接镜像时需要指定Failover节点的信息，例如：Server=IP,端口; Failover_Partner=IP,端口;

概述

镜像群集配置(AD域)

镜像群集配置(非域)

同步验证

参考链接