关于SQL Server的镜像

这两天做SQL Server镜像，碰到很多问题，在网上看了不少文章，终于配置完成，总结一下：

引用文章：http://www.cnblogs.com/mrhgw/p/3514855.html （Sqlserver2008R2配置数据库镜像之我的经验总结 ）

1. 环境
   主体：10.100.0.33     镜像：10.2.0.24   无见证机
2. 配置
   1.主体和镜像的恢复模式都必须为“完整”，具体操作：具体数据库右键-》属性-》选项-》恢复模式，选择  “完整”
   2.设置RemoteDACEnabled，具体操作：数据库实例--》方面--》外围应用配置器--》RemoteDACEnabled-》True 确定
   3.TCP端口，网上一般都用5022，所以必须保证两台机器之间都可以用此端口连通，
       这里当时走了点弯路，两台服务器的5022端口是可用的，防火墙是关闭的，但 telnet  IP 5022  就是不通，后来发现是服务器外面还有一堵防火墙，后来没办法占用了https的443端口。 一定要用确保可以连通，最好的方式是在主体服务器  telnet 镜像服务器IP  端口  看通不通。否则最后会出现经典的无法访问的情况，在日志看会出现 10060错误
   4. 网上还有说要修改一致的DNS后缀，后来经试验，这个应该是不用的
3. 开始（copy引用文章），请看以下a->d四步，说明一下，sql2008，只能使用高安全的同步方式   alter database testsync set safety off 会显示失败
4. 切换
   当主体断电后，因没有见证服务器，不能自动切换到镜像
   强制镜像启动
   alter database testsync set partner force_service_allow_data_loss
   主体恢复后，自动变动为镜像，且两边显示挂起，此时执行
   ALTER DATABASE testsync SET PARTNER RESUME
   重新切换过来
   alter database testsync set partner failover
   

a) 主体备份，镜像还原。  这里有一个疑问，镜像还原后，一直显示“正在还原”，访问不了，这个可以不用管它

-- 主机备份
USE master
GO

BACKUP DATABASE [TestSync] TO DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FORMAT, INIT, NAME = N'TestSync-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO


BACKUP LOG [TestSync] TO DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestSync-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO 





-- 镜像恢复
USE master
GO
RESTORE DATABASE [TestSync] FROM DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

RESTORE LOG [TestSync] FROM DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO

b)因为是不同域，需要用证书进行身份认证，证书创建后需要把主机的证书传到镜像，把镜像的证书传到主体，以备下一步的创建身份认证

------------------------------------------------------------------------
--============================ 主机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='PWD_DBMirror986252588';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='PWD_DBMirror986252588';
GO

IF EXISTS(select * from sys.certificates WHERE name='Cert_Host')
    DROP CERTIFICATE Cert_Host;
GO
CREATE CERTIFICATE Cert_Host

WITH SUBJECT=N'Cert_Host Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_Host TO FILE=N'D:\SqlserverMirrorCer\Cert_Host.cer';
GO

--创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Host')
    DROP ENDPOINT Endpoint_Host
GO
CREATE ENDPOINT Endpoint_Host
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)

FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE Cert_Host,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)

GO


------------------------------------------------------------------------
--============================ 镜像机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='PWD_DBMirror986252588';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='PWD_DBMirror986252588';
GO

IF EXISTS(select * from sys.certificates WHERE name='Cert_Mirror')
    DROP CERTIFICATE Cert_Mirror;
GO
CREATE CERTIFICATE Cert_Mirror

WITH SUBJECT=N'Cert_Mirror Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_Mirror TO FILE=N'D:\SqlserverMirrorCer\Cert_Mirror.cer';
GO

--创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirror')
    DROP ENDPOINT Endpoint_Mirror
GO
CREATE ENDPOINT Endpoint_Mirror
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)

FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE Cert_Mirror,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)

GO

c) 创建身份认证，让主机和镜像可以互通

------------------------------------------------------------------------
--============================ 主机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Mirror WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Mirror FOR LOGIN Login_For_Mirror;
CREATE CERTIFICATE Cert_For_Mirror AUTHORIZATION User_For_Mirror FROM FILE=N'D:\SqlserverMirrorCer\Cert_Mirror.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Host TO Login_For_Mirror;
GO

------------------------------------------------------------------------
--============================ 镜像机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Host WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Host FOR LOGIN Login_For_Host;
CREATE CERTIFICATE Cert_For_Host AUTHORIZATION User_For_Host FROM FILE =N'D:\SqlserverMirrorCer\Cert_Host.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirror TO Login_For_Host;
GO

d)开始设置主从关系，上面的执行没有顺序关系，但是这里须要依次执行。先执行的为镜像，后执行的为主体

-- 镜像机上执行：
-- 建立 主机 合作
ALTER DATABASE [TestSync] SET PARTNER =N'TCP://10.100.0.33:5022'; 

-- 主机上执行：
-- 建立 镜像机 合作
ALTER DATABASE [TestSync] SET PARTNER=N'TCP://10.2.0.24:5022';

1. 配置成功后，主体数据为会显示：主体，已同步，镜机库为：镜像，已同步，正在还原...
2. 如果镜像创建或同步失败，可通过企业管理器通过配置界面重新配置（数据库->右键 -> 任务 -> 镜像 -> 配置安全性，可参考此文：http://liulike.blog.51cto.com/1355103/339183）。