SQL+Server+2005+镜像(证书方式)

SQL Server 2005 镜像高可用性构建手册(证书方式)一，镜像的高可用性简介及技术分析1．1 镜像简介数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。

镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。

主服务器被称为“主机”，第二个服务器被称作“备机”。

主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。

当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。

见证服务器是第三个SQL Server 2005运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。

只有当你想实现自动错误恢复的时候用到这个选项。

它实现了2比1投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。

见证服务器只有在你想实现自动错误恢复的时候才需要用到。

1．2 优点下表是SQL Server可用性官方解决方案的一个对照表，通常来说“热备份”比“冷备份”的可用性更高，恢复更快。

如果不从成本考虑的话，“热备份”中的“故障转移群集”的可用性是最高的，但是故障转移群集需要借助磁盘阵列而且建设本身复杂性较高。

数据库镜像的建立并没有太多的硬件要求，最起码没有像“故障转移群集”需要共享存储这么高的要求。

1．3 缺点（1）由于SQL Server是一个实例多个数据库的产品，数据库镜像技术是基于数据库级别的，因此每次主数据库新增数据库都必须为备机增加数据库并且为新增的数据库建立镜像关系。

（2）数据库的登录名和用户是存储在master数据库，master数据库是不能做镜像的，所以每次操作数据库的登录名和用户也是需要多维护一份，（3）数据库作业不能得到相应的维护。

（4）微软号称镜像可以让客户端对故障透明，但是实际测试中发现只有满足特定的条件才能实现透明化，而且透明化得客户端支持才可行（.net Frame work 2.0以上，Microsoft jdbc驱动 1.1以上）。

（5）跨数据库事务和分布式事务均不支持数据库镜像。

纵观以上几种方式，仅有“热备份”的“故障转移群集”没有这些问题。

