SQLServer 数据库镜像+复制切换方案

目标:

主机做了Mirror和Replication,当主机出现问题时,Replication和Mirror实现自动的故障转移(Mirror 和Replication都切换到备机,而当主机

重新启动后,自动充当备机的角色)。

环境:

五台虚拟机,配置均为Windows2008 Enterprise + SQLServer2008R2 Enterprise

08R201:Mirror 见证机(WITNESS) IP:192.168.56.101

08R202:主机(Rep+Mirror) IP:192.168.56.102

08R203:Rep分发机 IP:192.168.56.103

08R204:Rep订阅机 IP:192.168.56.104

08R205:镜像机(Mirror) IP:192.168.56.105

步骤:

配置有见证服务器的镜像

  1. 创建证书和Endpoint

08R202(Master) 上运行下面的脚本:

--主机执行: USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123456abc'; CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , START_DATE = '11/08/2010',EXPIRY_DATE = '10/31/2099'; --主机执行: CREATE ENDPOINT Endpoint_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 ); --主机执行: BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:HOST_A_cert.cer';

08R205(Mirror) 上运行下面的脚本:

--备机执行: USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123456abc'; CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE = '11/08/2010',EXPIRY_DATE = '10/31/2099'; --备机执行: CREATE ENDPOINT Endpoint_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 ); --备机执行: BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:HOST_B_cert.cer';

08R201(WITNESS) 上运行下面的脚本:

--见证机执行 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123456abc'; CREATE CERTIFICATE HOST_W_cert WITH SUBJECT = 'HOST_W certificate', START_DATE = '11/08/2010',EXPIRY_DATE = '10/31/2099'; --见证机执行 CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_W_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); --见证机执行 BACKUP CERTIFICATE HOST_W_cert TO FILE = 'D:HOST_W_cert.cer';
  1. 交换证书

将HOST_B_cert.cer和HOST_W_cert.cer拷贝到 08R202 机器的”D:Cert“目录;

将HOST_A_cert.cer和HOST_W_cert.cer拷贝到 08R205 机器的”D:Cert“目录;

将HOST_A_cert.cer和HOST_B_cert.cer拷贝到 08R201 机器的”D:Cert“目录;

08R202(Master) 上运行下面的脚本:

--Master CREATE LOGIN HOST_B_login WITH PASSWORD = '123abc!@#'; CREATEUSER HOST_B_user FOR LOGIN HOST_B_login; CREATE CERTIFICATE HOST_B_certAUTHORIZATION HOST_B_user FROM FILE = 'D:CertHOST_B_cert.cer'; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; CREATE LOGIN HOST_W_login WITH PASSWORD = '123abc!@#'; CREATE USER HOST_W_user FORLOGIN HOST_W_login; CREATE CERTIFICATE HOST_W_cert AUTHORIZATIONHOST_W_user FROM FILE = 'D:CertHOST_W_cert.cer'; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_W_login];

08R205(Mirror) 上运行下面的脚本:

--Mirror CREATE LOGIN HOST_A_login WITH PASSWORD = '123abc!@#'; CREATEUSER HOST_A_user FOR LOGIN HOST_A_login; CREATE CERTIFICATE HOST_A_certAUTHORIZATION HOST_A_user FROM FILE = 'D:CertHOST_A_cert.cer'; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; CREATE LOGIN HOST_W_login WITH PASSWORD = '123abc!@#'; CREATE USER HOST_W_user FORLOGIN HOST_W_login; CREATE CERTIFICATE HOST_W_cert AUTHORIZATIONHOST_W_user FROM FILE = 'D:CertHOST_W_cert.cer'; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_W_login];

08R201(WITNESS) 上运行下面的脚本:

--WITNESS CREATE LOGIN HOST_A_login WITH PASSWORD = '123abc!@#'; CREATEUSER HOST_A_user FOR LOGIN HOST_A_login; CREATE CERTIFICATE HOST_A_certAUTHORIZATION HOST_A_user FROM FILE = 'D:CertHOST_A_cert.cer'; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; CREATE LOGIN HOST_B_login WITH PASSWORD = '123abc!@#'; CREATE USER HOST_B_user FORLOGIN HOST_B_login; CREATE CERTIFICATE HOST_B_cert AUTHORIZATIONHOST_B_user FROM FILE = 'D:CertHOST_B_cert.cer'; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
  1. 备份还原数据库

