SQLServer 2008的数据库镜像实施笔记

最初在为公司设计SQLServer数据库镜像的时候，首先考虑的是高可用性（三台计算机，一台见证服务器，一台做主数据库，一台做镜像） 在虚拟机环境下部署成功，一切都是那么的完美。 故障转移3秒之内就可以顺利完成。 1.高可用性的实施代码： 主体数据库 /* ***

最初在为公司设计SQLServer数据库镜像的时候，首先考虑的是高可用性（三台计算机，一台见证服务器，一台做主数据库，一台做镜像）

在虚拟机环境下部署成功，一切都是那么的完美。 故障转移3秒之内就可以顺利完成。

1.高可用性的实施代码：

主体数据库
/********************************************************
此脚本在主体服务器执行
********************************************************/
--镜像只支持完全恢复模式，在备份数据库之前检查恢复的模式
--对要镜像的数据库进行完整备份后，复制到镜像数据库以NORECOVERNY选项进行恢复
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--为此服务器实例制作一个证书。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A 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_A_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = PARTNER
);
GO

--备份 HOST_A 证书，并将其复制到其他机器，将 C:\HOST_A_cert.cer 复制到 HOST_B\HOST_C。
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\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 = '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 = 'e:\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 = 'password';
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 = 'e:\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 = '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];
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 = 'password';
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/2009';
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 = 'e:\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 = '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 = 'e:\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 = 'password';
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 = 'e:\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 = 'password';
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 = 'password';
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 = 'e:\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 = '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 = 'e:\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 = '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 = 'e:\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 = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR CERTIFICATE HOST_C_cert;
GO

可能有朋友们会比较有疑惑，你一下搞两个数据库出来，他们的ip地址都不一样，到时候数据库切换过去了，我的数据库的连接字符串可如何是好？难道还得在代码中去控制是连接哪个数据库吗？

其实这个问题是这样的，使用ADO.NET或者SQL Native Client能够自动连接到故障转移后的伙伴，连接字符串如下所示：

ConnectionString="DataSource= A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=true;"

DataSource= A;这个就是我们常用的主数据库的ip地址，Failover Partner=B;这个填写的就是镜像数据库的ip地址，一旦出现了连接错误，ado.net会在超时以后自动去连接镜像数据库。

2.高级别保护模式

在昨天晚上加班做实施的时候，才发现我的设计已经被修改了，由于以前的项目有java写的也有c#写的，全自动的故障转移不能够实现 。换句话说，由于老项目中的历史遗留问题，以及特殊模块的耦合性过高，无法解耦，只能在高级别保护模式或高性能模式中选择一种了。那么这两者有什么区别呢？

简单一点来说，区别就在与事务安全模式上跟应用场景上。

高级别保护模式采用的是同步镜像， SAFETY FULL。应用场景：通常在局域网中或对数据要求比较高的场景中。

高性能保护模式采用的是异步镜像， SAFETY OFF。应用场景：通常在广域网或对数据要求不太高，丢失几条数据是允许的，但是必须保证它不中断服务。

在微软的SQLServer2005的课程上是这么说的。如果是高级别保护模式的话，主、从数据库只要有一台不能正常保证服务，数据库就不能够对外进行服务了，我在开始的时候就没有打算采用这种模式，因为部门经理说了，丢失一两条数据是可以接受的，况且我们公司是做运营的，按照起先微软的课程的理论，高级别保护模式是不太适合我们公司的应用场景的，万一有一台数据库出问题了，整个服务就被中断，这是不能让人接受的。再说了，公司对数据要求不太苛刻，两台服务器都有内网线连接，由于内网传输速度非常的快，即使采用高性能模式，一般来说也是不会丢失数据的。于是我打算采用高性能模式来做数据库的镜像。由于公司服务器没有域环境，所以我就采用了证书验证来做SQLServer镜像。

意外收获：

两台服务器全部都安装了SQLServer2008，在设置事务安全模式的时候，才发现SQLServer2008不支持异步模式。提示大概如下：此SQLServer版本不支持修改事务安全模式，alter database失败。 我当时汗都出来了，忙活了一晚上，到最后居然是这个结果。

