SQL Log shipping (Database Server)
Microsoft SQL Server Transaction Log shipping is a mathod of implementing manual failover/redundancy/high-availability to a SQL Server 2000 Enterprise or newer.
Manual failover versus Automatic failover
Automatic failover is when services such as databases can be automatically accessed from a standby/secondary server should the primary server fail for any reason without any intervention from a systems administrator. It either has a shared-nothing model where both servers have all the data they need on its own server or both servers share a storage device that is seperate to both the primary and standby/secondary servers; such as a NAS (Network Attached Storage).
Manual failover is when a secondary/standby server exists and has an exact or recent copy of all the data required but a systems administrator is required to perform some relatively painless and brief actions to allow the services offered by the primary server to be accessed by the secondary/standby server.
Methodology
- Full database backup of primary is done and restored to the secondary.
- Transaction logs from the primary need to be stored on a network drive accesable to both servers - it is usually bad practice to automatically place the primary's transaction logs on the secondary.
- Secondary server's SQL server agent must have access to the location of the primary's transaction logs.
- Transaction logs from the primary are applied to the secondary periodically.
- Either the primary, secondary or a seperate server can be used for monitoring log shipping.
- Secondary's databases are always in read-only mode until the failover - apart from when it is updated with new data from the primary on a periodic basis.
Actions required when a fail-over occurs
- Perform one last backup on the primary if possible.
- Copy primary's transaction logs to the secondary and apply them in the same order as they were created. Restore the last transaction log with the WITH RECOVERY clause to allow it to become operational.
- Re-create any logins required.
Because Log shipping is manual failover and relies on periodic transaction logs for data recovery it is unsuitable for many live environments. With the advent of NLB (Network Load Balancing) coming with Server 2003 and with clustering easier to implement its popularity is decreasing.