sqlserver 镜像同步

防火墙打开5022进出.
完整备份A服务器上的Test库.

主机执行完整备份:

USEmaster;
ALTERDATABASETestSETRECOVERYFULL;
GO
BACKUPDATABASETestTODISK='D:\SQLServerBackups\Test.bak'WITHFORMAT;
GO
BACKUPLOGTestTODISK='D:\SQLServerBackups\Test.bak';
GO

--将备份文件拷贝到B上.
一定要执行完整备份.
在B服务器上完整还原数据库:

RESTOREDATABASETestFROMDISK='D:\Back\Test.bak'WITHNORECOVERY
GO
RESTORELOGTestFROMDISK='D:\Back\Test_log.bak'WITHFILE=1,NORECOVERY
GO
在Host_A上执行
USEmaster;
GO
//在master数据库中,创建数据库主密钥(如果需要).
CREATEMASTERKEYENCRYPTIONBYPASSWORD='password';
GO
CREATECERTIFICATEHOST_A_cert
WITHSUBJECT='HOST_Acertificate';
GO
\\\\\\Object:Endpoint[镜像]ScriptDate:09/01/200917:44:54\\\\\\
IFEXISTS(SELECT*FROMsys.endpointseWHEREe.name=N'镜像')
DROPENDPOINT[镜像]
GO
\\\\\\Object:Endpoint[镜像]ScriptDate:09/01/200917:44:54\\\\\\
CREATEENDPOINT[镜像]
AUTHORIZATION[sa]
STATE=STARTED
ASTCP(LISTENER_PORT=5022,LISTENER_IP=ALL)
FORDATA_MIRRORING(ROLE=PARTNER,AUTHENTICATION=CERTIFICATEHOST_A_cert
,ENCRYPTION=REQUIREDALGORITHMRC4)
GO
BACKUPCERTIFICATEHOST_B_certTOFILE='D:\Data\HOST_A_cert.cer';

步骤二

在Host_B上执行:

//在master数据库中,创建数据库主密钥(如果需要).
CREATEMASTERKEYENCRYPTIONBYPASSWORD='password';
GO
CREATECERTIFICATEHOST_B_cert
WITHSUBJECT='HOST_Bcertificate';
GO
\\\\\\Object:Endpoint[镜像]ScriptDate:09/01/200917:44:54\\\\\\
IFEXISTS(SELECT*FROMsys.endpointseWHEREe.name=N'镜像')
DROPENDPOINT[镜像]
GO
\\\\\\Object:Endpoint[镜像]ScriptDate:09/01/200917:44:54\\\\\\
CREATEENDPOINT[镜像]
AUTHORIZATION[sa]
STATE=STARTED
ASTCP(LISTENER_PORT=5022,LISTENER_IP=ALL)
FORDATA_MIRRORING(ROLE=PARTNER,AUTHENTICATION=CERTIFICATEHOST_B_cert
,ENCRYPTION=REQUIREDALGORITHMRC4)
GO
BACKUPCERTIFICATEHOST_B_certTOFILE='D:\Data\HOST_B_cert.cer';
GO

步骤三

复制HOST_A_cert.cer到Host_B,复制复制HOST_B_cert.cer到Host_A.

步骤四

在Host_A上执行:

USEmaster;
CREATELOGINHOST_B_loginWITHPASSWORD='password';
GO
CREATEUSERHOST_B_userFORLOGINHOST_B_login;
GO
CREATECERTIFICATEHOST_B_cert
AUTHORIZATIONHOST_B_user
FROMFILE='D:\backup\HOST_B_cert.cer'
GO
GRANTCONNECTONENDPOINT::[镜像]TO[HOST_B_login];

步骤五

在Host_B上执行:

USEmaster;
CREATELOGINHOST_A_loginWITHPASSWORD='password';
GO
CREATEUSERHOST_A_userFORLOGINHOST_A_login;
GO
CREATECERTIFICATEHOST_A_cert
AUTHORIZATIONHOST_A_user
FROMFILE='D:\backup\HOST_A_cert.cer'
GO
GRANTCONNECTONENDPOINT::[镜像]TO[HOST_A_login]

先在镜像服务器上执行:

ALTERDATABASETestSETPARTNER='TCP://192.168.10.2:5022';

成功之后再在主机上执行:

ALTERDATABASETestSETPARTNER='TCP://192.168.10.2:5022';

这样两台服务器的镜像就同步了.
删除镜像:

ALTERDATABASETestSETPARTNEROFF

如果主机出现问题,在主机执行:

USEMASTERGoALTERDATABASETestSETPARTNERFAILOVERGo

总结:

如果在建立镜像的时候中间的那个步骤出问题,需要重新执行的时候一定要把该删得东西删除掉.

--查询镜像select*fromsys.endpoints
--删除端口dropendpointEndpoint_As
--查询证书select*fromsys.symmetric_keys
--删除证书,先删除证书再删除主键DROPCERTIFICATEAs_A_cert
--删除主键DROPMASTERKEY
--删除镜像alterdatabase<dbname>setpartneroff
--删除登录名droplogin<login_name></login_name></dbname>