SQLServer的数据库镜像实施笔记.docx
《SQLServer的数据库镜像实施笔记.docx》由会员分享，可在线阅读，更多相关《SQLServer的数据库镜像实施笔记.docx（14页珍藏版）》请在冰豆网上搜索。
SQLServer的数据库镜像实施笔记
SQLServer的数据库镜像实施笔记
作者:
深山老林 来源:
博客园 发布时间:
2009-04-1015:
24 阅读:
1645次 原文链接 全屏阅读 [收藏]
最初在为公司设计SQLServer数据库镜像的时候，首先考虑的是高可用性（三台计算机，一台见证服务器，一台做主数据库，一台做镜像）
在虚拟机环境下部署成功，一切都是那么的完美。
故障转移3秒之内就可以顺利完成。
1.高可用性的实施代码：
主体数据库
/********************************************************
此脚本在主体服务器执行
********************************************************/
--镜像只支持完全恢复模式，在备份数据库之前检查恢复的模式
--对要镜像的数据库进行完整备份后，复制到镜像数据库以NORECOVERNY选项进行恢复
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';
GO
--为此服务器实例制作一个证书。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',START_DATE = '01/01/2012',EXPIRY_DATE='01/01/2020';
GO 设置上开始日期，证书有效期为一年。
不设开始日期，即日启算有效期也为一年。
为了避免麻烦，使用上述参数设置有效期，否则任期内你会很惨。
。
。
。
。
。
EXPIRY_DATE='01/01/2020'有效期到2020年，这个一定要有
--使用该证书为服务器实例创建一个镜像端点。
--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 证书，并将其复制到其他机器，将 C:
\backup\HOST_A_cert.cer 复制到 HOST_B\HOST_C。
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:
\backup\HOST_A_cert.cer';
GO
--为入站连接配置 Host_A
--在 HOST_A 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'P@ssw0rd0';
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 = C:
\backup\HOST_B_cert.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 = 'P@ssw0rd0';
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 = C:
\backup\HOST_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 PASSWORD = 'P@ssw0rd0';
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_A 的主体服务器实例上，将 HOST_B 上的服务器实例设置为伙伴（使其成为初始镜像服务器实例）。
ALTER DATABASE crm
SET PARTNER = 'TCP:
//192.168.1.205:
5022';
GO
--设置见证服务器
ALTER DATABASE crm SET WITNESS = N'TCP:
//192.168.1.204:
5022';
GO
镜像数据库
/***********************************************
在镜像服务器执行此脚本
***********************************************/
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';
GO
--为 HOST_B 服务器实例制作一个证书。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',START_DATE = '01/01/2012',EXPIRY_DATE='01/01/2020';
;
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 证书，将 C:
\HOST_B_cert.cer 复制到 HOST_A\HOST_C。
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'c:
\backup\HOST_B_cert.cer';
GO
--为入站连接配置 Host_B
--在 HOST_B 上为 HOST_A 创建一个登录名。
USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'P@ssw0rd0';
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 = 'c:
\backup\HOST_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 = 'P@ssw0rd0';
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 = 'c:
\backup\HOST_C_cert.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT:
:
Endpoint_Mirroring TO [HOST_C_login];
GO
--在 HOST_B 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'P@ssw0rd0';
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_B 的镜像服务器实例上，将 HOST_A 上的服务器实例设置为伙伴（使其成为初始主体服务器实例）。
ALTER DATABASE crm
SET PARTNER = 'TCP:
//192.168.1.203:
5022';
GO
见证服务器
/****************************
见证服务器执行
*****************************/
--ALTER DATABASE MirrorDB SET PARTNER OFF
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';
GO
--为此服务器实例制作一个证书。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate',START_DATE = '01/01/2009';
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_B\HOST_A。
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'c:
\backup\HOST_C_cert.cer';
GO
--为入站连接配置 Host_C
--在 HOST_C 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'P@ssw0rd0';
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 = 'c:
\backup\HOST_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_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'P@ssw0rd0';
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 = 'c:
\backup\HOST_A_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 = 'P@ssw0rd0';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR CERTIFICATE HOST_C_cert;
GO
可能有朋友们会比较有疑惑，你一下搞两个数据库出来，他们的ip地址都不一样，到时候数据库切换过去了，我的数据库的连接字符串可如何是好？
难道还得在代码中去控制是连接哪个数据库吗？
其实这个问题是这样的，使用ADO.NET或者SQLNativeClient能够自动连接到故障转移后的伙伴，连接字符串如下所示：
ConnectionString="DataSource=A;FailoverPartner=B;InitialCatalog=AdventureWorks;IntegratedSecurity=true;"
DataSource=A;这个就是我们常用的主数据库的ip地址，FailoverPartner=B;这个填写的就是镜像数据库的ip地址，一旦出现了连接错误，会在超时以后自动去连接镜像数据库。
2.高级别保护模式
在昨天晚上加班做实施的时候，才发现我的设计已经被修改了，由于以前的项目有java写的也有c#写的，全自动的故障转移不能够实现。
换句话说，由于老项目中的历史遗留问题，以及特殊模块的耦合性过高，无法解耦，只能在高级别保护模式或高性能模式中选择一种了。
那么这两者有什么区别呢？
简单一点来说，区别就在与事务安全模式上跟应用场景上。
高级别保护模式采用的是同步镜像，SAFETYFULL。
应用场景：
通常在局域网中或对数据要求比较高的场景中。
高性能保护模式采用的是异步镜像，SAFETYOFF。
应用场景：
通常在广域网或对数据要求不太高，丢失几条数据是允许的，但是必须保证它不中断服务。
在微软的SQLServer2005的课程上是这么说的。
如果是高级别保护模式的话，主、从数据库只要有一台不能正常保证服务，数据库就不能够对外进行服务了，我在开始的时候就没有打算采用这种模式，因为部门经理说了，丢失一两条数据是可以接受的，况且我们公司是做运营的，按照起先微软的课程的理论，高级别保护模式是不太适合我们公司的应用场景的，万一有一台数据库出问题了，整个服务就被中断，这是不能让人接受的。
再说了，公司对数据要求不太苛刻，两台服务器都有内网线连接，由于内网传输速度非常的快，即使采用高性能模式，一般来说也是不会丢失数据的。
于是我打算采用高性能模式来做数据库的镜像。
由于公司服务器没有域环境，所以我就采用了证书验证来做SQLServer镜像。
意外收获：
两台服务器全部都安装了SQLServer2008，在设置事务安全模式的时候，才发现SQLServer2008不支持异步模式。
提示大概如下：
此SQLServer版本不支持修改事务安全模式，alterdatabase失败。
我当时汗都出来了，忙活了一晚上，到最后居然是这个结果。
由于是服务器维护时间，我大胆的把镜像服务器停止了，结果却让我大吃一惊，主数据库依旧可以正常工作，正常对外提供服务。
也就是说，起先微软的课程讲的知识是错误的，两台数据库做镜像，不管是哪台数据库出了问题，另外的一台数据库都可以保证正常对外提供服务。
于是我反复试验反复切换了一下，结果依然是这样。
由于高级别保护模式与高性能模式代码差不太多，只是在事务安全模式的设置上有些小区别，前面已经提到，这里就不再多解释了。
实施的代码如下：
主体服务器
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '01/01/2012',EXPIRY_DATE='01/01/2020';
;
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 = ALL ）;
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'c:
\backup\HOST_A_cert.cer';
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'c:
\backup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT:
:
Endpoint_Mirroring TO [HOST_B_login];
ALTER DATABASE crm SET PARTNER = 'TCP:
//10.10.10.8:
5022';
镜像数据库USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '01/01/2012',EXPIRY_DATE='01/01/2020';
;
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP （ LISTENER_PORT=5022 , LISTENER_IP = ALL ）
FOR
DATABASE_MIRRORING
（ AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION =
升级会员 