Implementing Log Shipping

High Availability Features

• In order to reduce or avoid server or database downtime so that the applications/users works continuesly with the server we can configure high availability features.
• SQL Server supports 3 types of DR (Disaster Recovery) features.

                   1. Server Level HA features
                                    * Clustering
                   2. Database Level ,,   ,,
                                   * T.Log Shipping
                                   * Database Mirroring
                   3. Object Level HA features
                                  * Replication

1. Log shipping

• It is simple method of keeping entire database in different server.
• It works with backup, copy and restore jobs.
• We need 3 servers
   * Primary server
   * Secondary ,,
   * Monitor server (optional)
• In primary server database is in online state where the appls or users are connected.
• In secondary server database is in standby mode where we can read data.
• In secondary server T.Log backups are applied either with
       * Norecovery or
       * Standby
• We can have multiple secondary servers.

Advantages

• To reduce downtime for the applications or users in case of primary server failed.
• To implement load balancing. i.e we can allow reading from secondary server and manipulations in primary server.
• To maintain multiple secondary databases.

Points to Remember

• We can ship all databases except master, model, read-only and the databases whose recovery model is SIMPLE.
• We should have sysadmin privileges on both servers.
• Changing recovery model from FULL or BULK LOGGED to SIMPLE will break log shipping process.
• We cannot take ad-hoc Transaction Log backups from primary server as it breaks log shipping log backup chain.

(I will continue...)