Ubuntu上如何实现SQL Server的高可用性

Prerequisites for High Availability on Ubuntu
Before configuring SQL Server high availability, ensure the following:

• Supported Versions: Use Ubuntu 18.04 LTS or later and SQL Server 2017 or later (Enterprise Edition is recommended for advanced features like automatic failover).
• Network Configuration: All nodes must be on the same network, with firewall rules allowing SQL Server ports (default: 1433 for database traffic, 5022 for Always On endpoints).
• Time Synchronization: Use NTP (e.g., chrony) to sync time across all nodes—critical for replication and failover consistency.
• Storage: For Always On Availability Groups (AGs), shared storage (e.g., NFS, iSCSI) is optional but recommended for storing transaction logs and backups.

1. Install SQL Server on All Nodes
Install SQL Server uniformly across all servers (primary and secondary) to ensure compatibility. Use the following commands:

# Add Microsoft package repository
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)"
sudo apt-get update

# Install SQL Server
sudo apt-get install -y mssql-server

# Set SA password and complete setup
sudo /opt/mssql/bin/mssql-conf setup

Verify installation by connecting to the instance:

sqlcmd -S localhost -U SA -P 'YourStrongPassword!'

2. Configure SQL Server for Always On Availability Groups
Enable Always On and configure the endpoint for secure communication between replicas:

# Enable Always On (requires SQL Server restart)
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

# Create a database master key (if none exists)
sqlcmd -S localhost -U SA -Q "IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword';"

# Create a certificate for endpoint authentication
sqlcmd -S localhost -U SA -Q "CREATE CERTIFICATE AGCert WITH SUBJECT = 'AG Authentication Certificate', START_DATE = '2025-01-01', EXPIRY_DATE = '2027-01-01';"
sqlcmd -S localhost -U SA -Q "BACKUP CERTIFICATE AGCert TO FILE = '/var/opt/mssql/backup/AGCert.cer';"

Secure the certificate file (chmod 600 /var/opt/mssql/backup/AGCert.cer).

3. Create an Availability Group
On the primary node, create an AG with one or more synchronous replicas (for automatic failover) and optional asynchronous replicas (for disaster recovery):

-- Connect to the primary node via SSMS or sqlcmd
CREATE AVAILABILITY GROUP [MyAG]
WITH (
    DB_FAILOVER = ON,          -- Enables automatic failover when primary is down
    DTC_SUPPORT = NONE         -- Disable for Linux (not supported)
)
FOR DATABASE [YourDatabase]    -- Replace with your database name
REPLICA ON
    N'PrimaryNode' WITH (
        ENDPOINT_URL = N'TCP://PrimaryNode:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  -- Ensures data sync before commit
        FAILOVER_MODE = AUTOMATIC,               -- Automatic failover
        SEEDING_MODE = AUTOMATIC                 -- Automatically seeds the secondary
    ),
    N'SecondaryNode' WITH (
        ENDPOINT_URL = N'TCP://SecondaryNode:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC
    );

Add the target database to the AG:

ALTER DATABASE [YourDatabase] SET HADR AVAILABILITY GROUP = [MyAG];

4. Configure a Listener for Client Connectivity
A listener is a virtual IP (VIP) that clients use to connect to the AG. It automatically redirects traffic to the current primary node:

-- Create a VIP (replace with your subnet and subnet mask)
CREATE AVAILABILITY GROUP LISTENER [MyAGListener]
WITH IP (
    ('192.168.1.100', '255.255.255.0'),  -- Replace with your desired VIP and subnet
    ('192.168.1.101', '255.255.255.0')   -- Optional: Secondary VIP for redundancy
),
PORT = 1433;  -- Must match SQL Server's listening port

5. Join Secondary Nodes to the Availability Group
On each secondary node, join the AG to start replicating data from the primary:

-- Connect to the secondary node
ALTER AVAILABILITY GROUP [MyAG] JOIN WITH (
    ENDPOINT_URL = N'TCP://PrimaryNode:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC
);

6. Test Failover
Validate the AG by simulating a primary node failure:

# On the primary node, stop SQL Server
sudo systemctl stop mssql-server

# On a secondary node, check if it becomes the new primary
sqlcmd -S localhost -U SA -Q "SELECT replica_server_name, role_desc FROM sys.dm_hadr_availability_replica_states;"

The secondary node should show ROLE_DESC as PRIMARY. Restore the original primary node and verify it rejoins as a secondary.

Alternative: Use Pacemaker/Corosync for Advanced Clustering
For environments requiring multi-resource management (e.g., integrating with other services), combine Always On with Pacemaker/Corosync:

1. Install Pacemaker and Corosync on all nodes:

   sudo apt-get install -y pacemaker corosync pcs
   

2. Configure Corosync (edit /etc/corosync/corosync.conf) to define the cluster nodes and communication settings.
3. Initialize and start the cluster:

   sudo pcs cluster auth node1 node2 -u hacluster -p YourClusterPassword
   sudo pcs cluster setup --name MyCluster node1 node2
   sudo pcs cluster start --all
   

4. Create a Pacemaker resource for the AG listener:

   sudo pcs resource create AGListener ocf:mssql:ag_listener ag_name=MyAG listener_name=MyAGListener ip=192.168.1.100 cidr_netmask=24 op monitor interval=30s
   

5. Set failover policies and test automatic failover.

Key Considerations

• Automatic vs Manual Failover: Use FAILOVER_MODE = AUTOMATIC for production (requires synchronous replicas) and MANUAL for testing.
• Monitoring: Use tools like pcs status (Pacemaker), sys.dm_hadr_availability_group_states (SQL Server), or third-party monitoring (e.g., Nagios, Prometheus) to track cluster health.
• Backups: Regularly back up databases and transaction logs—even with AGs, backups are critical for disaster recovery.
• Security: Encrypt endpoints (using certificates) and restrict access to the AG listener to trusted IPs.

By following these steps, you can achieve high availability for SQL Server on Ubuntu, ensuring minimal downtime for your applications.