好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

Sqlserver2008R2配置数据库镜像之我的经验总结

一. 相关环境介结。 数据库：Sqlserver2008R2 网络环境：主机、镜像机(阿里云，青岛节点同域)，见证机(本公司自己托管在上海) 二. 服务器相关配置。 1. 分别开启三台服务器5022的入站端口。 2. 目标数据库的恢复模式必须为完整，具体操作：企业管理器-数据库

一. 相关环境介结。

数据库：Sqlserver2008R2
网络环境：主机、镜像机(阿里云，青岛节点同域)，见证机(本公司自己托管在上海)

二. 服务器相关配置。

1. 分别开启三台服务器5022的入站端口。
2. 目标数据库的恢复模式必须为[完整]，具体操作：企业管理器->数据库右键->属性->选项->恢复模式选择[完整]->确定。
3. 远程连接设置( 非必要 )，具体操作：数据库服务实例->右键->方面->外围应用配置器->RemoteDACEnabled->True->确定。

三.配置服务器域。

1. 给每台服务器(主机、镜像机、见证机)加上FQDN，即设置同样的DNS后缀名。（方法：计算机右键->属性->更改设置->更改->其他->DNS后缀）,如：jike.cn，设置后需要重启服务器。

2. 修改每台机的HOST文件，将计算机名和IP绑定，如：
　 115.10.1.1 SqlHost.jike.cn
115.10.1.2 SqlMirror.jike.cn
210.14.79.98 SqlWitness.jike.cn
提示：SqlHost、SqlMirror和SqlWitness分别为三台服务器的机器名，绝对不能随便起个名。

四. 脚本证书方式配置。

1. 备份还原数据库

 --   主机备份 
 USE   master
  GO 

 BACKUP   DATABASE   [  TestSync  ]   TO   DISK   =  N '  D:\SqlserverMirrorCer\TestSync.bak  ' 
 WITH  FORMAT, INIT, NAME  =  N '  TestSync-Full Database Backup  ' , SKIP, NOREWIND, NOUNLOAD, STATS  =   10  ;
  GO 


 BACKUP   LOG   [  TestSync  ]   TO   DISK   =  N '  D:\SqlserverMirrorCer\TestSync.bak  ' 
 WITH  NOFORMAT, NOINIT, NAME  =  N '  TestSync-Transaction Log Backup  ' , SKIP, NOREWIND, NOUNLOAD, STATS  =   10  ;
  GO  





 --   镜像恢复 
 USE   master
  GO 
 RESTORE   DATABASE   [  TestSync  ]   FROM   DISK   =  N '  D:\SqlserverMirrorCer\TestSync.bak  ' 
 WITH   FILE   =   1  ,
NORECOVERY, NOUNLOAD,   REPLACE , STATS  =   10 
 GO 

 RESTORE   LOG   [  TestSync  ]   FROM   DISK   =  N '  D:\SqlserverMirrorCer\TestSync.bak  ' 
 WITH   FILE   =   2 , NORECOVERY, NOUNLOAD, STATS  =   10 
 GO

View Code

2. 创建证书

 --  ----------------------------------------------------------------------  
--  ============================ 主机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  创建证书，并备份 
 IF   EXISTS ( SELECT   *   FROM  sys.databases  WHERE  name =  '  master  '   and  is_master_key_encrypted_by_server =  1  )    
      OPEN  MASTER  KEY  DECRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  ELSE 
     CREATE  MASTER  KEY  ENCRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  GO 

 IF   EXISTS ( select   *   from  sys.certificates  WHERE  name =  '  Cert_Host  '  )
      DROP   CERTIFICATE Cert_Host;
  GO 
 CREATE   CERTIFICATE Cert_Host

  WITH  SUBJECT = N '  Cert_Host Certificate  ' ,START_DATE =  '  20120405  ' ,EXPIRY_DATE =  '  20990405  '  ;

  BACKUP  CERTIFICATE Cert_Host  TO   FILE  = N '  D:\SqlserverMirrorCer\Cert_Host.cer  '  ;
  GO 

 --  创建镜像端口 
 IF   EXISTS ( select   *   from  sys.database_mirroring_endpoints  WHERE  name =  '  Endpoint_Host  '  )
      DROP   ENDPOINT Endpoint_Host
  GO 
 CREATE   ENDPOINT Endpoint_Host
