mssql2005数据库镜像搭建教程

一 概述

数据库镜像是SQL SERVER 2005用于提高数据库可用性的新技术。数据库镜像将事务日志记录直接从一台服务器传输到另一台服务器，并且能够在出现故障时快速转移到备用服务器。可以编写客户端程序自动重定向连接信息，这样一旦出现故障转移就可以自动连接到备用服务器和数据库。

优势:数据库镜像可以在不丢失已提交数据的前提下进行快速故障转移，无须专门的硬件，并且易于配置和管理。

二 环境准备

操作系统:Window 2003 enterprise sp2(至少两台,如要启用自动故障转移,必需三台)

SQL版本:MSSQL SERVER 2005 SP3

检查SQL SERVER版本:

exec xp_msver

select SERVERPROPERTY('productlevel')

数据库准备:准备一个数据库:ccerp_jzt ,备份此数据库还原到另外一台机器上,另外一台必须是with no recovery

这里我假设服务器A,B,C

A为主体服务器,B为镜像服务器,C为见证服务器

restore database ccerp_jzt from disk=N'f:/databak/ccerp_jzt_backup_200911250100.bak' with replace,recovery,

move 'ccerp_ydswzip_Data' to 'd:/data/ccerp_jzt.mdf',

move 'ccerp_ydswzip_Log' to 'd:/data/ccerp_jzt_log.ldf'

exec sp_helpdb 'ccerp_jzt'

backup database ccerp_jzt to disk =N'f:/databak/sk.bak' with init

--更改恢复模式

alter database ccerp_jzt set recovery full

B服务器:

CREATE DATABASE ccerp_jzt

ON

( NAME = Sales_dat,

FILENAME = 'd:/data/ccerp_jzt.mdf',

SIZE = 10

)

LOG ON

( NAME = 'ccerp_jzt_log',

FILENAME = 'd:/data/ccerp_jzt_log.ldf',

SIZE = 5MB

)

GO

restore filelistonly from disk=N'f:/xxzx/data/sk.bak'

use master

go

restore database ccerp_jzt from disk=N'f:/xxzx/data/sk.bak' with replace,norecovery,

exec sp_helpdb 'ccerp_jzt'

C服务器只要装上SQL SERVER 2005就可以,无需其他准备

准备完成后如下图所示：

mssqlserver服务的的登陆方式为域帐户登陆方式:

进入windows服务管理控制台,更改服务登陆帐户,使域账户有更改MSSQL SERVER服务状态的权限．三台机器都做同样设置

3.1.2建立端点:

通过图形界面建立端点：

启动SQLWB，按图一直下一步

用域帐户登陆

主体服务器上执行

--backup certificate

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:/HOST_B_cert.cer'

见证服务器上执行：

BACKUP CERTIFICATE HOST_c_cert TO FILE = 'e:/HOST_C_cert.cer'

将备份到的证书进行互换，即HOST_A_cert.cer复制到B机的e:/ 将HOST_B_cert.cer复制到A机的E:/,也就是每台服务器有三个证书

三：建立登陆用户：

镜像服务器上执行：

--Create user

CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

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];

CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:/HOST_c_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_c_login];

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

--query user sid

select loginname,name,sid From syslogins

主体服务器上执行：

--Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

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];

-- add witness user

CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:/HOST_c_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_c_login];

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

--query sid

select loginname,name,sid From syslogins

见证服务器上执行：

--Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

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];

--add user host_b_login to have pemission to access witness

CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

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];

grant connect on endpoint::endpoint_mirroring to HOST_C_login

USE master;

exec sp_addlogin

@loginame = 'HOST_B_login',

@passwd = 'test',

@sid = 0x1A914CA3D1D00C4793EBC96E4C4F4352 ;

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

四．建立镜像：

先在镜像服务器上执行：

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.44:5022';

接着主体服务器执行：

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

ALTER DATABASE ccerp_jzt SET witness = 'TCP://192.168.137.49:5022';

至此引证书建立完毕

1、主备互换

--主机执行：

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

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

--备机执行：

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

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

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

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

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

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

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

错误说明：
消息1498，级别16，状态3，第1 行

默认情况下，数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用，并不应使用于生产环境中。若要以评估为目的启用数据库镜像，请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息，请参阅SQL Server 联机丛书。

解决办法：没打SP1以上补丁．强烈建议打SP3

消息1475，级别16，状态2，第1 行

由于"ccerp_jzt" 数据库可能有尚未备份的大容量日志记录更改，所以无法启用数据库镜像。必须在镜像上还原主体数据库的上一次日志备份。

主体上：backup log ccerp_jzt to disk ='e:/log.trn' with no_truncate

镜像上：restore log ccerp_jzt from disk='e:/log.trn' with norecovery