2026 iThome 鐵人賽

DAY 16

自我挑戰組

SQL Server 基礎&調教系列第 16 篇

【基礎】 16.AOAG 實作

18th鐵人賽

SQLMASTER

2026-08-16 20:54:03

55 瀏覽

分享至

第15天，就是昨天已經說明了高可用性的概念

從第15天可以知道有 FCI、AOAG、Log shipping

其中 AOAG 是最彈性的，因為它可以同時做 HA、DR或唯讀負載。

接下來的操作，會涉及到 Windows Server 的叢集，但那不是 SQL Server 範疇所以我只會帶出操作，不會深入探討那個功能是什麼。

之後會再開一篇更深入的 HA 去說明整個要怎麼操作。

對，其實目前寫的這些對我來說都叫做基礎，無論是效能、INDEX等等，但我認為足夠應付 80% 一般公司業務狀況。

剩下的20%，會非常艱澀深入，所以拉出去額外談。

操作環境

這次的操作會全部都在一台 HYPER-V 上面進行

嚴格來說正式環境並不會這樣做，你全部放同一台HYPER-V 那這樣有 HA 跟沒有其實也沒啥差別。

但這裡只是為了演示所以才全部放在同一台，我是為了盡量不要碰到我的正式環境。

所以我會建立總共 4 台虛擬機

一台 Domain

三台 SQL SERVER INSTANCE 用的

Hyper-V 設定

這邊是因為，我這是在正式環境底下做的，然後又要建立新 Domain 所以要隔開來，避免衝突。

1. 先去設定虛擬交換器

2. 建立虛擬交換器選私人

3. 然後給他一個名字，按確定

4. 建立 Domain 的那一台虛擬機

5. 選第2代

這一二代差在哪很難馬上講完，反正 1 代是舊的，Windows Server 2019 以後都支援 2 代，就用 2 代。

6. 設定 RAM，因為就測試給 4G就可

7. 選剛剛建立的那個私人交換器網路

8. 建立硬碟，這就改大小就好，什麼都不改下一步也行

9. 稍後安裝作業系統，我們現在要一次開出四台虛擬機的空殼，所以稍後安裝就好

10. 最後摘要，看一看沒問題就確定

11. 剩下三台 SQL Server node 都按照剛剛的操作方法一樣建空殼起來，ram 換成 8G、硬碟 100G 就可。

12. 去 DC01 那邊按右鍵 > 設定，然後選SCSI控制器 > DVD光碟機新增

13. 選映像擋，然後把 Windows Server 的 iso 放上去按確定

14. 然後就右鍵啟動、連線開始跑安裝

忘記補充，記得去在啟動安裝前先去把 vCPU 調整一下 Domain 2 其他 4不然就要關機才能調整

15. 然後另外三台 SQL NODE 也都一魔一樣的方式去安裝，到此 HYPER-V 就結束。

Windows Server DC設定

1. 先去改四台SERVER 的電腦名稱 DC01、SQLNODE1、SQLNODE2.…

2. 去改 DC01 的固定 IP

我這邊設定 10.10.10.10 是因為我正式環境不是用這個網段，反正就避開正式環境才改IP的，如果妳沒差就不用改。

3. 接下來要安裝 AD + DNS > 選新增角色功能，然後按照我的截圖去選

4. 升級網域，第三步安裝到最後會有一個升級網域要停下來選，不要太早直接把他關閉掉

這個不要勾然後下一步，不要建立 DNS 委派

開始安裝

然後你就會有新網域

5. 網域設定到此結束

開新網域的時候，DNS有可能會變，就去檢查一下現在是不是 10.10.10.10，不是的話改過來就好。

SQL NODE 設定

1. 首先一樣先去改固定 ip

這邊 node1 我設定 21、node2 設定 22、node3 設定 23

2. 設定好之後，打開powershell 去測試能不能連到網域主機，如果可以就加入網域。

ping 10.10.10.10
ping dc01
nslookup dc01.wsfc.lab

3. 都沒問題就加入網域

4. 然後另外兩台也做一模一樣的操作

5. 接下來 3台 SQLNODE 都要裝 Failover-clustering，用系統管理員開 powershell，然後安裝

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

6. 謹慎一點三台都裝完之後在 SQLNODE1 跑 cluster 測試

Test-Cluster -Node SQLNODE1,SQLNODE2,SQLNODE3

7. 然後在 SQLNODE1 上面建立 CLUSTER

New-Cluster -Name WSFC01 -Node SQLNODE1,SQLNODE2,SQLNODE3 -StaticAddress 10.10.10.100

8. 然後回 DC01 去 C曹建立一個資料夾 ClusterWitness，然後設定共用

9. 在 SQLNODE1 上設定 File Share Witness

Set-ClusterQuorum -FileShareWitness "\\DC01\ClusterWitness"

10. 把三台都安裝 SQL SERVER ENTERPRISE版

這邊選這樣就可以

11. 執行個體名稱一定要改PrimaryReplica、SyncHA、AsyncDR

