这时,你就必须改用证书做端点的身份验证才能将运行起数据库镜像。
现在假设环境:
Host_A为主服务器,Host_B为镜像服务器。
已经按要求进行将Host_A的目标数据库备份,并在Host_B中还原。
通过UI界面已经配置好镜像,端点的端口两边都是5022,名称都为[镜像]。
防火墙的相关端口已经打开,SQL Server的TCP/IP协议已经启用。
步骤一
在Host_A上执行
USE
master;
GO
// 在 master 数据库中,创建数据库主密钥(如果需要)。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' password ' ;
GO
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = ' HOST_A certificate ' ;
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
IF EXISTS ( SELECT * FROM sys.endpoints e WHERE e.name = N ' 镜像 ' )
DROP ENDPOINT [ 镜像 ]
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
CREATE ENDPOINT [ 镜像 ]
AUTHORIZATION [ sa ]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL )
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = ' D:\Data\HOST_A_cert.cer ' ;
GO
// 在 master 数据库中,创建数据库主密钥(如果需要)。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' password ' ;
GO
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = ' HOST_A certificate ' ;
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
IF EXISTS ( SELECT * FROM sys.endpoints e WHERE e.name = N ' 镜像 ' )
DROP ENDPOINT [ 镜像 ]
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
CREATE ENDPOINT [ 镜像 ]
AUTHORIZATION [ sa ]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL )
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = ' D:\Data\HOST_A_cert.cer ' ;
步骤二
在Host_B上执行
//
在 master 数据库中,创建数据库主密钥(如果需要)。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' password ' ;
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = ' HOST_B certificate ' ;
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
IF EXISTS ( SELECT * FROM sys.endpoints e WHERE e.name = N ' 镜像 ' )
DROP ENDPOINT [ 镜像 ]
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
CREATE ENDPOINT [ 镜像 ]
AUTHORIZATION [ sa ]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL )
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = ' D:\Data\HOST_B_cert.cer ' ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' password ' ;
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = ' HOST_B certificate ' ;
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
IF EXISTS ( SELECT * FROM sys.endpoints e WHERE e.name = N ' 镜像 ' )
DROP ENDPOINT [ 镜像 ]
GO
/* ***** Object: Endpoint [镜像] Script Date: 09/01/2009 17:44:54 ***** */
CREATE ENDPOINT [ 镜像 ]
AUTHORIZATION [ sa ]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL )
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = ' D:\Data\HOST_B_cert.cer ' ;
GO
步骤三
复制HOST_A_cert.cer到Host_B,复制复制HOST_B_cert.cer到Host_A
步骤四
在Host_A上执行
USE
master;
CREATE LOGIN HOST_B_login WITH PASSWORD = ' password ' ;
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = ' D:\backup\HOST_B_cert.cer '
GO
GRANT CONNECT ON ENDPOINT:: [ 镜像 ] TO [ HOST_B_login ] ;
CREATE LOGIN HOST_B_login WITH PASSWORD = ' password ' ;
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = ' D:\backup\HOST_B_cert.cer '
GO
GRANT CONNECT ON ENDPOINT:: [ 镜像 ] TO [ HOST_B_login ] ;
步骤五
在Host_B上执行
USE
master;
CREATE LOGIN HOST_A_login WITH PASSWORD = ' password ' ;
GO
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = ' D:\backup\HOST_A_cert.cer '
GO
GRANT CONNECT ON ENDPOINT:: [ 镜像 ] TO [ HOST_A_login ] ;
启动镜像!
CREATE LOGIN HOST_A_login WITH PASSWORD = ' password ' ;
GO
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = ' D:\backup\HOST_A_cert.cer '
GO
GRANT CONNECT ON ENDPOINT:: [ 镜像 ] TO [ HOST_A_login ] ;