SQL Server 提供了多種技術，讓我們能夠將工作負載水平擴展到多個資料庫之間，以避免鎖定競爭；也可以將工作負載水平擴展到多台伺服器之間，以分散資源使用率。

這些技術包括資料庫快照、複寫，以及 AlwaysOn 可用性群組。
AlwaysOn講過了、複寫不好用
這篇著重講快照

快照

DB 快照是 DB 在某一個時間的檢視，而且產生之後不會再改變，他使用 copy-on-write 技術運作。

他會被複製到一個 NTFS 的 Sparse File 中

Sparse File :
是一種一開始為空、尚未配置的磁碟空間檔案。
如果配置 100 GB，系統的確會顯示 100 GB 已被占用。
但這是占用不是使用，他實際可能才幾 MB，他是預先圈地。

例如 :
假設建立快照的時候，資料長這樣
Page A = 100
Page B = 200
Page C = 300
此時快照 Sparse File 不會有任何東西，這些資料都在原本的 data file

然後做了一個 Update 動作
Update PageA = 999

然後又做了一個 Delete 動作
Delete PageB

這個時候因為快照 Sparse File 就會去填入舊版本資料
PageA = 100
PageB = 200

所以，快照這東西就是去看當下的版本，如果這個版本有變動他就記錄起來，沒有的話她就不紀錄。

那 INSERT 呢?
這個比較複雜一點，因為 INSERT 一定是新紀錄，當下快照的時候一定沒有這筆，所以就算 INSERT 了，也不會出現在快照的 FILE 裡面。

但是這並不代表快照的 Sparse File 都沒有變。

因為 page 的 metadata 還是會因為這一筆 insert 變了，所以快照是會去存這個 metadata 的，只是一般使用下無感。

從上面案例就可以知道， 快照並不會存取整個資料庫所有內容，只有快照後變更的才會。
也就是說，如果今天去 SELECT 快照資料庫，我可以查到
Page A = 100
Page B = 200
Page C = 300

如果有充分理解上面說的快照運作方式，到這裡應該要有一個疑問 : PageC 從來沒改過沒刪掉過，會什麼也查得到?

這是快照的查詢機制，你今天去查快照資料庫，快照裡面有的，他會查給你看；快照裡面沒有的，他會去主資料庫查給你看。

所以 SELECT 出來看得到這三筆其實 A、B 是在 Sparse File，而 C 是在原本的 DATA FILE。

快照存在的理由

這大概是唯一理由 : 現行環境資料庫業務因為鎖定造成的競爭而受影響。

要注意我說的是鎖定競爭

快照限制在同一個 INSTANCE 上使用，所以這沒有辦法幫助解決資源使用率過高的問題，反而會讓資源使用更多。

因為任何要被修改的資料都要複製到 Sparse File 中，這一定會造成 I/O 負擔增加、記憶體占用增加 ( 每個 DB 的 PAGE 都會在 BUFFER CACHE 中被重複保存 )。

所以只有鎖定競爭的時候，你才會想到要用快照的方式去解決。

如果正在執行大量 I/O 的工作時，這個時候不要保留快照。

大量 I/O 工作不要保留的快照，原理之一是 Ghost record。

Ghost record :
SQL Server 在執行 DELETE 的時候，很多時候不是立刻把 ROW 從 PAGE 上面物理移除。
他通常會把那筆 ROW 標記成 : 已刪除，查詢不要看到。
這種用標記的，但其實還沒有實際刪除的，就叫做 ghost record。

Ghost cleanup :
這是一個程序，上面那個 ghost record 發生之後，會有一個這個 cleanup 程序去處理，在事後把它真正的從物理上抹除。

如果我今天資料庫要做 rebuild index 這種大量 I/O 工作，然後這個資料庫又有快照，那有可能會永遠做不完。

因為 REBUILD INDEX 會去大量的 PAGE 修改 / 刪除，每一次修改刪除，快照就要先 COPY 舊的 PAGE 到 SPARSE FILE，與此同時，ghost cleanup 又可能想要清理那些被刪掉的 ghost records。

結果就變成 :
copy-on-write 要處理舊 page
ghost cleanup 也要處理被刪除的 row/page
兩邊都在搶同一批資料頁或相關資源
在極端情況下，會造成嚴重的 blocking，導致永遠做不完。

要解決這個問題，最好的處理方式就是，有大量 I/O 工作的時候，先停用快照。

但是如果你非得在有快照的狀態下去執行這個工作，那還有一個辦法可以處理。

TF 661

這個是用來停用 ghost cleanup 程序的。

但一樣，前面有說過，任何 Trace Flag，都必須在窮盡最後手段、萬不得已的時候再去用。

而且要非常清楚，這東西用了，會有什麼後果。

這東西停用的話的確就不會有上面說的大量 I/O 又快照的 BLOCKING 問題

但是呢會變成，資料邏輯上刪除了，但 page 裡面還堆著很多 ghost records

會造成
資料頁空間不能正常回收
index 變膨脹
table / index 變肥
scan 變慢
I/O 增加
空間使用異常

要手動去清理掉。

快照實作

先建立測試資料庫