其他的都可以下一步下一步，唯獨這個東西一定要去改他，不然會完全看不懂哪一台是哪一台

12. 離線安裝 SSMS

SSMS 22 開始以後都要連網才能安裝，所以要去找 20以前的EXE檔來裝

13. 開 TCP / IP

去SQL 設定管理那邊，把 TCP / IP 打開，然後把動態通訊清空，把連接不改掉，不要用預設，在這個測試中沒有差，但這是好習慣，至於為什麼在安裝那邊有寫過。

然後記得也去防火牆把 14343 PORT 打開

New-NetFirewallRule -DisplayName "SQL Server SyncHA TCP 14343" -Direction Inbound -Protocol TCP -LocalPort 14343 -Action Allow
New-NetFirewallRule -DisplayName "SQL Server AsyncDR TCP 14343" -Direction Inbound -Protocol TCP -LocalPort 14343 -Action Allow

--另外三台也都要多開一個 AG專用的 PORT 預設是 5022 我會改成 15022
New-NetFirewallRule -DisplayName "SQL Server AG Endpoint TCP 15022" -Direction Inbound -Protocol TCP -LocalPort 15022 -Action Allow

14. 去設定管理員把 Always ON 打開，三台都要

15. 到此基本環境全部建立好了，現在架構會是

DC01 10.10.10.10
AD DS / DNS / File Share Witness

WSFC01 10.10.10.100
Windows Failover Cluster

SQLNODE1 10.10.10.21
SQL Server instance：PrimaryReplica
Always On：Enabled

SQLNODE2 10.10.10.22
SQL Server instance：SyncHA
Always On：Enabled

SQLNODE3 10.10.10.23
SQL Server instance：AsyncDR
Always On：Enabled

連線 Instance port 是 14343
暫定 AG Endpoint 是 15022

一、準備資料庫

模擬的環境是這樣
我會有兩個應用程式 App1、App2

App1 會有他自己的客戶、銷售資料庫

App2 會有他自己的客戶資料庫

然後把這些資料庫都設定成 FULL 模式，這是 AOAG 必要措施。

因為 SIMPLE 會自動截斷可以被截斷的 log，AOAG 又是以傳送 Log 作為同步手段，所以只能選 FULL 這種要做交易紀錄備份才會把 Log 截斷的復原模式，阿不然會沒有 log 可以傳送，同步不了，那也不用做這些了。

現在的操作全部都在 node1

CREATE DATABASE App1Customers ;
GO

ALTER DATABASE App1Customers SET RECOVERY FULL ;
GO

USE App1Customers
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 App1Sales ;
GO

ALTER DATABASE App1Sales SET RECOVERY FULL ;
GO

USE App1Sales
GO

CREATE TABLE [dbo].Orders NOT NULL 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 App2Customers ;
GO

ALTER DATABASE App2Customers SET RECOVERY FULL ;
GO

USE App2Customers
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 ;

完成之後要先做一次完整備份

因為 App1 跟 App2 我會做兩種不同的可用性，所以我這邊先只備份跟 App1 有關的資料庫

BACKUP DATABASE App1Customers
TO DISK = N'C:\Backups\App1Customers.bak'
WITH NAME = N'App1Customers-Full Database Backup' ;
GO

BACKUP DATABASE App1Sales
TO DISK = N'C:\Backups\App1Sales.bak'
WITH NAME = N'App1Sales-Full Database Backup' ;
GO

因為 AOAG 是用交易紀錄來同步資料，所以必須有一個明確的起點，這個起點就是這次的完整備份。

AOAG 的同步流程如下

Primary database
↓
先做 Full Backup，建立一個可還原的基準點
↓
Secondary 用這份 Full Backup 還原出同一個資料庫
↓
之後 Primary 產生的 Transaction Log
↓
持續送到 Secondary
↓
Secondary 套用 Log，保持同步

所以如果要做 AOAG 一定要有一個完整備份當作起點。

二、建立可用性群組 APP1

右鍵高可用性，選新增可用性群組精靈
這邊會要給他一個名字，然後把下面的勾勾打勾

叢集類型沒什麼好講的，因為目前為止都是建立在 WSFC 上，所以當然選 WINDOWS

他有另外兩個選項

外部 : 這是給 Linux / Pacemaker 去用的
無 : 這個會建立一個群組，純的群組，他不會變成 HA / DR，但是可以變成我在 12.1 有說過的就是給單純的報表查詢用的，不會變 HA / DR 的原因是因為這個群組沒有 WSFC 那個健康狀態偵測、仲裁、Failover判斷、Listener 管理等等的。

第一個勾勾資料庫層級健康情況偵測
這是如果 AG 裡面某個資料庫出問題，例如變成 Offline、suspect等等，SQL Server 會把這種狀況回報給 WSFC，讓他去判斷是否需要 Failover

那我勾不勾有什麼差別，不勾的話，WSFC 是去判斷 Instance 還有沒有活著，有勾的話會變成 SQL Server 主動去回報 database 還有沒有活著。