二、实验环境及结构说明2.1 软件环境说明(1)sql server 2005 SP1/SP2/SP3(2)sql server 2005 默认不支持镜像,但可以通过修改启动参数解决此问题，方法如下:开始->程序->Microsoft SQL Server 2005->配置工具->SQL Server Configuration Manager->SQL Server 配置管理器(本地) ->SQL Server 2005服务->SQL Server (MSSQLSERVER)->右键->属性->高级->双击启动参数->在弹出的最后一行句末舔加;-T1400 ->确定->重新启动SQL Server服务->完成2.2 拓扑结构图2.3 说明：DB_Mirror：本例中我们测试用的数据库名（A）10.0.0.11：主机（db0）（B）10.0.0.12：备机（db1）(C）10.0.0.13：见证（db2）三、配置主备机3．1 物理连接主、备、见证机须相互连通。

考虑日志传输的快速性，建议三台机器放在同一局域网内，如有条件可考虑主机和备机用交叉线直连，以利于日志文件的传输。

3．2 检查SQL Server 2005数据库版本只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。

其他版本即Express只能作为见证服务器。

如果实在不清楚什么版本，执行如下语句查看：3.3 建立要做镜像的数据库3.4 完全备份数据库用于在备机上还原数据库3.5 在备机上还原镜像数据库注意使用 WITH NORECOVERY 选项还原数据库四、主、备、见证、互通实例实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。

注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个SQL Server 2005的实例中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每一对主备实例（不是数据库）做一次互通。

4．1 作流程描述1、在主机、备机、见证机上分别创建数据库主密钥（用于加密证书）2、在主机、备机、见证机上分别创建证书（用于建立端点和登录用户）3、在主机、备机、见证机上分别创建端点（用于主、备、见证机的镜像连接）4、在主机、备机、见证机上分别备份各自的证书5、在主机、备机、见证机上分别创建登录用户创建原则如下：主机：分别用备机和见证机的证书创建主机的证书然后用该证书创建登录用户。

最后分别授予对主机数据库镜像端点的connect 权限备机：分别用主机和见证机的证书创建主机的证书然后用该证书创建登录用户。

最后分别授予对主机数据库镜像端点的connect 权限见证机：分别用备机和主机的证书创建主机的证书然后用该证书创建登录用户。

最后分别授予对主机数据库镜像端点的connect 权限1、在主机、备机上分别建立镜像：主机：启用到备机和见证机的数据库镜像备机：启用到主机的数据库镜像4．2 创建证书（主、备、见证可并行执行）--主机执行：--备机执行：--见证机执行：4.3 创建连接的端点（主、备、见证可并行执行）主机和备机的端点名称可以相同也可不同（建议相同）--主机执行：--备机执行：--见证机执行：以上用各自证书建立各自的镜像端点4.4 备份证书以备建立互联（主备可并行执行）--主机执行：--备机执行：--见证机执行：4．5 互换证书将备份到C:\的证书进行互换，即Db0_cert.cer复制到备机的C:\db0_cert.cer复制到见证机的C:\db1_cert.cer复制到主机的C:\db1_cert.cer复制到见证机的C:\db2_cert.cer复制到主机的C:\db2cert.cer复制到备机的C:\注意保存该三份证书存放目录可任意修改4．6 添加登陆名、用户（主、备、见证、可并行执行）以下操作只能通过命令行运行，通过图形界面无法完成。

到此为止可以认为备机数据库的环境已经与主机同步了，还差数据库内的数据未同步。

4.7 建立镜像--备机执行：--主机执行：如果建立失败，提示类似数据库事务日志未同步，则说主备数据库的数据（日志）未同步，为保证主备数据库内的数据一致，应在主数据库中实施一次“事务日志”备份，并还原到备数据库上。

备份“事务日志”如图所示：还原事务日志时需在选项中选择“restore with norecovery”，如图所示：成功还原以后再次执行建立镜像的SQL语句。

ALTER DATABASE DB_Mirror SET PARTNER ='TCP://10.0.0.12:5022';创建数据库主密钥：语法：CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'--DROP MASTER KEY '删除密钥数据库主密钥是指用于保护证书私钥的对称密钥以及数据库中存在的非对称密钥。

当创建主密钥时，会使用 Triple DES 算法以及用户提供的密码对其进行加密。

若要启用主密钥的自动解密功能，请使用服务主密钥对该主密钥的副本进行加密，并将副本存储在数据库和 master 中。

通常，每当主密钥更改时，便会在不进行提示的情况下更新存储在 master 中的副本。

可以使用ALTER MASTER KEY的 DROP ENCRYPTION BY SERVICE MASTER KEY 选项对该默认行为进行更改。

必须使用OPEN MASTER KEY语句和密码打开未使用服务主密钥进行加密的主密钥。

master 中 sys.databases 目录视图的 is_master_key_encrypted_by_server 列指示是否使用服务主密钥对数据库主密钥进行加密。

可以在 sys.symmetric_keys 目录视图中查看有关数据库主密钥的信息。

重要事项：您应该使用BACKUP MASTER KEY备份主密钥，并将备份存储于另外一个安全的位置中参数：PASSWORD = 'password' 用于对数据库中主密钥进行加密的密码。

创建证书：CREATE CERTIFICATE证书是一个数据库级别的安全对象，它遵循 X.509 标准并支持 X.509 V1 字段。

CREATE CERTIFICATE 可以通过文件或程序集加载证书。

该语句也可生成密钥对并创建自我签名的证书。

SQL Server 生成的私钥的长度为 1024 位。

从外部源导入的私钥的最小长度为 384 位，最大长度为 3,456 位。

导入的私钥的长度必须是 64 位的整数倍。

私钥必须与 certificate_name 指定的公钥相对应。

当您通过容器创建证书时，可选择是否加载私钥。

但是当 SQL Server 生成自我签名的证书时，始终会创建私钥。

默认情况下，私钥使用数据库主密钥进行加密。

如果数据库主密钥不存在并且未指定密码，则该语句将失败。

当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。

只有在使用密码对私钥进行加密时，才使用该选项。

如果未指定密码，则使用数据库主密钥对证书的私钥进行加密。

如果数据库主密钥无法打开，则省略该子句会导致错误。

如果使用数据库主密钥对私钥进行加密，则不一定必须指定解密密码。

1、端点：参见 sql server 2005 联机丛书 CREATE ENDPOINT 语句祥解5.1 为什么用set witness连接见证服务器时总报1416或1456的错误？答：请检查你参与镜像的三台服务器上用作镜像功能的网卡配置，比如DNS属性页上的DNS后缀选项、WINS属性页的启用LMHOSTS查找以及默认NETBIO S等这些最好都开启。

第二个问题我后来进一步验证了一下，只需要开启TCPIP上的NETBIOS就可以了。

检查一下开启NETBIOS的网卡IP是否是指定的IP，和你的ENDPOINT上指定的IP是否相同。

在主体服务器上telnet见证服务器的5022是否能连接上。

尝试用机器名指定见证，如：'TCP://机器名:5022'5．2 为么用set partner连接伙伴时总是报1418的错误？答：在配置时，特别是在工作组环境下最好使用证书，否则伙伴之间无法进行连接，域环境下用windows验证方式或证书验证方式均可。