No wonder the data on the internet has exploded drastically and everyday more and more is being added. What this has done is that it has made the tools, algorithms and servers to fail as a whole. What is needed is maximum uptime; data availability and level of compliance. The next wave of SQL Server investments further help protect an organization’s infrastructure. SQL HA server 2012 which stands for high availability has tried to address this issue.
Quite a few options are available in SQL HA, some of which would be described below:
This option is no doubt an expensive one but it provides the purpose which is to reduce down time. This is very well documented and has been available since quite some time as the failover solution for MS SQL Server. As part of the always on offering, failover clustering compliments windows server failover clustering functionality to provide high availability locally but on the cost of redundancy. This is the primary reason for the increased cost of this option since unlike Oracle; MS SQL is based on a shared nothing database management system. What this means is that your passive server would be sitting idle most of the time and would only come into play once a failover occurs in the primary server. There is a workaround to this which is by having an Active-Active cluster which is essentially 2 Active-Passive cluster combinations.
This is a relatively better option since what it does is that it maintains a mirror of the original database like a backup server so in case of a failover, the primary server is switched over to this backup server. The beauty of this is that it can provide a robust solution if implemented in parallel to clustering since incase of a site failure, the server can be switched to the mirror server (database mirroring) and in the case of a local failover, the clustering solution (failover clustering) does the job for the client.
The concept behind database snapshot is to keep the load minimal on the production server by generating scripts of the database at specific intervals of time (snapshots of the database) and offloading and running them on some other server so it’s like keeping copies of the original database but on an alternate server. This is a pretty easy to implement solution.
Despite being a low cost option, this has its setbacks which include data loss. Like database mirroring, log shipping operates at the database level. It can be used to maintain one or more warm standby databases (referred to as secondary databases) for a single production database that is referred to as the primary database. It can also be used in conjunction with failover clustering to provide a relatively fine and low cost site failure solution.