SQL Server Log Shipping
2023-04-07
筆記 SQL Server Log Shipping 的設定方式以及應用作為 HA / DR 以及 Migration 解決方案的方式。
說明
Log Shipping 由於無法主動 Failover，更適合使用的情境是 Disaster Recovery 而非 High Availability。 藉由 Log Shipping，以交易紀錄的方式達到同步出第二台，甚至是多台資料庫伺服器。這些同步後的伺服器則可以做為備援的節點，在原本的伺服器發生狀況的時候接手工作 (但需要人工介入，例如調整應用程式的連線字串或者是調整資料庫伺服器的 IP 等方式)。此外也可以用 Log Shipping 的方式作為同步遷移 (Migration) 資料庫的措施。
Log Shipping 是 HA / DR 設定門檻最低的解決方案，不需要 WSFC、沒有相同網域的限制，並且只需要標準版的授權即可使用。
實作的原理是藉由主要資料庫伺服器，透過交易紀錄備份，提供次要資料庫伺服器還原使用，以保持資料的同步。同時可以再搭配 Optional 的 Monitor 資料庫伺服器，確保藉由交易紀錄達成的同步有正常運作。
啟用 Log Shipping 會需要保存交易紀錄，可以搭配備份壓縮來減少所產生的資料量。
Benefits About Log Shipping
-
Disaster recovery: Log shipping can be used as a disaster recovery solution to maintain a secondary copy of the database at a remote location. In the event of a primary server failure, the secondary server can be used to bring the database back online with minimal data loss.
-
Reduced downtime: With log shipping, the secondary database is kept in sync with the primary database, which means that in the event of a failure, failover to the secondary database can be done quickly and with minimal downtime.
-
Customizable failover: Log shipping allows you to customize the failover process to meet your specific business needs. You can choose to manually failover to the secondary database, or you can set up automatic failover based on specific criteria such as database health or network latency.
-
Multiple secondary databases: You can configure log shipping to maintain multiple secondary databases, which provides added redundancy and availability for your critical data.
-
Granular backup and recovery: With log shipping, you can choose to backup and restore individual transaction logs, which allows for more granular backup and recovery options.
-
Cost-effective: Log shipping is a cost-effective solution for disaster recovery since it does not require expensive hardware or software.
Architecture
Servers
- Primary database server
- The primary database server is the server that contains the database that is being backed up and copied to a secondary server.
- Secondary database server
- The secondary database server is the server that receives and restores the transaction log backups from the primary server.
- Monitor server
- The monitor server is an optional component of log shipping that provides a centralized location for monitoring and managing log shipping.
Jobs
- Backup job
- This job is responsible for taking transaction log backups of the primary database on a scheduled basis.
- Copy job
- This job is responsible for copying the transaction log backups from the primary server to the secondary server.
- Restore job
- This job is responsible for restoring the transaction log backups on the secondary server.
- Log shipping alerting
- Log shipping alerting is another optional component that allows you to set up alerts to notify you of log shipping failures or issues.
Database State
- Recovery mode
- When restoring a database in Recovery mode, the database is fully recovered, and it is available for use. This mode is the default mode for restoring a database, and it is used when you want to restore the database to its most recent state.
- NoRecovery mode
- When restoring a database in NoRecovery mode, the database is not fully recovered, and it remains in a state where it cannot be used. This mode is used when you want to restore additional backups to the database before making it available for use. This mode is typically used when you want to restore a differential or log backup.
- STANDBY mode
- When restoring a database in STANDBY mode, the database is recovered, but it remains in a read-only state. This mode allows you to use the database for read-only operations, but you cannot modify the data in the database. This mode is typically used when you want to perform reporting or other read-only operations on the database while it is being restored.
Setup
預先步驟
本次示範首先建立 Group Managed Service Accounts SQLService，提供 SQL1 (Primary Server) 以及 SQL2 (Primary Server) 的 SQL Server Service 以及 SQL Server Agent Service 作為 Identity。
| IP | Name | Note |
|---|---|---|
| 192.168.127.100 | DC | |
| 192.168.127.101 | SQL1 | Primary Server |
| 192.168.127.102 | SQL2 | Secondary Server |
在 Share Folder 上，分別於 SQL1 以及 SQL2 建立 C:\LogShipping 並且開啟共用與安全性，授予 SQLService 具有讀取與寫入權限。
Configure security for SQL Server log shipping | learn.microsoft
建立步驟
Log Shipping 的設定方式 By Database，本次示範以 AdventureWorksLT 進行示範。
首先啟用交易紀錄傳送，在啟動前必須先進行該資料庫的完整備份。
接著設定用於儲存交易紀錄備份地點的共用資料夾，並調整備份交易紀錄 JOB 的備份排程，預設是 15 分鐘。
如果 Secondary 不存在目標資料庫，可以使用初始化次要資料庫進行建立。
接著設定複製交易紀錄的 JOB，設定 Secondary 的路徑以及進行複製的排程，預設是 15 分鐘。
最後設定還原交易紀錄的 JOB，預設是 15 分鐘，這個步驟可以設定 Secondary Server 要保持在 Recovery (無法被讀取、寫入) 或是 Standby (可以被讀取) 等狀態。
接著可以使用第三台資料庫伺服器扮演監視伺服器，用以確認交易紀錄的還原的同步程度。
一切順利，大功告成 😍
Log Shipping 進行方式
每 15 分鐘，在 Primary Server 的 Back Up Job 會觸發，從 Primary Server 複製交易紀錄並儲存在共用資料夾，使用的身分是 Primary Server SQL Server Service。
每 15 分鐘，在 Secondary Server 的 Copy Job 會觸發，從共用資料夾複製交易紀錄至 Secondary Server 的資料夾，使用的身分是 Secondary Server SQL Server Agent。
每 15 分鐘，在 Secondary Server 的 Restore Job 會觸發，將 Secondary Server 資料夾當中的交易紀錄進行還原，使用的身分是 Secondary Server SQL Server Agent。