镜像是sql server 2005新增的功能，为高可用性解决方案。
除了master,msdb,temp以及mode之外，sql 2005允许镜像任何数据库，镜像需要三个服务器，一个主体服务器，一个镜像服务器及一个见证服务器。
一、准备工作
1、3台服务器，其中一台为主服务器我们就叫A,另外一台为镜像服务器我们叫B，第三台服务器为见证服务器叫C。有3台服务器就可以配置镜像的带故障转移的高安全性镜像。测试环境下3台服务器可以为真实的，虚拟机，数据库不同实例，这都不影响下面的步骤实现。
2、sql server 2005 请安装sp2补丁，这样才可以直接支持镜像。注意，如果是后来安装了新实例，也要在补丁一次才可以。
3、在A服务器完全备份你需要镜像的数据库，我们这里的数据库名字就叫testsql。完全备份后，选择事务日志备份再次备份。（备份到同一文件）。
4、拷贝备份到B服务器上，然后还原。注意，还原的时候一定要选择RESTORE WITH NORECOVERY。这样，还原的数据库是不可以被访问的，并且应该显示正在还原。
二、镜像开始
1、可以在 sql server management studio 下配置镜像，我最开始就是采用这个方法，操作简便直观。但是后来出现问题，在启动镜像的时候总是说不能连接到镜像服务器，日志记录就是说用户验证失败什么什么。后来google了一下，这个错误好像很普遍，解决办法是用sql证书认证来解决。下面采用alan328.com上面的方案。
2、在A上面执行T-SQL
create master key encryption by password = 'abc123!!';
GO
create certificate HOST_A_cert with subject = 'HOST_A certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_A_cert, encryption = disabled, role = all);
GO
Backup certificate HOST_A_cert to file = 'd:\HOST_A_cert.cer';
GO
大概意思就是创建证书，然后创建镜像端点，然后保存证书到硬盘上。这里请根据实际情况修改backup file，然后修改一下密码吧。然后注意，监听端口可以自己设置，只要不冲突就可以了，还要注意在防火墙要允许访问。其他的可以不用修改
GO
create certificate HOST_A_cert with subject = 'HOST_A certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_A_cert, encryption = disabled, role = all);
GO
Backup certificate HOST_A_cert to file = 'd:\HOST_A_cert.cer';
GO
大概意思就是创建证书，然后创建镜像端点，然后保存证书到硬盘上。这里请根据实际情况修改backup file，然后修改一下密码吧。然后注意，监听端口可以自己设置，只要不冲突就可以了，还要注意在防火墙要允许访问。其他的可以不用修改
3、在B上执行：
create master key encryption by password = 'abc123!!';
GO
create certificate HOST_B_cert with subject = 'HOST_B certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_B_cert, encryption = disabled, role = all);
GO
Backup certificate HOST_B_cert to file = 'd:\HOST_B_cert.cer';
GO
和上面的意思一样，只不过是在镜像服务器罢了
GO
create certificate HOST_B_cert with subject = 'HOST_B certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_B_cert, encryption = disabled, role = all);
GO
Backup certificate HOST_B_cert to file = 'd:\HOST_B_cert.cer';
GO
和上面的意思一样，只不过是在镜像服务器罢了
然后在C上执行：
create master key encryption by password = 'abc123!!';
GO
create certificate HOST_W_cert with subject = 'HOST_W certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_W_cert, encryption = disabled, role = witness);
GO
Backup certificate HOST_W_cert to file = 'd:\HOST_W_cert.cer';
GO
执行完以后，分别拷贝3个证书文件到每个服务器，也就是说每个服务器都有这3个证书。这个时候可以在服务器对象---端点---数据库镜像里面看到新建立的端点了。
GO
create certificate HOST_W_cert with subject = 'HOST_W certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_W_cert, encryption = disabled, role = witness);
GO
Backup certificate HOST_W_cert to file = 'd:\HOST_W_cert.cer';
GO
执行完以后，分别拷贝3个证书文件到每个服务器，也就是说每个服务器都有这3个证书。这个时候可以在服务器对象---端点---数据库镜像里面看到新建立的端点了。
4、继续执行T-SQL语句，来绑定证书验证，分别在A B C上执行
-- HOST A again
create login HOST_B_login with PASSWORD = 'abc123!!';
GO
create user HOST_B_user from login HOST_B_login;
GO
Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'D:\HOST_B_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_B_login];
GO
------
create login HOST_W_login with PASSWORD = 'abc123!!';
GO
create user HOST_W_user from login HOST_W_login;
GO
Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'D:\HOST_W_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_W_login];
GO
-- HOST B again
create login HOST_A_login with PASSWORD = 'abc123!!';
GO
create user HOST_A_user from login HOST_A_login;
GO
Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'D:\HOST_A_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_A_login];
GO
-------
create login HOST_W_login with PASSWORD = 'abc123!!';
GO
create user HOST_W_user from login HOST_W_login;
GO
Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'D:\HOST_W_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_W_login];
GO
-- HOSTC again
create login HOST_A_login with PASSWORD = 'abc123!!';
GO
create user HOST_A_user from login HOST_A_login;
GO
Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'D:\HOST_A_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_A_login];
GO
-------
create login HOST_B_login with PASSWORD = 'abc123!!';
GO
create user HOST_B_user from login HOST_B_login;
GO
Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'D:\HOST_B_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_B_login];
GO
create login HOST_B_login with PASSWORD = 'abc123!!';
GO
create user HOST_B_user from login HOST_B_login;
GO
Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'D:\HOST_B_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_B_login];
GO
------
create login HOST_W_login with PASSWORD = 'abc123!!';
GO
create user HOST_W_user from login HOST_W_login;
GO
Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'D:\HOST_W_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_W_login];
GO
-- HOST B again
create login HOST_A_login with PASSWORD = 'abc123!!';
GO
create user HOST_A_user from login HOST_A_login;
GO
Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'D:\HOST_A_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_A_login];
GO
-------
create login HOST_W_login with PASSWORD = 'abc123!!';
GO
create user HOST_W_user from login HOST_W_login;
GO
Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'D:\HOST_W_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_W_login];
GO
-- HOST
GO
create user HOST_A_user from login HOST_A_login;
GO
Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'D:\HOST_A_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_A_login];
GO
-------
create login HOST_B_login with PASSWORD = 'abc123!!';
GO
create user HOST_B_user from login HOST_B_login;
GO
Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'D:\HOST_B_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_B_login];
GO
5、下面回到A服务器上，到登录名列表可以看到新增了两个用户HOST_B_LOGIN HOST_C_LOGIN.这两个用户使用来使A和C访问的。然后到用户属性的安全对象选项卡，看看是否有端点名的Connect权限，如果没有就点击添加，特定类型的所有对象，端点，然后勾选Connect就行了。3个服务器上面的六个帐户都检查一下。
6、在B上执行
alter database good set partner = 'TCP://server1.ace.local:7024';
GO
GO
在A上执行
alter database good set partner = 'TCP://server2.ace.local:7024';
GO
alter database good set witness = 'TCP://mc.ace.local:7024';
GO
alter database good set partner = 'TCP://server2.ace.local:7024';
GO
alter database good set witness = 'TCP://mc.ace.local:7024';
GO
7、如果不出意外的话，应该数据库镜像已经配置完成了。A服务器数据库会显示：主体，已同步。B服务器会显示：镜像，已同步/正在还原。OK，镜像可以使用了。
监视镜像状态和性能
sys.database_mirroring 这个目录显示在一个服务器实例中的每个镜像数库的数据库镜像无数据。
sys.databse_mirroing_endpoints 这个目录视图显示关于服务器实例的数据库镜像库端点信息
sys.databse_mirroring_witnesses 这个目录视图显示见证服务器的每个会话的数据库镜像端点元数据。
在性能监控中，可使用sql server:Database Mirroring 对象来监视镜像性能。
使用 Log Btytes Sents/sec(发送的日志字节／节)计数器秋监视日志数据每秒发送的数量。
使用Transaction Delay(事务延迟)计数器确定主体服务器上镜像是否正在影响性能。
使用Redo Queue(重做队列）和Log Send Queue(日志发送队列)计数器来确定镜像数据库是否和主体数据库保持一致。
参考文献：
