SQL Server 2005 镜像功能实现

SQL Server 2005 镜像功能实现

1. 主机、镜像分别MASTER KEY。

主机执行： 

1. USE master;  
2.   
3. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
4.   
5. CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,  
6.   
7. START_DATE = '01/01/2008';  

镜像执行：  

1. USE master;  
2.   
3. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
4.   
5. CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',  
6.   
7. START_DATE = '01/01/2008';  

这个MASTER KEY对于每个sql server实例唯一的，如果数据库中已经创建了MASTER KEY，可以用如下的sql删除之后重新执行：

1. DROP MASTER KEY  

2.主机、镜像分别创建EndPoint

主机执行：  

1. CREATE ENDPOINT Endpoint_Mirroring  
2.   
3. STATE = STARTED  
4.   
5. AS  
6.   
7. TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )  
8.   
9. FOR  
10.   
11. DATABASE_MIRRORING  
12.   
13. ( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );  

镜像执行：  

1. CREATE ENDPOINT Endpoint_Mirroring  
2.   
3. STATE = STARTED  
4.   
5. AS  
6.   
7. TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )  
8.   
9. FOR  
10.   
11. DATABASE_MIRRORING  
12.   
13. ( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );  

EndPoint 貌似也是唯一的，如果已经提示错误如没有权限之类的，可以先删除创建过的EndPoint（如果你用图形界面创建过镜像则ENDPOINT的名称默认为"镜像"）

1. DROP ENDPOINT 镜像  

3.备份证书，将主机和镜像的证书互换

主机执行  

1. BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:/HOST_A_cert.cer';  

镜像执行  

1. BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:/HOST_B_cert.cer';  

将备份好的证书文件相互copy

4.同步login

主机执行 

1. CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';  
2.   
3. CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
4.   
5. CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:/HOST_B_cert.cer';  
6.   
7. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];  

镜像执行 

1. CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';  
2.   
3. CREATE USER HOST_A_user FOR LOGIN HOST_A_login;  
4.   
5. CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:/HOST_A_cert.cer';  
6.   
7. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];  

      经过以上步骤，两个sql server 实例的准备工作就差不多了，加入我们要同步的数据库为billing。
         1. 首先将主机上的billing数据库完整备份，在镜像机上创建同名数据库，用主机的备份还原（选择覆盖现有数据库以及RESTOR WITH NORECOVERY），还原后数据库的状态为"正在还原"。
         2. 将主机的billing数据库的事务日志备份，同样在镜像机上还原。 

5.下面我们就要开始执行镜像的同步了，

镜像执行 

1. ALTER DATABASE Billing SET PARTNER = 'TCP://主机IP:5022';  

主机执行  

1. ALTER DATABASE Billing SET PARTNER = 'TCP://镜像IP:5022';  

如遇错误，首先保证防火墙等是否将相关端口屏蔽，sql server是否允许远程连接等，如果还是没有解决问题，如提示'TCP://镜像IP:5022'无法访问，

那么————————————"重启"！

完成后主机数据库显示"主题-已同步"，镜像显示"镜像-已同步/正在还原"。

也许你还会用到如下语句：

关闭镜像

1. ALTER DATABASE Billing SET PARTNER OFF  
2.   
3. 在主机执行一下sql切换主机和镜像  
4.   
5. USE MASTER  
6.   
7. Go  
8.   
9. ALTER DATABASE Billing SET PARTNER FAILOVER  
10.   
11. Go