SQL Log Shipping
In some scenarios, it may be desirable to preserve the existing setup when you perform the initial array deployment into an existing environment. You can familiarize yourself with the new environment because you can now restore the original if needed.
Log Shipping is a technique that keeps an almost-in-sync copy of the database in one or more warm standby databases.
Before you begin, consider the following:
| Item | Description |
|---|---|
| Frequency of replication | Defines how much of a replication delta there is between the secondaries from the primary. Replaying transactions into the secondary (after replication) can cause excessive load on the servers, which could potentially degrade performance for the applications. Normally the lag is around 15 minutes. |
| Size of the staging areas | Depends on the amount of churn in the database. The staging area must be big enough to hold logs so that secondaries can grab and replay. If logs are lost, replication stops and must be setup again from the beginning. |
| Retention policy on the staging area | Defines how long log are backups are kept. Keep the logs long enough so that secondary databases can copy them and apply them. Normally this is set up to 72 hours. |
Log shipping is a multi-step process. The steps are listed and a
diagram is provided to help you understand how to keep manage the warm standby
databases:
- Take a full backup of the database and restore it on each of the secondaries.
- On the primary, periodically take a backup of the log, and place it in a network share.
- On the secondary, periodically copy the logs from the original network share to a staging area and then apply the logs from the staging area to the secondary database.