Sql server 2005 镜像例证

前提：

Host_A 安装SQL Server 2005和sp3

Host_B安装SQL Server 2005和sp3

一、镜像配置（认证方式）

1．为出站连接配置 Host_A

--On master database, create the database Master Key, if needed CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testdatabase'; GO -- Make a certificate for this server instance. USE master; CREATE CERTIFICATE HOST_A_cert    WITH SUBJECT = 'HOST_A certificate',     START_DATE = '06/01/2009',     EXPIRY_DATE = '10/31/2009'; GO   --Create mirroring endpoint for server instance using the certificate CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=5024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE HOST_A_cert       , ENCRYPTION = REQUIRED ALGORITHM RC4       , ROLE = ALL    ); GO --drop ENDPOINT Endpoint_Mirroring --Back up HOST_A certificate and copy it to other system, HOST_B. BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer'; GO --Using any secure copy method, copy C:\HOST_A_cert.cer to HOST_B.

2．为出站连接配置 Host_B

--On master database, create the database Master Key, if needed. USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testdatabase'; GO -- Make a certiifcate on the HOST_B server instance. CREATE CERTIFICATE HOST_B_cert    WITH SUBJECT = 'HOST_B certificate',     START_DATE = '06/01/2009',     EXPIRY_DATE = '10/31/2009'; GO --drop CERTIFICATE HOST_B_cert --Create a mirroring endpoint for the server instance on HOST_B. CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=5024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE HOST_B_cert       , ENCRYPTION = REQUIRED ALGORITHM RC4       , ROLE = ALL    ); GO --Backup HOST_B certificate. BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer'; GO --Using any secure copy method, copy C:\HOST_B_cert.cer to HOST_A.

3．为入站连接配置 Host_A

上传备机文件：C:\HOST_B_cert.cer 到主机：D:\HOST_B_cert.cer

--Create a login on HOST_A for HOST_B USE master; CREATE LOGIN HOST_B_login WITH PASSWORD = 'logindatabase'; GO --Create a user for that login. CREATE USER HOST_B_user FOR LOGIN HOST_B_login; GO --Associate the certificate with the user CREATE CERTIFICATE HOST_B_cert    AUTHORIZATION HOST_B_user    FROM FILE = 'D:\HOST_B_cert.cer' GO --drop CERTIFICATE HOST_B_cert --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; GO

4．为入站连接配置 Host_B

上传主机文件：C:\HOST_A_cert.cer 到备机：D:\HOST_A_cert.cer

--Create a login on HOST_B for HOST_A USE master; CREATE LOGIN HOST_A_login WITH PASSWORD = 'logindatabase'; GO --Create a user for that login. CREATE USER HOST_A_user FOR LOGIN HOST_A_login; GO --Associate the certificate with the user CREATE CERTIFICATE HOST_A_cert    AUTHORIZATION HOST_A_user    FROM FILE = 'D:\HOST_A_cert.cer' GO --drop CERTIFICATE HOST_A_cert --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; GO

5．备份主机数据库到镜像服务器还原

（1）备份主机

USE [abank.cn]

BACKUP DATABASE [abank.cn]

    TO DISK = 'C:\bank.bak'

    WITH FORMAT

GO

backup log [abank.cn]

to disk='c:\bank_log.bak'

with format

（2）在镜像机还原

RESTORE database [abank.cn]

    FROM DISK = 'D:\bank.bak'

    WITH NORECOVERY

GO

 

RESTORE log [abank.cn]

    FROM DISK = 'D:\bank_log.bak'

    WITH NORECOVERY

GO

6．镜像

（1）备份机

ALTER DATABASE [abank.cn]

    SET PARTNER = 'TCP://192.168.20.202:5024';

GO

（2）主体机

ALTER DATABASE [abank.cn]

    SET PARTNER = 'TCP://192.168.20.203:5024';

GO

7．配置成功结果

 

 

8．停止镜像

ALTER DATABASE [abank.cn] SET PARTNER OFF

二、测试

1．主备互换测试

         在主机上执行：

         USE master;

         ALTER DATABASE [abank.cn] SET PARTNER FAILOVER;

    在对象管理器上刷新可以看到主备状态已改变

    （在备机上执行上面语句，切换成原来状态）

2．主服务器Down掉，使用备机作为主体服务

         主机Down掉后，备份机上的数据库显示如下状态

        

         USE master;

ALTER DATABASE [abank.cn] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

 

使用上述语句操作后，备份机变为主体，状态如下图所示：

 

（系统使用数据库需要更改连接地址）

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

原来的主体服务器恢复后，作为备份机的体数据库显示如下状态：

 

         在备份机上执行下列语句，恢复到故障前主备配置，需要更改应用系统的数据库连接。

（或者将原来配置的主备互换，不切换主备）
USE master;
ALTER DATABASE [abank.cn] SET PARTNER RESUME; --恢复镜像
ALTER DATABASE [abank.cn] SET PARTNER FAILOVER; --切换主备

  （在备机作为主体服务时的数据会自动恢复到现在主机）

4．暂停镜像会话

ALTER DATABASE [abank.cn] SET PARTNER SUSPEND --暂停

ALTER DATABASE [abank.cn] SET PARTNER RESUME--恢复镜像

5．备机Down掉测试

主机保持服务，备机恢复后自动恢复镜像。