第二個勾勾 DTC支援
這中文是啥我不知道英文是 : Distributed Transaction Coordinator，中文應該翻譯成分散式交易協調器吧?

這是在說如果一個交易會同時動到兩個資料庫，要確保兩個資料庫同時成功或同時失敗。

因為 AG 是以 “資料庫” 做為一單位去同步的。

所以假如說按照我們前面的資料庫架構
今天有一個應用程式同時去 UPDATE 客戶跟訂單

這種情況下，需要 DTC 來協調交易結果，確保這筆交易在不同資料庫之間維持一致性，不會出現一邊成功、一邊失敗的邏輯不一致狀態。

這東西跟交易 ROLLBACK 很像，而且確實 ROLLBACK 可以在跨資料庫交易的時候去做，但問題是現在不是單純的只在單一 INSTANCE 裡面要 ROLLBACK，是要跨 INSTANCE，如果要跨越 INSTANCE 的話，需要有一個人去協調這個交易確保他的一致性

例如我們目標的 HA 架構會是這樣

SQLNODE1 = PrimaryReplica
├─ App1Customers
└─ App1Sales

SQLNODE2 = SyncHA
├─ App1Customers
└─ App1Sales

今天應用程式同時去改客戶跟銷售，他會去改 PrimaryReplica，那當然可以用 Rollback 就可。

但問題是今天要 HA，AOAG 又是以資料庫為單位，所以他會個別傳送交易紀錄到 SyncHA

Customers 的 log 送到 SyncHA
Sales 的 log 也送到 SyncHA

那我不能讓 SyncHA 變成

Customers 有這筆交易

Sales 沒有這筆交易

所以我需要 DTC 出來去確保全部成功或全部失敗。

反正就勾吧沒有壞處
3. 選要成為 AG 一員的資料庫，這邊可以也注意到 App2 不能選，因為我沒有做完整備份過，這就是我剛剛說的，因為他沒有起點。

4. 指定複本先看複本選項

這邊一開始近來應開會只有一個節點就是 PrimaryReplica，所以要按下面那個加入複本去把其他節點也加進來

然後自動容錯移轉把 PrimaryReplica 跟 HA 打勾，因為這是我們做 HA 的目的，可以保證服務不斷，而 DR 的目的是災難發生時我有辦法復原所以不勾。

把自動容錯移轉打勾後，會發現可用性模式自動變成同步認可，這是 AOAG 如果要自動 failover 的話的必要條件。

同步認可 :
當一個應用程式發出交易，SQL Server 會先去次要伺服器把交易 commit 之後，才回到主要伺服器上 commit

非同步認可 :
當一個應用程式發出交易，SQL Server 會直接在主要伺服器上 commit，之後有空才去次要伺服器上 commit。

那這造成的結果大概是這樣
如果用同步，那我可以 99.9999% 保證，發生 failover 時資料不會遺失，因為我先去次要寫入；但是日常期間，因為我要先等次要寫入，才會寫入主要，整個交易才會結束，所以效能會降低。這在 12.1 有講過，就是在這邊選。

那非同步，就變成我無法保證資料不遺失，可能會遺失個幾分鐘，但對主要伺服器效能幾乎沒有影響。

最後一個可讀取次要的選項
他有三個選項

否 :
資料庫在處於次要角色的時候無法存取，不允許連線到次要伺服器的意思

僅限讀取意圖 :
應用程式的連線字串有設定
Application Intent=Read-only
的話，AG 的 Listener 可以將唯讀工作的負載導向這個次要複本。

是 :
不論應用程式有沒有連線字串設定，都可以重新導向這個複本，但一樣是唯讀。

最後雖然你可以亂搞，把 HA 的次要伺服器調成唯讀，但他不會有作用，這是在安裝精靈你可以按但是沒有作用，因為 HA 次要伺服器你給他設定唯讀，那這東西 failover 過去也不能用，所以他會給你選，但是沒用依然可讀寫。

端點設定

這是 AG 內的成員彼此傳輸資料專用的通道。

PORT 預設是 5022 但我不喜歡用預設，所以我改一下

這邊會有一個東西要改是 SQL Server 服務帳戶

預設近來會看到三個帳戶都不一樣，這是一開始在安裝的時候，我有說過的那個狀況

如果一個服務帳戶對應一個 instance，那維護會維護到死，例如每幾個月要改密碼，你用三個帳戶就要去改三個密碼。

還有還要額外確保這三個帳戶到其他 instance 都有該有的權限，否則你 failover 過去沒有權限依然是失敗。

那現在是三個帳號，如果三十個、三百個呢?

用同一帳戶缺點當然也是有，如果這帳號被盜，影響的是全部的 instance，所以如我先前建議，以應用程式的角度去建立帳號，同一個應用程式，就共用一組帳號，兼顧安全與可維護性。

所以這邊要暫停一下，先去 dc01 上面建立帳號，選新增使用者

然後回到 NODE1 去改組態，阿我們剛剛 HA 精靈跑到一半直接把他取消，等等重用就好，因為改帳號會要重啟 INSTANCE

