Step 1：准备工作

1.1 参考资料：

https://blog.csdn.net/roy_88/article/details/72833180
https://blog.csdn.net/dba_huangzj/article/details/27652857

1.2 服务器和数据库的准备

准备3台数据库服务器，安装SQL Server 2012，数据库版本要求尽量一致，避免因为数据库环境不同而遇到未知错误。

服务器类型 IP 服务器名 数据库文件夹 备份文件夹 共享文件夹
主服务器 192.168.68.129 SQL1 C:\Database
C:\ShareFile
镜像服务器 192.168.68.130 SQL2
C:\DBback \\SQL1\ShareFile
见证服务器 192.168.68.131 SQL3

\\SQL1\ShareFile

1.3 共享文件夹

本次操作因为3台服务器都在同一局域网下，所以使用共享文件夹来传输数据库的备份文件和数据库证书。当然，也可以直接复制或通过其他文件传输方式进行文件传输。
共享文件夹必须有足够的权限（右健—共享—特定用户—添加(Everyone)—权限（读/写））。

Step 2：主服务器（SQL1）

2.1 创建测试数据库MirrorDB

  1. CREATE DATABASE MirrorDB
  2. ON(
  3. NAME = MirrorDB_DATA,
  4. FILENAME = N'C:\Database\MirrorDB.mdf'
  5. )
  6. LOG ON(
  7. NAME = MirrorDB_LOG,
  8. FILENAME = N'C:\Database\MirrorDB.ldf'
  9. )
  10. ALTER DATABASE MirrorDB SET
  11. RECOVERY FULL

2.2 将数据库备份到共享文件夹

  1. --  完全备份
  2. BACKUP DATABASE MirrorDB
  3. TO DISK = N'C:\ShareFile\MirrorDB.bak'
  4. WITH FORMAT

Step 3： 镜像服务器（SQL2）

3.1 将共享文件夹ShareFile下的MirrorDB.bak复制到SQL2下的DBback文件夹下
3.2 还原数据库MirrorDB到镜像服务器（SQL2）
代码：

  1. RESTORE DATABASE MirrorDB
  2. FROM DISK = N'C:\DBback\MirrorDB.bak'
  3. WITH REPLACE
  4. , NORECOVERY
  5. -- 镜像数据库文件要放在指定位置, 则启用下面的 Move 选项
  6. , MOVE 'MirrorDB_DATA' TO N'C:\Database\MirrorDB.mdf'
  7. , MOVE 'MirrorDB_LOG' TO N'C:\Database\MirrorDB.ldf'

Step 4： 主服务器（SQL1）

4.1 使用数据库主密钥加密证书

  1. IF NOT EXISTS(  
  2. SELECT * FROM sys.symmetric_keys
  3. WHERE name = N'##MS_DatabaseMasterKey##')
  4. CREATE MASTER KEY
  5. ENCRYPTION BY PASSWORD = N'zero@123';
  7. CREATE CERTIFICATE CT_Mirror_SerA
  8. WITH
  9. SUBJECT = N'certificate for database mirror',
  10. START_DATE = '19990101',--证书的生效日期
  11. EXPIRY_DATE = '99991231'--证书的生效日期

4.2 创建一个数据库镜像端点

这里先要查看一下数据库镜像端点（DATABASE_MIRRORING）是否已经存在，若存在则先Drop或Alter。然后再创建端点。

  1. --select   *   from   sys.endpoints;
  2. --drop endpoint 镜像 --删除已经存在的数据库镜像端点
  4. --创建端点
  5. CREATE ENDPOINT EDP_Mirror
  6. STATE = STARTED
  7. AS TCP(
  8. LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
  9. LISTENER_IP = ALL)     -- 侦听的IP地址
  10.    FOR DATABASE_MIRRORING(
  11. AUTHENTICATION = CERTIFICATE CT_Mirror_SerA, -- 证书身份验证
  12. ENCRYPTION = DISABLED,  -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
  13. ROLE = ALL)   -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)


4.3 备份证书A到共享文件夹

  1. BACKUP CERTIFICATE CT_Mirror_SerA
  2. TO FILE = 'C:\ShareFile\CT_Mirror_SerA.cer'

