SQL Server Always On Availability Groups Initial Data Synchronization

SQL Server Always On Availability Groups Initial Data Synchronization

• Automatic seeding
• Full database and log backup
• Join only
• Skip initial data synchronization

先講建議: 

一般來說建置Always on，建議使用Join only或Skip initial data synchroniztion會比較少遇到問題

Automatic seeding

1.精靈會在背景執行以下動作

SQL Server performs the following steps to add a database using Automatic Seeding.

1. SQL Server performs a full database backup using Microsoft SQL Server Virtual Device Interface (VDI) full database
2. This VDI database backup is streamed through the network to all available secondary replicas
3. Secondary replica restores this streamed backup
4. Once the database restoration is complete, it is added into the availability group

https://www.sqlshack.com/automatic-seeding-in-always-on-availability-groups/

2.與下一個選項Full database and log backup類似，不同的是主節點執行的備份會串流直接傳送到次要節點，還原並加入AG

3.須符合各項需求才能成功，並且需考量網路流量

此選項會每個節點狀態是否符合需求與且做資料庫備份與傳輸，故會使用較多網路通訊(網路流量)

案例:

The 600 GB databases took about 66 minutes to seed across the network from a primary replica to the secondary replica.  I noticed 1.4 Gbps of consistent throughput during the seeding process.

https://johnsterrett.com/2016/06/29/automatic-seeding-vldb-sql-server-2016/

官方文件: Automatically initialize Always On Availability group.

Full database and log backup

指定一個網路分享路徑後，精靈會在主要執行備份，並在次要節點執行還原並將資料庫加入AG

PS.須符合各項必要需求才能成功

Join only

次要節點需事先執行 手動準備次要資料庫，作法在最下方

也就是次要資料庫已手動復原資料庫完成

選擇這個選項，精靈會嘗試將次要資料庫加入AG

Skip initial data synchronization

跳過資料同步，也就是先跳過次要資料庫的加入

Always on設定精靈完成結束後，管理員需手動在各台次要節點，準備次要資料庫並手動加入AG

手動準備次要資料庫

手動準備次要資料庫的方法 To Prepare Secondary Databases Manually