一、为主体数据库配置出站连接
 
--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';
GO

--在MIR-A上为数据库实例创建证书
USE master;
CREATE CERTIFICATE MIR_A_cert
   WITH SUBJECT = 'MIR_A certificate',
  START_DATE = '01/27/2008',
  EXPIRY_DATE = '01/01/2099'
GO

--在MIR-A上使用上面创建的证书为数据库实例创建镜像端点
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE MIR_A_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
--备份MIR-A上的证书并拷贝到MIR-B，MIR-W上
BACKUP CERTIFICATE MIR_A_cert TO FILE = 'C:\MIR_A_cert.cer';
GO

二、为镜像数据库配置出站连接

--创建数据库主密钥
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';
GO
--在MIR-B上为数据库实例创建证书
CREATE CERTIFICATE MIR_B_cert
   WITH SUBJECT = 'MIR_B certificate for database mirroring',
  START_DATE = '01/27/2008',
  EXPIRY_DATE = '01/01/2099';
GO
--在MIR-B上使用上面创建的证书为数据库实例创建镜像端点
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024,
      LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE MIR_B_cert,
      ENCRYPTION = REQUIRED ALGORITHM RC4,
      ROLE = ALL
   );
GO
--备份MIR-B上的证书并拷贝到MIR-A，MIR-W上
BACKUP CERTIFICATE MIR_B_cert TO FILE = 'C:\MIR_B_cert.cer';
GO

三、为见证数据库配置出站连接

--创建数据库主密钥
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';
GO
--在MIR-W上为数据库实例创建证书
CREATE CERTIFICATE MIR_W_cert
   WITH SUBJECT = 'MIR_W certificate for database mirroring',
  START_DATE = '01/27/2008',
  EXPIRY_DATE = '01/01/2099';
GO
--在MIR-W上使用上面创建的证书为数据库实例创建镜像端点
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024,
      LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE MIR_W_cert,
      ENCRYPTION = REQUIRED ALGORITHM RC4,
      ROLE = ALL
   );
GO
--备份MIR-W上的证书并拷贝到MIR-A，MIR-B上
BACKUP CERTIFICATE MIR_W_cert TO FILE = 'C:\MIR_W_cert.cer';
GO

四、为主体数据库配置入站连接

--在MIR-A上为MIR-B创建登陆
USE master;
CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_B_user FOR LOGIN MIR_B_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_B_cert
   AUTHORIZATION MIR_B_user
   FROM FILE = 'C:\MIR_B_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];
GO

--在MIR_A上为MIR_W创建登陆
USE master;
CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_W_user FOR LOGIN MIR_W_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_W_cert
   AUTHORIZATION MIR_W_user
   FROM FILE = 'C:\MIR_W_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login];
GO

五、为镜像数据库配置入站连接

--在MIR-B上为MIR-A创建登陆
USE master;
CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_A_user FOR LOGIN MIR_A_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_A_cert
   AUTHORIZATION MIR_A_user
   FROM FILE = 'C:\MIR_A_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login];
GO

--在MIR-B上为MIR-W创建登陆
USE master;
CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_W_user FOR LOGIN MIR_W_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_W_cert
   AUTHORIZATION MIR_W_user
   FROM FILE = 'C:\MIR_W_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login];
GO
 
六、为见证数据库配置入站连接
 
--在MIR-W上为MIR-A创建登陆
USE master;
CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_A_user FOR LOGIN MIR_A_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_A_cert
   AUTHORIZATION MIR_A_user
   FROM FILE = 'C:\MIR_A_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login];
GO

--在MIR-W上为MIR-B创建登陆
USE master;
CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';
GO
--为以上登陆创建一个用户
CREATE USER MIR_B_user FOR LOGIN MIR_B_login;
GO
--绑定证书到用户
CREATE CERTIFICATE MIR_B_cert
   AUTHORIZATION MIR_B_user
   FROM FILE = 'C:\MIR_B_cert.cer'
GO
--在镜像端点上为登陆赋予CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];
GO
 
七、在MIR-A上备份数据库
 
--修改Northwind数据库使用完整恢复模式
ALTER DATABASE Northwind
   SET RECOVERY FULL;
GO
--在 MIR_A 的主体服务器实例上备份数据库
--通过安全方法，将 C:\Northwind.bak 复制到MIR_B上
USE Northwind
BACKUP DATABASE Northwind
    TO DISK = 'C:\Northwind.bak'
    WITH FORMAT
GO
 
八、转到MIR-B上去恢复数据库
 
--在MIR-B的镜像服务器实例上还原数据库：
RESTORE DATABASE Northwind
    FROM DISK = 'C:\Northwind.bak'
    WITH NORECOVERY
GO
 
九、为镜像数据库配置镜像伙伴
 
--在 MIR-B 的镜像服务器实例上，将 MIR-A 上的服务器实例设置为伙伴
ALTER DATABASE Northwind
    SET PARTNER = 'TCP://MIR-A:5024';
GO
 
十、为主体数据库配置镜像伙伴和见证服务器
 
--在 MIR-A 的主体服务器实例上，将MIR-B上的服务器实例设置为伙伴
ALTER DATABASE Northwind
    SET PARTNER = 'TCP://MIR-B:5024';
GO
--在 MIR-A 的主体服务器实例上，将 MIR-W 上的服务器实例设置为见证服务器
ALTER DATABASE Northwind
    SET WITNESS = 'TCP://MIR-W:5024';
GO
 
十一、配置数据库镜像事务安全级别
 
ALTER DATABASE Northwind SET SAFETY FULL
GO
 
十二、分别在MIR-A和MIR-B上查看数据库镜像的配置状态
 
-- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态
-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态
-- 3.)通过系统目录视图查看数据库镜像配置情况

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 = 'Northwind')
GO

十三、测试故障转移情况