SQLServer2008的数据库镜像实施

最初在为公司设计SQLServer数据库镜像的时候,首先考虑的是高可用性(三台计算机,一台见证服务器,一台做主数据库,一台做镜像)
在虚拟机环境下部署成功,一切都是那么的完美。 故障转移3秒之内就可以顺利完成。

高可用性的实施代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
/********************************************************
此脚本在主体服务器执行
********************************************************/
--镜像只支持完全恢复模式,在备份数据库之前检查恢复的模式
--对要镜像的数据库进行完整备份后,复制到镜像数据库以NORECOVERNY选项进行恢复
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--为此服务器实例制作一个证书。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',START_DATE = '01/01/2009';
GO
--使用该证书为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = PARTNER
);
GO

--备份 HOST_A 证书,并将其复制到其他机器,将 C:\HOST_A_cert.cer 复制到 HOST_B\HOST_C。
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer';
GO
--为入站连接配置 Host_A
--在 HOST_A 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO

--创建一个使用该登录名的用户。
--DROP USER HOST_B_user
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'e:\HOST_B_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

--在 HOST_A 上为 HOST_C 创建一个登录名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO

--创建一个使用该登录名的用户。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'e:\HOST_C_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR CERTIFICATE HOST_A_cert;
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--必须要在镜像数据库中先设置好伙伴后,才能在主体服务器执行
--在 HOST_A 的主体服务器实例上,将 HOST_B 上的服务器实例设置为伙伴(使其成为初始镜像服务器实例)。
ALTER DATABASE crm
SET PARTNER = 'TCP://192.168.1.205:5022';
GO

--设置见证服务器
ALTER DATABASE crm SET WITNESS = N'TCP://192.168.1.204:5022';
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
/***********************************************
在镜像服务器执行此脚本
***********************************************/
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--为 HOST_B 服务器实例制作一个证书。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',START_DATE = '01/01/2009';
GO
--在 HOST_B 中为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = PARTNER
);
GO
--备份 HOST_B 证书,将 C:\HOST_B_cert.cer 复制到 HOST_A\HOST_C。
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer';
GO

--为入站连接配置 Host_B
--在 HOST_B 上为 HOST_A 创建一个登录名。
USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'e:\HOST_A_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--在 HOST_B 上为 HOST_C 创建一个登录名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO

--创建一个使用该登录名的用户。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'e:\HOST_C_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

--在 HOST_B 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_B_user
CREATE USER HOST_B_user FOR CERTIFICATE HOST_B_cert;
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
--在 HOST_B 的镜像服务器实例上,将 HOST_A 上的服务器实例设置为伙伴(使其成为初始主体服务器实例)。
ALTER DATABASE crm
SET PARTNER = 'TCP://192.168.1.203:5022';
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
/****************************
见证服务器执行
*****************************/
--ALTER DATABASE MirrorDB SET PARTNER OFF
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

--为此服务器实例制作一个证书。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate',START_DATE = '01/01/2009';
GO

--使用该证书为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_C_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = WITNESS
);
GO


--备份 HOST_C 证书,并将其复制到其他系统,即 HOST_B\HOST_A。
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'e:\HOST_C_cert.cer';
GO

--为入站连接配置 Host_C
--在 HOST_C 上为 HOST_B 创建一个登录名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO

--创建一个使用该登录名的用户。
--DROP USER HOST_B_user
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'e:\HOST_B_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

--在 HOST_C 上为 HOST_A 创建一个登录名。
USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'e:\HOST_A_cert.cer'
GO

--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--在 HOST_C 上为 HOST_C 创建一个登录名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO
--创建一个使用该登录名的用户。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR CERTIFICATE HOST_C_cert;
GO

可能有朋友们会比较有疑惑,你一下搞两个数据库出来,他们的ip地址都不一样,到时候数据库切换过去了,我的数据库的连接字符串可如何是好?难道还得在代码中去控制是连接哪个数据库吗?
其实这个问题是这样的,使用ADO.NET或者SQL Native Client能够自动连接到故障转移后的伙伴,连接字符串如下所示:

1
ConnectionString="DataSource= A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=true;"

DataSource= A;这个就是我们常用的主数据库的ip地址,Failover Partner=B;这个填写的就是镜像数据库的ip地址,一旦出现了连接错误,ado.net会在超时以后自动去连接镜像数据库。

