SQL Server High Availability Features ( Brief Description )

LOG SHIPPING :

Log shipping is a data base level high availability feature, Basically it works using log backup and restore.

  • 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 applications or users are connected.
  • In secondary server database is in standby( Read Only ) mode where we can read data.
  • In secondary server T.Log backups are applied either with
    * Norecovery or
    * Standby
  • It will take 45 min to apply the changes from primary to secondary data base.
  • 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(insert, update, delete) in primary server.
  • To maintain multiple secondary databases.

Disadvantages :

  • No automatic fail over, It supports only manual fail over.
  • It will take at least 45 min to apply the changes from primary to secondary data base.
  • In case primary data base crashed there might be a data loss.

MIRRORING :

It is another high availability feature available from SQL Server 2005. Previous versions support the simple high availability feature called Transaction Log Shipping. Log shipping has its own limitation as it doesn’t support automatic fail over as well as there might be data loss.  All these limitation we can overcome with database mirroring.

Database mirroring supports automatic fail over and the transactions are applied to standby (Mirror) server immediately once they are committed at principle server. Like Log shipping no need of backup, copy and restore operations and jobs.

  • In Principal server database is in ONLINE state.
  • In mirror server database is in a restoring state, which means it is not available for incoming             requests.

Advantages and benefits:

  • Protection against database failures
  • Automatic failure detection and fail over
  • Support of easy manual fail over
  • Automatic client redirection
  • Multiple operating modes
  • No special hardware requirements
  • Minimized chance of data loss
  • Relatively ease of set up and configuration

Note: If we want to use secondary data base for reading purpose it is not possible in mirroring because secondary database will be in restoring state.

Replication:

Replication allows to maintain same database multiple copies at different locations. Log shipping and mirroring allows to maintain complete database redundancy whereas replication allows to maintain some part of the database (a set of required objects) at users location. Changes made at different user locations are synchronized to the main server. It is object level high availability feature

Advantages :

  • Improved performance
  • To reduce locking conflicts when multiple users are working
  • Improved availability
  • Easy maintenance
  • To allow sites work independently. So that each location can set up its own rules and procedures for working with its copy of the data.
  • To move data closer to the user
  1. Snapshot Replication
  • The snapshot process is commonly used to provide the initial set of data and database objects fortransnationaland merge publications.
  • It copies and distributes data and database objects exactly as they appear at the current moment of time.
  • Snapshot replication is used to provide the initial data set for transnationaland merge replication.
  • It can also be used when complete refreshes of data are appropriate (BOL).
  • Scenarios
  • When the data is not changing frequently.
  • If we want to replicate small amount of data.
  • To replicate Look-up tables which are not changing frequently.

Note: We can go with Snapshot Replication if data is not changing frequently also there is delay in replicating the data from publisher to subscriber (secondary database).

Posted by Sheikvara

+919840688822, +919003270444

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s