由于是服务器维护时间，我大胆的把镜像服务器停止了，结果却让我大吃一惊，主数据库依旧可以正常工作，正常对外提供服务。也就是说，起先微软的课程讲的知识是错误的，两台数据库做镜像，不管是哪台数据库出了问题，另外的一台数据库都可以保证正常对外提供服务。于是我反复试验反复切换了一下，结果依然是这样。

由于高级别保护模式与高性能模式代码差不太多，只是在事务安全模式的设置上有些小区别，前面已经提到，这里就不再多解释了。实施的代码如下：

主体服务器
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '01/01/2009';


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 = 'e:\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 = 'e:\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/2009';


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 = ALL );


BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer';


CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'e:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];


ALTER DATABASE crm SET PARTNER = 'TCP://10.10.10.6:5022';

可能有朋友会比较奇怪，你这里也没有使用ALTER DATABASE crm SET SAFETY FULL; 按理应该是高性能模式才对呀？

其实这个问题是这样的，我的这个SQLServer2008默认已经是将事务安全模式设置为full了，即使是手动设置也一样，并且我实施的时候SQLServer2008不支持将

事务安全模式设置为OFF。

OK,一切都设置好了,那么就可以模拟服务器真的down机时候的操作了，后续的工作我也把代码做了总结，具体代码如下：

手动故障转移代码
--主备互换
--主机执行：

ALTER DATABASE crm SET PARTNER FAILOVER

--主服务器Down掉,备机紧急启动并且开始服务
ALTER DATABASE crm SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


原来的主服务器恢复,可以继续工作,需要重新设定镜像
--备机执行：
USE master
ALTER DATABASE crm SET PARTNER RESUME --恢复镜像

ALTER DATABASE crm SET PARTNER FAILOVER; --切换主备

3.监视数据库镜像

SQLServer提供了一些视图，可以供查询镜像的各种状态，到时候可以根据这个做一个监视，一旦发生故障转移群集，发邮件给系统管理员，好让系统管理员及时的知道数据库服务器发生了什么问题，即使的做故障分析、排查。有关这方面资料，MSDN上已经提供太多资料了。感兴趣的朋友可以去查这方面的资料。

在文章的最后提出一个有争议的问题：SQLServer（2008）高级别保护模式，只要有一台数据库能够保证正常运行，就可以正常对外提供服务。我的实验结果是这样的，这的确跟以往的理论知识有些出入。

还等什么，赶快搭环境动手实验一下吧，体验一下SQLServer镜像带来的快感。 希望有兴趣的朋友们一起学习探讨。

后话：

在发布本文以后，有朋友问到说SQLServer镜像在实施过程中不知道开放什么端口，导致防火墙必须关闭掉的这个问题。因为我这里的环境已经没有了，搭建真实环境进行模拟测试也不太可能，简单看了下，SQLServer服务需要用到了如下端口如图所示：

另外，请参考msdn的这篇文章：http://msdn.microsoft.com/zh-cn/library/cc646023.aspx

数据库引擎使用的端口

下表列出了数据库引擎经常使用的端口。

