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为见证服务器
    clip_image002
    A服务器
    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服务器:
    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就可以,无需其他准备
    准备完成后如下图所示：
    clip_image004
    三 三种模式的搭建
    数据库镜像要建立必需得建立信任关系,那么在WIN环境下建立信任关系可以通过三种方式:域帐户,证书信任,windows 匿名登陆,现就前两种模式做配置说明.
    3.1 域帐户模式:
    3.1.1 更改mssqlserver服务的的登陆方式为域帐户登陆方式:
    进入windows服务管理控制台,更改服务登陆帐户,使域账户有更改MSSQL SERVER服务状态的权限．三台机器都做同样设置
    clip_image006
    将域帐户赋予sysadmin角色
    clip_image009
    
    3.1.2 建立端点:
    通过图形界面建立端点：
    启动SQLWB，按图一直下一步
    clip_image011clip_image013clip_image015
    clip_image017clip_image020
    用域帐户登陆
    如果成功则：
    clip_image022
    
    3.2 证书模式
    3.2.1建立证书&端点
    参与数据库镜像会话的服务器必须彼此信任。对于本地通信而言，例如一个域内的通信，信任意味着SQL Server实例登陆账号必须有权限连接到其他镜像服务器，也包括endpoints。首先在每个服务器上使用CREATE LOGIN命令，然后使用GRANT CONNECT ON ENDPOINT命令．非信任域之间的通信必须使用证书。如果使用CREATE CERTIFICATE语句创建自签名的证书，基本上所有数据镜像证书的要求都可以满足。确认在CREATE CERTIFICATE语句中将证书标记为ACTIVE FOR BEGIN_DIALOG。
    一　建立证书：
    镜像服务器上执行：
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';
    CREATE CERTIFICATE HOST_A_cert WITH SUBJECT='HOST_A certificate', START_DATE='2010-03-10';
    主体服务器上执行：
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';
    CREATE CERTIFICATE HOST_B_cert WITH SUBJECT='HOST_B certificate', START_DATE='2010-03-10';
    见证服务器上执行：
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';
    CREATE CERTIFICATE HOST_C_cert WITH SUBJECT='HOST_C certificate', START_DATE='2010-03-10';
    
    二　建立端点：
    镜像服务器上执行：
    
    --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 );
    主体服务器上执行：
    --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 );
    见证服务器上执行：
    --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;
    证书互备：
    镜像服务器上执行：
    --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:\,也就是每台服务器有三个证书
    三：建立登陆用户：
    镜像服务器上执行：
    --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';
    至此引证书建立完毕
    clip_image024
    四、测试操作
    clip_image027
    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
相关文章！