检查SQL Server 2005数据库
只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本，执行如下语句查看：
1
select @@version;
select @@version;
若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用 Transact-SQL 实现此目的，请使用 ALTER DATABASE 语句：
1 USE master;
2 ALTER DATABASE <DatabaeName>
3 SET RECOVERY FULL;
4
2 ALTER DATABASE <DatabaeName>
3 SET RECOVERY FULL;
4
实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个SQL Server 2005的实例中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每一对主备实例（不是数据库）做一次互通。
1、创建证书（主备可并行执行）
--主机执行：
1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
3CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
4START_DATE = '01/01/2008';
5
USE master;2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';3
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 4
START_DATE = '01/01/2008';5
--备机执行：
1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
3CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
4START_DATE = '01/01/2008';
5
USE master;2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';3
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 4
START_DATE = '01/01/2008';5
2、创建连接的端点（主备可并行执行）
--主机执行：
1CREATE ENDPOINT Endpoint_Mirroring
2STATE = STARTED
3AS
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
5FOR
6DATABASE_MIRRORING
7( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8
CREATE ENDPOINT Endpoint_Mirroring 2
STATE = STARTED 3
AS 4
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 5
FOR 6
DATABASE_MIRRORING 7
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );8
--备机执行：
1CREATE ENDPOINT Endpoint_Mirroring
2STATE = STARTED
3AS
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
5FOR
6DATABASE_MIRRORING
7( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8
CREATE ENDPOINT Endpoint_Mirroring 2
STATE = STARTED 3
AS 4
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 5
FOR 6
DATABASE_MIRRORING 7
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );8
3、备份证书以备建立互联（主备可并行执行）
--主机执行：
1BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
--备机执行：
1BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
4、互换证书
将备份到D:\SQLBackup\的证书进行互换，即HOST_A_cert.cer复制到备机的D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\
5、添加登陆名、用户（主备可并行执行）
以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束，SQL Server2005的不定号为SP2）
--主机执行：
1CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
2CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
3CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
5
CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';2
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;3
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';4
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];5
--备机执行：
1CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
2CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
3CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
5
CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';2
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;3
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';4
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];5
主机备机执行
ALTER DATABASE db SET PARTNER = 'TCP://*.*.*.*:5022'
亲，好好休息，注意身体