注意有一個跟以往不同的是我故意 VARBINARY(8000)
這是為了後面測是快照的資料頁變化方便用的。

CREATE DATABASE SNAP;
GO

USE SNAP
GO

CREATE TABLE Customers
(
    ID                  INT PRIMARY KEY IDENTITY,
    FirstName           NVARCHAR(30),
    LastName            NVARCHAR(30),
    CreditCardNumber    VARBINARY(8000)
);
GO

--填入資料

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 Customers(FirstName, LastName, CreditCardNumber)
SELECT
    (
        SELECT TOP 1 FirstName
        FROM @Names
        ORDER BY NEWID()
    ) AS FirstName,
    (
        SELECT TOP 1 LastName
        FROM @Names
        ORDER BY NEWID()
    ) AS LastName,
    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()
        )
    ) AS CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b;

然後建立資料庫快照

這裡有個細節是我的檔案副檔名是 ss。

有一些人會用 ndf 為了避免公司防毒去擋這個新的附檔名，但我還是建議可以的話用 ss 比較好辨識

CREATE DATABASE SNAP_ss_0626
ON PRIMARY
(
    NAME = N'SNAP',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.PRIMARYREPLICA\MSSQL\DATA\SNAP_ss_0626.ss'
)
AS SNAPSHOT OF SNAP;

模擬1 有人不小心砍了 TABLE 把她還原回來

--截斷資料表

TRUNCATE TABLE SNAP.dbo.Customers;

--允許重新插入 Identity 值

SET IDENTITY_INSERT SNAP.dbo.Customers ON;

--插入資料

INSERT INTO SNAP.dbo.Customers
(
    ID,
    FirstName,
    LastName,
    CreditCardNumber
)
SELECT *
FROM SNAP_ss_0626.dbo.Customers;

--關閉 IDENTITY_INSERT

SET IDENTITY_INSERT SNAP.dbo.Customers OFF;

其實就是很簡單的 INSERT 而已

模擬2 資料庫被有人搞到錯很多，所以想一次全部復原

USE Master
GO

RESTORE DATABASE SNAP
    FROM DATABASE_SNAPSHOT = 'SNAP_ss_0626';

複寫

就是複製資料到別的地方去

有三種複寫方法 快照、交易、合併。

快照式複寫

這是定期把一批資料完整打包，然後整包送到另一台 SQL Server。

快照式複寫除了同步期間之外，沒有額外負擔。不過，當同步發生時，如果需要複寫的資料量很大，資源額外負擔可能會非常高。

沒有額外負擔是說因為快照式複寫，他不在意這之間發生了什麼交易，他只在乎他要打包的時候資料長怎樣而已。

由於這種資源使用特性，快照式複寫最適合用於要複寫的資料集較小、變更不頻繁的情境；或者是在短時間內會發生大量變更的情境。例如每月更新一次的價格表。

此外，快照式複寫也是用來執行交易式複寫與合併式複寫初始同步的預設機制。

當使用快照式複寫時，Snapshot Agent 會在發行者上執行，以產生發行集。Distribution Agent 則會在散發者或每個訂閱者上執行，然後將發行集套用到訂閱者。

快照式複寫永遠只會單向運作，這表示訂閱者永遠無法更新發行者。

交易式複寫

交易式複寫的運作方式，是從發行者的交易記錄中讀取交易，然後將這些交易傳送到訂閱者上重新套用。

Log Reader Agent 會在發行者上執行並讀取交易，而且在所有標記為要複寫的記錄都處理完成之前，VLF 不會被截斷。這表示如果兩次同步之間間隔很長，而且期間發生大量資料修改，你的交易記錄可能會成長，甚至可能耗盡空間。

交易從交易記錄中讀取完成後，Distribution Agent 會將交易套用到訂閱者。在推送訂閱模型中，這個代理程式會在散發者上執行；在提取訂閱模型中，則會在每個訂閱者上執行。

同步作業是由 SQL Server Agent 作業排程的，這些作業會設定為執行複寫代理程式。你可以依需求設定同步方式，讓它持續進行，或定期執行。初始資料同步預設會使用 Snapshot Agent 執行。

交易式複寫通常用於伺服器對伺服器的情境，特別是在發行者上有大量資料修改，而且發行者與訂閱者之間有可靠網路連線的情況。全球資料倉儲就是一個例子，例如將部分資料複寫到區域資料倉儲。

標準交易式複寫永遠只會單向運作，這表示訂閱者不可能更新發行者。

不過，SQL Server 也提供對等式交易式複寫。在對等式拓撲中，每一台伺服器都同時扮演發行者與訂閱者，並與拓撲中的其他伺服器互相複寫。這表示你在任何一台伺服器上所做的變更，都會被複寫到拓撲中的所有其他伺服器。

因為所有伺服器都可以接受更新，所以可能會發生衝突。因此，當每個對等節點都接受不同資料分割區上的更新時，對等式複寫最適合使用。

如果發生衝突，可以設定 SQL Server 套用具有最高 OriginatorID 的交易。OriginatorID 是指派給拓撲中每個節點的唯一整數。你也可以選擇手動解決衝突，而這是建議的做法。

