当前位置：Gxlcms > mysql > Sqlserver2008R2配置数据库镜像之我的经验总结

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

时间：2021-07-01 10:21:17 帮助过：30人阅读

一. 相关环境介结。 数据库：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. 备份还原数据库

  1. <span>--</span><span> 主机备份</span>
  2. <span>USE</span><span> master
  3. </span><span>GO</span>
  4. <span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>TestSync</span><span>]</span> <span>TO</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\SqlserverMirrorCer\TestSync.bak</span><span>'</span>
  5. <span>WITH</span> FORMAT, INIT, NAME <span>=</span> N<span>'</span><span>TestSync-Full Database Backup</span><span>'</span>, SKIP, NOREWIND, NOUNLOAD, STATS <span>=</span> <span>10</span><span>;
  6. </span><span>GO</span>
  7. <span>BACKUP</span> <span>LOG</span> <span>[</span><span>TestSync</span><span>]</span> <span>TO</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\SqlserverMirrorCer\TestSync.bak</span><span>'</span>
  8. <span>WITH</span> NOFORMAT, NOINIT, NAME <span>=</span> N<span>'</span><span>TestSync-Transaction Log Backup</span><span>'</span>, SKIP, NOREWIND, NOUNLOAD, STATS <span>=</span> <span>10</span><span>;
  9. </span><span>GO</span> 
  10. <span>--</span><span> 镜像恢复</span>
  11. <span>USE</span><span> master
  12. </span><span>GO</span>
  13. <span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>TestSync</span><span>]</span> <span>FROM</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\SqlserverMirrorCer\TestSync.bak</span><span>'</span>
  14. <span>WITH</span> <span>FILE</span> <span>=</span> <span>1</span><span>,
  15. NORECOVERY, NOUNLOAD, </span><span>REPLACE</span>, STATS <span>=</span> <span>10</span>
  16. <span>GO</span>
  17. <span>RESTORE</span> <span>LOG</span> <span>[</span><span>TestSync</span><span>]</span> <span>FROM</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\SqlserverMirrorCer\TestSync.bak</span><span>'</span>
  18. <span>WITH</span> <span>FILE</span> <span>=</span> <span>2</span>, NORECOVERY, NOUNLOAD, STATS <span>=</span> <span>10</span>
  19. <span>GO</span>

View Code

