High Availability in SQLServer (1) ---- high availability overview

Since SQLServer2005, Microsoft has provided a variety of high availability technologies to reduce downtime and increase protection for business data. With the continuous release of SQLServer2008, SQLServer2008R2, and SQLServer2012, SQLServer has multiple high availability technologies that meet different scenarios. Before the beginning of the article

Since SQL Server 2005, Microsoft has provided a variety of high availability technologies to reduce downtime and increase protection for business data. With SQL Server 2008, SQL Server 2008 R2, with the continuous release of SQL Server 2012, SQL Server already has a variety of high availability technologies that meet different scenarios. Before the beginning of the article

Since SQL Server 2005, Microsoft has provided a variety of high availability technologies to reduce downtime and increase protection for business data. With SQL Server 2008, SQL Server 2008 R2, with the continuous release of SQL Server 2012, SQL Server already has a variety of high availability technologies that meet different scenarios.

Before starting the article, I would like to give a brief overview of what determines which High Availability Technology to use.

 

What determines which High Availability Technology to use?

Many enterprises require high availability of all or part of their data. For example, online shopping websites and online commodity databases must be online 7x24 hours a day. Otherwise, in a highly competitive market environment, downtime means losing customers and revenue. For example, a call center dependent on SQL Server. If the database goes down, all the callers can only sit there and reply to the customer "sorry, system fault ", this is also unacceptable.

Of course, in an ideal world, all the key data will always be online, but in the real world, there will be various causes that make the database unavailable, since it is impossible to predict the time and form of a disaster, measures must be taken in advance to prevent various emergencies. Therefore, SQL Server provides a variety of high availability technologies, including: high Availability Technology for a single instance, such as cluster, copy, image, log transmission, AlwaysOn availability group, backup and restoration of file groups, and online re-indexing. What kind of high availability technology is used is not just to pick a familiar technology for direct use, but based on comprehensive business and technology considerations. Because no single technology can implement all functions. How to use these technologies based on specific business and budget is the so-called high availability policy.

When designing a high availability policy, consider the following factors:

 

Cold backup, warm backup, and Hot Backup Based on the degree of data synchronization between the host and the backup, backup can be divided into three types: Cold backup, warm backup and hot backup.

With cold backup to warm backup to hot backup, the cost will soar.

 

High Availability features supported by SQL Server

The high availability features supported by SQL Server are closely related to the version. The Enterprise Edition supports all the high availability features, including:

For details about which versions support high availability features, see: the free Express version can be used as the witness server for database images, thus saving costs.

Failover Cluster

The failover cluster provides high availability support for the entire SQL Server instance, this means that if a hardware error or operating system error occurs on the SQL Server instance of a node in the cluster, it will fail over to other nodes in the cluster. Multiple servers (nodes) share one or more disks to achieve high availability. Failover clusters appear on the network in the same way as a single computer, but have high availability. It is worth noting that because the Failover cluster is based on shared disks, there will be a single disk failure. Therefore, you need to deploy SAN replication and other protection measures at the disk level. The most common failover cluster is a dual-node failover cluster, including the master and slave nodes.

 

Transaction Log Transfer

Transaction Log transmission provides database-level High Availability protection. Log transmission can be used to maintain one or more slave databases (referred to as "secondary databases") of the corresponding production database (referred to as "primary database "). Before failover, you must manually apply all unrestored log backups to completely update the secondary database. Log shipper provides the flexibility to support multiple standby databases. If multiple slave databases are required, you can use Log transmission Alone or supplement it as a database image. When these solutions are used together, the main database configured for the current database image is also the master database configured for the current log transmission.

Transaction Log transmission can be used for cold backup and warm backup.

 

Database image

Database Mirroring is actually a software solution. It also provides database-level protection and provides almost instantaneous failover to improve Database Availability. Database images can be used to maintain a single backup database (or an image database) of the corresponding production database (referred to as the "main database ").
Because the image database has been restored, but it does not restore the database, the United States space, so you cannot directly access the image database. However, for read-only loads such as reports, you can create database snapshots of the image database to indirectly use the image database. Database snapshots provide the client with read-only access to data in the database when a snapshot is created. Each database image configuration involves the "master server" that contains the master database and the backup server that contains the image database. The backup storage keeps updating the backup storage along with the master database.
Database images run synchronously in high-security mode or asynchronously in high-performance mode. In high-performance mode, transactions can be committed without waiting for the backup storage to write logs to the disk. This maximizes performance. In high security mode, committed transactions will be submitted by both partners, but the transaction latency will be prolonged. The simplest configuration of database images only involves the master server and backup server. In this configuration, if the master server is lost, the backup server can be used as a backup server, but data may be lost. The high-security mode supports the standby configuration with the automatic failover function. This configuration involves a third-party server instance called a "witness server", which enables the backup server to be used as a hot backup server. Failover from the primary database to the backup database usually takes several seconds.

Database images can be used for warm backup and hot backup.

 

Copy

Replication is not strictly designed for high availability, but can indeed be applied to high availability. Replication provides database object-level protection. Replication uses the publish-subscribe mode, that is, the master server (called the publish server) publishes data to one or more secondary servers or subscription servers. Replication provides real-time availability and scalability between these servers. It supports filtering to provide subscriber with a subset of data and partition update. The subscription server is online and can be used for reports or other functions without the need for query recovery. SQL Server provides four types of replication: snapshot replication, transaction replication, peer replication, and merge replication.

 

AlwaysOn availability Group

AlwaysOn availability group is a new feature of SQL Server 2012. It also provides database-level protection. It takes the database image and the long length of the Failover cluster, so that the database associated with the business can be used as an availability group for failover. This function also expands the database image only has one to one limit, hong Kong space, so that one primary copy can correspond to a maximum of four secondary copies (in SQL Server 2014, this limit is extended to eight ), two secondary replicas can be used as hot backup and real-time synchronization of the primary copy, while the other two secondary replicas can be used as warm backup. In addition, secondary replicas can be configured as read-only and used for backup load.

Because of this, database images are marked as "obsolete" in SQL Server 2012 ".

 

High Availability policy design

After learning about the basic concepts of high availability and the high availability technology provided by SQL Server, let's take a look at the design of high availability policies. High Availability policy planning can be divided into four stages:

Collection requirements

The first step in determining a high availability policy is undoubtedly to collect business requirements to establish SLA. The RTO and RPO mentioned earlier in this article are the most critical parts. On this basis, practical expectations are established for availability requirements, and feasible high availability policies are established based on these expectations.

Assessment restrictions