You can configure a read-scale availability group for SQL Server on Linux. There are two architectures for availability groups. A high availability (HA) architecture uses a cluster manager to provide improved business continuity. This architecture can also include read-scale replicas. To create the HA architecture, see Configure Always On availability group for SQL Server on Linux.
This document explains how to create a read-scale availability group without a cluster manager. This architecture only provides read-only scalability. It does not provide HA.
Before you create the availability group, you need to:
- Set your environment so all servers that will host availability replicas can communicate
- Install SQL Server
On Linux, you must create an availability group before adding it as a cluster resource to be managed by the cluster. This document provides an example that creates the availability group. For distribution specific instructions to create the cluster and add the availability group as a cluster resource, see the links under Next steps.
Update the computer name for each host
Each SQL Server name must be:
- 15 characters or less
- Unique within the network
To set the computer name, edit
/etc/hostname. The following script lets you edit
sudo vi /etc/hostname
Configure the hosts file
If hostnames are registered with their IP in the DNS server, there is no need to do the steps below. Validate that all nodes that are going to be part of the availability group configuration can communicate with each other (pinging the hostname should reply with the corresponding IP address). Also, make sure that /etc/hosts file does not contain a record that maps localhost IP address 127.0.0.1 with the hostname of the node.
The hosts file on every server contains the IP addresses and names of all servers that will participate in the availability group.
The following command returns the IP address of the current server:
sudo ip addr show
/etc/hosts. The following script lets you edit
sudo vi /etc/hosts
The following example shows
/etc/hosts on node1 with additions for node1 and node2. In this document node1 refers to the primary SQL Server replica. node2 refers to the secondary SQL Server.;
127.0.0.1 localhost localhost4 localhost4.localdomain4 ::1 localhost localhost6 localhost6.localdomain6 10.128.18.128 node1 10.128.16.77 node2
Install SQL Server
Install SQL Server. The following links point to SQL Server installation instructions for various distributions.
Enable Always On availability groups and restart sqlserver
Enable Always On availability groups on each node hosting SQL Server service, then restart
mssql-server. Run the following script:
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server
Enable AlwaysOn_health event session
You can optionaly enable Always On Availability Groups specific extended events to help with root-cause diagnosis when you troubleshoot an availability group.
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); GO
For more information about this XE session, see Always On Extended Events.
Create db mirroring endpoint user
The following Transact-SQL script creates a login named
dbm_login, and a user named
dbm_user. Update the script with a strong password. Run the following command on all SQL Servers to create the database mirroring endpoint user.
CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**'; CREATE USER dbm_user FOR LOGIN dbm_login;
Create a certificate
SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints.
The following Transact-SQL script creates a master key and certificate. It then backs the certificate up and secures the file with a private key. Update the script with strong passwords. Connect to the primary SQL Server and run the following Transact-SQL to create the certificate:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'; BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**' );
At this point your primary SQL Server replica has a certificate at
/var/opt/mssql/data/dbm_certificate.cer and a private key at
var/opt/mssql/data/dbm_certificate.pvk. Copy these two files to the same location on all servers that will host availability replicas. Use the mssql user or give permission to mssql user to access these files.
For example on the source server, the following command copies the files to the target machine. Replace the
cd /var/opt/mssql/data scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/
On the target server, give permission to mssql user to access the certificate.
cd /var/opt/mssql/data chown mssql:mssql dbm_certificate.*
Create the certificate on secondary servers
The following Transact-SQL script creates a master key and certificate from the backup that you created on the primary SQL Server replica. The command also authorizes the user to access the certificate. Update the script with strong passwords. The decryption password is the same password that you used to create the .pvk file in a previous step. Run the following script on all secondary servers to create the certificate.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'; CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', DECRYPTION BY PASSWORD = '**<Private_Key_Password>**' );
Create the database mirroring endpoints on all replicas
Database mirroring endpoints use Transmission Control Protocol (TCP) to send and receive messages between the server instances participating database mirroring sessions or hosting availability replicas. The database mirroring endpoint listens on a unique TCP port number.
The following Transact-SQL creates a listening endpoint named
Hadr_endpoint for the availability group. It starts the endpoint, and gives connect permission to the user that you created. Before you run the script, replace the values between
**< ... >**.
For this release, do not use a different IP address for the listener IP. We are working on a fix for this issue, but the only acceptable value for now is '0.0.0.0'.
Update the following Transact-SQL for your environment on all SQL Server instances:
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES ); ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED; GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
The TCP port on the firewall needs to be open for the listener port.
For complete information, see The Database Mirroring Endpoint (SQL Server).
Create the availability group
Create the availability group. In order to create the availability group for read-scale on Linux, set
CLUSTER_TYPE = NONE. In addition, set each replica with
FAILOVER_MODE = NONE. In this configuration the availability group does not provide HA, but it does provide read-scale. The client applications running analytics or reporting workloads can directly connect to the secondary databases. Or the customer can setup a read only routing list and connect to the primary that will forward the connection request to each of the secondary replicas from the routing list in a round robin fashion.
The following Transact-SQL script creates an availability group name
ag1. The script configures the availability group replicas with
SEEDING_MODE = AUTOMATIC. This setting causes SQL Server to automatically create the database on each secondary server after it is added to the availability group. Update the following script for your environment. Replace the
**<node2>** values with the names of the SQL Server instances that will host the replicas. Replace the
**<5022>** with the port you set for the endpoint. Run the following Transact-SQL on the primary SQL Server replica to create the availability group.
CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = NONE) FOR REPLICA ON N'**<node1>**' WITH ( ENDPOINT_URL = N'tcp://**<node1>:**<5022>**', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ), N'**<node2>**' WITH ( ENDPOINT_URL = N'tcp://**<node2>**:**<5022>**', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
Running the CREATE AVAILABILITY GROUP command will complete with a warning: "Attempt to access non-existent or uninitialized availability group with ID . This is usually an internal condition, such as the availability group is being dropped or the local WSFC node has lost quorum. In such cases, and no user action is required.". This is a known issue and product team is working on a fix. Meanwhile, users should assume command completed successfully.
Join secondary SQL Servers to the availability group
The following Transact-SQL script joins a server to an availability group named
ag1. Update the script for your environment. On each secondary SQL Server replica, run the following Transact-SQL to join the availability group.
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
Add a database to the availability group
Ensure the database you are adding to the Availability group is in full recovery mode and has a valid log backup. If this is a test database or a new database created, take a database backup. On the primary SQL Server, run the following Transact-SQL to create and back up a database called
CREATE DATABASE [db1]; ALTER DATABASE [db1] SET RECOVERY FULL; BACKUP DATABASE [db1] TO DISK = N'var/opt/mssql/data/db1.bak';
On the primary SQL Server replica, run the following Transact-SQL to add a database called
db1 to an availability group called
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];
Verify that the database is created on the secondary servers
On each secondary SQL Server replica, run the following query to see if the
db1 database has been created and is synchronized.
SELECT * FROM sys.databases WHERE name = 'db1'; GO SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;
This is not an HA configuration, if you need HA, follow the instructions at Configure Always On Availability Group for SQL Server on Linux. Specifically, create the availability group with
CLUSTER_TYPE=WSFC (in Windows) or
CLUSTER_TYPE=EXTERNAL (in Linux) and integrate with a cluster manager - either WSFC on Windows or Pacemaker on Linux.
Connect to read only secondary replicas
There are two ways to connect to the read only secondary replicas. Applications can connect directly to the SQL Server instance that hosts the secondary replica and query the databases, or they can use read-only routing. Read only routing requires a listener.
Failover primary replica on read-scale availability group
Each availability group has only one primary replica. The primary replica allows reads and writes. To change which replica is the primary, you can failover. In an availability group for HA, the cluster manager automates in the failover process. In a read-scale availability group, the failover process is manual. There are two ways to failover the primary replica in a read scale availability group.
Forced manual failover with data loss
Manual failover without data loss
Forced failover with data loss
Use this method when the primary replica is not available and can not be recovered. You can find more information about forced failover with data loss at Perform a Forced Manual Failover.
To issue a forced failover with data loss, connect to the SQL instance hosting the target secondary replica and run:
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;
Manual failover without data loss
Use this method when the primary replica is available, but you need to temporarly or permanently change the configuration and change the SQL instance that hosts the primary replica. One such case is during SQL Server upgrades. Before issuing manual failover, the user has to ensure that the target secondary replica is up to date, and there is no potential of data loss. The steps below describe how to achive this safely, without incuring data loss.
Make the target secondary replica synchronous commit.
ALTER AVAILABILITY GROUP [ag1] MODIFY REPLICA ON N'**<node2>*' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
This ensures no active transactions are committed to the primary replica without committing first to at least one synchronous secondary. The availabilty group is ready to failover when they synchronization_state_desc is SYNCHRONIZED and the sequence_number is the same for both primary and target secondary replica. Run this query to check:
SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id, drs.synchronization_state_desc, ag.sequence_number FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag WHERE drs.group_id = ag.group_id;
Demote the primary replica to secondary replica. After the primary replica is demoted, connections to the primary replica will not be able to write to the databases. Run this command on the SQL instance hosting the primary replica to to update the role to SECONDARY:
ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);
Promote the target secondary replica to primary.
ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;
To delete an availability group use DROP AVAILABILITY GROUP. For an availability group created with CLUSTER_TYPE NONE or EXTERNAL, the command has to be executed on all replicas part of the avilability group.