Skip to content

Latest commit

 

History

History
86 lines (62 loc) · 5.58 KB

configure-read-scale-availability-groups.md

File metadata and controls

86 lines (62 loc) · 5.58 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic
Configure read-scale for an availability group
Learn how to configure your SQL Server Always On availability group for read-scale workloads on Windows.
MashaMSFT
mathoma
05/24/2018
sql
availability-groups
how-to

Configure read-scale for an Always On availability group

[!INCLUDE SQL Server]

You can configure a SQL Server Always On availability group for read-scale workloads on Windows. There are two types of architecture for availability groups:

  • An architecture for high availability that uses a cluster manager to provide improved business continuity and that can include readable-secondary replicas. To create this high-availability architecture, see Create and configure availability groups on Windows.
  • An architecture that supports only read-scale workloads.

This article explains how to create an availability group 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 that are hosted on a variety of operating system platforms. It cannot support high availability. For the Linux operating system, see Configure a SQL Server availability group for read-scale on Linux.

[!INCLUDE Create prerequisites]

Create an availability group

Create an availability group. Set CLUSTER_TYPE = NONE. In addition, set each replica with FAILOVER_MODE = NONE. Client applications that run analytics or reporting workloads can directly connect to the secondary databases. You can also 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 availability group named 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 <node1> and <node2> values with the names of the SQL Server instances that host the replicas. Replace the <5022> value with the port that 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;

Join secondary SQL Server instances to the availability group

The following Transact-SQL script joins a server to an availability group named ag1. Update the script for your environment. To join the availability group, run the following Transact-SQL script on each secondary SQL Server replica:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

[!INCLUDE Create post]

This availability group 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.

Connect to read-only secondary replicas

You can connect to read-only secondary replicas in either of two ways:

  • Applications can connect directly to the SQL Server instance that hosts the secondary replica and query the databases. For more information, see Readable secondary replicas.
  • Applications can also use read-only routing, which requires a listener. If you are deploying a read-scale scenario without a cluster manager, you can still create a listener that points to the IP address of the current primary replica and the same port as SQL Server listens on. You will need to recreate the listener to point to the new primary IP address after a failover. For more information, see Read-only routing.

Fail over the primary replica on a read-scale availability group

[!INCLUDEForce failover]

Note that if you are using a listener to connect, you will need to re-create the listener after performing the failover.

Next steps