SQL 2008 配置镜像执行SQL

超、凢脫俗 2021-12-19 05:41 180阅读 0赞

主机执行:

USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ics2012%Qaz. ';  
CREATE CERTIFICATE HOST\_A\_cert WITH SUBJECT = ' HOST\_A certificate ' ,  
EXPIRY\_DATE = ' 01/01/2280 ';  
  
  
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 = ' E:\\SQLBackup\\HOST\_A\_cert.cer ';  
  
CREATE LOGIN HOST\_B\_login WITH PASSWORD = ' ics2012%Qaz.';  
CREATE USER HOST\_B\_user FOR LOGIN HOST\_B\_login;  
CREATE CERTIFICATE HOST\_B\_cert AUTHORIZATION HOST\_B\_user FROM FILE = ' E:\\SQLBackup\\HOST\_B\_cert.cer ';  
GRANT CONNECT ON ENDPOINT::Endpoint\_Mirroring TO \[ HOST\_B\_login \];  
  
  
ALTER DATABASE ICSDB SET PARTNER = ' TCP://192.168.5.14:5022 ';

\--DROP ENDPOINT Endpoint\_Mirroring  
\--DROP CERTIFICATE HOST\_A\_cert  
\--DROP CERTIFICATE HOST\_B\_cert  
\--DROP USER HOST\_A\_user  
\--DROP MASTER KEY  
\--SELECT \* FROM sys.database\_principals

备机执行:

USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ics2012%Qaz.';  
CREATE CERTIFICATE HOST\_B\_cert WITH SUBJECT = ' HOST\_B certificate ',  
EXPIRY\_DATE = ' 01/01/2280 ';  
  
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:\\SQLBackup\\HOST\_B\_cert.cer ';  
  
CREATE LOGIN HOST\_A\_login WITH PASSWORD = ' ics2012%Qaz.';  
CREATE USER HOST\_A\_user FOR LOGIN HOST\_A\_login;  
CREATE CERTIFICATE HOST\_A\_cert AUTHORIZATION HOST\_A\_user FROM FILE = ' D:\\SQLBackup\\HOST\_A\_cert.cer ';  
GRANT CONNECT ON ENDPOINT::Endpoint\_Mirroring TO \[ HOST\_A\_login \];

如果主机做个镜像，必须取消镜像，同时执行以下命令：

\--DROP ENDPOINT Endpoint\_Mirroring  
\--DROP CERTIFICATE HOST\_A\_cert  
\--DROP CERTIFICATE HOST\_B\_cert  
\--DROP USER HOST\_A\_user  
\--DROP MASTER KEY

最后删除用户：HOST\_A\_user，HOST\_B\_user

如果提示无法配置成功，可能是5022端口被封，可以使用其他未用的端口，如：8080等。

转载于:https://www.cnblogs.com/zping/archive/2012/01/11/2319610.html