sqlserver 镜像同步

发表于 更新于 分类于

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

1
2
3
4
5
6
7
USEmaster;
ALTERDATABASETestSETRECOVERYFULL;
GO
BACKUPDATABASETestTODISK='D:\SQLServerBackups\Test.bak'WITHFORMAT;
GO
BACKUPLOGTestTODISK='D:\SQLServerBackups\Test.bak';
GO

一定要执行完整备份. 在B服务器上完整还原数据库:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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上执行:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//在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上执行:

1
2
3
4
5
6
7
8
9
10
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上执行:

1
2
3
4
5
6
7
8
9
10
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]

先在镜像服务器上执行:

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

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

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

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

1
ALTERDATABASETestSETPARTNEROFF

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

1
USEMASTERGoALTERDATABASETestSETPARTNERFAILOVERGo

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

1
2
3
4
5
6
7
--查询镜像select*fromsys.endpoints
--删除端口dropendpointEndpoint_As
--查询证书select*fromsys.symmetric_keys
--删除证书,先删除证书再删除主键DROPCERTIFICATEAs_A_cert
--删除主键DROPMASTERKEY
--删除镜像alterdatabasesetpartneroff
--删除登录名droplogin