2. 创建证书

  1. <span>--</span><span>----------------------------------------------------------------------</span><span>
  2. --</span><span>============================ 主机上执行 ============================--</span><span>
  3. --</span><span>----------------------------------------------------------------------</span>
  4. <span>USE</span><span> master
  5. </span><span>GO</span>
  6. <span>--</span><span>创建证书，并备份</span>
  7. <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.databases <span>WHERE</span> name<span>=</span><span>'</span><span>master</span><span>'</span> <span>and</span> is_master_key_encrypted_by_server<span>=</span><span>1</span><span>)    
  8.     </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  9. </span><span>ELSE</span>
  10.     <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  11. </span><span>GO</span>
  12. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.certificates <span>WHERE</span> name<span>=</span><span>'</span><span>Cert_Host</span><span>'</span><span>)
  13.     </span><span>DROP</span><span> CERTIFICATE Cert_Host;
  14. </span><span>GO</span>
  15. <span>CREATE</span><span> CERTIFICATE Cert_Host
  16. </span><span>WITH</span> SUBJECT<span>=</span>N<span>'</span><span>Cert_Host Certificate</span><span>'</span>,START_DATE<span>=</span><span>'</span><span>20120405</span><span>'</span>,EXPIRY_DATE<span>=</span><span>'</span><span>20990405</span><span>'</span><span>;
  17. </span><span>BACKUP</span> CERTIFICATE Cert_Host <span>TO</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Host.cer</span><span>'</span><span>;
  18. </span><span>GO</span>
  19. <span>--</span><span>创建镜像端口</span>
  20. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.database_mirroring_endpoints <span>WHERE</span> name<span>=</span><span>'</span><span>Endpoint_Host</span><span>'</span><span>)
  21.     </span><span>DROP</span><span> ENDPOINT Endpoint_Host
  22. </span><span>GO</span>
  23. <span>CREATE</span><span> ENDPOINT Endpoint_Host
  24. STATE </span><span>=</span><span> STARTED
  25. </span><span>AS</span><span> TCP
  26. (
  27.     LISTENER_PORT</span><span>=</span><span>5022</span><span>,
  28.     LISTENER_IP</span><span>=ALL</span><span>
  29. )
  30. </span><span>FOR</span><span> DATABASE_MIRRORING
  31. (
  32.     AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Host,
  33.     ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
  34.     ROLE</span><span>=</span><span>PARTNER
  35. )
  36. </span><span>GO</span>
  37. <span>--</span><span>----------------------------------------------------------------------</span><span>
  38. --</span><span>============================ 镜像机上执行 ============================--</span><span>
  39. --</span><span>----------------------------------------------------------------------</span>
  40. <span>USE</span><span> master
  41. </span><span>GO</span>
  42. <span>--</span><span>创建证书，并备份</span>
  43. <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.databases <span>WHERE</span> name<span>=</span><span>'</span><span>master</span><span>'</span> <span>and</span> is_master_key_encrypted_by_server<span>=</span><span>1</span><span>)    
  44.     </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  45. </span><span>ELSE</span>
  46.     <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  47. </span><span>GO</span>
  48. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.certificates <span>WHERE</span> name<span>=</span><span>'</span><span>Cert_Mirror</span><span>'</span><span>)
  49.     </span><span>DROP</span><span> CERTIFICATE Cert_Mirror;
  50. </span><span>GO</span>
  51. <span>CREATE</span><span> CERTIFICATE Cert_Mirror
  52. </span><span>WITH</span> SUBJECT<span>=</span>N<span>'</span><span>Cert_Mirror Certificate</span><span>'</span>,START_DATE<span>=</span><span>'</span><span>20120405</span><span>'</span>,EXPIRY_DATE<span>=</span><span>'</span><span>20990405</span><span>'</span><span>;
  53. </span><span>BACKUP</span> CERTIFICATE Cert_Mirror <span>TO</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Mirror.cer</span><span>'</span><span>;
  54. </span><span>GO</span>
  55. <span>--</span><span>创建镜像端口</span>
  56. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.database_mirroring_endpoints <span>WHERE</span> name<span>=</span><span>'</span><span>Endpoint_Mirror</span><span>'</span><span>)
  57.     </span><span>DROP</span><span> ENDPOINT Endpoint_Mirror
  58. </span><span>GO</span>
  59. <span>CREATE</span><span> ENDPOINT Endpoint_Mirror
  60. STATE </span><span>=</span><span> STARTED
  61. </span><span>AS</span><span> TCP
  62. (
  63.     LISTENER_PORT</span><span>=</span><span>5022</span><span>,
  64.     LISTENER_IP</span><span>=ALL</span><span>
  65. )
  66. </span><span>FOR</span><span> DATABASE_MIRRORING
  67. (
  68.     AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Mirror,
  69.     ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
  70.     ROLE</span><span>=</span><span>PARTNER
  71. )
  72. </span><span>GO</span>
  73. <span>--</span><span>----------------------------------------------------------------------</span><span>
  74. --</span><span>============================ 见证机上执行 ============================--</span><span>
  75. --</span><span>----------------------------------------------------------------------</span>
  76. <span>USE</span><span> master
  77. </span><span>GO</span>
  78. <span>--</span><span>创建证书，并备份</span>
  79. <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.databases <span>WHERE</span> name<span>=</span><span>'</span><span>master</span><span>'</span> <span>and</span> is_master_key_encrypted_by_server<span>=</span><span>1</span><span>)    
  80.     </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  81. </span><span>ELSE</span>
  82.     <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  83. </span><span>GO</span>
  84. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.certificates <span>WHERE</span> name<span>=</span><span>'</span><span>Cert_Witness</span><span>'</span><span>)
  85.     </span><span>DROP</span><span> CERTIFICATE Cert_Witness;
  86. </span><span>GO</span>
  87. <span>CREATE</span><span> CERTIFICATE Cert_Witness
  88.     </span><span>WITH</span> SUBJECT<span>=</span>N<span>'</span><span>Cert_Witness Certificate</span><span>'</span>,START_DATE<span>=</span><span>'</span><span>20120405</span><span>'</span>,EXPIRY_DATE<span>=</span><span>'</span><span>20990405</span><span>'</span><span>;
  89. </span><span>BACKUP</span> CERTIFICATE Cert_Witness <span>TO</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Witness.cer</span><span>'</span><span>;
  90. </span><span>GO</span>
  91. <span>--</span><span>创建镜像端口</span>
  92. <span>IF</span> <span>EXISTS</span>(<span>select</span> <span>*</span> <span>from</span> sys.database_mirroring_endpoints <span>WHERE</span> name<span>=</span><span>'</span><span>Endpoint_Witness</span><span>'</span><span>)
  93.     </span><span>DROP</span><span> ENDPOINT Endpoint_Witness
  94. </span><span>GO</span>
  95. <span>CREATE</span><span> ENDPOINT Endpoint_Witness
  96. STATE </span><span>=</span><span> STARTED
  97. </span><span>AS</span><span> TCP
  98. (
  99.     LISTENER_PORT</span><span>=</span><span>5022</span><span>,
  100.     LISTENER_IP</span><span>=ALL</span><span>
  101. )
  102. </span><span>FOR</span><span> DATABASE_MIRRORING
  103. (
  104.     AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Witness,
  105.     ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
  106.     ROLE</span><span>=</span><span>WITNESS
  107. )
  108. </span><span>GO</span>

View Code

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

  1. <span>--</span><span> 把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书。</span>
  2. <span>--</span><span>----------------------------------------------------------------------</span><span>
  3. --</span><span>============================ 主机上执行 ============================--</span><span>
  4. --</span><span>----------------------------------------------------------------------</span>
  5. <span>USE</span><span> master
  6. </span><span>GO</span>
  7. <span>--</span><span>为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限</span>
  8. <span>CREATE</span> LOGIN Login_For_Mirror <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  9. </span><span>CREATE</span> <span>USER</span> User_For_Mirror <span>FOR</span><span> LOGIN Login_For_Mirror;
  10. </span><span>CREATE</span> CERTIFICATE Cert_For_Mirror <span>AUTHORIZATION</span> User_For_Mirror <span>FROM</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Mirror.cer</span><span>'</span><span>;
  11. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Host <span>TO</span><span> Login_For_Mirror;
  12. </span><span>GO</span>
  13. <span>--</span><span>为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限</span>
  14. <span>CREATE</span> LOGIN Login_For_Witness <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  15. </span><span>CREATE</span> <span>USER</span> User_For_Witness <span>FOR</span><span> LOGIN Login_For_Witness;
  16. </span><span>CREATE</span> CERTIFICATE Cert_For_Witness <span>AUTHORIZATION</span> User_For_Witness <span>FROM</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Witness.cer</span><span>'</span><span>;
  17. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Host <span>TO</span><span> Login_For_Witness;
  18. </span><span>GO</span>
  19. <span>--</span><span>----------------------------------------------------------------------</span><span>
  20. --</span><span>============================ 镜像机上执行 ============================--</span><span>
  21. --</span><span>----------------------------------------------------------------------</span>
  22. <span>USE</span><span> master
  23. </span><span>GO</span>
  24. <span>--</span><span>为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限</span>
  25. <span>CREATE</span> LOGIN Login_For_Host <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  26. </span><span>CREATE</span> <span>USER</span> User_For_Host <span>FOR</span><span> LOGIN Login_For_Host;
  27. </span><span>CREATE</span> CERTIFICATE Cert_For_Host <span>AUTHORIZATION</span> User_For_Host <span>FROM</span> <span>FILE</span> <span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Host.cer</span><span>'</span><span>;
  28. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Mirror <span>TO</span><span> Login_For_Host;
  29. </span><span>GO</span>
  30. <span>--</span><span>为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限</span>
  31. <span>CREATE</span> LOGIN Login_For_Witness <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  32. </span><span>CREATE</span> <span>USER</span> User_For_Witness <span>FOR</span><span> LOGIN Login_For_Witness;
  33. </span><span>CREATE</span> CERTIFICATE Cert_For_Witness <span>AUTHORIZATION</span> User_For_Witness <span>FROM</span> <span>FILE</span> <span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Witness.cer</span><span>'</span><span>;
  34. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Mirror <span>TO</span><span> Login_For_Witness;
  35. </span><span>GO</span>
  36. <span>--</span><span>----------------------------------------------------------------------</span><span>
  37. --</span><span>============================ 见证机上执行 ============================--</span><span>
  38. --</span><span>----------------------------------------------------------------------</span>
  39. <span>USE</span><span> master
  40. </span><span>GO</span>
  41. <span>--</span><span>为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限</span>
  42. <span>CREATE</span> LOGIN Login_For_Host <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  43. </span><span>CREATE</span> <span>USER</span> User_For_Host <span>FOR</span><span> LOGIN Login_For_Host;
  44. </span><span>CREATE</span> CERTIFICATE Cert_For_Host <span>AUTHORIZATION</span> User_For_Host <span>FROM</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Host.cer</span><span>'</span><span>;
  45. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Witness <span>TO</span><span> Login_For_Host;
  46. </span><span>GO</span>
  47. <span>--</span><span>为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限</span>
  48. <span>CREATE</span> LOGIN Login_For_Mirror <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
  49. </span><span>CREATE</span> <span>USER</span> User_For_Mirror <span>FOR</span><span> LOGIN Login_For_Mirror;
  50. </span><span>CREATE</span> CERTIFICATE Cert_For_Mirror <span>AUTHORIZATION</span> User_For_Mirror <span>FROM</span> <span>FILE</span><span>=</span>N<span>'</span><span>D:\SqlserverMirrorCer\Cert_Mirror.cer</span><span>'</span><span>;
  51. </span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Witness <span>TO</span><span> Login_For_Mirror;
  52. </span><span>GO</span>

View Code

4.最后一步开始镜像。

  1. <span>--</span><span> 镜像机上执行：</span><span>
  2. --</span><span> 建立 主机 合作</span>
  3. <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>TestSync</span><span>]</span> <span>SET</span> PARTNER <span>=</span>N<span>'</span><span>TCP://SqlHost.jike.cn:5022</span><span>'</span><span>; 
  4. </span><span>--</span><span> 主机上执行：</span><span>
  5. --</span><span> 建立 镜像机 合作</span>
  6. <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>TestSync</span><span>]</span> <span>SET</span> PARTNER<span>=</span>N<span>'</span><span>TCP://SqlMirror.jike.cn:5022</span><span>'</span><span>;
  7. </span><span>--</span><span> 建立 见证机 合作</span>
  8. <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>TestSync</span><span>]</span> <span>SET</span> WITNESS<span>=</span>N<span>'</span><span>TCP://SqlWitness.jike.cn:5022</span><span>'</span>;

View Code

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

五。其它问题或说明。

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

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

http://www.cnblogs.com/Joe-T/archive/2012/04/06/2434350.html
http://liulike.blog.51cto.com/1355103/339183

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！