使用证书设置SQL Server数据库镜像详解

 数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”，第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

实现互通可以使用域或证书来实现，非域环境下使用证书配置数据库镜像(如不需要见证服务器，省略掉主体服务器和镜像服务器建立见证服务器的登录、见证服务器的配置、主体服务器设置见证服务器三个环节即可)。

 

1、 环境

主机名	主机操作系统	主机SQL配置	主机IP	初始镜像角色
host_A	Windows Server 2003	Sql Server 2005  SP3	192.168.1.21	主体服务器
host_B	Windows Server 2003	Sql Server 2005  SP3	192.168.1.254	镜像服务器
host_C	Windows Server 2003	Sql Server 2005  SP3	192.168.1.22	见证服务器

 

2、 配置出站连接

       主要工作是为服务器实例制作证书：

(一)为主机配置出站连接：

/******************************************************** 此脚本在主体服务器执行 ********************************************************/ USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为主体服务器 host_A 实例制作一个证书。 --DROP CERTIFICATE host_A_cert CREATE CERTIFICATE host_A_cert WITH SUBJECT = 'host_A certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --使用该证书为服务器实例创建一个镜像端点。 --DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER ); GO --备份host_A 证书，并将其复制到镜像服务器 host_B 和见证服务器 host_C BACKUP CERTIFICATE host_A_cert TO FILE = 'D:/dbmirror/host_A.cer'; GO

 

（二）为镜像服务器配置出站连接

 /*********************************************** 在镜像服务器 host_B 执行此脚本 ***********************************************/ USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为镜像服务器 host_B 实例制作一个证书。 --DROP CERTIFICATE host_B_cert CREATE CERTIFICATE host_B_cert WITH SUBJECT = 'host_B certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --在 host_B 中为服务器实例创建一个镜像端点。 --DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER ); GO --备份 host_B 证书，并将其复制到主体服务器 host_A 和见证服务器 host_C 上 BACKUP CERTIFICATE oa-canoe_cert TO FILE = 'D:/dbmirror/host_B.cer'; GO

 

(三)为见证服务器配置出站连接

/**************************** 见证服务器 host_C 执行 *****************************/ --ALTER DATABASE MirrorDB SET PARTNER OFF USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为此服务器实例制作一个证书。 --DROP CERTIFICATE host_C_cert CREATE CERTIFICATE host_C_cert WITH SUBJECT = 'host_C certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --使用该证书为服务器实例创建一个镜像端点。 --DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = WITNESS ); GO --备份 host_C 证书，并将其复制到主体服务器 host_A 和镜像服务器 host_C 上 BACKUP CERTIFICATE oa-tjcfw_cert TO FILE = 'D:/dbmirror/host_C.cer'; GO

 

注意：

       1. 证书必须指定过期时间，如果未指定，则将 EXPIRY_DATE 设置为 START_DATE 一年之后的日期。

       2. 如果出现 “默认情况下，数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用，并不应使用于生产环境中。若要以评估为目的启用数据库镜像，请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息，请参阅SQL Server 联机丛书”错误，请执行  DBCC TRACEON(1400,-1)  并安装SP3补丁即可

3、 配置入站连接

(一)为主体服务器配置入站连接：

--在 host_A 上为镜像服务器 host_B 创建一个登录名。 USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; 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:/dbmirror/host_B.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT 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 = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP 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:/dbmirror/host_C.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login]; GO --主体服务器上创建用于本机的登录名(MS可以省略) USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD= 'OA@asp.net'; 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]; GO

 

(二)为镜像服务器配置入站连结：

--在镜像服务器 host_B 上为主体服务器 host_A 创建一个登录名。 USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net'; 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:/dbmirror/host_A.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 = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP 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:/dbmirror/host_C.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login]; GO --在镜像服务器 host_B 上为本机创建一个登录名(MS可以省略) USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_B_user CREATE USER host_B_user FOR CERTIFICATE host_B_cert; GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]; GO

 

(三)为见证服务器配置入站连接：

--在见证服务器 host_C 上为主体服务器 host_A 创建一个登录名。 USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net'; 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:/dbmirror/host_A.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login]; GO --在见证服务器 host_C 上为镜像服务器 host_B 创建一个登录名。 USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; 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:/dbmirror/host_B.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]; GO

 

4、 配置镜像数据库用户信息
数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。
通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用 'uhost' 作为登录名访问数据库，但是在

备机中没有 'uhost' 这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户".

 

在主数据库中执行如下语句

USE master; select sid,name from syslogins;

 

查找出相应的用户名和sid，例如：上述的 'uhost'

在备数据库中执行如下语句：

USE master; exec sp_addlogin @loginame = '<LoginName>', @passwd = '<Password>', @sid = <sid> ;

 

这里的 'LoginName' 即主数据库中的登录名，sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

sid name
---------------------------------- -----------------
50x074477739DCA0E499C29394FFFC4ADE4 uhost

则建立登录名的SQL语句：

USE master; exec sp_addlogin @loginame = 'uhost', @passwd = 'OA@asp.net', @sid = 0x074477739DCA0E499C29394FFFC4ADE4;

 

到此为止可以认为备机数据库的环境已经与主机同步了

在主体服务器 host_A 上备份数据库，镜像只支持完全恢复模式，在备份数据库之前检查恢复的模式,需要同时完整备份数据库和事务日志。

USE MASTER; GO BACKUP DATABASE DBMirror TO DISK = 'D:/dbmirror/DB.bak' WITH INIT GO BACKUP LOG Credit TO DISK = 'D:/dbmirror/DB_log.bak' WITH INIT GO

 

将DB.bak、DB_log.bak复制到镜像服务器 host_B 上，复制到 host_B 的镜像服务器实例上以NORECOVERNY选项进行恢复.
注意：这里数据库必须备份两次，一次全备份和一次事务日志备份，在镜像服务器上还原数据库必须使用NORECOVERNY选项进行恢复.

 

5、 配置镜像伙伴

(一)在镜像服务器上配置

--在镜像服务器 host 实例上设置其伙伴，使其伙伴成为主体服务器 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.21:5022'; GO

 

(二)在主体服务器上配置

--必须要在镜像数据库中先设置好伙伴后，才能在主体服务器执行 --在主体服务器 host_A 实例上设置镜像服务器为伙伴 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.254:5022'; GO

 

(三)在主体服务器上配置见证服务器

--设置见证服务器，在主体服务器 host_A 实例上设置见证服务器 ALTER DATABASE DBMirror SET WITNESS = 'TCP://192.168.1.22:5022'; GO

 

至此，镜像服务器配置成功，测试操作：
1、主备互换

--主机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

 

2、主服务器Down掉,备机紧急启动并且开始服务

--备机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

 

3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行： USE master; ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --恢复镜像 ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --切换主备

 

默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。
关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

USE master; ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事务安全,同步模式 ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; --事务不安全,异步模式