Skip to content

sql-server-linux-availability-group-configure-rs.md

Latest commit

 

History

History
148 lines (104 loc) · 8.59 KB

sql-server-linux-availability-group-configure-rs.md

File metadata and controls

148 lines (104 loc) · 8.59 KB
title Configure read-scale availability group for SQL Server on Linux | Microsoft Docs
description
author MikeRayMSFT
ms.author mikeray
manager jhubbard
ms.date 05/17/2017
ms.topic article
ms.prod sql-linux
ms.technology database-engine
ms.assetid

Configure read-scale availability group for SQL Server on Linux

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.

[!INCLUDE Create Prereq]

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 **<node1>** and **<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;

Note

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;

[!INCLUDE Create Post]

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.

Readable secondary replicas

Read only routing

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.

  1. Make the target secondary replica synchronous commit.

    ALTER AVAILABILITY GROUP [ag1] MODIFY REPLICA ON N'**<node2>*' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

  2. Update REQUIRED_COPIES_TO_COMMIT to 1.

    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;

  3. 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);

  4. Promote the target secondary replica to primary.

    ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

    [!NOTE] 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.

Next steps

Configure distributed availability group

Learn more about availability groups