Common high-availability solutions in SQL Server

Database environment: SQL Server 2005 +

1. Replication. We often use transactional replication. Supports one-way and two-way data synchronization. Work at table level. Because it is based on transaction (the method similar to snapshot chain is used internally), the synchronization unit is small. In practice, it usually takes several minutes to get the latest data. Disadvantage: Although replication schema change is allowed, you sometimes need to manually modify the actual DDL modification. Data Synchronization is prone, and complex queries may result in replication performance and data synchronization problems.

2. Log shipping. Based on backup/restore. In other words, it is actually automatically transferred to the remote log backup and then restored. Just as when the restore backs up the database, the database will be in the single-user State. During the restore, all current user links will be disconnected. Of course, you can configure it to wait until all operations are completed. There is no problem of data non-synchronization.

3. databaes indexing ing. This technology was only involved in the MSITS test and has never been used. The synchronized database is unavailable, but can be accessed through snapshot. The number of snapshots is not limited, but different names must be selected. This is troublesome for database queries. There is no problem of data non-synchronization.

The biggest advantage of replication is that data synchronization is fast, and users do not feel the changes, and user queries are not affected by data updates.
Logshipping features ease of use and high fault tolerance. However, users are often interrupted.

Databaes indexing ing database images are much more available than Microsoft SQL Server, and provide a manageable alternative or supplement for failover clusters or log shipping. When a database image session is synchronized, the database image provides a hot backup server that supports fast failover when committed transactions do not lose data. During a general image session, if the production server fails, the client applicationProgramYou can reconnect to the backup server for quick recovery.
The following figure shows the features:

	Replication)	Log Shipping)	Mirroring)	Failover Clustering)
Standard/Enterprise Edition	Yes	Yes	Standard Edition only supports synchronization mode	Standard Edition only 2 nodes
Number of nodes	1: N	1: N	1:1	Depends on the maximum number of operating system nodes
Granularity	Table	Database	Database	Instance
Recovery mode	Full/simple	Full/bulk_logged	Full	All
Monitor master database faults?	No The monitor only monitors the synchronization status, Disable monitoring of publication Server failure	No Monitor server only monitors backup/retore status Do not monitor primary server failure	Witness to monitor master database faults	Yes
Automatic failover?	No (warm standby)	No (warm standby)	Yes (hot standby ), If there is no witness, it cannot be automatic. It is warm standby.	Yes
Latency	Depends on Log Reader Agent & distribution agent	Depends on log backup/restore frequency	Synchronous/asynchronous mode	Only one copy of data
Synchronization mode	Distribute from distributor, push, and read	Copy/restore of Database Backup Files	Log stream	Only one copy of data
Whether the slave database can be accessed	Submodules can be read and written, However, the default write will not be passed back to publication.	Secondary server can be set to standby mode, read-only	Mirrored dB can be read-only through Snapshot	Only one copy of data
Application scenarios	Read/write splitting	Read/write splitting and warm backup	Read/write splitting, warm standby/hot standby	Backup of servers, operating systems, and applications