SQL Server 2005 镜像构建手册 (转)

   文章来源：http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html

  

一、 镜像简介

1、 简介

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

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

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

2、 优点

下表是SQL Server可用性官方解决方案的一个对照表，现时我中心使用的恢复模式是“冷备份”中的“备份/恢复”，通常来说“热备份”比“冷备份”的可用性更高，恢复更快，更适合我中心现时的实际情况。如果不从成本考虑的话，“热备份”中的“故障转移群集”的可用性是最高的，但是故障转移群集需要借助磁盘阵列而且建设本身复杂性较高。数据库镜像的建立并没有太多的硬件要求，最起码没有像“故障转移群集”需要共享存储这么高的要求。

2、 缺点

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

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

（3）数据库作业不能得到相应的维护。

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

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

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

一、配置主备机

1、 物理连接

将主备数据库按照如图所示连接：

2、 检查SQL Server 2005数据库

只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本，执行如下语句查看：

1select @@version;

若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用 Transact-SQL 实现此目的，请使用 ALTER DATABASE 语句：

1 USE master;
2 ALTER DATABASE <DatabaeName> 
3 SET RECOVERY FULL;
4 

二、主备实例互通

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

1、创建证书（主备可并行执行）

--主机执行：

1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
3CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
4START_DATE = '01/01/2008';
5

--备机执行：

1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
3CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
4START_DATE = '01/01/2008';
5

2、创建连接的端点（主备可并行执行）

--主机执行：

1CREATE ENDPOINT Endpoint_Mirroring 
2STATE = STARTED 
3AS 
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
5FOR 
6DATABASE_MIRRORING 
7( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8

--备机执行：

1CREATE ENDPOINT Endpoint_Mirroring 
2STATE = STARTED 
3AS 
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
5FOR 
6DATABASE_MIRRORING 
7( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8

3、备份证书以备建立互联（主备可并行执行）

--主机执行：

1BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';

 --备机执行：

1BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';

4、互换证书

将备份到D:\SQLBackup\的证书进行互换，即HOST_A_cert.cer复制到备机的D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\

5、添加登陆名、用户（主备可并行执行）

以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束，SQL Server2005的不定号为SP2）

--主机执行：

1CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
2CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
3CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
5

--备机执行：

1CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
2CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
3CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
5

三、建立镜像关系

以下步骤是针对每个数据库进行的，例如：现有主机中有5个数据库以下过程就要执行5次。

1、 手工同步登录名和密码

在第一章中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"

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

1USE master;
2select sid,name from syslogins;
3

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

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

1USE master;
2exec sp_addlogin 
3@loginame = '<LoginName>', 
4@passwd = '<Password>', 
5@sid = <sid> ;
6

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

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

1sid name
2
3---------------------------------- -----------------
4
50x074477739DCA0E499C29394FFFC4ADE4 cz_account
6
7

则建立登录名的SQL语句：

1USE master;
2exec sp_addlogin 
3@loginame = 'cz_account', 
4@passwd = 'password', 
5@sid = 0x074477739DCA0E499C29394FFFC4ADE4;
6

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

2、 准备备机数据库

承接上文，该节是描述如何同步主备数据库内的数据。

可以尝试从刚刚使用的全备文件进行还原，在还原数据的时候需要使用选上“with non recover”。如图所示：

如果执行成功数据库将会变成这个样子：

3、 建立镜像

由于是实验，没有为服务器配置双网卡，IP地址与图有点不一样，但是原理一样。

--主机执行：

1ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.45:5022';

--如果主体执行不成功，尝试在备机中执行如下语句：

1ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.49:5022';

 

如果执行成功，则主备数据库将会呈现如上图所示的图标。

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

还原事务日志时需在选项中选择“restore with norecovery”，如图所示：

成功还原以后再执行建立镜像的SQL语句。

四、测试操作

1、主备互换

--主机执行：

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;
3

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

--备机执行：

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
3

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

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

4、原来的主服务器恢复,可以继续工作

--默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。

--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

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

   配置说明：

     在配置过程中，出现了备机一直还原，不能同步，后来是删除镜像。 alter databse tb  set partner off 再还原一次数据库日志，不回滚。再执行：
     ALTER DATABASE td SET PARTNER = 'TCP://192.168.149.128:5022';
     这里的IP地址是对方的IP地址。需要在2个数据库中都要执行。
     注意，建立镜像的命令都是在Master表中进行。
     是针对实例级别的。建立镜像的数据库名称必须一致。

   

    实现故障转移，不能直接关闭数据库实现，必须发送 PARTNER FAILOVER 命令来实现，如果主服务器down机，数据库在备机上更改数据，一旦主机接管，数据也能更改到主机里。实现无缝结合。   

   一开始无法创建endpoint,

   执行
   USE MASTER
   GO
SELECT * FROM sys.database_mirroring_endpoints;
GO
SELECT * FROM sys.database_mirroring WHERE database_id =
(SELECT database_id FROM sys.databases WHERE name = 'td')

  直接查询
  SELECT * FROM sys.database_mirroring_endpoints;
  执行drop endpoint 镜像即可。同时也有drop掉
  DROP MASTER KEY
  DROP CERTIFICATE MIR_A_cert
  DROP CERTIFICATE host_105
  DROP CERTIFICATE HOST_A_cert
 
  select * from sys.symmetric_keys

 

  运行模式

     高性能模式（异步运行）：事务不需要等待镜像服务器将日志写入磁盘便可提交，这样可最大程度地提高性能。这意味着事务不需要等待镜像服务器将日志写入磁盘便可提交，而此操作允许主体服务器在事务滞后时间最小的条件下运行，但可能会丢失某些数据。

     模式（同步运行）：当会话开始时，镜像服务器使镜像数据库尽快与主体数据库同步。一旦同步了数据库，事务将在双方提交，这会延长事务滞后时间。

 注意：

   

    开始镜像会话之前，数据库所有者或系统管理员必须创建镜像数据库，设置端点和登录名。在某些情况下，还要创建并设置证书。

    创建新镜像数据库的最低要求是：执行主体数据库的完整备份和一个后续日志备份，并使用 WITH NORECOVERY 将这两个备份还原到镜像服务器实例上。为使镜像正常运行，镜像数据库必须处于 RESTORING 状态。

    并且在开始镜像之前，如果在执行完必要的日志备份之后又执行了任何其他日志备份，则还必须手动应用其他每个日志备份（始终使用 WITH NORECOVERY）。如果计划在数据库中非常频繁地运行日志备份作业，则可能需要禁用备份作业，直到镜像启动为止

  

会发送一个经过压缩的事务日志流到Mirror database

1.停止当前镜像（如果在源头有日志收缩等操作，必须停掉）
  alter database database_name
  set partner off

2.删除当前endpoint节点
  drop endpoint [Endpoint_Mirroring]

3.按给定的账号，重建endpoint节点
  CREATE ENDPOINT [Endpoint_Mirroring]
 AUTHORIZATION [idc5173\sqlservice]
 STATE=STARTED
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)

4.重新做起镜像
  在备机上运行：
   ALTER DATABASE database_name SET PARTNER = 'TCP://主机IP:5022';