NODE2、NODE3 一樣改
然後再回來重新按一次精靈，就會看到帳戶便一樣了

6. 備份喜好設定

這是 AOAG 的一個大優點，他可以允許用次要伺服器去做備份，這樣主要伺服器就沒有因為要備份而造成的效能損耗

但是，這不是沒有代價的。看我這邊還是選主要去備份就知道。

這代價是 RTO。

如果我今天有一個 RTO 目標，然後現在有人誤刪了 TABLE，要還原，那我得從次要伺服器把備份複製到主要伺服器上，這點馬上就增加 RTO。

另外，如果允許在多台次要伺服器上都可以備份，那你會在多台伺服器之間手忙腳亂地群找所有備份檔，因為 SQL Server 只會在實際執行備份的那個 instance 上維護備份的歷史紀錄。

如果其中一台完整中斷，還可能會陷入交易紀錄鏈中段的情境

所以如果以 DBA 的角度，我不認為應該為了效能，去犧牲這些東西。

當然，這是有替代方案的，我剛才提到的大多數問題，其替代做法是使用 NAS 的共享資料夾，設定每個執行個體都備份到同一個共享位置。

不過，這種做法的問題是，當你這樣設定時，所有備份都會透過網路傳送，而不是在本機執行備份。這可能會增加備份所需時間，也會增加網路流量。

所以團隊要去權衡，要怎麼做。

接聽

設定一個虛擬入口給應用程式去連 AOAG，阿不然應用程式連接字串還是用原本的主資料庫，那主資料庫掛掉的時候，應用程式也不能使用，那 HA 還是失敗這些都白做，所以要讓應用程式去連這個。
選取資料同步處理

自動植入 :
SQL Server 自己把資料庫送到 Secondary。
它會在 Secondary 上自動建立資料庫，然後透過 AG 的資料傳輸機制把資料送過去。
人類不需要自己備份、複製、還原。
但是主要伺服器跟次要伺服器之間的檔案路徑必須相同。

完整的資料庫及記錄備份 :
這個跟上面那個差不多，差別在能不能指定備份路徑，我是建議選這個，跟我前面那個備份喜好設定的邏輯一樣。
不過呢如果選這個，就要去一個地方建立共享資料夾然後分給服務權限。
這時候剛剛把服務帳戶全部變成一個的優點就出來了，我只要去改一個帳號就可以。

僅連結 :
自己手動去完成在次要伺服器上的備份還原
這是有好處的，因為如果資料庫很大的話，你不會希望做這個 HA 初始化花很久時間但是你什麼都不能控制。
所以這選項可以自己手動去分批備份還原
但是選這個的話要先去次要伺服器上還原完整備份，然後一定要選 WITH NORECOVERY，等到次要伺服器上的 DATABASE 處於 RESTROING 狀態時，再回來選這個選項。

略過
這什麼都不會幫你做，只會建立一個 AG 設定而已

我這邊選用完整資料庫及記錄備份。

所以呢我要先去找一個地方建立共用資料夾並且發權限出去，一般實際環境，不會把這個共用資料夾放在 NODE 上，你這樣放相當於沒有 HA，那個 NODE 死了你就沒有備份了。

但我這邊只是演示，而且我又不能上網，所以我會放在 NODE1，一般來說應該要放在 NAS 上。

所以我在 C 槽上建立一個 AOAGShare 資料夾，然後開共用跟權限

如果操作全部跟我一樣，然後按下一步你就會發現報錯了

這是因為 node2、node3 沒有一模一樣的檔案路徑，這問題要馬一開始主資料庫就額外拉出來建，讓三個節點都用一樣的路徑，要馬就是現在跟我一樣，去另外這兩個節點上，也建立一個一模一樣的資料夾，然後給權限。

就會過了

到此 APP1 的 AOAG 建立結束

三、建立可用性群組 APP2

接下來做 App2 的 HA，這次我會用另外一種方式，不是用精靈，用對話框，這種方式有更多的彈性。

這種方式沒辦法讓 SQL Server 跟剛剛一樣透過備份還員還執行初始資料庫同步。只有以下兩種方式可以初始同步

將資料庫備份到前一個示範中建立的共享資料夾，
然後將完整備份與交易記錄備份還原到次要執行個體。
使用 Automatic Seeding。

這邊我會用 Automatic Seeding ，但是一樣要先做完整備份

1. 完整備份

ALTER DATABASE [App2Customers] SET RECOVERY FULL;
GO

BACKUP DATABASE [App2Customers]
TO DISK = N'\\SQLNODE1\AOAGShare\App2Customers_full.bak'
WITH FORMAT, INIT, REWIND, COMPRESSION, STATS = 5;
GO

BACKUP LOG [App2Customers]
TO DISK = N'\\SQLNODE1\AOAGShare\App2Customers_log.trn'
WITH INIT, COMPRESSION, STATS = 5;
GO

2. 建立 endpoint

如果這是一個全新的，才需要去建立這個東西

