1. SQL Server Always On Availability Groups (AG)
Always On Availability Groups is a leading high availability (HA) solution for SQL Server on CentOS, offering database-level redundancy with support for synchronous/asynchronous replication, read-scale replicas, and automatic failover (when configured with a Windows Server Failover Cluster, WSFC). It requires at least two CentOS nodes with identical SQL Server versions and a shared storage or WSFC setup.
Implementation Steps:
- Prepare Nodes: Ensure all CentOS servers run the same SQL Server version and are network-compatible.
- Enable Always On: Run T-SQL commands to enable the feature on each instance (
sp_configure 'Always On Availability Groups', 1; RECONFIGURE).
- Configure WSFC: Set up a Windows Server Failover Cluster (even on Linux, WSFC is required for AG coordination) to manage node membership and quorum.
- Create Availability Group: Use SSMS or T-SQL to create an AG on the primary node, add databases, and specify replicas (e.g.,
CREATE AVAILABILITY GROUP [YourAG] FOR DATABASE [YourDB] REPLICA ON 'Node1' WITH (ENDPOINT_URL = 'TCP://Node1:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT), 'Node2' WITH (ENDPOINT_URL = 'TCP://Node2:5022', FAILOVER_MODE = AUTOMATIC)).
- Join Replicas: On secondary nodes, execute
ALTER AVAILABILITY GROUP [YourAG] JOIN to add them to the group.
- Configure Listener: Create a virtual IP (VIP) listener to route client traffic to the current primary replica (e.g.,
CREATE AVAILABILITY GROUP LISTENER [YourListener] WITH IP ('192.168.1.100', '255.255.255.0') PORT 1433;).
- Test Failover: Validate automatic/manual failover by simulating node failures and verifying client connectivity to the new primary.
2. SQL Server Failover Clustering (FC)
SQL Server Failover Clustering provides instance-level HA by clustering the entire SQL Server instance across multiple nodes. It relies on shared storage (e.g., SAN) to ensure data consistency and uses WSFC to manage failover.
Implementation Steps:
- Install SQL Server: Deploy SQL Server on all cluster nodes with the same configuration.
- Configure WSFC: Create a WSFC with all nodes, ensuring proper quorum settings (e.g., majority node majority).
- Install SQL Server in Cluster Mode: During installation, select “Failover Cluster Installation” and follow prompts to add the instance to the WSFC.
- Validate Cluster: Use Failover Cluster Manager to test node failover and ensure the SQL Server resource fails over correctly.
3. SQL Server Mirroring
SQL Server Mirroring is a legacy HA solution that provides database-level redundancy with synchronous (high safety) or asynchronous (high performance) replication. It includes an optional witness server for automatic failover (creating a “witness quorum”).
Implementation Steps:
- Prepare Servers: Install SQL Server on the principal, mirror, and (optional) witness servers.
- Configure Mirroring: On the principal, set up a mirroring endpoint (
CREATE ENDPOINT MirroringEP STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER);).
- Establish Partnership: Use T-SQL or SSMS to connect the mirror to the principal (
ALTER DATABASE [YourDB] SET PARTNER = 'TCP://Principal:5022';).
- Add Witness (Optional): For automatic failover, configure a witness server (
ALTER DATABASE [YourDB] SET WITNESS = 'TCP://Witness:5022';).
- Test Failover: Initiate manual failover to verify the mirror becomes the principal.
4. Log Shipping
Log Shipping is a simple, cost-effective HA/disaster recovery solution that automates log backups from a primary server to one or more secondary servers. It provides standby databases but no automatic client failover (requires manual intervention).
Implementation Steps:
- Configure Primary Server: Set up regular log backups (
BACKUP LOG [YourDB] TO DISK = '\\Share\LogBackup.trn' WITH INIT;) and copy them to secondary servers.
- Restore on Secondaries: On each secondary server, restore logs in “NoRecovery” mode (
RESTORE LOG [YourDB] FROM DISK = '\\Share\LogBackup.trn' WITH NORECOVERY;).
- Monitor and Failover: Use a monitoring tool (e.g., SQL Server Agent alerts) to track log shipping status. For failover, manually restore the latest log with “RECOVERY” on the secondary and redirect clients.
5. Third-Party Tools
Third-party tools can enhance HA for SQL Server on CentOS by providing monitoring, automation, and load balancing:
- Red Gate SQL Monitor: Tracks SQL Server health, performance, and HA status, alerting admins to issues before they cause downtime.
- SolarWinds Database Performance Analyzer: Optimizes query performance and identifies bottlenecks that could impact HA.
- Keepalived + LVS: Implements virtual IP (VIP) failover and load balancing for SQL Server instances, ensuring continuous availability.
- Docker/Kubernetes: Containerize SQL Server using Docker and manage HA with Kubernetes (e.g., ReplicaSets, StatefulSets) for automated failover and scaling.