SQL Server High Availability Solutions

1. Database Mirroring (数据库镜像)

Architecture Diagram

         +--------------------+
         |    Primary DB      |
         | (SQL Server 1)     |
         +--------------------+
                   |
                   | Transaction Logs
                   |
         +--------------------+
         |   Mirror DB        |
         | (SQL Server 2)     |
         +--------------------+

Working Principle

Database Mirroring involves maintaining two copies of a database: one primary (active) database and one mirror (standby) database. The primary database streams transaction logs to the mirror database, which can be either in synchronous or asynchronous mode:

• Synchronous Mode: Ensures real-time data consistency between the primary and mirror.
• Asynchronous Mode: Prioritizes performance but may result in data loss if a failure occurs before data synchronization.

Advantages and Disadvantages

Advantages:

• Simple to configure and low cost.
• Automatic failover support (only in High-Safety mode).
• Suitable for basic disaster recovery scenarios.

Disadvantages:

• Supports only two servers (Primary and Mirror).
• No scalability or support for read-write splitting.
• Limited to SQL Server Standard and above versions.
• Not suitable for high-load systems.

Best Practices

• Use Synchronous Mode to ensure data consistency.
• Automate Failover to reduce downtime.
• Avoid using in high-load scenarios due to potential performance degradation.

Use Cases:

• Small Enterprises or Simple Applications: Where high availability is not critical, but disaster recovery is required.
• Low-Budget Projects: For cost-effective, easy-to-implement high availability.
• Disaster Recovery: Quickly failover to the mirror database in case of primary database failure.

2. Always On Availability Groups (Always On 可用性组)

Architecture Diagram

         +--------------------+
         |   Primary Replica  | <--- Read/Write
         | (SQL Server 1)     |
         +--------------------+
                   |
        +------------------------+
        |   Secondary Replica    | <--- Read-Only
        | (SQL Server 2)         |
        +------------------------+
                   |
         +--------------------+
         |   Secondary Replica  | <--- Read-Only
         | (SQL Server 3)       |
         +--------------------+

Working Principle

Always On Availability Groups allow for a group of databases to be replicated to multiple secondary replicas (both read-write and read-only). These replicas support both synchronous and asynchronous modes. The key features include:

• Automatic Failover: When the primary replica fails, the system automatically switches to one of the healthy secondary replicas, ensuring minimal downtime.
• Read-Write and Read-Only Separation: Offloads read queries to secondary replicas to reduce load on the primary replica.

Advantages and Disadvantages

Advantages:

• Supports multiple replicas (up to 8), improving scalability and redundancy.
• Automatic failover reduces downtime.
• Supports cross-geographical deployment for disaster recovery.
• Enables read-write splitting to offload read-heavy workloads.

Disadvantages:

• Requires SQL Server Enterprise Edition (some features also supported in Standard Edition with limitations).
• Complex setup and maintenance, especially for larger deployments.

Best Practices

• Configure at least two replicas and use synchronous mode to ensure data consistency.
• Enable automatic failover to ensure minimal downtime.
• Leverage read replicas for query offloading to optimize performance.
• Regularly monitor replica health to ensure data consistency across replicas.

Use Cases:

• Large-Scale Enterprise Applications: High availability, performance, and disaster recovery requirements.
• Read-Write Separation: Suitable for OLTP systems with heavy read and write traffic.
• Cross-Geographical Deployments: Disaster recovery across multiple data centers.

3. Failover Cluster Instances (FCI) (故障转移集群)

Architecture Diagram

         +--------------------+
         |  Cluster Node 1    | <--- Primary SQL Instance
         |  (SQL Server 1)    |
         +--------------------+
                   |
                   | Shared Storage
                   |
         +--------------------+
         |  Cluster Node 2    | <--- Secondary SQL Instance
         |  (SQL Server 2)    |
         +--------------------+

Working Principle

FCI relies on shared storage to provide high availability for SQL Server instances. All nodes in the cluster share a single storage area, with only one node accessing the storage at a time. In case of failure, the SQL Server instance is automatically moved to another node, ensuring minimal downtime.

Advantages and Disadvantages

Advantages:

• Zero Downtime: No application interruption during failover.
• Transparent failover to a secondary node without user impact.
• Suitable for high-availability environments with strict uptime requirements.

Disadvantages:

• High hardware and storage requirements (e.g., SAN).
• No read-write splitting – all nodes must use the same instance.
• Complex to configure and requires Windows Server Failover Clustering (WSFC).

Best Practices

• Use high-performance shared storage (e.g., SAN) for optimal failover performance.
• Prioritize cluster nodes to manage failover smoothly.
• Regularly test failover procedures to ensure system resilience.
• Monitor cluster health and ensure node performance to prevent failover issues.

