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 |