2023-04-07

SQL Server Log Shipping

說明
1. Benefits About Log Shipping
Architecture
Setup
相關連結

筆記 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。