工作组模式下SQL Server 2008 R2 数据库镜像

生产环境要上镜像，自己又做了一遍测试，总结记录一下。

目的：实现关键数据库的热备和故障自动切换。

环境：Win2008_R2_X64,SQLServer2008_R2_X64,WorkGroup网络模式。

数据库：people

主机：192.168.1.3

镜像机：192.168.1.110

见证机：192.168.1.111

准备工作：

1. 打开每个实例的RemoteDACEnable.

   方法：SSMS选中实例右键Facets(方面)在Facet列表选中"Surface area configuration"设定"RemoteDacEnabled"为true。    

2. 在每台Server的防火墙入站规则中添加镜像所需端口(我用的是5022).

   方法：开始控制面板Windows防火墙高级设置入站规则新增规则    

3. 对主机的people数据库做一个全备和事务日志备份并将其COPY到镜像机上用Replace,NoRecovery选项恢复。

   主机备份：

   USE master

   GO

   BACKUP DATABASE [people] TO DISK = N'D:\people.bak'

   WITH FORMAT, INIT, NAME = N'people-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

   GO

   BACKUP LOG [people] TO DISK = N'D:\people.bak'

   WITH NOFORMAT, NOINIT, NAME = N'people-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

   GO

镜像恢复：

USE master

GO

RESTORE DATABASE [people] FROM DISK = N'F:\people.bak'

WITH FILE = 1,

NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

GO

RESTORE LOG [people] FROM DISK = N'F:\people.bak'

WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10

GO

实施，以下步骤按编号顺序执行：

1. 主机上执行：

USE master

GO

--创建证书，并备份

CREATE MASTER KEY ENCRYPTION BY PASSWORD=N'joe123';

CREATE CERTIFICATE Cert_JOEPC

WITH SUBJECT=N'JOEPC Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_JOEPC TO FILE=N'C:\Cert_JOEPC.cer';

GO

--创建镜像端口

CREATE ENDPOINT EP_JOEPC_Mirr

STATE=STARTED

AS TCP

(

LISTENER_PORT=5022,

LISTENER_IP=ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION=CERTIFICATE Cert_JOEPC,

ENCRYPTION=REQUIRED ALGORITHM AES,

ROLE=PARTNER

)

 

                 GO

2. 镜像机上执行：

USE master

GO

--创建证书，并备份

CREATE MASTER KEY ENCRYPTION BY PASSWORD=N'joe123';

CREATE CERTIFICATE Cert_110

WITH SUBJECT=N'Certificate 110',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_110 TO FILE=N'C:\Cert_110.cer';

GO

--创建镜像端口

CREATE ENDPOINT EP_110_Mirr

STATE=STARTED

AS TCP

(

LISTENER_PORT=5022,

LISTENER_IP=ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION=CERTIFICATE Cert_110,

ENCRYPTION=REQUIRED ALGORITHM AES,

ROLE=PARTNER

)

GO

 

3. 见证机上执行：

USE master

go

--创建证书，并备份

CREATE MASTER KEY ENCRYPTION BY PASSWORD =N'joe123';

CREATE CERTIFICATE Cert_111

WITH SUBJECT=N'Certificate 111';

BACKUP CERTIFICATE Cert_111 TO FILE=N'C:\Cert_111.cer';

GO

--创建镜像端口

CREATE ENDPOINT EP_111_Mirr

AS TCP

(

LISTENER_PORT=5022,

LISTENER_IP=ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION=CERTIFICATE Cert_111,

ENCRYPTION=REQUIRED ALGORITHM AES,

ROLE=WITNESS

)

 

            GO

4. 把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书。

5. 主机上执行：

             USE master

       GO

--为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_110 WITH PASSWORD=N'joe123';

CREATE USER User_For_110 FOR LOGIN Login_For_110;

CREATE CERTIFICATE Cert_For_110 AUTHORIZATION User_For_110 FROM FILE=N'C:\Cert_110.cer';

GRANT CONNECT ON ENDPOINT::EP_JOEPC_Mirr TO Login_For_110;

GO

--为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_111 WITH PASSWORD=N'joe123';

CREATE USER User_For_111 FOR LOGIN Login_For_111;

CREATE CERTIFICATE Cert_For_111 AUTHORIZATION User_For_111 FROM FILE=N'C:\Cert_111.cer';

GRANT CONNECT ON ENDPOINT::EP_JOEPC_Mirr TO Login_For_111;

       GO

6. 镜像机上执行：

USE master

GO

--为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_JOEPC WITH PASSWORD=N'joe123';

CREATE USER User_For_JOEPC FOR LOGIN Login_For_JOEPC;

CREATE CERTIFICATE Cert_For_JOEPC AUTHORIZATION User_For_JOEPC

FROM FILE =N'c:\Cert_JOEPC.cer';

GRANT CONNECT ON ENDPOINT::EP_110_Mirr TO Login_For_JOEPC;

GO

--为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_111 WITH PASSWORD=N'joe123';

CREATE USER User_For_111 FOR LOGIN Login_For_111;

CREATE CERTIFICATE Cert_For_111 AUTHORIZATION User_For_111

FROM FILE =N'c:\Cert_111.cer';

GRANT CONNECT ON ENDPOINT::EP_110_Mirr TO Login_For_111;

GO

 

7. 见证机上执行：

USE master

GO

--为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_JOEPC WITH PASSWORD=N'joe123';

CREATE USER User_For_JOEPC FOR LOGIN Login_For_JOEPC;

CREATE CERTIFICATE Cert_For_JOEPC AUTHORIZATION User_For_JOEPC FROM FILE=N'c:\Cert_JOEPC.cer';

GRANT CONNECT ON ENDPOINT::EP_111_Mirr TO Login_For_JOEPC;

GO

--为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限

CREATE LOGIN Login_For_110 WITH PASSWORD=N'joe123';

CREATE USER User_For_110 FOR LOGIN Login_For_110;

CREATE CERTIFICATE Cert_For_110 AUTHORIZATION User_For_JOEPC FROM FILE=N'c:\Cert_110.cer';

GRANT CONNECT ON ENDPOINT::EP_111_Mirr TO Login_For_110;

GO

8. 镜像机上执行：

      ALTER DATABASE people SET PARTNER =N'TCP://192.168.1.3:5022';

9. 主机上执行：

ALTER DATABASE [people] SET PARTNER=N'TCP://192.168.1.110:5022';

ALTER DATABASE [people] SET WITNESS=N'TCP://192.168.1.111:5022';

10. 完成以上步骤，正常情况，people数据库镜像就建立起来了。将会看到：

主机：

镜像机：

然后在主机上简单地查询一下见证机的状态：我这里的见证机因为后来重新用GUI配置过，所以显示为计算机名。

SELECT 'people' AS [DB_Name],mirroring_role_desc,

mirroring_partner_name,mirroring_witness_name,mirroring_witness_state_desc

FROM sys.database_mirroring

WHERE database_id=DB_ID(N'people')

总结：

1. 工作组模式下，曾经遇到过两次主机和镜像配置成功，见证机总是连接不上的问题。我尝试过以下方法解决：
   1. 检查网络联通性，确保见证机的IP和端口可用。
   2. 给每台机加上FQDN，即设置同样的DNS后缀名。（方法：计算机右键，属性更改设置更改其他DNS后缀）
   3. 修改每台机的HOST文件，将计算机名和IP绑定。
   4. 用SSMS GUI重新配置一次见证机。
2. 还遇到过一些其它问题，错误提示很明显，根据其基本能解决。最常见无法建立镜像的错误，是因为镜像未能与主机一致，备份主机日志到镜像恢复一次或者重做镜像。