STATE   =   STARTED
  AS   TCP
(
    LISTENER_PORT  =  5022  ,
    LISTENER_IP  =ALL  
)

  FOR   DATABASE_MIRRORING
(
    AUTHENTICATION  =  CERTIFICATE Cert_Host,
    ENCRYPTION  =  REQUIRED ALGORITHM AES,
    ROLE  =  PARTNER
)

  GO 


 --  ----------------------------------------------------------------------  
--  ============================ 镜像机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  创建证书，并备份 
 IF   EXISTS ( SELECT   *   FROM  sys.databases  WHERE  name =  '  master  '   and  is_master_key_encrypted_by_server =  1  )    
      OPEN  MASTER  KEY  DECRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  ELSE 
     CREATE  MASTER  KEY  ENCRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  GO 

 IF   EXISTS ( select   *   from  sys.certificates  WHERE  name =  '  Cert_Mirror  '  )
      DROP   CERTIFICATE Cert_Mirror;
  GO 
 CREATE   CERTIFICATE Cert_Mirror

  WITH  SUBJECT = N '  Cert_Mirror Certificate  ' ,START_DATE =  '  20120405  ' ,EXPIRY_DATE =  '  20990405  '  ;

  BACKUP  CERTIFICATE Cert_Mirror  TO   FILE  = N '  D:\SqlserverMirrorCer\Cert_Mirror.cer  '  ;
  GO 

 --  创建镜像端口 
 IF   EXISTS ( select   *   from  sys.database_mirroring_endpoints  WHERE  name =  '  Endpoint_Mirror  '  )
      DROP   ENDPOINT Endpoint_Mirror
  GO 
 CREATE   ENDPOINT Endpoint_Mirror
STATE   =   STARTED
  AS   TCP
(
    LISTENER_PORT  =  5022  ,
    LISTENER_IP  =ALL  
)

  FOR   DATABASE_MIRRORING
(
    AUTHENTICATION  =  CERTIFICATE Cert_Mirror,
    ENCRYPTION  =  REQUIRED ALGORITHM AES,
    ROLE  =  PARTNER
)

  GO 



 --  ----------------------------------------------------------------------  
--  ============================ 见证机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  创建证书，并备份 
 IF   EXISTS ( SELECT   *   FROM  sys.databases  WHERE  name =  '  master  '   and  is_master_key_encrypted_by_server =  1  )    
      OPEN  MASTER  KEY  DECRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  ELSE 
     CREATE  MASTER  KEY  ENCRYPTION  BY  PASSWORD =  '  PWD_DBMirror986252588  '  ;
  GO 

 IF   EXISTS ( select   *   from  sys.certificates  WHERE  name =  '  Cert_Witness  '  )
      DROP   CERTIFICATE Cert_Witness;
  GO 

 CREATE   CERTIFICATE Cert_Witness
      WITH  SUBJECT = N '  Cert_Witness Certificate  ' ,START_DATE =  '  20120405  ' ,EXPIRY_DATE =  '  20990405  '  ;

  BACKUP  CERTIFICATE Cert_Witness  TO   FILE  = N '  D:\SqlserverMirrorCer\Cert_Witness.cer  '  ;
  GO 

 --  创建镜像端口 
 IF   EXISTS ( select   *   from  sys.database_mirroring_endpoints  WHERE  name =  '  Endpoint_Witness  '  )
      DROP   ENDPOINT Endpoint_Witness
  GO 
 CREATE   ENDPOINT Endpoint_Witness
STATE   =   STARTED
  AS   TCP
(
    LISTENER_PORT  =  5022  ,
    LISTENER_IP  =ALL  
)

  FOR   DATABASE_MIRRORING
(
    AUTHENTICATION  =  CERTIFICATE Cert_Witness,
    ENCRYPTION  =  REQUIRED ALGORITHM AES,
    ROLE  =  WITNESS
)

  GO

View Code

3.创建登录用户(把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书)

 --   把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书。 

 --  ----------------------------------------------------------------------  