08R202(Master) 上备份数据库:

BACKUP DATABASE RepTest TO DISK='D:tempRepTest.bak' BACKUP LOG RepTestTO DISK='D:tempRepTest.trn'

将备份文件拷贝到 08R205(Mirror) 上做还原(最好以SA帐号登录,使得数据库所有者为”SA“):

--RESTORE RESTORE DATABASE RepTest FROM DISK = N'D:RepTest.bak' WITHFILE = 1, MOVE N'RepTest' TO N'D:DataRepTest.mdf', MOVE N'RepTest_log'TO N'D:DataRepTest_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 GORESTORE LOG RepTest FROM DISK ='D:RepTest.trn' WITH NORECOVERY
  1. 建立镜像

在08R205(Mirror) 上执行:

--Mirror ALTER DATABASE RepTest SET PARTNER ='TCP://192.168.56.102:5022';

在 08R202(Master)上执行:

ALTER DATABASE RepTest SET PARTNER = 'TCP://192.168.56.105:5022'; ALTERDATABASE RepTest SET WITNESS = 'TCP://192.168.56.101:5022'; ALTERDATABASE RepTest SET SAFETY FULL

到此,镜像已经建立完成。

配置Replication(事务型复制)

Replicaiton不做具体的搭建过程,只做几点说明:

1. 08R202和08R205都需要搭建到08R203的Distribution;

2. 08R202和08R205上的发布库的所有者必须为“SA”,否则切换会出现如下错误:The process could not execute ‘sp_replcmds’ on ‘WIN-08R205′.

3. 08R203分发机上配置文件需要做如下修改(配置故障转移Partner):

--配置复制故障转移参数 --查看代理配置,在分发服务器运行 execsp_help_agent_profile --Agent_Type含义 --1 = Snapshot Agent; 2 = Log Reader Agent; 3 = Distribution Agent; --4 = Merge Agent; 9 = Queue Reader Agent. --对于事务复制,需查看Agent_Type=1,2的Profile_id --对于事务复制,需要配置快照代理(Snapshot Agent)和日志读取代理(Log Reader Agent) execsp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'WIN-08R205'--镜像服务器名称 exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'WIN-08R205'--镜像服务器名称

修改完成后,记得重启Agent服务,使之生效。

4. 从08R202创建到08R204的同步链。

模拟故障转移并观察结果

1. 暂停08R202的SQLServer服务;

2. 到08R205上观察镜像和同步链是否转移过去了;

3. 如果同步链正常,修改某个表中的数据,看是否能同步到08R204上;

4. 重新启动08R202的SQLServer服务,看其是否成为了镜像机;

5. 如果一起都正常,那恭喜啦,我们的测试成功。

原文发布于微信公众号 - 我为Net狂(dotNetCrazy)

原文发表时间:2016-11-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

ORA-00439的解决(笔记93天)

今天用gc配置了stream后,重启数据库后发现有以下的问题 SQL> startup ORA-00439: feature not enabled: Real...

34810
来自专栏大内老A

谈谈基于SQL Server 的Exception Handling[中篇]

三、TRY CATCH & Return 在上面一节中,我通过RAISERROR重写了创建User的Stored procedure,实际上上面的Stored ...

1886
来自专栏杨建荣的学习笔记

一个普通数据库用户所能查到的"意料之外"的信息(r2笔记98天)

有时候限于工作环境的情况,大多数开发人员只得到了一个权限收到限制的数据库用户。 可能你都不知道你所拥有的数据库用户都能查到哪些你想象不到的数据库信息,其实你知道...

3448
来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

2913
来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

932
来自专栏杨建荣的学习笔记

关于等待事件"read by other session"(r3笔记第89天)

在查看数据库负载的时候,发现早上10点开始到12点的这两个钟头,系统负载异常的高。于是抓取了一个awr报告。 Snap IdSnap TimeSessions...

2969
来自专栏杨建荣的学习笔记

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3415
来自专栏杨建荣的学习笔记

关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用cascade选项,有些时候却不需要,想知道在这个简单的命令之后数据库倒底在干什么, 这时候给一些指定的操作...

3908
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止80倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

2865
来自专栏拂晓风起

Log4Net ,.net和SQL Server的完美结合

1255

扫码关注云+社区

领取腾讯云代金券