SQL 通过镜像数据库实现 故障自动切换
--1.备份主数据库 backup database test to disk ='D:\test.bak'; --2.还原备份,生成镜像数据库(镜像服务器) Restore database test from disk='D:\test.bak' with NoRecovery,--保持数据一直处于恢复状态 move 'Test' to 'd:\test.mdf', move 'Test_log' to 'd:\test_log.ldf'; --3.1创建日志传端口 create endpoint Mirroring state=Started as TCP(Listener_port=5022) for Database_Mirroring(Role=Partner) --3.2创建日志传端口(镜像服务器) create endpoint Mirroring state=Started as TCP(Listener_port=5022) for Database_Mirroring(Role=Partner) --4.创建监听端口(监视服务器) create EndPoint Mirroring state = Started as Tcp(Listener_Port=5024) for Database_Mirroring(Role=Witness) --5.1打开连接 alter database test set Partner = 'tcp://DemoServer:5022'; --5.1打开连接(镜像服务器) alter database test set Partner = 'tcp://MainServer:5022'; --6.设置主服务与镜像服务器数据同步 alter database Test set Partner Safety Full --7.指定监视服务器连接 alter database Test set Witness='Tcp://ListenerServer:5024'; --8.手动切换,实现故障自动恢复 alter database Test set partner Failover; --9.同步主数据库建的登陆名到镜像服务器 --客户端连接字符串配置failover partner=partnerServer,指定镜像服务器 server=.;failover partner=partnerServer; user=sa;pwd=sa;database=test;