注：新添加的文件显示在其它服务显示不出来时把此文件共享权限设置为Everyone读写

Step 5： 镜像服务器（SQL2）

重复上述步骤：

5.1 使用数据库主密钥加密证书

  1. --1.创建数据库主密钥
  2. IF NOT EXISTS(  
  3. SELECT * FROM sys.symmetric_keys
  4. WHERE name = N'##MS_DatabaseMasterKey##')
  5. CREATE MASTER KEY
  6. ENCRYPTION BY PASSWORD = N'zero@123';
  8. --2.创建数据库证书CT_Mirror_SerB
  9. CREATE CERTIFICATE CT_Mirror_SerB
  10. WITH
  11. SUBJECT = N'certificate for database mirror',
  12. START_DATE = '19990101',--证书的生效日期
  13. EXPIRY_DATE = '99991231'--证书的失效日期

5.2 备份证书

  1. BACKUP CERTIFICATE CT_Mirror_SerB
  2. TO FILE = 'C:\DBback\CT_Mirror_SerB.cer';
  4. BACKUP CERTIFICATE CT_Mirror_SerB
  5. TO FILE = '\\SQL1\ShareFile\CT_Mirror_SerB.cer';

5.3 创建一个数据库镜像端点

这里先要查看一下数据库镜像端点（DATABASE_MIRRORING）是否已经存在，若存在则先Drop或Alter。然后再创建端点。

  1. --select * from   sys.endpoints;
  2. --drop endpoint 镜像 --删除已经存在的数据库镜像端点
  3. --创建数据库镜像端点
  4. CREATE ENDPOINT EDP_Mirror
  5. STATE = STARTED
  6. AS TCP(
  7. LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
  8. LISTENER_IP = ALL)     -- 侦听的IP地址
  9.    FOR DATABASE_MIRRORING(
  10. AUTHENTICATION = CERTIFICATE CT_Mirror_SerB, -- 证书身份验证
  11. ENCRYPTION = DISABLED,   -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
  12. ROLE = ALL)    -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)

5.4 登记主服务器的证书

将在主服务器的证书A从共享文件夹复制到副本服务器中的C盘DBback文件夹里，然后执行如下SQL语句：

  1. --在副本服务器创建主服务器的证书
  2. CREATE CERTIFICATE CT_Mirror_SerA
  3. FROM FILE = 'C:\DBback\CT_Mirror_SerA.cer'     --'\\SQL1\ShareFile\CT_Mirror_SerA.cer'
  5. --建立登录
  6. CREATE LOGIN LOGIN_Mirror_SerA
  7. FROM CERTIFICATE CT_Mirror_SerA
  9. --授予对数据库镜像端点的 connect 权限
  10. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  11. TO LOGIN_Mirror_SerA

Step 6： 主服务器（SQL1）

6.1 登记镜像服务器的数据库证书

  1. --在主服务器创建镜像服务器的证书
  2. CREATE CERTIFICATE CT_Mirror_SerB
  3. FROM FILE = 'C:\ShareFile\CT_Mirror_SerB.cer'
  5. --建立登录
  6. CREATE LOGIN LOGIN_Mirror_SerB
  7. FROM CERTIFICATE CT_Mirror_SerB
  9. --授予对数据库镜像端点的 connect 权限
  10. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  11. TO LOGIN_Mirror_SerB

Step 7： 启用数据库镜像

7.1 镜像服务器（SQL2）启用数据库镜像

  1. ALTER DATABASE MirrorDB SET
  2. PARTNER = 'TCP://SQL1:5022'

7.2 主服务器（SQL1）启用数据库镜像

  1. ALTER DATABASE MirrorDB SET
  2. PARTNER = 'TCP://SQL1:5022'

注：默认为高安全性模式,所以不用进行模式设置

7.3 结果

查看主数据库的镜像

查看数据库镜像监视器

主备同步方案已经完成，因为没有见证服务器不可进行自动故障转移，下面就是加入见证服务器，实现自动故障转移即数据库高可用。

Step 8：配置见证服务器（SQL3）

