On high availability in SQL Server (1) High Availability overview

Since SQL Server 2005, Microsoft has provided a variety of high-availability technologies to reduce downtime and increase protection of business data, and as SQL Server 2008,sql Server 2008 R2,SQL Server 2012 continues to publish, SQL There are many high-availability technologies in the server that meet different scenarios.

Before the article starts, I'll start with a brief overview of what high availability technology is used to decide what to use.

Depending on what kind of high availability technology to use?

Many companies need all or part of their data to be highly available, such as online shopping sites, online commodity databases must be 7*24 hours online, otherwise in a highly competitive market environment, downtime means loss of customers and income. For example, a call center that relies on SQL Server, if the database is down, then all callers can only sit there and reply to the customer "sorry, system failure", which is also very difficult to accept.

Of course, in an ideal world, all the key data will always be online, but in the real world, there will be a variety of reasons for the database is not available, due to the inability to predict the time and form of disaster, the need to take early steps to prevent all kinds of emergencies, so SQL Server provides a variety of high-availability technologies, including clustering, replication, mirroring, log shipping, AlwaysOn availability groups, and other high availability technologies such as filegroup backup restores, online rebuilding indexes, and so on. What high availability technology is used is not to randomly pick a familiar technology to use directly, but to be based on business and technology comprehensive consideration. Because there is no single technology to achieve all the functionality. How to apply these technologies to specific business and budget is the so-called high availability strategy.

The following factors should be considered first when designing a high availability strategy:

RTO (Recovery time Objective)--that is, the recovery timing goal, which means how much downtime is allowed, usually in a few 9, for example 99.999% of availability means that the annual downtime is no more than 5 minutes, 99.99% Availability means that the annual downtime is no more than 52.5 minutes, and 99.9% of the availability means that there is no more than 8.75 hours of downtime per year. It is worth noting that the method of calculating RTO is to consider whether the system is 24*365 or just 6 o'clock in the morning to 9 o'clock in the afternoon. You also need to be aware that the time to maintain Windows is within downtime and that it is easier to achieve higher availability if you allow database maintenance and patching during Maintenance window time.

RPO (Recovery Point Objective)--that is, the recovery points target, means how much data loss is allowed. Usually as long as the backup, you can easily achieve zero data loss. However, when a disaster occurs, depending on the extent of database corruption, the time it takes to recover data from a backup can cause the database to become unusable, which can affect the implementation of RTO. An early and famous example is the banking system in Europe and America, only the RPO considered, the system only has full backup and log backup, every 3 months a full backup, every 15 minutes a log backup, when the disaster occurs, only through full backup and log backup to recover data, so although no data loss, But because the recovery of data took two days, resulting in 2 days of the banking system is not available, so the loss of a large number of customers. Another example is a domestic online video web site, using SQL Server as a backend relational database, the front-end uses no-sql, regularly no-sql data into the relational database as a backup, when a disaster occurs when the maximum allowable loss of data, but to ensure high availability.

Budget –rto and RPO are collectively called SLAs (Service level agreements), and when designing a high-availability strategy, it is up to the business to measure how much of the SLA is met, depending on the budget and the cost of the different SLA failures. SLAs are not as high as possible. Rather, it is based on business requirements, generally, it is difficult to achieve a high SLA under a limited budget, and even if a high SLA is achieved through a complex architecture, a complex architecture also means Gao Yunwi costs, so the appropriate technology needs to be selected within the budget to meet SLAs.

Thus, in a comprehensive context, a large framework of high availability can be identified through a number of order-taking issues:

What is the amount of downtime that shareholders can accept?

What are the downtime times that managers can accept?

What is the budget for the high availability scenario?

How much is the cost per hour of downtime?

Cold backup, warm backup and hot backup

Depending on how much data is synchronized between the host and the standby, the backup can be divided into three different cases, namely cold backup, warm backup and hot backup.

Cold backup: The so-called backup, the standby server is configured to accept the primary server data, when a failure, manually restore the data to the primary database, or reconfigure the program's connection string or permissions to make the backup database online.

Warm backup: The main server data will be kept log to the standby server (indefinite, can be 15 minutes, 30 minutes, 1 minutes, etc.), in this way, the primary server to the backup server is usually asynchronous update, so the primary server and backup server data are not guaranteed to be consistent. In addition, the scheme usually does not implement automatic fault monitoring and failover.

Hot backup: The primary server's data is automatically synchronized on the backup server, which in most cases includes automatic fault monitoring and failover, and ensures data consistency between the primary and backup servers.

With cold backup to warm backup to hot backup, the cost will rise in a straight line.

High-availability features supported in SQL Server

The high-availability features supported in SQL Server are related to the version, and the Enterprise Edition supports all high-availability features, including:

• Failover Cluster

L Database Mirroring

L Transaction Log Shipping

L Database Snapshots

L High Availability upgrades

L Hot-load memory

L Online Indexing operation

L Database part Online (only restores primary filegroup or primary filegroup and additional NDF files)

Which versions support which high-availability features are available, see: Http://msdn.microsoft.com/zh-cn/library/cc645993.aspx, it is worth noting that the free Express version can be used as a witness for database mirroring, thus saving costs.