但是剛剛 App1 已經建過了，一個 instance 只能建立一個所以這裡不用再建了。

如果是全新的要建，可以用下面的語法當參考。所有節點都要跑過一次。

USE master;
GO

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP 
    (
        LISTENER_PORT = 15022,
        LISTENER_IP = ALL
    )
    FOR DATA_MIRRORING
    (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.server_principals
    WHERE name = N'WSFC\sqlsvc'
)
BEGIN
    CREATE LOGIN [WSFC\sqlsvc] FROM WINDOWS;
END
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [WSFC\sqlsvc];
GO

3. 新增可用性群組

因為這次只有一個資料庫，所以剛剛有勾的 DTC、健全狀況偵測都不用勾了，只有一個資料庫有勾沒勾一樣。

但是有一個 要認可的必要同步次數 剛剛 App1 是 0 現在我調成 1

這東西在目前架構下是用來控制資料遺失的或是可用性的。

注意我說的是目前架構。

現在架構是 NODE1 是主要伺服器、NODE2 是同步伺服器用來做 HA、NODE3 是非同步伺服器用來做 DR。

同步伺服器的意思剛剛有講過，就是要等次要伺服器 COMMIT，主要伺服器才會 COMMIT。

那這個 要認可的必要同步次數 意思就是 :
主伺服器在 COMMIT 之前，必須要確認至少有幾台次要伺服器已經 COMMIT 了。

如果這數字是 3 ，那就是必須至少有 3 台次要伺服器 COMMIT，主要伺服器才 COMMIT。
很嚴格的去確保資料不會遺失。

那在現在這個架構下去設定 1 會怎麼樣? 答案是前半段不會怎麼樣，因為本來同步就是會等次要伺服器先 COMMIT，而我們也只有一台次要的同步伺服器。

有差的在後半段 :
如果今天 NODE1 死了
切換到 NODE2
然後，又有設定這個數字 = 1

那會發生，有新交易要寫進 NODE2，但是因為 NODE 1 死了，沒有次要伺服器可以比 NODE2 還要早寫進硬碟，所以 SQL SERVER 會不允許寫入新的交易。但可以保證絕對不掉資料。

如果數字設定跟 App1 一樣= 0

那會變成，有新交易要寫進 NODE2，此時因為沒有那個更嚴格的限制，所以雖然是設定同步，但仍然可以接受新的交易。
如果這時候 NODE2 又死，NODE3 還在追交易追不到，那就有可能會掉資料。

所以 要認可的必要同步次數 比起前面那麼字面上的意思，更重要的是後半段發生 failover 後，你想要什麼效果。

剩下的選項都跟精靈一樣，只有一個工作階段逾時(秒) 這是設定複本之間多久沒有收到彼此的 ping 之後，才會進入 DISCONNECTED 狀態並結束 session。

4. 備份喜好設定，這個一樣

5. endpoint

第三點有認真看的話會注意到，端點 URL，那邊的 PORT 是 5022，但是我們前面設定的是 15022

可是這裡不能改，所以呢要把這一切倒出成指令，然後再去改他，再執行