SQL代码：

  1. --注：每个步骤的SQL逐个执行
  2. --1.创建数据库主密钥
  3. IF NOT EXISTS(  
  4. SELECT * FROM sys.symmetric_keys
  5. WHERE name = N'##MS_DatabaseMasterKey##')
  6. CREATE MASTER KEY
  7. ENCRYPTION BY PASSWORD = N'zero@123';
  9. --2.创建证书
  10. CREATE CERTIFICATE CT_Mirror_SerCWitness
  11. WITH
  12. SUBJECT = N'certificate for database mirror',
  13. START_DATE = '19990101',
  14. EXPIRY_DATE = '99991231';
  16. --3.创建数据库端点
  17. --select   *   from   sys.endpoints;
  18. --drop endpoint 镜像 --删除已经存在的数据库镜像端点
  19. CREATE ENDPOINT EDP_Mirror
  20. STATE = STARTED
  21. AS TCP(
  22. LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
  23. LISTENER_IP = ALL)     -- 侦听的IP地址
  24.    FOR DATABASE_MIRRORING(
  25. AUTHENTICATION = CERTIFICATE CT_Mirror_SerCWitness, -- 证书身份验证
  26. ENCRYPTION = DISABLED,   -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
  27. ROLE = ALL)   -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)
  29. --4.备份证书到本地和共享文件夹
  30. BACKUP CERTIFICATE CT_Mirror_SerCWitness
  31. TO FILE = 'C:\DBback\CT_Mirror_SerCWitness.cer';
  33. BACKUP CERTIFICATE CT_Mirror_SerCWitness
  34. TO FILE = '\\SQL1\ShareFile\CT_Mirror_SerCWitness.cer';
  36. --5.建立主体服务器上的证书
  37. --5.1 创建证书
  38. CREATE CERTIFICATE CT_Mirror_SerA
  39. FROM FILE = '\\SQL1\ShareFile\CT_Mirror_SerA.cer';
  41. --5.2 建立登录
  42. CREATE LOGIN LOGIN_Mirror_SerA
  43. FROM CERTIFICATE CT_Mirror_SerA;
  45. --5.3 授予对数据库镜像端点的 connect 权限
  46. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  47. TO LOGIN_Mirror_SerA;
  49. --6.建立镜像服务器上的证书
  50. --6.1 创建证书
  51. CREATE CERTIFICATE CT_Mirror_SerB
  52. FROM FILE = '\\SQL1\ShareFile\CT_Mirror_SerB.cer';
  54. --6.2 建立登录
  55. CREATE LOGIN LOGIN_Mirror_SerB
  56. FROM CERTIFICATE CT_Mirror_SerB;
  58. --6.3 授予对数据库镜像端点的 connect 权限
  59. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  60. TO LOGIN_Mirror_SerB;

Step 9：登记见证服务器证书

9.1 镜像服务器

  1. CREATE CERTIFICATE CT_Mirror_SerCWitness
  2. FROM FILE = '\\SQL1\ShareFile\CT_Mirror_SerCWitness.cer';
  4. CREATE LOGIN LOGIN_Mirror_SerCWitness
  5. FROM CERTIFICATE CT_Mirror_SerCWitness;
  7. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  8. TO LOGIN_Mirror_SerCWitness;

9.2 主服务器

  1. CREATE CERTIFICATE CT_Mirror_SerCWitness
  2. FROM FILE = '\\SQL1\ShareFile\CT_Mirror_SerCWitness.cer'
  4. CREATE LOGIN LOGIN_Mirror_SerCWitness
  5. FROM CERTIFICATE CT_Mirror_SerCWitness
  7. GRANT CONNECT ON ENDPOINT::EDP_Mirror
  8. TO LOGIN_Mirror_SerCWitness

Step 10：启用见证服务器

10.1 主服务器（SQL1）启用见证服务器（SQL3）

  1. ALTER DATABASE MirrorDB SET
  2. WITNESS = 'TCP://SQL3:5022'

10.2 结果

如下图

10.3 测试

断开主服务器（SQL1）后，发现SQL2变为主体服务器，SQL1变为镜像服务器，且SQL1状态为断开连接。

再次连接服务器SQL1，结果SQL1还是镜像服务器，主体服务器为SQL2。

至此，SQLServer高可用方案—数据库镜像搭建完毕。