如果無法將可更新資料在各節點之間分割，而且很可能會發生衝突，那麼會發現合併式複寫是更好的技術選擇。

合併式複寫

合併式複寫允許同時更新發行者與訂閱者。

這很適合用於用戶端／伺服器情境，例如行動業務人員可以在筆記型電腦上輸入訂單，之後再與主要銷售資料庫進行同步。

它也可以用於某些伺服器／伺服器情境，例如區域資料倉儲先透過 ETL 流程更新，然後再彙總到全球資料倉儲。

合併式複寫的運作方式，是在發行集中作為發行項目的每個資料表上維護一個 rowguid。如果資料表沒有設定 ROWGUID 屬性的 uniqueidentifier 欄位，合併式複寫就會加入一個。

當資料表發生資料修改時，會觸發一個 trigger，用來維護一系列變更追蹤資料表。當 Merge Agent 執行時，它只會套用該資料列的最新版本。

這表示追蹤變更所需的資源使用量很高，但相對地，真正同步變更時的額外負擔最低。

因為訂閱者和發行者都可以被更新，所以資料列之間可能會發生衝突。可以使用衝突解析器來管理這些衝突。

合併式複寫內建提供 12 種衝突解析器，包括最早者勝出、最新者勝出，以及訂閱者永遠勝出。也可以自行撰寫以 COM 為基礎的衝突解析器，或選擇手動解決衝突。

因為合併式複寫可以用於用戶端／伺服器情境，所以它提供一種稱為 Web Synchronization 的技術，用來更新訂閱者。

當使用 Web Synchronization 時，在擷取變更之後，Merge Agent 會向 IIS 發出 HTTPS 要求，並以 XML 訊息的形式將資料變更傳送給訂閱者。

Replication Listener 和 Merge Replication Reconciler 是在訂閱者上執行的程序，它們會處理資料變更，然後將訂閱者上所做的任何資料修改傳回發行者。

跟高可用性的區別

其實複寫就是把資料般到別的地方去的一種功能。

這聽起來跟 AOAG、Log Shipping 很像。

但是他只是很像，其中還是有很大的差別。

1. HA 通常只能指定一整個 DATABASE，而複寫可以只複寫某些 Table、欄位甚至可以下 WHERE 去複寫。
2. HA 的目標伺服器不能去新增 SCHEMA、INDEX，但是複寫的可以加索引、改結構，讓報表查詢的時候更好用。
3. AOAG 要求很多，複寫可以隨便你複製，但是維護複雜度跟 HA 比不會比較簡單。
4. HA 架構不允許次要伺服器變更修改資料，合併式複寫可以支援多點的資料同步場景，例如分公司離線接單、業務離線更新等到接回公司內網的時候再一起同步。

實作交易式複寫 ( 發行 )

這次會用兩個 INSTANCE

SQLNODE1、SQLNODE2

但是 SQLNODE1、SQLNODE2 在安裝的時候都沒有勾複寫，所以要先去把這功能安裝起來

兩台都要

1. 首先要去把 INSTANCE 設定成發散者

2. 選初始同步資料夾

這邊最好去選網路共用資料夾，因為如果是本機資料夾的話，就不支援提取訂閱。


下一步下一步就完成了，非常簡單，這裡什麼都不用調。

3. 設定發行集

選要發行的TABLE

然後這邊有一堆東西可以調

加入篩選條件，要將資料分割到多個訂閱者之間時，這特別有用

我隨便設定一個 ID > 500

執行快照的Agent 至少要有 distribution 資料庫中的 db_owner 角色。

實作交易式複寫 ( 訂閱 )

散發代理位置

交易式複寫大概有三個角色 :

發行者，資料來源

散發者，中轉站

訂閱者，接收資料的

其中，散發者的工作就是，把資料變更的紀錄，推送到訂閱者上套用

這裡的選項就是，散發者，應該要在哪裡跑?

發送 或是

提取

發送 或是又叫推送

Distributor 主動把資料推給訂閱者

路徑是

Publisher → Distributor
↓
Distribution Agent 跑這裡
↓
Subscriber

這種適合再少量訂閱的時候，因為只有一兩台的話 push 很簡單，全部都在 Distributor 伺服器上做 ok

提取

Distribution Agent 跑在訂閱端，訂閱端自己主動去 Distributor 那邊拉資料回來

路徑是

Publisher → Distributor
↑
|
Subscriber 自己跑 Agent 去拿資料

可以看出來，整個複寫結構，發行者一定是先推送去散發者。

散發者存在的目的就是為了避免訂閱者，去消耗發行者效能，所以才多中轉一層。

下一個選項因為目前沒有資料庫所以要選新增

點訂閱者連接

這邊要注意帳號權限

使用推送時這個帳號應該具有

1. distribution database 的 db_owner
2. 是 publication access list 成員
3. 對快照所在共用資料夾有讀取權限

使用提取時

1. 是訂閱資料庫的 db_owner
2. 是 publication access list 成員
3. 對快照所在共用資料夾有讀取權限
4. 在訂閱者上具有檢視伺服器狀態的權限
   

剩下的都預設下一步就好