| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | |
|---|---|---|---|---|---|---|---|---|---|---|
Configure a read-scale availability group (SQL Server on Linux) |
Learn about configuring a SQL Server Always On Availability Group (AG) for read-scale workloads on Linux. |
rwestMSFT |
randolphwest |
vanto, randolphwest |
04/11/2022 |
sql |
linux |
conceptual |
|
[!INCLUDE SQL Server - Linux]
This article explains how to create a SQL Server Always On Availability Group (AG) on Linux without a cluster manager. This architecture provides read-scale only. It doesn't provide high availability.
There are two types of architectures for AGs. An architecture for high availability uses a cluster manager to provide improved business continuity. To create the high-availability architecture, see Configure SQL Server Always On Availability Group for high availability on Linux.
An availability group with CLUSTER_TYPE = NONE can include replicas hosted on different operating system platforms. It can't support high availability.
[!INCLUDE Create prerequisites]
Create the AG. Set CLUSTER_TYPE = NONE. In addition, set each replica with FAILOVER_MODE = MANUAL. Client applications running analytics or reporting workloads can directly connect to the secondary databases. You also can create a read-only routing list. Connections to the primary replica forward read connection requests to each of the secondary replicas from the routing list in a round-robin fashion.
The following Transact-SQL script creates an AG named ag1. The script configures the AG replicas with SEEDING_MODE = AUTOMATIC. This setting causes SQL Server to automatically create the database on each secondary server after it's added to the AG. Update the following script for your environment. Replace the <node1> and <node2> values with the names of the SQL Server instances that host the replicas. Replace the <5022> value with the port you set for the endpoint. Run the following Transact-SQL script on the primary SQL Server replica:
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;The following Transact-SQL script joins a server to an AG named ag1. Update the script for your environment. On each secondary SQL Server replica, run the following Transact-SQL script to join the AG:
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;[!INCLUDE Create post]
This AG isn't a high-availability configuration. If you need high availability, follow the instructions at Configure an Always On Availability Group for SQL Server on Linux. Specifically, create the AG with CLUSTER_TYPE=WSFC (in Windows) or CLUSTER_TYPE=EXTERNAL (in Linux). You can then integrate with a cluster manager, by using either Windows Server failover clustering on Windows, or Pacemaker on Linux.
There are two ways to connect to read-only secondary replicas. Applications can connect directly to the SQL Server instance that hosts the secondary replica and query the databases. They also can use read-only routing, which requires a listener.
[!INCLUDEForce failover]