数据库自动故障转移的配置方法

1、数据库自动故障转移的配置方法基础知识1. 镜像模式根据镜像回话是否同步和镜像是否自动故障转移分为三个模式：Ø 高性能模式（镜像回话异步）；Ø 不带自动故障转移的高安全模式（镜像回话同步且无见证服务器）；Ø 带自动故障转移的高安全模式（镜像回话同步且设置见证服务器）。2. 镜像回话同步：主服务器向镜像服务器发送日志记录后，需要等待镜像服务器返回确认消息后才向客户端发送确认信息。3. 镜像自动故障转移：在配置见证服务器后，主服务器因故障不能提供服务，见证服务器和镜像服务器通过投票方式来确定是否自动将镜像服务器切换成主服务器提供服务。4. 配置镜像条件：Ø 数

2、据库服务器为SQL 2005及以上版本（版本相同）Ø 镜像回话双方相互信任；Ø 主服务器上数据库是完整恢复模式；Ø 镜像服务器上数据库处于恢复中状态并且日志与主服务器上同步。5. 镜像回话信任可通过使用域账户（Windows验证）或证书方式（SQL SERVER 验证）来配置。6. 数据库日志同步可使用完整备份还原+日志备份还原的方式完成，且还原时使用WITH NORECOVERY来设置镜像服务器上数据库处于还原中状态。7. 见证服务器定期向主服务器和镜像服务器发送请求来获取状态下面例子中所使用的3台服务器设定如下：Host_A: 主服务器 Host_B: 镜像服

3、务器 Host_C: 见证服务器操作步骤如下：1. 在主服务器上创建和备份证书USE master; -DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>' GO -为此服务器实例制作一个证书。 -DROP CERTIFICATE HOST_A_cert CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate',START_DATE = '11/15/2015

4、' GO -使用该证书为服务器实例创建一个镜像端点。 -DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=7024 -访问端口默认 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER -端点类型

5、PARTNER（主端、镜像）WITNESS（见证） ); GO -备份HOST_A 证书，并将其复制到其他机器，将D:DBHOST_A_cert.cer 复制到HOST_BHOST_C。注意证书文件的权限 BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:DBHOST_A_cert.cer' GO2. 在镜像服务器上创建和备份证书USE master; -DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'

6、; GO -为HOST_B 服务器实例制作一个证书。 -DROP CERTIFICATE HOST_B_cert CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate for database mirroring',START_DATE = '11/15/2015' GO -在HOST_B 中为服务器实例创建一个镜像端点。 -DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = START

7、ED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER ); GO -备份HOST_B 证书，将D:DBHOST_B_cert.cer 复制到HOST_AHOST_C。 BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:DBHOST_B_cert.cer'

8、; GO3. 在见证服务器上创建和备份证书-ALTER DATABASE MirrorDB SET PARTNER OFF USE master; -DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>' GO -为此服务器实例制作一个证书。 -DROP CERTIFICATE HOST_C_cert CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate',START

9、_DATE = '11/15/2015' GO -使用该证书为服务器实例创建一个镜像端点。 -DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE

10、= WITNESS ); GO -备份HOST_C 证书，并将其复制到其他系统，即HOST_BHOST_A。 BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:HOST_C_cert.cer' GO4. 主服务器/镜像服务器/见证服务器三者间证书相互拷贝5. 在主服务器上实现回话信任-为入站连接配置Host_A -在HOST_A 上为HOST_B 创建一个登录名。 USE master; -DROP LOGIN HOST_B_login CREATE LOGIN HOST_B_login WITH PASSWORD = '1Samp

11、le_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_B_user CREATE USER HOST_B_user FOR LOGIN HOST_B_login; GO -使证书与该用户关联。 -DROP CERTIFICATE HOST_B_cert CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:DBHOST_B_cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONN

12、ECT ON ENDPOINT:Endpoint_Mirroring TO HOST_B_login; GO -在HOST_A 上为HOST_C 创建一个登录名。 USE master; -DROP LOGIN HOST_C_login CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_C_user CREATE USER HOST_C_user FOR LOGIN HOST_C_login; GO -使证书与该用户关联

13、。 -DROP CERTIFICATE HOST_C_cert CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:DBHOST_C_cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_C_login; GO USE master; -DROP LOGIN HOST_A_login CREATE LOGIN HOST_A_login WITH PASSWO

14、RD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_A_user CREATE USER HOST_A_user FOR CERTIFICATE HOST_A_cert; GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_A_login; GO6. 在镜像服务器上实现回话信任 -为入站连接配置Host_B -在HOST_B 上为HOST_A 创建一个登录名。 USE master;

15、 -DROP LOGIN HOST_A_login CREATE LOGIN HOST_A_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_A_user CREATE USER HOST_A_user FOR LOGIN HOST_A_login; GO -使证书与该用户关联。 -DROP CERTIFICATE HOST_A_cert CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM

16、 FILE = 'D:DBHOST_A_cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_A_login; GO -在HOST_B 上为HOST_C 创建一个登录名。 USE master; -DROP LOGIN HOST_C_login CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP

17、USER HOST_C_user CREATE USER HOST_C_user FOR LOGIN HOST_C_login; GO -使证书与该用户关联。 -DROP CERTIFICATE HOST_C_cert CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:DBHOST_C_cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_C_login;

18、 GO -在HOST_B 上为HOST_B 创建一个登录名。 USE master; -DROP LOGIN HOST_B_login CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_B_user CREATE USER HOST_B_user FOR CERTIFICATE HOST_B_cert; GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:End

19、point_Mirroring TO HOST_B_login; GO7. 在见证服务器上实现回话信任 -为入站连接配置Host_C -在HOST_C 上为HOST_B 创建一个登录名。 USE master; -DROP LOGIN HOST_B_login CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_B_user CREATE USER HOST_B_user FOR LOGIN HOST_B_login; G

20、O -使证书与该用户关联。 -DROP CERTIFICATE HOST_B_cert CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:DBHOST_B_cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_B_login; GO -在HOST_C 上为HOST_A 创建一个登录名。 USE master; -DROP LOGIN HOST_A_logi

21、n CREATE LOGIN HOST_A_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_A_user CREATE USER HOST_A_user FOR LOGIN HOST_A_login; GO -使证书与该用户关联。 -DROP CERTIFICATE HOST_A_cert CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:DBHOST_A_

22、cert.cer' GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_A_login; GO -在HOST_C 上为HOST_C 创建一个登录名。 USE master; -DROP LOGIN HOST_C_login CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!#' GO -创建一个使用该登录名的用户。 -DROP USER HOST_C_user CREATE

23、USER HOST_C_user FOR CERTIFICATE HOST_C_cert; GO -授予对远程镜像端点的登录名的CONNECT 权限。 GRANT CONNECT ON ENDPOINT:Endpoint_Mirroring TO HOST_C_login; GO8. 在主服务器上备份数据库- 主机备份USE masterGOBACKUP DATABASE Stu TO DISK = N'D:DBStu.bak'WITH FORMAT, INIT, NAME = N'ICDSync-Full Database Backup', SKIP, NOR

24、EWIND, NOUNLOAD, STATS = 10;GOBACKUP LOG Stu TO DISK = N'D:DBStu.bak'WITH NOFORMAT, NOINIT, NAME = N'ICDSync-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;GO9. 在镜像服务器上实现数据日志同步- 镜像恢复USE masterGORESTORE DATABASE Stu FROM DISK = N'D:DBStu.bak'WITH FILE = 1,NORECOVERY, NOUNLOAD, REPLACE, STATS = 10GORESTORE LOG