What is High Availability?
High availability refers to the system designed to avoid loss of service by minimizing planned downtime of the system. Downtime here refers to the unavailability of the system for the users to access. People expect services, that are sometimes life dependent, to always be available, or in other words, to have high availability. Unfortunately, even the services with the highest availability occasionally go out. But when this occurs, people expect the services to be restored as soon as possible.
How do you measure High Availability?
Availability is often presented in terms of percentage – to be precise in terms of “nines”. This number represents your annual uptime of the server. For four nines (99.99%), your server can’t be offline for more than 52 minutes per year; for five nines it can’t be offline for more than 52.6 minutes per year and so on. As the goal is to achieve the most “nines” as possible, your technology must improve along with personnel working on improving High Availability. But how do you calculate High Availability?
Availability is defined as a portion of time that a unit can be used. IT professionals define it as division of Operating time with Elapsed time, with Elapsed time being a continuous time (operating time + downtime).
What factors affect High Availability?
Many factors affect High Availability. There are some which are controllable and some which are unpredictable. However, here are some factors you have the most control over:
Mean time to detect a problem.
- Mean time to repair.
- Mean time to decide which technique to implement in order to solve the problem.
In order to detect a problem, you will need the technology as well as the high trained personnel. Many IT professionals make a big mistake by thinking high availability is one time deal – you buy a lot of expensive hardware and software and you have sealed the deal. Wrong! The technology is only one flower in the garden called High Availability. Highly trained personnel can detect and prevent some failures of even occurring in the first place. They implement the right techniques so that the system doesn’t go offline at all.
The only factor that is totally unpredictable in High Availability maintenance is the mean time between failures. However, as this factor can’t be controlled, your investments should go on things that you actually can control. The goal is to minimize the time for executing the three factors mentioned above and to prevent failures from occurring.
How to improve your High Availability?
There are many possible solutions you can take in order to improve your High Availability. Some of the solutions are Clustering and Synchronous Database Mirroring which we will closely examine further below. We will go through the process of when to implement, how to implement and through all pros and cons of each method.
Clustering
Clustering had a bad reputation back in the SQL 2000 days but nowadays it is much easier to manage. It is now proved to be one of the best and most commonly used methods in High Availability industry. But how does it work? Basically, two physical servers are involved, they share the same storage, where data is contained, and if one of the servers fail, everything gets transferred from server one to server two. Nothing gets left behind, everything is transferred, including the master database, model database, log-ins, jobs, everything. This process is automated, so that if a hardware or software failure occurs, all data fails over from one server (node) to another. From the user’s perspective, nothing has changed. Everything still functions in the same manner. Now let’s get on to the pros and cons of this method.
Pros
- As mentioned above, process is automated.
- It is available for all versions of SQL Server.
- There is no loss of data.
Cons
- This is not data recovery solution.
- Servers must be geographically in the same area.
- It doesn’t allow you to create failover clusters at the database level.
- If you delete a record, it’s gone – it’s going off of the shared storage.
- As identical hardware and shared storage is required it can be rather expensive.
Synchronous Database Mirroring
First of all, Synchronous Database Mirroring doesn’t use nor require shared storage like Clustering. With synchronous mirroring, transaction cannot commit on the principal until all transaction log records have been successfully copied to the mirror. Taking this into consideration, if a failure occurs on the principal, committed transactions are presented in the mirror when it comes online. In other words, if something gets messed up in node one, it’s ok, because we have that transaction in node two. With this process you can achieve zero data loss and moreover, it can be automated with the use of witness server. Witness server is usually hosted on another physically separate server and it’s purpose is to agree with the mirror that the principal cannot be contacted. However, bare in mind that only databases can be mirrored (and only one database at the time) but not the whole server. Only the stuff that is in that very database will fail over.
Pros
- Database Mirroring can be used as a Disaster Recovery solution as well.
- As mentioned above, the process can be automated.
- Zero data loss can be achieved.
- No use of shared storage.
Cons
- Synchronous Database Mirroring can be slow.
- It carries a lot of risks which can result in slowing the performance.
- If something is changed in node one, for example a user’s password, the user wont be able to access with changed password on the node two.
Author Bio:
Ivan Dimitrijevic has a strong background in Social Media Marketing and Blogging. Among other things, he has interests in Search Engine Marketing, WordPress, SQL Server Databases and contributes to many blogs including ApexSQL and writing about SQL Server Tools for database auditing, database recovery, database comparison, scripting and more.
Twitter
Facebook
Reddit
RSS
Add Comment