部署SQL Server 2016基于工作组的AlwaysOn AG

更新时间：2022-09-28 17:12:26

部署说明：

以下部署基于工作组环境、不用证书、启动账号为Users组成员、各节点位于同一网段。

前期环境准备参考之前的文章：

部署Windows Server 2016基于工作组的集群

安装和配置SQL Server 2016 With SP1

Part1:创建测试数据库

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内数据库的完整备份

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

--提前创建端点
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