在没有域的工作组环境中必须配置证书才能够镜像成功,首先在三台服务器上配置出站连接,并把三个证
书互相复制到每台服务器上,然后分别配置入站连接

一、主体服务器
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,然后就应该可以了。
posted on 2009-04-18 13:25  yxbsmx  阅读(222)  评论(0编辑  收藏  举报