--  ============================ 主机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Mirror  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Mirror  FOR   LOGIN Login_For_Mirror;
  CREATE  CERTIFICATE Cert_For_Mirror  AUTHORIZATION  User_For_Mirror  FROM   FILE  = N '  D:\SqlserverMirrorCer\Cert_Mirror.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Host  TO   Login_For_Mirror;
  GO 

 --  为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Witness  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Witness  FOR   LOGIN Login_For_Witness;
  CREATE  CERTIFICATE Cert_For_Witness  AUTHORIZATION  User_For_Witness  FROM   FILE  = N '  D:\SqlserverMirrorCer\Cert_Witness.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Host  TO   Login_For_Witness;

  GO 

 --  ----------------------------------------------------------------------  
--  ============================ 镜像机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Host  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Host  FOR   LOGIN Login_For_Host;
  CREATE  CERTIFICATE Cert_For_Host  AUTHORIZATION  User_For_Host  FROM   FILE   = N '  D:\SqlserverMirrorCer\Cert_Host.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Mirror  TO   Login_For_Host;
  GO 

 --  为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Witness  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Witness  FOR   LOGIN Login_For_Witness;
  CREATE  CERTIFICATE Cert_For_Witness  AUTHORIZATION  User_For_Witness  FROM   FILE   = N '  D:\SqlserverMirrorCer\Cert_Witness.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Mirror  TO   Login_For_Witness;
  GO 


 --  ----------------------------------------------------------------------  
--  ============================ 见证机上执行 ============================--  
--  ---------------------------------------------------------------------- 
 USE   master
  GO 

 --  为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Host  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Host  FOR   LOGIN Login_For_Host;
  CREATE  CERTIFICATE Cert_For_Host  AUTHORIZATION  User_For_Host  FROM   FILE  = N '  D:\SqlserverMirrorCer\Cert_Host.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Witness  TO   Login_For_Host;
  GO 

 --  为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限 
 CREATE  LOGIN Login_For_Mirror  WITH  PASSWORD = N '  PWD_DBMirror986252588  '  ;
  CREATE   USER  User_For_Mirror  FOR   LOGIN Login_For_Mirror;
  CREATE  CERTIFICATE Cert_For_Mirror  AUTHORIZATION  User_For_Mirror  FROM   FILE  = N '  D:\SqlserverMirrorCer\Cert_Mirror.cer  '  ;
  GRANT  CONNECT  ON  ENDPOINT::Endpoint_Witness  TO   Login_For_Mirror;
  GO

View Code

4.最后一步开始镜像。

 --   镜像机上执行：  
--   建立 主机 合作 
 ALTER   DATABASE   [  TestSync  ]   SET  PARTNER  = N '  TCP://SqlHost.jike.cn:5022  '  ; 

  --   主机上执行：  
--   建立 镜像机 合作 
 ALTER   DATABASE   [  TestSync  ]   SET  PARTNER = N '  TCP://SqlMirror.jike.cn:5022  '  ;
  --   建立 见证机 合作 
 ALTER   DATABASE   [  TestSync  ]   SET  WITNESS = N '  TCP://SqlWitness.jike.cn:5022  ' ;

View Code

1. 配置成功后，主体数据为会显示：主体，已同步，镜机库为：镜像，已同步，正在还原...
2. 如果镜像创建或同步失败，可通过企业管理器通过配置界面重新配置（数据库->右键 -> 任务 -> 镜像 -> 配置安全性，可参考此文：http://liulike.blog.51cto测试数据/1355103/339183）。

五。其它问题或说明。

1. 一台服务器只能有一个端点，即每台服务器只能承担主机、镜像、见证其中一个角色。
2. 配置域和Host很重要，否则问题很多，笔者在此担搁不少时间。
3. 见证服务器必须要做，否则不带自动故障转移的镜像没什么大用。

参考文章：
----------------------------------------------------------------------------------

http://HdhCmsTestcnblogs测试数据/Joe-T/archive/2012/04/06/2434350.html
http://liulike.blog.51cto测试数据/1355103/339183

查看更多关于Sqlserver2008R2配置数据库镜像之我的经验总结的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did97215
  阅读：60次

上一篇： 如何订阅SqlServer数据库操作相关响应事件，例如：打开连接、

下一篇：求个高效的sqlserver连oracle的方法