首页 >数据库 >mysql教程 >Sqlserver2008R2配置数据库镜像之我的经验总结

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

WBOY原创: 2016-06-07 15:39:281107浏览

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

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

<span>--</span><span> 主机备份</span>
<span>USE</span><span> master
</span><span>GO</span>

<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>
<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>;
</span><span>GO</span>


<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>
<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>;
</span><span>GO</span> 





<span>--</span><span> 镜像恢复</span>
<span>USE</span><span> master
</span><span>GO</span>
<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>
<span>WITH</span> <span>FILE</span> <span>=</span> <span>1</span><span>,
NORECOVERY, NOUNLOAD, </span><span>REPLACE</span>, STATS <span>=</span> <span>10</span>
<span>GO</span>

<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>
<span>WITH</span> <span>FILE</span> <span>=</span> <span>2</span>, NORECOVERY, NOUNLOAD, STATS <span>=</span> <span>10</span>
<span>GO</span>

View Code

2. 创建证书

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

<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 主机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>创建证书，并备份</span>
<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>)    
    </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>ELSE</span>
    <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>GO</span>

<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>)
    </span><span>DROP</span><span> CERTIFICATE Cert_Host;
</span><span>GO</span>
<span>CREATE</span><span> CERTIFICATE Cert_Host

</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>;

</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>;
</span><span>GO</span>

<span>--</span><span>创建镜像端口</span>
<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>)
    </span><span>DROP</span><span> ENDPOINT Endpoint_Host
</span><span>GO</span>
<span>CREATE</span><span> ENDPOINT Endpoint_Host
STATE </span><span>=</span><span> STARTED
</span><span>AS</span><span> TCP
(
    LISTENER_PORT</span><span>=</span><span>5022</span><span>,
    LISTENER_IP</span><span>=ALL</span><span>
)

</span><span>FOR</span><span> DATABASE_MIRRORING
(
    AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Host,
    ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
    ROLE</span><span>=</span><span>PARTNER
)

</span><span>GO</span>


<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 镜像机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>创建证书，并备份</span>
<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>)    
    </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>ELSE</span>
    <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>GO</span>

<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>)
    </span><span>DROP</span><span> CERTIFICATE Cert_Mirror;
</span><span>GO</span>
<span>CREATE</span><span> CERTIFICATE Cert_Mirror

</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>;

</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>;
</span><span>GO</span>

<span>--</span><span>创建镜像端口</span>
<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>)
    </span><span>DROP</span><span> ENDPOINT Endpoint_Mirror
</span><span>GO</span>
<span>CREATE</span><span> ENDPOINT Endpoint_Mirror
STATE </span><span>=</span><span> STARTED
</span><span>AS</span><span> TCP
(
    LISTENER_PORT</span><span>=</span><span>5022</span><span>,
    LISTENER_IP</span><span>=ALL</span><span>
)

</span><span>FOR</span><span> DATABASE_MIRRORING
(
    AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Mirror,
    ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
    ROLE</span><span>=</span><span>PARTNER
)

</span><span>GO</span>



<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 见证机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>创建证书，并备份</span>
<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>)    
    </span><span>OPEN</span> MASTER <span>KEY</span> DECRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>ELSE</span>
    <span>CREATE</span> MASTER <span>KEY</span> ENCRYPTION <span>BY</span> PASSWORD<span>=</span><span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>GO</span>

<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>)
    </span><span>DROP</span><span> CERTIFICATE Cert_Witness;
</span><span>GO</span>

<span>CREATE</span><span> CERTIFICATE Cert_Witness
    </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>;

</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>;
</span><span>GO</span>

<span>--</span><span>创建镜像端口</span>
<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>)
    </span><span>DROP</span><span> ENDPOINT Endpoint_Witness
</span><span>GO</span>
<span>CREATE</span><span> ENDPOINT Endpoint_Witness
STATE </span><span>=</span><span> STARTED
</span><span>AS</span><span> TCP
(
    LISTENER_PORT</span><span>=</span><span>5022</span><span>,
    LISTENER_IP</span><span>=ALL</span><span>
)

</span><span>FOR</span><span> DATABASE_MIRRORING
(
    AUTHENTICATION</span><span>=</span><span>CERTIFICATE Cert_Witness,
    ENCRYPTION</span><span>=</span><span>REQUIRED ALGORITHM AES,
    ROLE</span><span>=</span><span>WITNESS
)

</span><span>GO</span>

View Code

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

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

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

<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 主机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Mirror <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Mirror <span>FOR</span><span> LOGIN Login_For_Mirror;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Host <span>TO</span><span> Login_For_Mirror;
</span><span>GO</span>

<span>--</span><span>为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Witness <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Witness <span>FOR</span><span> LOGIN Login_For_Witness;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Host <span>TO</span><span> Login_For_Witness;

</span><span>GO</span>

<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 镜像机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Host <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Host <span>FOR</span><span> LOGIN Login_For_Host;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Mirror <span>TO</span><span> Login_For_Host;
</span><span>GO</span>

<span>--</span><span>为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Witness <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Witness <span>FOR</span><span> LOGIN Login_For_Witness;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Mirror <span>TO</span><span> Login_For_Witness;
</span><span>GO</span>


<span>--</span><span>----------------------------------------------------------------------</span><span>
--</span><span>============================ 见证机上执行 ============================--</span><span>
--</span><span>----------------------------------------------------------------------</span>
<span>USE</span><span> master
</span><span>GO</span>

<span>--</span><span>为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Host <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Host <span>FOR</span><span> LOGIN Login_For_Host;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Witness <span>TO</span><span> Login_For_Host;
</span><span>GO</span>

<span>--</span><span>为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限</span>
<span>CREATE</span> LOGIN Login_For_Mirror <span>WITH</span> PASSWORD<span>=</span>N<span>'</span><span>PWD_DBMirror986252588</span><span>'</span><span>;
</span><span>CREATE</span> <span>USER</span> User_For_Mirror <span>FOR</span><span> LOGIN Login_For_Mirror;
</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>;
</span><span>GRANT</span> CONNECT <span>ON</span> ENDPOINT::Endpoint_Witness <span>TO</span><span> Login_For_Mirror;
</span><span>GO</span>

View Code

4.最后一步开始镜像。

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

<span>--</span><span> 镜像机上执行：</span><span>
--</span><span> 建立 主机 合作</span>
<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>; 

</span><span>--</span><span> 主机上执行：</span><span>
--</span><span> 建立 镜像机 合作</span>
<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>;
</span><span>--</span><span> 建立 见证机 合作</span>
<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

声明：

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：sqlserver 2005,设备异常断电,通过检查发现数据库不可用，数据库下一篇：如何订阅 SqlServer 数据库操作相关响应事件，例如：打开连接、

查看更多

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

相关文章