部署说明：
以下部署基于工作组环境、不用证书、启动账号为Users组成员、各节点位于同一网段。
前期环境准备参考之前的文章：
Part1:创建测试数据库
|
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
|
CREATE
DATABASE
Chapter5App1Customers ;
GO
ALTER
DATABASE
Chapter5App1Customers
SET
RECOVERY
FULL
;
GO
USE Chapter5App1Customers
GO
CREATE
TABLE
App1Customers
(
ID
INT
PRIMARY
KEY
IDENTITY,
FirstName NVARCHAR(30),
LastName NVARCHAR(30),
CreditCardNumber VARBINARY(8000)
) ;
GO
--Populate the table
DECLARE
@Numbers
TABLE
(
Number
INT
)
;
WITH
CTE(Number)
AS
(
SELECT
1 Number
UNION
ALL
SELECT
Number + 1
FROM
CTE
WHERE
Number < 100
)
INSERT
INTO
@Numbers
SELECT
Number
FROM
CTE
DECLARE
@Names
TABLE
(
FirstName
VARCHAR
(30),
LastName
VARCHAR
(30)
) ;
INSERT
INTO
@Names
VALUES
(
'Peter'
,
'Carter'
),
(
'Michael'
,
'Smith'
),
(
'Danielle'
,
'Mead'
),
(
'Reuben'
,
'Roberts'
),
(
'Iris'
,
'Jones'
),
(
'Sylvia'
,
'Davies'
),
(
'Finola'
,
'Wright'
),
(
'Edward'
,
'James'
),
(
'Marie'
,
'Andrews'
),
(
'Jennifer'
,
'Abraham'
),
(
'Margaret'
,
'Jones'
)
INSERT
INTO
App1Customers(Firstname, LastName, CreditCardNumber)
SELECT
FirstName, LastName, CreditCardNumber
FROM
(
SELECT
(
SELECT
TOP
1 FirstName
FROM
@Names
ORDER
BY
NEWID()) FirstName
,(
SELECT
TOP
1 LastName
FROM
@Names
ORDER
BY
NEWID()) LastName
,(
SELECT
CONVERT
(VARBINARY(8000)
,(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()))) CreditCardNumber
FROM
@Numbers a
CROSS
JOIN
@Numbers b
CROSS
JOIN
@Numbers c
) d ;
CREATE
DATABASE
Chapter5App1Sales ;
GO
ALTER
DATABASE
Chapter5App1Sales
SET
RECOVERY
FULL
;
GO
USE Chapter5App1Sales
GO
CREATE
TABLE
dbo.Orders(
OrderNumber
int
NOT
NULL
IDENTITY(1,1)
PRIMARY
KEY
CLUSTERED,
OrderDate
date
NOT
NULL
,
CustomerID
int
NOT
NULL
,
ProductID
int
NOT
NULL
,
Quantity
int
NOT
NULL
,
NetAmount money
NOT
NULL
,
TaxAmount money
NOT
NULL
,
InvoiceAddressID
int
NOT
NULL
,
DeliveryAddressID
int
NOT
NULL
,
DeliveryDate
date
NULL
,
) ;
DECLARE
@Numbers
TABLE
(
Number
INT
)
;
WITH
CTE(Number)
AS
(
SELECT
1 Number
UNION
ALL
SELECT
Number + 1
FROM
CTE
WHERE
Number < 100
)
INSERT
INTO
@Numbers
SELECT
Number
FROM
CTE
--Populate ExistingOrders with data
INSERT
INTO
Orders
SELECT
(
SELECT
CAST
(DATEADD(dd,(
SELECT
TOP
1 Number
FROM
@Numbers
ORDER
BY
NEWID()),getdate())
as
DATE
)),
(
SELECT
TOP
1 Number -10
FROM
@Numbers
ORDER
BY
NEWID()),
(
SELECT
TOP
1 Number
FROM
@Numbers
ORDER
BY
NEWID()),
(
SELECT
TOP
1 Number
FROM
@Numbers
ORDER
BY
NEWID()),
500,
100,
(
SELECT
TOP
1 Number
FROM
@Numbers
ORDER
BY
NEWID()),
(
SELECT
TOP
1 Number
FROM
@Numbers
ORDER
BY
NEWID()),
(
SELECT
CAST
(DATEADD(dd,(
SELECT
TOP
1 Number - 10
FROM
@Numbers
ORDER
BY
NEWID()),getdate())
as
DATE
))
FROM
@Numbers a
CROSS
JOIN
@Numbers b
CROSS
JOIN
@Numbers c ;
CREATE
DATABASE
Chapter5App2Customers ;
GO
ALTER
DATABASE
Chapter5App2Customers
SET
RECOVERY
FULL
;
GO
USE Chapter5App2Customers
GO
CREATE
TABLE
App2Customers
(
ID
INT
PRIMARY
KEY
IDENTITY,
FirstName NVARCHAR(30),
LastName NVARCHAR(30),
CreditCardNumber VARBINARY(8000)
) ;
GO
--Populate the table
DECLARE
@Numbers
TABLE
(
Number
INT
) ;
;
WITH
CTE(Number)
AS
(
SELECT
1 Number
UNION
ALL
SELECT
Number + 1
FROM
CTE
WHERE
Number < 100
)
INSERT
INTO
@Numbers
SELECT
Number
FROM
CTE ;
DECLARE
@Names
TABLE
(
FirstName
VARCHAR
(30),
LastName
VARCHAR
(30)
) ;
INSERT
INTO
@Names
VALUES
(
'Peter'
,
'Carter'
),
(
'Michael'
,
'Smith'
),
(
'Danielle'
,
'Mead'
),
(
'Reuben'
,
'Roberts'
),
(
'Iris'
,
'Jones'
),
(
'Sylvia'
,
'Davies'
),
(
'Finola'
,
'Wright'
),
(
'Edward'
,
'James'
),
(
'Marie'
,
'Andrews'
),
(
'Jennifer'
,
'Abraham'
),
(
'Margaret'
,
'Jones'
)
INSERT
INTO
App2Customers(Firstname, LastName, CreditCardNumber)
SELECT
FirstName, LastName, CreditCardNumber
FROM
(
SELECT
(
SELECT
TOP
1 FirstName
FROM
@Names
ORDER
BY
NEWID()) FirstName
,(
SELECT
TOP
1 LastName
FROM
@Names
ORDER
BY
NEWID()) LastName
,(
SELECT
CONVERT
(VARBINARY(8000)
,(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()) +
'-'
+
(
SELECT
TOP
1
CAST
(Number * 100
AS
CHAR
(4))
FROM
@Numbers
WHERE
Number
BETWEEN
10
AND
99
ORDER
BY
NEWID()))) CreditCardNumber
FROM
@Numbers a
CROSS
JOIN
@Numbers b
CROSS
JOIN
@Numbers c
) d ;
|
Part2:配置SQL Server
Part4:创建AG内数据库的完整备份
|
1
2
3
4
5
6
7
8
|
BACKUP
DATABASE
Chapter5App1Customers
TO
DISK = N
'C:\Backups\Chapter5App1Customers.bak'
WITH
NAME
= N
'Chapter5App1Customers-Full Database Backup'
;
GO
BACKUP
DATABASE
Chapter5App1Sales
TO
DISK = N
'C:\Backups\Chapter5App1Sales.bak'
WITH
NAME
= N
'Chapter5App1Sales-Full Database Backup'
;
GO
|
Part5:将备份在AG内的其他副本上恢复为NORECOVERY
Part6:将启动账号添加到Logins
|
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
|
--提前创建端点
USE [master]
GO
CREATE
ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS
TCP (LISTENER_PORT = 5022, LISTENER_IP =
ALL
)
FOR
DATA_MIRRORING (ROLE =
ALL
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
DECLARE
@Server sysname,
@HostName sysname,
@Login sysname,
@SQL nvarchar(
max
)
;
--获取实例名
SET
@Server =
CAST
(SERVERPROPERTY(
'ServerName'
)
AS
sysname);
--获取机器名（远程执行时HOST_NAME()函数不代表远程主机）
IF CHARINDEX(
'\\'
,@Server,1) <> 0
SET
@HostName =
SUBSTRING
(@Server,1,CHARINDEX(
'\\'
,@Server,1)-1);
ELSE
SET
@HostName = @Server;
--用到SQL Server Database Engine服务启动账号
SET
@Login = @HostName +
'\SQLService'
;
IF
NOT
EXISTS(
SELECT
name
FROM
sys.syslogins
WHERE
isntuser=1
AND
name
= @Login)
BEGIN
--SELECT @Login
EXEC
sp_grantlogin @Login;
--EXEC sp_addsrvrolemember @Login, 'sysadmin';
--端点访问授权
SET
@SQL = N
'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO ['
+ @Login +
']'
;
--SELECT @SQL
EXEC
sp_executesql @SQL;
END
|
Part7:创建AG
SQL Server 2016 AlwaysOn AG有很多提升，令我影响深刻的是日志吞吐量的提升和redo的提升。这里讲介绍它的另一项关键提升，也就是这个配置选项：Database Level Health Detection。
SQL Server 2016使用数据库健康检测增强了AlwaysOn健康诊断。如果你的AG的该选项被勾选，而其中某个数据库变为非ONLINE状态，那么整个AG将会自动故障转移。
Part8:检查AG状态
Part9:测试Server01宕机后
Part10:测试Server02恢复后
参考：
Workgroup and Multi-domain clusters in Windows Server 2016
《SQL Server AlwaysOn Revealed》
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1883400 ，如需转载请自行联系原作者
