生产环境要上镜像,自己又做了一遍测试,总结记录一下。
目的:实现关键数据库的热备和故障自动切换。
环境:Win2008_R2_X64,SQLServer2008_R2_X64,WorkGroup网络模式。
数据库:people
主机:192.168.1.3
镜像机:192.168.1.110
见证机:192.168.1.111
准备工作:
-
打开每个实例的RemoteDACEnable.
方法:SSMS选中实例右键Facets(方面)在Facet列表选中"Surface area configuration"设定"RemoteDacEnabled"为true。
-
在每台Server的防火墙入站规则中添加镜像所需端口(我用的是5022).
方法:开始控制面板Windows防火墙高级设置入站规则新增规则
-
对主机的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
实施,以下步骤按编号顺序执行:
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
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
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
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
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
ALTER DATABASE people SET PARTNER =N'TCP://192.168.1.3:5022';
ALTER DATABASE [people] SET PARTNER=N'TCP://192.168.1.110:5022';
ALTER DATABASE [people] SET WITNESS=N'TCP://192.168.1.111:5022';
主机:
镜像机:
然后在主机上简单地查询一下见证机的状态:我这里的见证机因为后来重新用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')
总结:
- 工作组模式下,曾经遇到过两次主机和镜像配置成功,见证机总是连接不上的问题。我尝试过以下方法解决:
- 检查网络联通性,确保见证机的IP和端口可用。
- 给每台机加上FQDN,即设置同样的DNS后缀名。(方法:计算机右键,属性更改设置更改其他DNS后缀)
- 修改每台机的HOST文件,将计算机名和IP绑定。
- 用SSMS GUI重新配置一次见证机。
- 还遇到过一些其它问题,错误提示很明显,根据其基本能解决。最常见无法建立镜像的错误,是因为镜像未能与主机一致,备份主机日志到镜像恢复一次或者重做镜像。