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

SQLServer 数据库镜像+复制切换方案目标：主机做了Mirror和Replication，当主机出现问题时，Replication和Mirror实现自动的故障转移（Mirror 和Replication都切换到备机，而当主机重新启动后，自动充当备机的角色）。环境：五台虚拟机，配置均为Windows2008 Enterprise + SQLServer2008R2 Enterprise08R201：Mirror 见证机(WITNESS) IP:192.168.56.10108R202：主机（Rep+Mirror） IP:192.168.56.10208R203：Rep分发机 IP:192.168.56.10308R204：Rep订阅机 IP:192.168.56.10408R205：镜像机（Mirror） IP:192.168.56.105步骤：配置有见证服务器的镜像创建证书和Endpoint08R202(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;交换证书将HOST_B_cert.cer和HOST_W_cert.cer拷贝到08R202 机器的”D:\Cert