[b]一 概述[/b] 数据库镜像是SQL SERVER 2005用于提高数据库可用性的新技术。数据库镜像将事务日志记录直接从一台服务器传输到另一台服务器，并且能够在出现故障时快速转移到备用服务器。可以编写客户端程序自动重定向连接信息，这样一旦出现故障转移就可以自动连接到备用服务器和数据库。 优势:数据库镜像可以在不丢失已提交数据的前提下进行快速故障转移，[b]无须专门的硬件[/b]，并且易于配置和管理。 [b]二 环境准备[/b] 操作系统:Window 2003 enterprise sp2(至少两台,如要启用自动故障转移,必需三台) SQL版本:MSSQL SERVER 2005 SP3 检查SQL SERVER版本: exec xp_msver select SERVERPROPERTY('productlevel') 数据库准备:准备一个数据库:ccerp_jzt ,备份此数据库还原到另外一台机器上,另外一台必须是[b]with no recovery[/b] 这里我假设服务器A,B,C A为主体服务器,B为镜像服务器,C为见证服务器 [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image002_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261819.jpg[/img] [/url] [b]A[/b][b]服务器[/b] use master go restore filelistonly from disk=N'f:\databak\ccerp_jzt_backup_200911250100.bak' 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]B[/b][b]服务器:[/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就可以,无需其他准备 准备完成后如下图所示： [b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image004_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261820.jpg[/img] [/url][/b][b][/b] [b]三 三种模式的搭建[/b] 数据库镜像要建立必需得建立信任关系,那么在WIN环境下建立信任关系可以通过三种方式:域帐户,证书信任,windows 匿名登陆,现就前两种模式做配置说明. [b]3.1 [/b][b]域帐户模式:[/b] 3.1.1 更改mssqlserver服务的的登陆方式为域帐户登陆方式: 进入windows服务管理控制台,更改服务登陆帐户,使域账户有更改MSSQL SERVER服务状态的权限．三台机器都做同样设置 [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image006_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261821.jpg[/img] [/url] 将域帐户赋予sysadmin角色 [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image007_2.gif][/url][b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image009_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261823.jpg[/img] [/url][/b] [b][/b] [b]3.1.2[/b][b] [/b][b]建立端点:[/b] 通过图形界面建立端点： 启动SQLWB，按图一直下一步 [b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image011_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261824.jpg[/img] [/url][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image013_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261825.jpg[/img] [/url][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image015_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261826.jpg[/img] [/url] [/b][b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image017_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261827.jpg[/img] [/url][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image018_2.gif][/url][/b][b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image020_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261829.jpg[/img] [/url][/b] 用域帐户登陆 [b][/b][b]如果成功则：[/b] [b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image022_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261830.jpg[/img] [/url][/b] [b][/b] [b]3.2 [/b][b]证书模式[/b] [b]3.2.1[/b][b]建立证书&端点[/b] 参与数据库镜像会话的服务器必须彼此信任。对于本地通信而言，例如一个域内的通信，信任意味着SQL Server实例登陆账号必须有权限连接到其他镜像服务器，也包括endpoints。首先在每个服务器上使用CREATE LOGIN命令，然后使用GRANT CONNECT ON ENDPOINT命令．非信任域之间的通信必须使用证书。如果使用CREATE CERTIFICATE语句创建自签名的证书，基本上所有数据镜像证书的要求都可以满足。确认在CREATE CERTIFICATE语句中将证书标记为ACTIVE FOR BEGIN_DIALOG。[b][/b] [b]一　建立证书：[/b] [b]镜像服务器上执行：[/b] USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST'; CREATE CERTIFICATE HOST_A_cert WITH SUBJECT='HOST_A certificate', START_DATE='2010-03-10'; [b]主体服务器上执行：[/b][b][/b] USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST'; CREATE CERTIFICATE HOST_B_cert WITH SUBJECT='HOST_B certificate', START_DATE='2010-03-10'; [b]见证服务器上执行：[/b][b][/b] USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST'; CREATE CERTIFICATE HOST_C_cert WITH SUBJECT='HOST_C certificate', START_DATE='2010-03-10'; [b][/b] [b]二　建立端点：[/b][b][/b] [b]镜像服务器上执行：[/b] [b][/b] --create mirror endpoint on primary A 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 ); [b]主体服务器上执行：[/b][b][/b] --Create endpoint on mirror server B 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 ); [b]见证服务器上执行：[/b][b][/b] --Create endpoint on witness server C 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 ); SELECT * FROM sys.database_mirroring_endpoints; [b]证书互备：[/b] [b]镜像服务器上执行：[/b] --backup certificate BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer' 主体服务器上执行 --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:\,也就是每台服务器有三个证书 [b]三：建立登陆用户：[/b] [b]镜像服务器上执行：[/b] --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 [b]主体服务器上执行：[/b] --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 [b]见证服务器上执行：[/b][b][/b] --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'; [b]四．建立镜像：[/b][b][/b] 先在镜像服务器上执行： 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'; 至此引证书建立完毕 [b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image024_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261831.jpg[/img] [/url][/b][b][/b] [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image025_2.gif][/url][b][url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image027_2.jpg][img]http://files.jb51.net/file_images/article/201211/2012113014261833.jpg[/img] [/url][/b] [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028_15.gif][/url]USE master; 2[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B1%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B3%5D.gif][/url]USE master; 2[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B4%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;  [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B6%5D.gif][/url]--备机执行： 2[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B7%5D.gif][/url]USE master; 3[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B8%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --恢复镜像 4[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B9%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --切换主备  [url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B11%5D.gif][/url]USE master; 2[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B12%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事务安全,同步模式 3[url=http://images.cnblogs.com/cnblogs_com/wenanry/WindowsLiveWriter/9009bcbe2398_F96C/clip_image028%5B13%5D.gif][/url]ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; --事务不安全,异步模式 [b]错误说明：[/b] 消息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