Use Cases:

• High-Availability Critical Applications: For environments where zero downtime is crucial (e.g., financial services).
• Data Center Applications: Suitable for enterprises with shared storage systems and stringent high-availability requirements.

4. Log Shipping (日志传送)

Architecture Diagram

         +--------------------+
         |   Primary Server   | <--- Transaction Log Backup
         | (SQL Server 1)     |
         +--------------------+
                   |
        +-------------------------+
        |    Backup Server 1      | <--- Restore Transaction Logs
        | (SQL Server 2)          |
        +-------------------------+
                   |
        +-------------------------+
        |    Backup Server 2      | <--- Optional
        | (SQL Server 3)          |
        +-------------------------+

Working Principle

Log Shipping works by periodically backing up transaction logs from the primary database and shipping them to one or more secondary databases. The secondary databases then apply the log files. However, manual failover is required in the event of a failure.

Advantages and Disadvantages

Advantages:

• Simple to configure and low-cost option for disaster recovery.
• Provides asynchronous log backup to minimize impact on performance.
• Suitable for remote disaster recovery setups.

Disadvantages:

• Manual failover is required, meaning no automatic switchover on primary server failure.
• Recovery latency depends on the backup frequency.
• Not suitable for systems requiring real-time data replication.

Best Practices

• Adjust log backup frequency to minimize recovery time during failover.
• Ensure network reliability between primary and secondary servers to prevent delays.
• Test failover procedures regularly to ensure readiness.

Use Cases:

• Disaster Recovery: Suitable for backup and restore processes across different geographical locations.
• Low-Budget Environments: For small enterprises with limited budgets and simpler requirements.

5. Transactional Replication (同步复制)

Architecture Diagram

         +--------------------+
         |   Publisher        | <--- Master SQL Server
         | (SQL Server 1)     |
         +--------------------+
                   |
                   | Transaction Log Replication
                   |
         +--------------------+
         |   Subscriber 1      | <--- Read-Only Replicas
         | (SQL Server 2)     |
         +--------------------+
                   |
         +--------------------+
         |   Subscriber 2      | <--- Read-Only Replicas
         | (SQL Server 3)     |
         +--------------------+

Working Principle

Transactional Replication involves copying transactional data from a Publisher database to one or more Subscriber databases. This can be configured to support both read-write and read-only replicas.

Advantages and Disadvantages

Advantages:

• Real-time synchronization ensures high data consistency.
• Read-only replicas provide load balancing for read-heavy applications.
• Suitable for multi-site replication and distributed systems.

Disadvantages:

• Configuration is complex and requires ongoing management.
• Latency can occur due to the replication process, especially with large volumes of data.

Best Practices

• Ensure minimal latency by fine-tuning replication settings.
• Use monitoring tools to keep track of replication health and identify issues.
• Configure conflict resolution mechanisms to handle issues in multi-master replication scenarios.

Use Cases:

• Real-Time Data Synchronization: For applications requiring high consistency across multiple locations.
• Read-Write Separation: Offload read queries to secondary subscribers to balance primary database load.

6. SQL Server Replication (SQL Server 复制)

Architecture Diagram

         +--------------------+
         |   Publisher        | <--- Publish Changes
         | (SQL Server 1)     |
       	 +--------------------+
                   |
        +-------------------------+
        |    Distributor Server   | <--- Distribute Data
        | (SQL Server 2)          |
        +-------------------------+
                   |
         +--------------------+
         |   Subscriber 1      | <--- Receive Data
         | (SQL Server 3)     |
         +--------------------+
                   |
         +--------------------+
         |   Subscriber 2      | <--- Receive Data
         | (SQL Server 4)     |
         +--------------------+

Working Principle

SQL Server Replication is used for distributing data from a Publisher to Subscribers. Different replication types include Transactional, Merge, and Snapshot replication, each suitable for different scenarios.

Advantages and Disadvantages

Advantages:

• Flexible in terms of data distribution and application architecture.
• Supports asynchronous replication for lower impact on the source database.
• Allows for multiple subscribers to receive changes.

Disadvantages:

• Complex to configure, especially in large environments.
• Potential for replication latency or conflicts in multi-master setups.

Best Practices

• Use transactional replication for real-time data distribution.
• Monitor replication performance to avoid delays.
• Regularly back up replication configurations to prevent data loss.

Use Cases:

• Data Distribution: Distribute data across multiple locations or departments.
• Low-Latency Requirements: Applications that require low-latency data replication for real-time decision-making.

Comparison Summary

This version provides a comprehensive yet structured overview of the solutions with clear headings, architecture diagrams, working principles, advantages, and use cases, making it suitable for professional documentation.