SQL 2008 配置镜像执行SQL

最新推荐文章于 2025-09-07 20:08:22 发布

weixin_34357928

最新推荐文章于 2025-09-07 20:08:22 发布

阅读量53

点赞数

CC 4.0 BY-SA版权

文章标签：网络数据库

原文链接：http://www.cnblogs.com/zping/archive/2012/01/11/2319610.html

本文详细介绍了如何在SQL数据库中实现主机与备机之间的镜像配置与管理，包括密钥、证书、端口设置及角色分配等关键步骤。同时，提供了遇到问题时的排查建议，以及端口配置调整策略。

主机执行:

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
--SELECT * FROM sys.database_principals

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

--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