USE [master]
GO
CREATE AVAILABILITY GROUP [App2]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1)
FOR DATABASE [App2Customers]
REPLICA ON N'SQLNODE1\PRIMARYREPLICA' WITH (ENDPOINT_URL = N'TCP://SQLNODE1.wsfc.lab:15022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 60, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
	N'SQLNODE2\SYNCHA' WITH (ENDPOINT_URL = N'TCP://SQLNODE2.wsfc.lab:15022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 60, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
	N'SQLNODE3\ASYNCDR' WITH (ENDPOINT_URL = N'TCP://SQLNODE3.wsfc.lab:15022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 60, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

然後就會看到有新群組，但是次要伺服器都還沒有連上線，這是因為還沒有接聽

6. 建立接聽

7. 最後去賦予權限

若要讓 Automatic Seeding 正常運作，必須在次要伺服器上授與可用性群組 CREATE ANY DATABASE 權限。

去 node2、node3 建立

ALTER AVAILABILITY GROUP [App2] JOIN;
GO

ALTER AVAILABILITY GROUP [App2] GRANT CREATE ANY DATABASE;
GO

就完成了，App2 HA 到此結束

四、內含式可用性群組

概念

如果很仔細的看，會發現上面在做的時候，有一個東西沒有講過也沒有勾選過，就是內含。

這東西翻譯成這樣我頭也是很痛

簡單說這是執行個體層級的物件。

在以前，資料庫依賴的執行個體物件，例如 login、sql agent job等等的，這些不會在 node 上面複寫。

這表示 DBA 要手動去同步這些執行個體層級物件。有人是真的會去手動啦，也有人寫一些 powershell、puppet 去搞自動化。

那如果不做這些執行個體的複寫會怎樣? 那你的 HA 依然失敗，因為沒有這些執行個體層級的複寫，應用程式依然無法使用，你只是確保資料庫而已，沒有確保應用程式，所以在一開始 HA 概念的時候我就有先說，一定是要以應用程式的角度去做 HA 才有意義。

不過呢上述方法都有作業上的考量。DBA 必須隨著執行個體層級需求的演變，持續更新自己的自動化流程。我們也需要監控這些自動化流程，以便在發生容錯移轉時，將停機時間降到最低。此外，這類組態管理方案通常需要 DBA 具備一定程度的平台工程能力，才能開發與維護這套解決方案。

所以 SQL Server 2022 開始，引入了這個 “內含”，用來解決這個問題，以下簡稱 CAG。

這個功能會為每一個 CAG 建立一份額外的 master & msdb 資料庫複本，並讓他們隨著可用性資料庫一起複寫。

但是這個功能並不是真的復寫系統資料庫本身，這邊是複寫這些系統資料庫的副本，而這些副本只支援這個 CAG，所以不是說拿這個複寫可以到處去用。

假設我現在建立一個新的 AG，叫做 App3 :
SQL Server 會建立類似 master_App3、msdb_App3 的東西，從這裡也可以看出他不是真的執行個體資料庫備份。
而這個東西，會依賴 AG Listener，一定要連上 Listener 你才會看到正常的 master、msdb。

但是這東西有一個奇怪的副作用，我不知道為什麼微軟沒改
就是如果你用 Listener 去連

那確實你只會看到
master
tempdb
model
msdb
使用者資料庫
使用者資料庫

但是你如果不是用 Listener 去連，例如用本機連，你就會看到
master
tempdb
model
msdb
master_App3
msdb_App3
使用者資料庫
使用者資料庫

依照剛剛的說法，master_App3、msdb_App3 這是專門給 Listener 用的，所以用 Listener 去看，會看到正常的東西，其實實際上看到這兩個。

那這會造成一個問題 : 資料庫 ID 不一致

由上到下，在 Listener 視角，master 是 1、tempdb 是 2、model 是 3、msdb 是 4、使用者資料庫是 5….
但是在本機的視角，master 是 1、tempdb 是 2、model 是 3、msdb 是 4、master_App3 是 5、msdb_App3 是 6、使用者資料庫是 7.…

那這會造成，因為平常我在寫維護腳本的時候

備份所有使用者資料庫
重建索引
更新統計資料
檢查 DBCC CHECKDB

我都會下一個條件 : WHERE database_id > 4，因為一般前四個系統資料庫不需要去做這些維護

如果沒有特別注意這個 id 的不同就去備份、維護、重建索引、做一些不該做的事，
那頭就會很痛。

所以如果有做這個 CAG，要記得排除

name NOT LIKE N'master[_]%' AND name NOT LIKE N'msdb[_]%'

實作CAG

`一樣都是在 NODE1 做`

1. 一樣先建立資料庫來測試叫做 App3Sales，然後隨便塞資料

CREATE DATABASE App3Sales;
GO

ALTER DATABASE App3Sales SET RECOVERY FULL;
GO

USE App3Sales;
GO

CREATE TABLE [dbo].[Orders]
(
    [OrderNumber] [int] IDENTITY(1,1) NOT NULL 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
);
GO

DECLARE @Numbers TABLE
(
    Number INT
);

;WITH CTE(Number)
AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM CTE
    WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number
FROM CTE;
GO

USE App3Sales;
GO

DECLARE @Numbers TABLE
(
    Number INT
);

;WITH CTE(Number)
AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM CTE
    WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number
FROM CTE;

INSERT INTO dbo.Orders
(
    OrderDate,
    CustomerID,
    ProductID,
    Quantity,
    NetAmount,
    TaxAmount,
    InvoiceAddressID,
    DeliveryAddressID,
    DeliveryDate
)
SELECT
    (
        SELECT CAST(DATEADD(DAY,
            (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(DAY,
            (SELECT TOP 1 Number - 10 FROM @Numbers ORDER BY NEWID()),
            GETDATE()
        ) AS DATE)
    )
FROM @Numbers AS a
CROSS JOIN @Numbers AS b
CROSS JOIN @Numbers AS c;
GO

2. 然後完整備份他

BACKUP DATABASE App3Sales
TO DISK = N'C:\Backups\App3Sales.bak'
WITH NAME = N'App3Sales-Full Database Backup',
     INIT,
     COMPRESSION,
     STATS = 5;
GO

3. 跟 App1 一樣選精靈，只是這次會選內含

重複使用系統資料庫，這個是如果以前有做過 CAG 他可以，但是出於某種原因把它砍了，但是 master_App3 還在，然後現在又要重做 CAG，那就可以勾這個，他就不會重建一個會沿用之前的。

4. 剩下的步驟就跟App1 一模一樣

5. DB ID

這時候就可以去看我剛才說的內含造成的問題

這是我用本機的方式去聯 instance 的結果

SELECT 
    name,
    database_id
FROM sys.databases
ORDER BY database_id;

這是我用 App3 Listener 去連看到的結果

內含到此結束

管理

設定完之後還有一些管理工作要做，例如容錯移轉、監控、新增額外 Listener

手動Failover

同步模式下，有故障會自動移轉

但這也可以手動移轉，可以當作測試或是幹嘛的

下面那個有警告標示，那意思是資料有可能會遺失，因為那是非同步，但我們從建好到現在從來都沒有改過資料，所以其實他不會遺失任何東西。

要注意這邊是強迫具名的連線方式，如果 100% 全新的，100% 按照這篇文章的步驟去做，可能會在這裡卡住連不上。
這是因為預設沒有開具名連線、沒有開Browser、沒有開 UDP 1434 port
去把這些打開就可以，前兩個在 SQL 設定管理員那邊，1434在防火牆。
這邊 1434 跟 TCP 那個不一樣，這個寫死的不能改，在設定INSTANCE 那邊有講過原理。

成功後就可以去看到 App1 在 Node1 變次要、在 Node2 上變主要

也可以用語法簡單執行，但注意要在即將成為主要的那一台上面執行

ALTER AVAILABILITY GROUP App1 FAILOVER;

那如果要切換到非同步的那一台的話，就要注意有可能會掉資料然後語法要改用

ALTER AVAILABILITY GROUP App1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

監控

有兩種方法去監控

System Operations Center 這是超大型企業實作了很多個 AG，要有效且全面的監控的唯一方法

SQL Server AlwaysOn Dashboard 和 AlwaysOn Health Trace 這是如果只有少量 AG 的可行方案

AlwaysOn Dashboard

右鍵 AG 群組就可以看

只有出現未同步這三個字，才是有問題其他的都正常

啊非同步那個會永遠顯示同步中，那也正常

這裡只能看到同步狀態，但還有一個作業狀態可以看要用這個看

SELECT
    ag.name AS [可用性群組名稱],
    ar.replica_server_name AS [複本名稱],

    CASE ars.is_local
        WHEN 1 THEN N'本機複本'
        WHEN 0 THEN N'遠端複本'
    END AS [是否為本機],

    ars.role_desc AS [目前角色],
    CASE ars.role_desc
        WHEN 'PRIMARY' THEN N'主要複本'
        WHEN 'SECONDARY' THEN N'次要複本'
        WHEN 'RESOLVING' THEN N'解析中'
        ELSE ars.role_desc
    END AS [目前角色_中文],

    ars.operational_state_desc AS [作業狀態],
    CASE ars.operational_state_desc
        WHEN 'ONLINE' THEN N'線上'
        WHEN 'OFFLINE' THEN N'離線'
        WHEN 'PENDING' THEN N'擱置中'
        WHEN 'PENDING_FAILOVER' THEN N'容錯移轉擱置中'
        WHEN 'FAILED' THEN N'失敗'
        WHEN 'FAILED_NO_QUORUM' THEN N'失敗_沒有仲裁'
        ELSE ars.operational_state_desc
    END AS [作業狀態_中文],

    ars.connected_state_desc AS [連線狀態],
    CASE ars.connected_state_desc
        WHEN 'CONNECTED' THEN N'已連線'
        WHEN 'DISCONNECTED' THEN N'已中斷'
        ELSE ars.connected_state_desc
    END AS [連線狀態_中文],

    ars.recovery_health_desc AS [復原健康狀態],
    CASE ars.recovery_health_desc
        WHEN 'ONLINE' THEN N'線上'
        WHEN 'ONLINE_IN_PROGRESS' THEN N'正在上線'
        ELSE ars.recovery_health_desc
    END AS [復原健康狀態_中文],

    ars.synchronization_health_desc AS [同步健康狀態],
    CASE ars.synchronization_health_desc
        WHEN 'HEALTHY' THEN N'健康'
        WHEN 'PARTIALLY_HEALTHY' THEN N'部分健康'
        WHEN 'NOT_HEALTHY' THEN N'不健康'
        ELSE ars.synchronization_health_desc
    END AS [同步健康狀態_中文],

    ars.last_connect_error_number AS [最後連線錯誤代碼],
    ars.last_connect_error_description AS [最後連線錯誤描述],
    ars.last_connect_error_timestamp AS [最後連線錯誤時間]
FROM sys.dm_hadr_availability_replica_states AS ars
INNER JOIN sys.availability_replicas AS ar
    ON ars.replica_id = ar.replica_id
INNER JOIN sys.availability_groups AS ag
    ON ars.group_id = ag.group_id
ORDER BY
    ag.name,
    ar.replica_server_name;

可能的作業狀態包括：

PENDING_FAILOVER
PENDING
ONLINE
OFFLINE
FAILED
FAILED_NO_QUORUM
NULL（當 Replica 已斷線時）

AlwaysOn Health Trace

這是一個擴充事件，他會隨著第一組 AG 建立時自動建立，後面會再詳細講擴充事件是什麼

透過它的內容選單，你可以檢視目前正在擷取的即時資料，或是進入該 session 的屬性，變更它所擷取的事件設定。

深入展開這個 session 後，會看到該 session 的 package。從 package 的內容選單中，你可以檢視先前已擷取的事件。

單人模式

AOAG 會有一些限制。

其中最嚴格的限制之一就是 : 不能設定成單人模式跟不能設定成 READ-ONLY

這會影響在維護時，如何將應用程式放入安全狀態。

所以等等下面演練的 DR SOP 我會去停用 LOGIN 而不是直接切換成單人模式就是這個原因，我當然也知道切換單人模式最快，但 AOAG 限制不能，所以我只好停用其他 LOGIN。

如果今天真得有一個萬不得已的需求一定要切成單人模式，那就必須先把 DB 從 AG 中移除。

ALTER DATABASE App2Customers SET HADR OFF;

如果是同步狀態下的 AG ，

有效能需求或某種需求，也可以暫停某 DB 在 AG 之間傳送資料

ALTER DATABASE App2Customers SET HADR SUSPEND;
GO --暫停

ALTER DATABASE App2Customers SET HADR RESUME;
GO -- 繼續

DR 發生時的 SOP

`只有在完全崩潰、沒有別的選擇、所有同步節點都死光、完全沒辦法拿到結尾交易紀錄備份的時候，才會去做 FORCE_FAILOVER_ALLOW_DATA_LOSS`

正常情況下遵循下列步驟去做切換或是演練

停用登入

阻止應用程式繼續寫入資料庫

-- 這是停用登入的語法，先找出 AG 裡面有哪些 DB
-- 然後找 DB 有哪些 Login
-- 最後再停用他們，除了現在登入操作的那一個
DECLARE @AOAGDBs TABLE
(
    DBName NVARCHAR(128)
);

INSERT INTO @AOAGDBs
SELECT database_name
FROM sys.availability_groups AG
INNER JOIN sys.availability_databases_cluster ADC
    ON AG.group_id = ADC.group_id
WHERE AG.name = 'App2';

DECLARE @Mappings TABLE
(
    LoginName NVARCHAR(128),
    DBname NVARCHAR(128),
    Username NVARCHAR(128),
    AliasName NVARCHAR(128)
);

INSERT INTO @Mappings
EXEC sp_msloginmappings;

DECLARE @SQL NVARCHAR(MAX);

SELECT DISTINCT @SQL =
(
    SELECT 'ALTER LOGIN [' + LoginName + '] DISABLE; ' AS [data()]
    FROM @Mappings M
    INNER JOIN @AOAGDBs A
        ON M.DBname = A.DBName
    WHERE LoginName <> SUSER_NAME()
    FOR XML PATH ('')
);

EXEC(@SQL);
GO

把原本非同步那一台，也就是現在實作中的 NODE3 從非同步切換成同步

盡可能讓他的交易追到跟主資料庫一樣新

-- 把原本非同步那一台 改成同步
ALTER AVAILABILITY GROUP App2
MODIFY REPLICA ON N'SQLNODE3\ASYNCDR' WITH
(AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
GO

執行 Failove

-- 執行 FAILOVER
ALTER AVAILABILITY GROUP App2 FAILOVER;
GO

改回非同步

-- 改回非同步
ALTER AVAILABILITY GROUP App2
MODIFY REPLICA ON N'SQLNODE3\ASYNCDR' WITH
(AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
GO

啟用登入

-- 重啟 LOGIN
DECLARE @AOAGDBs TABLE
(
    DBName NVARCHAR(128)
);

INSERT INTO @AOAGDBs
SELECT database_name
FROM sys.availability_groups AG
INNER JOIN sys.availability_databases_cluster ADC
    ON AG.group_id = ADC.group_id
WHERE AG.name = 'App2';

DECLARE @Mappings TABLE
(
    LoginName NVARCHAR(128),
    DBname NVARCHAR(128),
    Username NVARCHAR(128),
    AliasName NVARCHAR(128)
);

INSERT INTO @Mappings
EXEC sp_msloginmappings;

DECLARE @SQL NVARCHAR(MAX);

SELECT DISTINCT @SQL =
(
    SELECT 'ALTER LOGIN [' + LoginName + '] ENABLE; ' AS [data()]
    FROM @Mappings M
    INNER JOIN @AOAGDBs A
        ON M.DBname = A.DBName
    WHERE LoginName <> SUSER_NAME()
    FOR XML PATH ('')
);

EXEC(@SQL);
GO

當然，這是理想我還能操作主資料庫的時候，要是真的完全沒辦法了

那才有用 FORCE_FAILOVER_ALLOW_DATA_LOSS 的理由強制切換過去，但要知道可能會損失資料，而且損失多少不知道。

【基礎】 15.HA & DR 概念

【基礎】 17.LogShipping 實作

系列文

SQL Server 基礎&調教共 17 篇

RSS系列文訂閱系列文

3 人訂閱

完整目錄

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

232 組

團體組數

7 組

累計文章數

1822 篇

最後報名日

9/15

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 17th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# 18th鐵人賽 linux windows server css

IT邦幫忙

SQL Server 基礎&調教系列 第 16 篇