SQL 2005镜像操作--证书认证

在没有域的工作组环境中必须配置证书才能够镜像成功,首先在三台服务器上配置出站连接，并把三个证

书互相复制到每台服务器上，然后分别配置入站连接

一、主体服务器
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO
USE master;
CREATE CERTIFICATE A_cert
   WITH SUBJECT = 'A certificate for database mirroring',
      start_date = '11/01/2007',
      EXPIRY_DATE = '10/31/2099' ;
GO

 

USE master;
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE A_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
BACKUP CERTIFICATE A_cert TO FILE = 'e:\key\A_cert.cer';
GO

 

--copy A_CERT.cer to other servers

 

--================以上据配置完出站========================

 

--================以下配置入站连接========================

 

-- 1 ==配置镜像服务器入站

 

USE master;
CREATE LOGIN B_login WITH PASSWORD = 'your password';
GO

 

USE master;
CREATE USER B_user FOR LOGIN B_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE B_cert
   AUTHORIZATION B_user
   FROM FILE = 'e:\key\B_cert.cer'
GO

 

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [B_login];
GO

 

-- 2 ===配置见证服务器入站
USE master;
CREATE LOGIN C_login WITH PASSWORD = 'your password';
GO

 

USE master;
CREATE USER C_user FOR LOGIN C_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE C_cert
   AUTHORIZATION C_user
   FROM FILE = 'e:\key\C_cert.cer'
GO

 

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [C_login];
GO

二、镜像服务器
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO

CREATE CERTIFICATE B_cert
   WITH SUBJECT = B certificate for database mirroring',
       start_date = '01/01/2005',
      EXPIRY_DATE = '10/31/2099' ;
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE B_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
--Backup HOST_B certificate.
BACKUP CERTIFICATE B_cert TO FILE = 'e:\key\B_cert.cer';
GO

 

--======================出站配置完毕====

 

--======================配置入站========

 

-- 1 ==配置主体服务器入站
USE master;

CREATE LOGIN A_login WITH PASSWORD = 'your password';
GO

CREATE USER A_user FOR LOGIN A_login
GO
--Obtain HOST_A certificate. (See the note
--   preceding this example.)
--Asscociate this certificate with the user, HOST_A_user.
CREATE CERTIFICATE A_cert
   AUTHORIZATION A_user
   FROM FILE = 'e:\key\A_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO A_login
GO

 

-- 2 ===配置见证服务器入站
USE master;
CREATE LOGIN C_login WITH PASSWORD = 'your password';
GO

 

USE master;
CREATE USER C_user FOR LOGIN C_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE C_cert
   AUTHORIZATION C_user
   FROM FILE = 'e:\key\C_cert.cer'
GO

 

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [C_login];
GO

 

三、见证服务器

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO
USE master;
CREATE CERTIFICATE C_cert
   WITH SUBJECT = 'C certificate for database mirroring',
      start_date = '01/01/2005',
      EXPIRY_DATE = '10/31/2099' ;
GO

 

USE master;
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5025
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE C_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
BACKUP CERTIFICATE C_cert TO FILE = 'e:\key\C_cert.cer';
GO

 

--copy C_CERT.cer to 其他两台服务器

 

--================以上据配置完出站========================

 

--================以下配置入站连接========================

 

-- 1 ==配置主体服务器入站

 

USE master;
CREATE LOGIN A_login WITH PASSWORD = 'your password';
GO

 

USE master;
CREATE USER A_user FOR LOGIN A_login;
GO

USE master;
CREATE CERTIFICATE A_cert
   AUTHORIZATION A_user
   FROM FILE = 'e:\key\A_cert.cer'
GO

 

USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [A_login];
GO

 

-- 2 ===配置镜像服务器入站
USE master;
CREATE LOGIN B_login WITH PASSWORD = 'your password';
GO

 

USE master;
CREATE USER B_user FOR LOGIN B_login;
GO

USE master;
CREATE CERTIFICATE B_cert
   AUTHORIZATION B_user
   FROM FILE = 'e:\key\B_cert.cer'
GO

 

USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [B_login];
GO

最后我使用的是向导启用的镜像功能，注意的是用向导在最后不必填写用户帐号。

注意:

故障:
    服务器网络地址 "TCP://test-server:5022" 无法访问或不存在。请检查网络地址名称，并检查本地

和远程端点的端口是否正常运行。

解决办法:
    如果是这种情况，请检查一下每台SQL2k5引擎的远程TCP连接是否已经被启用，具体通过外围应用配

置工具-->服务和连接的外围应用配置器 --> Database Engine --> 远程连接，在这里启用"同时使用

TCP/IP和named pipes"，启用SQLBrowser服务，重新启动SQL Server，然后就应该可以了。