您好,登錄后才能下訂單哦!
部署SQL Server 2016基于工作組的AlwaysOn AG
部署說(shuō)明:
以下部署基于工作組環(huán)境、不用證書(shū)、啟動(dòng)賬號(hào)為Users組成員、各節(jié)點(diǎn)位于同一網(wǎng)段。
前期環(huán)境準(zhǔn)備參考之前的文章:
部署Windows Server 2016基于工作組的集群
安裝和配置SQL Server 2016 With SP1
Part1:創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)
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:創(chuàng)建AG內(nèi)數(shù)據(jù)庫(kù)的完整備份
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內(nèi)的其他副本上恢復(fù)為NORECOVERY
Part6:將啟動(dòng)賬號(hào)添加到Logins
--提前創(chuàng)建端點(diǎn) 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) ; --獲取實(shí)例名 SET @Server = CAST(SERVERPROPERTY('ServerName') AS sysname); --獲取機(jī)器名(遠(yuǎn)程執(zhí)行時(shí)HOST_NAME()函數(shù)不代表遠(yuǎn)程主機(jī)) IF CHARINDEX('\\',@Server,1) <> 0 SET @HostName = SUBSTRING(@Server,1,CHARINDEX('\\',@Server,1)-1); ELSE SET @HostName = @Server; --用到SQL Server Database Engine服務(wù)啟動(dòng)賬號(hào) 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'; --端點(diǎn)訪問(wèn)授權(quán) SET @SQL = N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @Login + ']'; --SELECT @SQL EXEC sp_executesql @SQL; END
Part7:創(chuàng)建AG
SQL Server 2016 AlwaysOn AG有很多提升,令我影響深刻的是日志吞吐量的提升和redo的提升。這里講介紹它的另一項(xiàng)關(guān)鍵提升,也就是這個(gè)配置選項(xiàng):Database Level Health Detection。
SQL Server 2016使用數(shù)據(jù)庫(kù)健康檢測(cè)增強(qiáng)了AlwaysOn健康診斷。如果你的AG的該選項(xiàng)被勾選,而其中某個(gè)數(shù)據(jù)庫(kù)變?yōu)榉荗NLINE狀態(tài),那么整個(gè)AG將會(huì)自動(dòng)故障轉(zhuǎn)移。
Part8:檢查AG狀態(tài)
Part9:測(cè)試Server01宕機(jī)后
Part10:測(cè)試Server02恢復(fù)后
參考:
Workgroup and Multi-domain clusters in Windows Server 2016
《SQL Server AlwaysOn Revealed》
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。