应用场景	端口	注释
通过 TCP 运行的 SQL Server 默认实例	TCP 端口 1433	这是允许通过防火墙的最常用端口。它适用于与默认数据库引擎安装或作为计算机上唯一运行实例的命名实例之间的例行连接。（命名实例具有特殊的注意事项。请参阅本主题后面的动态端口）。
采用默认配置的 SQL Server 命名实例	此 TCP 端口是在启动数据库引擎时确定的动态端口。	请参阅下面动态端口部分中的描述。当使用命名实例时，SQL Server Browser 服务可能需要 UDP 端口 1434。
配置为使用固定端口的 SQL Server 命名实例	由管理员配置的端口号。	请参阅下面动态端口部分中的描述。
专用管理员连接	对于默认实例，为 TCP 端口 1434。其他端口用于命名实例。有关端口号，请查看错误日志。	默认情况下，不会启用与专用管理员连接 (DAC) 的远程连接。若要启用远程 DAC，请使用外围应用配置器方面。有关详细信息，请参阅了解外围应用配置器。
SQL Server Browser 服务	UDP 端口 1434	SQL Server Browser 服务用于侦听指向命名实例的传入连接，并为客户端提供与此命名实例对应的 TCP 端口号。通常，只要使用数据库引擎的命名实例，就会启动 SQL Server Browser 服务。如果客户端配置为连接到命名实例的特定端口，则不必启动 SQL Server Browser 服务。
通过 HTTP 端点运行的 SQL Server 实例。	可以在创建 HTTP 端点时指定。对于 CLEAR_PORT 通信，默认端口为 TCP 端口 80，对于 SSL_PORT 通信，默认端口为 443。	用于通过 URL 实现的 HTTP 连接。
通过 HTTPS 端点运行的 SQL Server 默认实例。	TCP 端口 443	用于通过 URL 实现的 HTTPS 连接。HTTPS 是使用安全套接字层 (SSL) 的 HTTP 连接。
Service Broker	TCP 端口 4022。若要验证使用的端口，请执行下面的查询： SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER'	对于 SQL Server Service Broker，没有默认端口，不过这是联机丛书示例中使用的常规配置。
数据库镜像	管理员选择的端口。若要确定此端口，请执行以下查询： SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING'	对于数据库镜像，没有默认端口，不过联机丛书示例使用 TCP 端口 7022。务必避免中断正在使用的镜像端点，尤其是处于带有自动故障转移功能的高安全模式下时。防火墙配置必须避免破坏仲裁。有关详细信息，请参阅指定服务器网络地址（数据库镜像）。
复制	与 SQL Server 的复制连接使用典型的常规数据库引擎端口（供默认实例使用的 TCP 端口 1433 等） 复 制快照的 Web 同步和 FTP/UNC 访问要求在防火墙上打开其他端口。为了将初始数据和架构从一个位置传输到另一个位置，复制可以使用 FTP（TCP 端口 21）或者通过 HTTP（TCP 端口 80）或文件和打印共享（TCP 端口 137、138 或 139）进行的同步。	对于通过 HTTP 进行的同步，复制使用 IIS 端点（其端口可配置，但默认情况下为端口 80），不过 IIS 进程通过标准端口（对于默认实例为 1433）连接到后端 SQL Server。 在使用 FTP 进行 Web 同步期间，FTP 传输是在 IIS 和 SQL Server 发布服务器之间进行，而非在订阅服务器和 IIS 之间进行。 有关详细信息，请参阅Configuring Microsoft Internet Security and Acceleration Server for Microsoft SQL Server 2000 Replication over the Internet（为通过 Internet 进行的 Microsoft SQL Server 2000 复制配置 Microsoft Internet Security and Acceleration Server）。
Transact-SQL 调试器	TCP 端口 135 请参阅端口 135 的特殊注意事项 可能还需要 IPsec 例外。	如果使用 Visual Studio，则在 Visual Studio 主机计算机上，还必须将 Devenv.exe 添加到“例外”列表中并打开 TCP 端口 135。 如果使用 Management Studio，则在 Management Studio 主机计算机上，还必须将 ssms.exe 添加到“例外”列表中并打开 TCP 端口 135。有关详细信息，请参阅配置和启动 Transact-SQL 调试器。

有关为数据库引擎配置 Windows 防火墙的分步说明，请参阅如何为数据库引擎访问配置 Windows 防火墙。

动态端口

默 认情况下，命名实例（包括 SQL Server Express）使用动态端口。也就是说，每次启动数据库引擎时，它都将确定一个可用端口并使用此端口号。如果命名实例是安装的唯一数据库引擎实例，则它 可能使用 TCP 端口 1433。如果还安装了其他数据库引擎实例，则它可能会使用其他 TCP 端口。由于所选端口可能会在每次启动数据库引擎时更改，因而很难配置防火墙以启用对正确端口号的访问。因此，如果使用防火墙，则建议重新配置数据库引擎以 每次都使用同一端口号。这称为固定端口或静态端口。有关详细信息，请参阅配置固定端口。

另一种配置命名实例以侦听固定端口的方法是在防火墙中为诸如 sqlservr.exe 之类的 SQL Server 程序创建例外（针对数据库引擎）。这会非常方便，但当使用高级安全 Windows 防火墙 MMC 管理单元时，端口号将不会显示在“入站规则”页的“本地端口”列中。这会使审核哪些端口处于打开状态变得更为困难。另一注意事项是 Service Pack 或累积的更新可能会更改 SQL Server 可执行文件的路径，这将使防火墙规则作废。

希望可以帮到那些困惑中的人们。祝：好运。