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

最新推荐文章于 2024-01-10 15:07:37 发布

HeavenBen

最新推荐文章于 2024-01-10 15:07:37 发布

阅读量1.1k

点赞数

分类专栏： SqlServer 文章标签： sql server 数据库镜像

SqlServer 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

数据库镜像是将数据库事务处理从一个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、配置出站连接

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

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

[c-sharp]view plaincopy 
   
 /******************************************************** 
  
 此脚本在主体服务器执行 
  
 ********************************************************/  
   
 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  

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

[c-sharp]view plaincopy 
   
 /*********************************************** 
 在镜像服务器 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  

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

[c-sharp]view plaincopy 
   
 /**************************** 
 见证服务器 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、配置入站连接

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

[c-sharp]view plaincopy 
   
 --在 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  

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

[c-sharp]view plaincopy 
   
 --在镜像服务器 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  

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

[c-sharp]view plaincopy 
   
 --在见证服务器 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' 这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户".

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

[c-sharp]view plaincopy 
   
 USE master;  
 select sid,name from syslogins;  

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

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

[c-sharp]view plaincopy 
   
 USE master;  
 exec sp_addlogin   
 @loginame = '<LoginName>',   
 @passwd = '<Password>',   
 @sid = <sid> ;  

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

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

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

则建立登录名的SQL语句：

[c-sharp]view plaincopy 
   
 USE master;  
 exec sp_addlogin   
 @loginame = 'uhost',  
 @passwd = 'OA@asp.net',   
 @sid = 0x074477739DCA0E499C29394FFFC4ADE4;  

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

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

[c-sharp]view plaincopy 
   
 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、配置镜像伙伴

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

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

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

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

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

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

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

[c-sharp]view plaincopy 
   
 --主机执行：  
 USE master;  
 ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;  

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

[c-sharp]view plaincopy 
   
 --备机执行：  
 USE master;  
 ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;  

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

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

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

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