Friday, June 3, 2011

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.
  • 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...)

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA