一. 相关环境介结。
数据库：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 mastergobackup database [testsync] to disk = n'd:\sqlservermirrorcer\testsync.bak'with format, init, name = n'testsync-full database backup', skip, norewind, nounload, stats = 10;gobackup 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 mastergorestore database [testsync] from disk = n'd:\sqlservermirrorcer\testsync.bak'with file = 1,norecovery, nounload, replace, stats = 10gorestore log [testsync] from disk = n'd:\sqlservermirrorcer\testsync.bak'with file = 2, norecovery, nounload, stats = 10go
view code
2. 创建证书
--------------------------------------------------------------------------============================ 主机上执行 ============================--------------------------------------------------------------------------use mastergo--创建证书，并备份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';goif exists(select * from sys.certificates where name='cert_host') drop certificate cert_host;gocreate certificate cert_hostwith 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_hostgocreate endpoint endpoint_hoststate = startedas tcp( listener_port=5022, listener_ip=all)for database_mirroring( authentication=certificate cert_host, encryption=required algorithm aes, role=partner)go--------------------------------------------------------------------------============================ 镜像机上执行 ============================--------------------------------------------------------------------------use mastergo--创建证书，并备份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';goif exists(select * from sys.certificates where name='cert_mirror') drop certificate cert_mirror;gocreate certificate cert_mirrorwith 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_mirrorgocreate endpoint endpoint_mirrorstate = startedas tcp( listener_port=5022, listener_ip=all)for database_mirroring( authentication=certificate cert_mirror, encryption=required algorithm aes, role=partner)go--------------------------------------------------------------------------============================ 见证机上执行 ============================--------------------------------------------------------------------------use mastergo--创建证书，并备份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';goif exists(select * from sys.certificates where name='cert_witness') drop certificate cert_witness;gocreate 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_witnessgocreate endpoint endpoint_witnessstate = startedas 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 mastergo--为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限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 mastergo--为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限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 mastergo--为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限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.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