Shulou(Shulou.com)06/01 Report--

Deploy SQL Server 2016 based on the workgroup's AlwaysOn AG

Deployment instructions:

The following deployment is based on the workgroup environment, does not require a certificate, the startup account is a member of the Users group, and each node is located in the same network segment.

Refer to the previous article for pre-environmental preparation:

Deploy Windows Server 2016 workgroup-based clusters

Install and configure SQL Server 2016 With SP1

Part1: creating a test database

CREATE DATABASE Chapter5App1Customers; GOALTER DATABASE Chapter5App1Customers SET RECOVERY FULL; GOUSE Chapter5App1CustomersGOCREATE TABLE App1Customers (ID INT PRIMARY KEY IDENTITY,FirstName NVARCHAR (30), LastName NVARCHAR (30), CreditCardNumber VARBINARY (8000)); GO--Populate the tableDECLARE @ Numbers TABLE (Number INT); WITH CTE (Number) AS (SELECT 1 NumberUNION ALLSELECT Number + 1FROM CTEWHERE Number)

< 100)INSERT INTO @NumbersSELECT Number FROM CTEDECLARE @Names TABLE(FirstName VARCHAR(30),LastName VARCHAR(30)) ;INSERT INTO @NamesVALUES('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 @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumberFROM @Numbers aCROSS JOIN @Numbers bCROSS JOIN @Numbers c) d ;CREATE DATABASE Chapter5App1Sales ;GOALTER DATABASE Chapter5App1Sales SET RECOVERY FULL ;GOUSE Chapter5App1SalesGOCREATE 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 NumberUNION ALLSELECT Number + 1FROM CTEWHERE Number < 100)INSERT INTO @NumbersSELECT Number FROM CTE--Populate ExistingOrders with dataINSERT INTO OrdersSELECT(SELECT CAST(DATEADD(dd,(SELECT TOP 1 NumberFROM @NumbersORDER 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 - 10FROM @NumbersORDER BY NEWID()),getdate()) as DATE))FROM @Numbers aCROSS JOIN @Numbers bCROSS JOIN @Numbers c ;CREATE DATABASE Chapter5App2Customers ;GOALTER DATABASE Chapter5App2Customers SET RECOVERY FULL ;GOUSE Chapter5App2CustomersGOCREATE TABLE App2Customers(ID INT PRIMARY KEY IDENTITY,FirstName NVARCHAR(30),LastName NVARCHAR(30),CreditCardNumber VARBINARY(8000)) ;GO--Populate the tableDECLARE @Numbers TABLE(Number INT) ;;WITH CTE(Number)AS(SELECT 1 NumberUNION ALLSELECT Number + 1FROM CTEWHERE Number < 100)INSERT INTO @NumbersSELECT Number FROM CTE ;DECLARE @Names TABLE(FirstName VARCHAR(30),LastName VARCHAR(30)) ;INSERT INTO @NamesVALUES('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 @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))FROM @NumbersWHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumberFROM @Numbers aCROSS JOIN @Numbers bCROSS JOIN @Numbers c) d ; Part2:配置SQL Server Part4:创建AG内数据库的完整备份 BACKUP DATABASE Chapter5App1CustomersTO DISK = N'C:\Backups\Chapter5App1Customers.bak'WITH NAME = N'Chapter5App1Customers-Full Database Backup' ;GOBACKUP DATABASE Chapter5App1SalesTO DISK = N'C:\Backups\Chapter5App1Sales.bak'WITH NAME = N'Chapter5App1Sales-Full Database Backup' ;GO Part5:将备份在AG内的其他副本上恢复为NORECOVERY Part6:将启动账号添加到Logins --提前创建端点USE [master]GOCREATE 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)GODECLARE @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

There are many improvements in SQL Server 2016 AlwaysOn AG, and what impressed me deeply was the improvement in log throughput and redo. Here is another key enhancement, that is, this configuration option: Database Level Health Detection.

SQL Server 2016 enhances AlwaysOn health diagnosis using database health testing. If this option for your AG is checked and one of the databases becomes non-ONLINE, then the entire AG will fail over automatically.

Part8: checking AG statu

Part9: after testing Server01 downtime

Part10: after testing Server02 recovery

Reference:

Workgroup and Multi-domain clusters in Windows Server 2016

"SQL Server AlwaysOn Revealed"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.