高级别保护模式

在昨天晚上加班做实施的时候,才发现我的设计已经被修改了,由于以前的项目有java写的也有c#写的,全自动的故障转移不能够实现 。换句话说,由于老项目中的历史遗留问题,以及特殊模块的耦合性过高,无法解耦,只能在高级别保护模式或高性能模式中选择一种了。那么这两者有什么区别 呢?
简单一点来说,区别就在与事务安全模式上跟应用场景上。
高级别保护模式采用的是同步镜像, SAFETY FULL。应用场景:通常在局域网中或对数据要求比较高的场景中。
高性能保护模式采用的是异步镜像, SAFETY OFF。应用场景:通常在广域网或对数据要求不太高,丢失几条数据是允许的,但是必须保证它不中断服务。
在微软的SQLServer2005的课程上是这么说的。如果是高级别保护模式的话,主、从数据库只要有一台不能正常保证服务,数据库就不能够对外 进行服务了,我在开始的时候就没有打算采用这种模式,因为部门经理说了,丢失一两条数据是可以接受的,况且我们公司是做运营的,按照起先微软的课程的理 论,高级别保护模式是不太适合我们公司的应用场景的,万一有一台数据库出问题了,整个服务就被中断,这是不能让人接受的。再说了,公司对数据要求不太苛 刻,两台服务器都有内网线连接,由于内网传输速度非常的快,即使采用高性能模式,一般来说也是不会丢失数据的。于是我打算采用高性能模式来做数据库的镜 像。由于公司服务器没有域环境,所以我就采用了证书验证来做SQLServer镜像。
意外收获:
两台服务器全部都安装了SQLServer2008,在设置事务安全模式的时候,才发现SQLServer2008不支持异步模式。提示大概如下: 此SQLServer版本不支持修改事务安全模式,alter database失败。 我当时汗都出来了,忙活了一晚上,到最后居然是这个结果。
由于是服务器维护时间,我大胆的把镜像服务器停止了,结果却让我大吃一惊,主数据库依旧可以正常工作,正常对外提供服务。也就是说,起先微软的课程 讲的知识是错误的,两台数据库做镜像,不管是哪台数据库出了问题,另外的一台数据库都可以保证正常对外提供服务。于是我反复试验反复切换了一下,结果依然 是这样。
由于高级别保护模式与高性能模式代码差不太多,只是在事务安全模式的设置上有些小区别,前面已经提到,这里就不再多解释了。实施的代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
USE  master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '01/01/2009';


CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );


BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer';


CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'e:\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];


ALTER DATABASE crm SET PARTNER = 'TCP://10.10.10.8:5022';

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '01/01/2009';


CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );


BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer';


CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'e:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];


ALTER DATABASE crm SET PARTNER = 'TCP://10.10.10.6:5022';

可能有朋友会比较奇怪,你这里也没有使用ALTER DATABASE crm SET SAFETY FULL; 按理应该是高性能模式才对呀?
其实这个问题是这样的,我的这个SQLServer2008默认已经是将事务安全模式设置为full了,即使是手动设置也一样,并且我实施的时候SQLServer2008不支持将
事务安全模式设置为OFF。

OK,一切都设置好了,那么就可以模拟服务器真的down机时候的操作了,后续的工作我也把代码做了总结,具体代码如下:

1
2
3
4
5
6
7
--主备互换
--主机执行:

ALTER DATABASE crm SET PARTNER FAILOVER

--主服务器Down掉,备机紧急启动并且开始服务
ALTER DATABASE crm SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

原来的主服务器恢复,可以继续工作,需要重新设定镜像

1
2
3
4
5
--备机执行:
USE master
ALTER DATABASE crm SET PARTNER RESUME --恢复镜像

ALTER DATABASE crm SET PARTNER FAILOVER; --切换主备

监视数据库镜像

SQLServer提供了一些视图,可以供查询镜像的各种状态,到时候可以根据这个做一个监视,一旦发生故障转移群集,发邮件给系统管理员,好让系 统管理员及时的知道数据库服务器发生了什么问题,即使的做故障分析、排查。

坚持原创技术分享,您的支持将鼓励我继续创作!