SQL Server High-availability scenarios

SQL Server High-availability scenarios

Scenario One:asynchronous Mirror + Alias Program Introduction

The database server configures an asynchronous mirroring relationship, and the program client connection string configures the alias connection.

1. Create an alias in the SQL Server client configuration, replacing the server name or IP address with an alias in the client's connection string settings.

2. Write a utility that updates the alias when mirroring the role switch.

3. Update aliases can be done by modifying the appropriate registry string, located in the Hkey_local_machine\software\microsoft\mssqlserver\client\connectto

Solution Pros and cons

Pros: It's safe, fast, and easy to implement a planned database migration. Just modify the alias to correspond to the IP address.

Cons: For Small business application management is more practical.

Scenario Two:asynchronous Mirror + Hosts Program Introduction

The database server configures the asynchronous mirroring relationship, the program client configures the domain name connection, and the hosts file configures the correspondence between the IP and the domain name.

1. Modify the Hosts file in the client machine to create a correspondence between the domain name and the primary database server IP.

2. Write a utility to update the IP address in the Hosts file when mirroring the role switch. Hosts file is located in the Hosts file at C:\Windows\System32\drivers\etc\Hosts

3. Clear the DNS cache for the client machine.

Solution Pros and cons

Advantage: After you modify the IP and domain name correspondence in the client Hosts file, clear the DNS cache and retrieve the new master server information again.

Disadvantage: Compared with scheme one, there is a certain delay.

Scenario Three:asynchronous Mirror + DNS Program Introduction

The database server configures the asynchronous mirroring relationship, the program client configures the domain name connection, and the DNS server configures the correspondence between the IP and the domain name.

1. Create a DNS cname/a record to point to the active database server.

2. In the client connection string setting, use a virtual name.

3. Write a utility to complete the following tasks:

A) Update DNS records

b) Empty the client machine's DNS cache while mirroring the role switch

Solution Pros and cons

Advantages: It is more practical to realize the platform management of high-volume business application.

Disadvantage: You need to modify the DNS server-side record configuration information, then clean up the client DNS cache, and retrieve the new master server information again. The process is relatively cumbersome, the need to consider the high availability of DNS servers, compared to scenario one or two, there is a certain delay.

Scenario Four:AlwaysOn Program Introduction

AlwaysOn is a powerful "high Availability + disaster recovery" technology that combines the benefits of failover clustering, database mirroring, and log shipping.

AlwaysOn leverages the health monitoring and automatic failover characteristics of Windows failover clustering, so it must be built on top of a Windows failover cluster. However, unlike SQL Server clusters, databases in an availability group are not necessarily required to be stored on shared storage (Shareddisk), and they can also be stored on a local disk. In addition, availability groups are health detection and failover as a collection of user databases, rather than as an entire instance of SQL Server clusters.

Solution Pros and cons

Pros: AlwaysOn availability groups, each availability group is a container that contains one or more user databases, all databases in the availability group fail over as a whole, the secondary server can be used for read-only access requests, and the secondary server can perform backup and DBCC commands.

Disadvantage: AlwaysOn SQL Server instances, preferably with the same fixed port, are based on Windows Clustering.

Programme five:synchronous Mirror Program Introduction

When transaction security is set to full, the database mirroring session runs synchronously in high-security mode after the initial synchronization phase. Also subdivided into:

1. High-safety mode without automatic fail-over function

Manual failover is supported when the partner is connected and the database is synchronized. If the mirror server instance fails, the principal server instance is not affected and runs publicly (that is, the data is not mirrored). If the principal server is missing, the mirror hangs, but the service can be forced to the mirror server (which can result in data loss).

2. High-safety mode with automatic applause transfer function

Automatic failover provides high availability by ensuring that a server remains available to the database after it has been lost. Automatic failover requires the session to have a third server instance (the "witness"), ideally a witness server resides on a third computer.

Solution Pros and cons

Pros: No data loss.

Disadvantage: It has a certain impact on business performance requirements; A witness server needs to be deployed.

Scenario Six:SQL Server Cluster Program Introduction

