SQL Server: Master SQL Chef series: Implementing Availability feature #sql2k8r2cookbook

When we talk about availability the key step in providing the feature is to ensure such feature is available and easily implemented.

High Availability is purely dependant on hardware and software that will activate the failover or failback whenever the system is affected with the problem. Similarly, more complex and larger a database application, the more important it is to automate administrative tasks with proactive maintenance tasks to ensure data is available. The key factors for hardware and software solutions are cost, implementation, and feature support, which has its own advantages and disadvantages.

High Availability requires an effective strategy which in turn requires planning and implementation at the SQL Server infrastructure level with the required configuration. No wonder that SQL Server 2008 onwards the new features on HA are efficient and from 2008 R2 version have had

enhancements in this area being the performance improvements to database mirroring, replication, and database backup strategies.

When we talk about HA, since 2005 version Database Mirroring has been a desired solution that will handle the automated availability of database in case of any disaster on the database level. Just to mention, Database Mirroring (DBM) is the software solution for increasing the database availability,

Increased data protection

  • Increased database availability
  • Automated failover support, which provides minimized downtime for the rolling upgrade of databases
  • Partners

: Principal, Mirror, and Witness

to communicate with one another.


By default, the DBM endpoint requires the encryption of data that is sent over DBM connections, which contains a bit of a performance lag. Unless you obtain a guarantee that both the mirroring partners’ network is secure, you should not disable the encryption to gain performance.

Do not mix up performance with availability , the methods of improving the performance and reliability of the storage depends on the configuration of drives, which implies the way data is divided between disks to distribute load, or mirrored to recover from disk failure. By default the database recovery model plays important role on providing High Availability to the databases.

The detailed version on how to implement the effective HA solutions refer to the chapters within the book Microsoft SQL Server 2008 R2 Administration cookbook along with list of best practices.

where all the bulk operations are fully logged. The main benefits of database mirroring are:



Satya Shyam K Jayanty

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook


Leave a Reply

Your email address will not be published.