关于SQL Server的镜像

邴俊友
2023-12-01

这两天做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)。




 类似资料: