가용성 그룹에 대한 읽기 전용 라우팅 구성(SQL Server)Configure Read-Only Routing for an Availability Group (SQL Server)

SQL Server 2017SQL Server 2017에서 읽기 전용 라우팅을 지원하도록 Always On 가용성 그룹을 구성하려면 Transact-SQLTransact-SQL 이나 PowerShell을 사용합니다.To configure an Always On availability group to support read-only routing in SQL Server 2017SQL Server 2017, you can use either Transact-SQLTransact-SQL or PowerShell. 읽기 전용 라우팅 이란 특정 읽기 전용 연결 요청을 Always On의 사용 가능하고 SQL ServerSQL Server 읽기 가능한 보조 복제본 (즉, 보조 역할로 실행될 때 읽기 전용 작업을 허용하도록 구성된 복제본)으로 라우팅하는 기능을 말합니다.Read-only routing refers to the ability of SQL ServerSQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). 읽기 전용 라우팅을 지원하려면 가용성 그룹에 가용성 그룹 수신기가 있어야 합니다.To support read-only routing, the availability group must possess an availability group listener. 읽기 전용 클라이언트는 해당 연결 요청을 이 수신기에 전달해야 하며, 클라이언트의 연결 문자열에서는 응용 프로그램 의도를 "읽기 전용"으로 지정해야 합니다.Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." 즉, 해당 연결 요청은 읽기 전용 연결 요청이어야 합니다.That is, they must be read-intent connection requests.

참고

읽기 가능한 보조 복제본을 구성하는 방법은 가용성 복제본에 대한 읽기 전용 액세스 구성(SQL Server)가 있어야 합니다.For information about how to configure a readable secondary replica, see Configure Read-Only Access on an Availability Replica (SQL Server).

시작하기 전 주의 사항 Before You Begin

필수 구성 요소 Prerequisites

읽기 전용 라우팅을 지원하도록 구성하는 데 필요한 복제본 속성 What Replica Properties Do you Need to Configure to Support Read-Only Routing?

  • 읽기 전용 라우팅을 지원할 읽기 가능한 보조 복제본 각각에 대해 읽기 전용 라우팅 URL을 지정해야 합니다.For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. 이 URL은 로컬 복제본이 보조 역할로 실행되는 경우에만 적용됩니다.This URL takes effect only when the local replica is running under the secondary role. 필요에 따라 복제본별로 읽기 전용 라우팅 URL을 지정해야 합니다.The read-only routing URL must be specified on a replica-by-replica basis, as needed. 각 읽기 전용 라우팅 URL은 읽기 전용 연결 요청을 지정된 읽기 가능한 보조 복제본으로 라우팅하는 데 사용됩니다.Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. 일반적으로 모든 읽기 가능한 보조 복제본에는 읽기 전용 라우팅 URL이 할당됩니다.Typically, every readable secondary replica is assigned a read-only routing URL.

    가용성 복제본에 대한 읽기 전용 라우팅 URL을 계산하는 방법은 Always On에 대한 read_only_routing_url 계산For information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for Always On

  • 주 복제본으로 사용될 때 읽기 전용 라우팅을 지원하도록 할 각 가용성 복제본에 대해 읽기 전용 라우팅 목록을 지정해야 합니다.For each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. 지정된 읽기 전용 라우팅 목록은 로컬 복제본이 주 역할로 실행되는 경우에만 적용됩니다.A given read-only routing list takes effect only when the local replica is running under the primary role. 필요에 따라 복제본별로 이 목록을 지정해야 합니다.This list must be specified on a replica-by-replica basis, as needed. 일반적으로 각 읽기 전용 라우팅 목록의 끝에는 로컬 복제본의 URL과 함께 모든 읽기 전용 라우팅 URL이 포함됩니다.Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

    참고

    읽기 전용 연결 요청은 현재 주 복제본의 읽기 전용 라우팅 목록에 있는 사용 가능한 첫 번째 항목으로 라우팅됩니다.Read-intent connection requests are routed to the first available entry on the read-only routing list of the current primary replica. 그러나 읽기 전용 복제본에 대한 부하 분산이 지원됩니다.However, load-balancing across read-only replicas is supported. 자세한 내용은 읽기 전용 복제본에 대한 부하 분산 구성을 참조하세요.For more information, see Configure load-balancing across read-only replicas.

참고

가용성 그룹 수신기 및 읽기 전용 라우팅에 대한 자세한 내용은 가용성 그룹 수신기, 클라이언트 연결 및 응용 프로그램 장애 조치(failover)(SQL Server)가 있어야 합니다.For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

보안 Security

사용 권한 Permissions

태스크Task 사용 권한Permissions
가용성 그룹을 만들 때 복제본을 구성하려면To configure replicas when creating an availability group CREATE AVAILABILITY GROUP 서버 권한, ALTER ANY AVAILABILITY GROUP 권한, CONTROL SERVER 권한 중 하나와 sysadmin 고정 서버 역할의 멤버 자격이 필요합니다.Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
가용성 복제본을 수정하려면To modify an availability replica 가용성 그룹에 대한 ALTER AVAILABILITY GROUP 권한, CONTROL AVAILABILITY GROUP 권한, ALTER ANY AVAILABILITY GROUP 권한 또는 CONTROL SERVER 권한이 필요합니다.Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Transact-SQL 사용 Using Transact-SQL

읽기 전용 라우팅 목록 구성Configure a read-only routing list

Transact-SQL을 사용하여 읽기 전용 라우팅을 구성하려면 다음 단계를 수행합니다.Use the following steps to configure read-only routing using Transact-SQL. 코드 예제를 보려면 이 섹션의 뒷부분에 나오는 예(Transact-SQL)를 참조하세요.For a code example, see Example (Transact-SQL), later in this section.

  1. 주 복제본을 호스팅하는 서버 인스턴스에 연결합니다.Connect to the server instance that hosts the primary replica.

  2. 새 가용성 그룹에 대한 복제본을 지정하려는 경우 CREATE AVAILABILITY GROUP Transact-SQLTransact-SQL 문을 사용합니다.If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUP Transact-SQLTransact-SQL statement. 기존 가용성 그룹에 대한 복제본을 추가하거나 수정하려는 경우 ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL 문을 사용합니다.If you are adding or modifying a replica for an existing availability group, use the ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL statement.

    • 보조 역할에 대한 읽기 전용 라우팅을 구성하려면 ADD REPLICA 또는 MODIFY REPLICA WITH 절에서 다음과 같이 SECONDARY_ROLE 옵션을 지정합니다.To configure read-only routing for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

      읽기 전용 라우팅 URL의 매개 변수는 다음과 같습니다.The parameters of the read-only routing URL are as follows:

      system-addresssystem-address
      대상 컴퓨터 시스템을 명확하게 식별하는 시스템 이름, 정규화된 도메인 이름 또는 IP 주소 등의 문자열입니다.Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

      portport
      SQL ServerSQL Server 인스턴스의 데이터베이스 엔진에서 사용하는 포트 번호입니다.Is a port number that is used by the Database Engine of the SQL ServerSQL Server instance.

      예를 들어 SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')For example: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      복제본이 읽기 전용 연결을 허용하도록 이미 구성되어 있는 경우 MODIFY REPLICA 절에서 ALLOW_CONNECTIONS는 선택 사항입니다.In a MODIFY REPLICA clause the ALLOW_CONNECTIONS is optional if the replica is already configured to allow read-only connections.

      자세한 내용은 Always On에 대한 read_only_routing_url 계산을 참조하세요.For more information, see Calculating read_only_routing_url for Always On.

    • 주 역할에 대한 읽기 전용 라우팅을 구성하려면 ADD REPLICA 또는 MODIFY REPLICA WITH 절에서 다음과 같이 PRIMARY_ROLE 옵션을 지정합니다.To configure read-only routing for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

      PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(‘server [ ,...n ] ))PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(‘server [ ,...n ] ))

      여기서 server 는 가용성 그룹의 읽기 전용 보조 복제본을 호스트하는 서버 인스턴스를 식별합니다.where, server identifies a server instance that hosts a read-only secondary replica in the availability group.

      예를 들어 PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))For example: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      참고

      읽기 전용 라우팅 목록을 구성하기 전에 읽기 전용 라우팅 URL을 설정해야 합니다.You must set the read-only routing URL before configuring the read-only routing list.

읽기 전용 복제본에 대한 부하 분산 구성 Configure load-balancing across read-only replicas

SQL Server 2016SQL Server 2016부터는 읽기 전용 복제본 집합에서 부하 분산을 구성할 수 있습니다.Beginning with SQL Server 2016SQL Server 2016, you can configure load-balancing across a set of read-only replicas. 이전에는 읽기 전용 라우팅에서 항상 라우팅 목록에서 사용 가능한 첫 번째 복제본으로 트래픽을 전송했습니다.Previously, read-only routing always directed traffic to the first available replica in the routing list. 이 기능을 사용하려면 CREATE AVAILABILITY GROUP 또는 ALTER AVAILABILITY GROUP 명령에서 READ_ONLY_ROUTING_LIST 서버 인스턴스 주위에 한 수준의 중첩 괄호를 사용합니다.To take advantage of this feature, use one level of nested parentheses around the READ_ONLY_ROUTING_LIST server instances in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP commands.

예를 들어 다음 라우팅 목록은 두 읽기 전용 복제본 Server1Server2에 대해 읽기 전용 연결 요청을 부하 분산합니다.For example, the following routing list load balances read-intent connection request across two read-only replicas, Server1 and Server2. 이 두 서버를 묶는 중첩 괄호는 부하 분산되는 집합을 식별합니다.The nested parentheses that surround these servers identify the load-balanced set. 해당 집합에서 두 복제본을 모두 사용할 수 없으면 읽기 전용 라우팅 목록의 다른 복제본인 Server3Server4에 순서대로 계속 연결을 시도합니다.If neither replica is available in that set, it will proceed to attempt to sequentially connect to the other replicas, Server3 and Server4, in the read-only routing list.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')  

라우팅 목록의 각 항목 자체가 부하 분산된 읽기 전용 복제본 집합일 수 있습니다.Note that each entry in the routing list can itself be a set of load-balanced read-only replicas. 다음 예에 이러한 부하 분산 방식이 나와 있습니다.The following example demonstrates this.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')  

하나의 중첩 괄호 수준만 사용할 수 있습니다.Only one level of nested parentheses is supported.

예(Transact-SQL) Example (Transact-SQL)

다음 예에서는 기존 가용성 그룹 AG1 의 두 가용성 복제본 중 하나가 현재 주 역할을 소유하고 있는 경우 가용성 복제본이 읽기 전용 라우팅을 지원하도록 수정합니다.The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. 이 예에서는COMPUTER01COMPUTER02를 인스턴스 이름으로 지정하여 가용성 복제본을 호스팅하는 서버 인스턴스를 식별합니다.To identify the server instances that host the availability replica, this example specifies the instance names—COMPUTER01 and COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  

ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO  

PowerShell 사용 Using PowerShell

읽기 전용 라우팅 목록 구성Configure a read-only routing list

PowerShell을 사용하여 읽기 전용 라우팅을 구성하려면 다음 단계를 수행합니다.Use the following steps to configure read-only routing using PowerShell. 코드 예제를 보려면 이 섹션의 뒷부분에 나오는 예제(PowerShell)을 참조하세요.For a code example, see Example (PowerShell), later in this section.

  1. 기본값(cd)을 주 복제본을 호스트하는 서버 인스턴스로 설정합니다.Set default (cd) to the server instance that hosts the primary replica.

  2. 가용성 그룹에 가용성 복제본을 추가하는 경우 New-SqlAvailabilityReplica cmdlet을 사용합니다.When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. 기존 가용성 복제본을 수정하는 경우 Set-SqlAvailabilityReplica cmdlet을 사용합니다.When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. 관련 매개 변수는 다음과 같습니다.The relevant parameters are as follows:

    • 보조 역할에 대한 읽기 전용 라우팅을 구성하려면 ReadonlyRoutingConnectionUrl"url" 매개 변수입니다.To configure read-only routing for the secondary role, specify the ReadonlyRoutingConnectionUrl"url" parameter.

      여기서 url 은 읽기 전용 연결을 위해 복제본으로 라우팅할 때 사용할 연결 FQDN(정규화된 도메인 이름) 및 포트입니다.where, url is the connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections. 예를 들어 -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"For example: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      자세한 내용은 Always On에 대한 read_only_routing_url 계산을 참조하세요.For more information, see Calculating read_only_routing_url for Always On.

    • 주 역할에 대한 연결 액세스를 구성하려면 ReadonlyRoutingList"server" [ ,...n ]를 지정합니다. 여기서 server 는 가용성 그룹의 읽기 전용 보조 복제본을 호스트하는 서버 인스턴스를 식별합니다.To configure connection access for the primary role, specify ReadonlyRoutingList"server" [ ,...n ], where server identifies a server instance that hosts a read-only secondary replica in the availability group. 예를 들어 -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"For example: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      참고

      복제본의 읽기 전용 라우팅 목록을 구성하기 전에 읽기 전용 라우팅 URL을 설정해야 합니다.You must set the read-only routing URL of a replica before configuring its read-only routing list.

    참고

    cmdlet의 구문을 보려면 PowerShell 환경에서 Get-Help SQL ServerSQL Server cmdlet을 사용합니다.To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 자세한 내용은 Get Help SQL Server PowerShell을 참조하세요.For more information, see Get Help SQL Server PowerShell.

SQL Server PowerShell 공급자 설정 및 사용Set up and use the SQL Server PowerShell provider

예제(PowerShell) Example (PowerShell)

다음 예에서는 읽기 전용 라우팅을 위해 가용성 그룹에 주 복제본과 보조 복제본 하나를 구성합니다.The following example configures the primary replica and one secondary replica in an availability group for read-only routing. 먼저, 이 예에서는 각 복제본에 읽기 전용 라우팅 URL을 할당합니다.First, the example assigns a read-only routing URL to each replica. 그런 다음 주 복제본에 읽기 전용 라우팅 목록을 설정합니다.Then it sets the read-only routing list on the primary replica. 연결 문자열에 "ReadOnly" 속성이 설정된 연결은 보조 복제본으로 리디렉션됩니다.Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. 이 보조 복제본을 읽을 수 없는 경우( ConnectionModeInSecondaryRole 설정으로 확인) 연결이 주 복제본으로 다시 디렉션됩니다.If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica  

후속 작업: 읽기 전용 라우팅을 구성한 후의 작업 Follow Up: After Configuring Read-Only Routing

현재 주 복제본과 읽기 가능한 보조 복제본 두 역할 모두가 읽기 전용 라우팅을 지원하도록 구성되고 나면 읽기 가능한 보조 복제본은 가용성 그룹 수신기를 통해 연결한 클라이언트로부터 읽기 전용 연결 요청을 받을 수 있습니다.Once the current primary replica and the readable secondary replicas are configured to support read-only routing in both roles, the readable secondary replicas can receive read read-intent connection requests from clients that connect via the availability group listener.

bcp 유틸리티 또는 sqlcmd 유틸리티를 사용하는 경우 -K ReadOnly 스위치를 지정하여 읽기 전용 액세스를 사용하도록 설정된 보조 복제본에 대한 읽기 전용 액세스를 지정할 수 있습니다.When using the bcp Utility or sqlcmd Utility, you can specify read-only access to any secondary replica that is enabled for read-only access by specifying the -K ReadOnly switch.

클라이언트 연결 문자열에 대한 요구 사항 및 권장 사항 Requirements and Recommendations for Client Connection-Strings

클라이언트 응용 프로그램에서 읽기 전용 라우팅을 사용하려면 해당 연결 문자열이 다음 요구 사항을 충족해야 합니다.For a client application to use read-only routing, its connection string must satisfy the following requirements:

  • TCP 프로토콜을 사용합니다.Use the TCP protocol.

  • 응용 프로그램 의도 특성/속성을 읽기 전용으로 설정합니다.Set the application intent attribute/property to readonly.

  • 읽기 전용 라우팅을 지원하도록 구성된 가용성 그룹의 수신기를 참조합니다.Reference the listener of an availability group that is configured to support read-only routing.

  • 해당 가용성 그룹의 데이터베이스를 참조합니다.Reference a database in that availability group.

    또한 연결 문자열에서 각 서브넷의 각 복제본에 대해 병렬 클라이언트 스레드를 지원하는 다중 서브넷 장애 조치(Failover)를 설정하는 것이 좋습니다.In addition, we recommend that connection strings enable multi-subnet failover, which supports a parallel client thread for each replica on each subnet. 이렇게 하면 장애 조치 후 클라이언트 재연결 시간이 최소화됩니다.This minimizes client reconnection time after a failover.

    연결 문자열 구문은 응용 프로그램에서 사용하는 SQL Server 공급자에 따라 달라집니다.The syntax for a connection string depends on the SQL Server provider an application is using. 다음 예에 나온 .NET Framework Data Provider 4.0.2 for SQL Server용 연결 문자열은 읽기 전용 라우팅에 사용해야 하며 권장되는 연결 문자열을 보여 줍니다.The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True  

읽기 전용 응용 프로그램 방식 및 읽기 전용 라우팅에 대한 자세한 내용은 가용성 그룹 수신기, 클라이언트 연결 및 응용 프로그램 장애 조치(failover)(SQL Server)가 있어야 합니다.For more information about read-only application intent and read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

읽기 전용 라우팅이 올바르게 작동하지 않는 경우If Read-Only Routing is Not Working Correctly

읽기 전용 라우팅 구성 문제를 해결하는 방법은 읽기 전용 라우팅이 올바르게 작동하지 않음을 참조하세요.For information about troubleshooting a read-only routing configuration, see Read-Only Routing is Not Working Correctly.

읽기 전용 라우팅 구성을 보려면To view read-only routing configurations

참고 항목See Also

Always On 가용성 그룹 개요(SQL Server) Overview of Always On Availability Groups (SQL Server)
Always On 가용성 그룹 개요(SQL Server) Overview of Always On Availability Groups (SQL Server)
활성 보조: 읽기 가능한 보조 복제본(Always ON 가용성 그룹) Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)
가용성 복제본에 대한 클라이언트 연결 액세스 정보(SQL Server) About Client Connection Access to Availability Replicas (SQL Server)
가용성 그룹 수신기, 클라이언트 연결 및 응용 프로그램 장애 조치(SQL Server)Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)