A SQL Server failover cluster is built on top of a Windows server failover cluster. Windows Server failover clusters are designed to provide high availability services or to run failovers within an application cluster. It contains a set of independently running servers to increase the availability of applications and services. A failover cluster can prevent hardware and software failures and transfer failed resources from one server (or cluster node) to another node. A failover is a process that takes a clustered service or application offline on one node and comes back online on another node. The entire process is transparent to the user.

Solution Pros and cons

Advantages: The effect of performance is small.

Disadvantage: Windows-based clustering, downtime approximately equals SQL Server service restart time + database recovery time, unable to protect against shared storage disk failures.

Scenario Seven:Hyper-V + SQL Server Cluster Program Introduction

Many users use MS SQL Server in a Hyper-V virtual machine. However, SQL Server alone (standalone) does not provide high availability and disaster recovery capabilities. In front of Hyper-V users with high availability requirements, failover clustering (Failover cluster) is a necessary feature. When a virtual production server goes down, a virtual server in a hot backup can be put into work quickly.

Solution Pros and cons

Pros: Reduce planned business downtime to take advantage of HPYER-V's dynamic migration capabilities.

Cons: Virtualization is needed for small-pressure businesses.

Scheme VIII:Log Shipping Program Introduction

You can use log shipping to send transaction logs uninterrupted from one database (primary database) to another (secondary database). Continuously back up the transaction logs in the primary database, and then copy and restore them to the secondary database, which basically keeps the secondary database in sync with the primary database. The target server acts as a backup server, and the query processing can be reassigned from the primary server to one or more read-only secondary servers. Log shipping can be used with databases that use the full or bulk-logged recovery model.

Solution Pros and cons

Pros: Support multiple copies.

Cons: Irreversible after failover, opaque to client, long downtime.

Programme IX:transactional Replication Program Introduction

Transactional replication typically starts with a snapshot of the publication database objects and data. After the initial snapshot has been created, data changes and schema modifications made at the publisher are usually passed to the subscriber at the time of the modification (almost real-time). Data changes are applied to subscribers in the order that they occur at the publisher and transaction boundaries, so transactional consistency can be guaranteed within the publication.

Solution Pros and cons

Advantage: The database object level.

Disadvantage: The impact on performance is high.

Scenario Ten: mixed use of the above scenarios Program Introduction

such as mirroring combined with log shipping.

Solution Pros and cons

Pros: Overlay The benefits of multiple scenarios.

Cons: Increases the impact on performance and the complexity of management.

Scenario 11:NLB Cluster Program Introduction

NLB Cluster

Solution Pros and cons

Advantages: Transparent

Disadvantage: The system layer is technically complex.

Schedule 1:sql Server High Availability scenario comparison

Function	Fail-over cluster	Log shipping	Database Mirroring	Transactional replication	AlwaysOn
Protection level	Instance level	Database-level	Database-level	Database Object Level	Database-level
Whether there is data loss	/	There may be a small amount of data loss	Unlimited data loss (synchronous mode)	There may be a small amount of data loss	Unlimited data loss (synchronous commit mode)
Auto Fail-Over	Is	Whether	Yes (highly available operating mode)	Whether	Yes (auto fail-over mode)
Whether it is reversible after failover	Is	Whether	Is	Whether	Is
Whether the client is transparent	Yes, automatic reconnection to another node of the same IP	Whether	Yes, auto Redirect (requires driver support)	Whether	Is
Down time	Approximately equals the time of the SQL Server service restart + database recovery time	More long	Approximately equals database recovery time	More long	Approximately equals database recovery time
Multiple copies of standby data	Whether	Is	Whether	Is	Yes (max 4)
Standby data copy is readable	/	Is	Whether	Is	Is
Protects against user misuse	Whether	Is	Whether	Whether	Whether
Protects against disk failures	Whether	Is	Is	Is	Is
Whether there are specific hardware requirements	Windows Clustering	No	Requires a better disk and network	No	Windows Clustering
Impact on performance	Low	In	In	High	In
Other features	/	Automatic page Repair	/	Conflict resolution, bidirectional data synchronization, etc.	Automatic page repair, read-only routing, secondary database backup, secondary database execution DBCC command
Version support	SQL Server 2000 and later	SQL Server 2000 and later	SQL Server 2005 and later	SQL Server 2000 and later	SQL Server 2012

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1579613

SQL Server High-availability scenarios