| title | Configure a SQL Server Availability Group for read-scale on Windows | Microsoft Docs |
|---|---|
| description | |
| author | MashaMSFT |
| ms.author | mathoma |
| manager | craigg |
| ms.reviewer | |
| ms.date | 05/24/2018 |
| ms.topic | conceptual |
| ms.prod | sql |
| ms.suite | sql |
| ms.technology | high-availability |
| ms.tgt_pltfrm |
[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]
You can configure a SQL Server Always On Availability Group (AG) for read-scale workloads on Windows. There are two types of architectures for AGs. An architecture for high availability uses a cluster manager to provide improved business continuity and can include readable-secondary replicas. To create this high-availability architecture, see Creation and Configuration of Availability Groups on Windows. The other architecture supports only read-scale workloads. This article explains how to create an AG without a cluster manager for read-scale workloads. This architecture provides read-scale only. It doesn't provide high availability.
Note
An availability group with CLUSTER_TYPE = NONE can include replicas hosted on different operating system platforms. It cannot support high availability. For Linux operating system see Configure a SQL Server Availability Group for read-scale on Linux
[!INCLUDE Create prerequisites]
Create the AG. Set CLUSTER_TYPE = NONE. In addition, set each replica with FAILOVER_MODE = NONE. 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 is 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 or Creation and Configuration of Availability Groups on Windows.
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]