SQLserver镜像

--配置要求：sql server版本必须是2005 SP2以上，sqlserver2008 R2同样适用
一、主体服务器配置
--创建数据库cs_a，注意数据库文件的位置，我这里选的是默认，具体配置以实际为准
create database cs_a


USE [master]   
GO   
ALTER DATABASE cs_a SET RECOVERY FULL WITH NO_WAIT   
GO  
--创建主密钥
use master
create master key encryption by password='123456'
--创建证书
create certificate Host_A_cert with subject = 'Host_A_certificate',start_date = '2015-04-27', Expiry_Date = '2099-1-1'; --过
go
/*  
--删除主密钥  
USE master;  
DROP MASTER KEY  
*/ 
--DROP CERTIFICATE Host_A_cert
 
--创建端点
IF NOT EXISTS ( SELECT  1   
                FROM    sys.database_mirroring_endpoints )   
    BEGIN   
        CREATE ENDPOINT [Database_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 );   
    END 
--删除端点 drop ENDPOINT [Database_Mirroring]

--备份证书
BACKUP CERTIFICATE Host_A_Cert   
TO FILE = 'd:\cert_backup\Host_A_Cert.cer';
--创建帐号，并映射到创建的登录账号中给镜像服务器使用
CREATE LOGIN Host_B_Login WITH PASSWORD = '987654321'; 
CREATE USER Host_B_User For Login Host_B_Login;
--建立一个文件夹d:\cert_share 用来存放主体和镜像的生成的证书
--创建证书授权用户
CREATE CERTIFICATE Host_B_Cert   
AUTHORIZATION Host_B_User   
FROM FILE = 'd:\cert_share\Host_B_Cert.cer';  
--把登录账号授权访问端口
GRANT CONNECT ON ENDPOINT::[Database_Mirroring] TO [Host_B_Login];
--完整备份数据库并复制到镜像服务器
backup database cs_a to disk = 'd:\cert_share\cs_a.bak' with format
backup log cs_a to disk = 'd:\cert_share\cs_a_log.bak'
--先在镜像服务器上执行同样的命令，注意IP地址
ALTER DATABASE cs_a SET PARTNER = 'TCP://192.168.20.176:5022';
-----------------到此主体服务器配置完成-------------------------
 
二、镜像服务器配置
--创建数据库cs_a
create database cs_a


USE [master]   
GO   
ALTER DATABASE cs_a SET RECOVERY FULL WITH NO_WAIT   
GO  
--创建主密钥
use master
create master key encryption by password='123456'
--创建证书
create certificate Host_B_cert with subject = 'Host_B_certificate',start_date = '2015-04-27', Expiry_Date = '2099-1-1'; --过
go
/*  
--删除主密钥  
USE master;  
DROP MASTER KEY  
*/ 
--DROP CERTIFICATE Host_B_cert
 
--创建端点
IF NOT EXISTS ( SELECT  1   
                FROM    sys.database_mirroring_endpoints )   
    BEGIN   
        CREATE ENDPOINT [Database_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 );   
    END 
--删除端点 drop ENDPOINT [Database_Mirroring]

--备份证书
BACKUP CERTIFICATE Host_B_Cert   
TO FILE = 'd:\cert_backup\Host_B_Cert.cer';
--创建帐号，并映射到创建的登录账号中给镜像服务器使用
CREATE LOGIN Host_A_Login WITH PASSWORD = '987654321'; 
CREATE USER Host_A_User For Login Host_A_Login;
--建立一个文件夹d:\cert_share 用来存放主体和镜像的生成的证书
--创建证书授权用户
CREATE CERTIFICATE Host_A_Cert   
AUTHORIZATION Host_A_User   
FROM FILE = 'd:\cert_share\Host_A_Cert.cer';  
--把登录账号授权访问端口
GRANT CONNECT ON ENDPOINT::[Database_Mirroring] TO [Host_A_Login];
--还原备份数据库 注意数据库存放的位置，我这里选的是默认位置
restore database cs_a to disk = 'd:\cert_share\cs_a.bak' with norecovery
restore log cs_a to disk = 'd:\cert_share\cs_a_log.bak' with norecovery
--添加伙伴
ALTER DATABASE cs_a SET PARTNER = 'TCP://192.168.20.218:5022';
-----------------------到此镜像服务器配置完成-------------------------------




--由于镜像数据库的数据不能查看，如果要查看镜像数据需使用数据库快照
CREATE DATABASE cs_a_snap_20150428 ON
( NAME = cs_a, FILENAME = 
'd:\cs_a_snap_20150428.ss' ) --数据库快照存放的路径
AS SNAPSHOT OF cs_a;
GO




--主体和镜像切换，在主体服务器上执行，主体和镜像就会互换
use master
ALTER DATABASE cs_a SET PARTNER FAILOVER;
--强制故障转移：在主体数据库发生故障的情况下切换镜像数据库为主体数据库
ALTER DATABASE cs_a SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
--当故障主机恢复后
use master;
alter database backupdatabase set partner resume;
alter database backupdatabase set partner failover;


--镜像服务器可以创建快照以提供数据查询
CREATE DATABASE cs_a_snap_20150430
ON (NAME = 'cs_a', FILENAME = 'd:\cs_a_snap_20150430.SNP')
   AS SNAPSHOT OF cs_a
   
1、镜像与主体切换
--主机
use master;
alter database backupdatabase set partner failover;
执行成功后原主体数据库会显示正在还原，备机数据库显示主体正在同步字样。
2、测试主备切换
主机崩溃，强制备机当主机，原主机恢复后再切换回去
主机：A
备机：B
此时，在B机上执行
use master;
alter database backuptest set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收
当主机出现故障时（断电），使用此命令后，备机上的数据库会由显示正在恢复状态变成backupdatabase(主体，已断开链接),即现在的镜像B可以用来充当主机了，假如现在有业务往数据库里插也是能成功的。
现在把原主机A恢复(通电)，然后在B机里操作
use master;
alter database backupdatabase set partner resume;
此时A机是作为镜像的，B机是作为主机的，要再切换一下，则再在B上执行
alter database backupdatabase set partner failover;
   
   
---SQL镜像维护----
---查看端点名
SELECT * FROM sys.endpoints