Настройка маршрутизации только для чтения в группе доступности Always On

Область применения:SQL Server

Чтобы настроить группу доступности Always On для поддержки маршрутизации только для чтения в SQL Server, можно использовать Transact-SQL или PowerShell. Маршрутизация только для чтения означает возможность SQL Server направлять соответствующие запросы на подключение, предназначенные только для чтения, к доступной вторичной реплике для чтения в группе доступности Always On (то есть к реплике, настроенной на разрешение рабочих нагрузок только для чтения при работе во вторичной роли). Для поддержки маршрутизации только для чтения группа доступности должна иметь прослушиватель группы доступности. Клиенты, запрашивающие данные в режиме "Только для чтения", должны направлять свои запросы к данному прослушивателю, и в строке подключения клиента должно быть задано намерение приложения read-only, то есть это должны быть запросы только для чтения.

Маршрутизация только для чтения доступна в SQL Server 2016 (13.x) и более поздних версий.

Примечание.

Дополнительные сведения о настройке доступной для чтения вторичной реплики см. в статье Настройка доступа только для чтения в реплике доступности (SQL Server).

Предварительные требования

Какие свойства реплики необходимо настроить для поддержки маршрутизации только для чтения?

  • Для каждой доступной для чтения вторичной реплики, которая поддерживает маршрутизацию только для чтения, необходимо указать URL-адрес маршрутизации только для чтения. Этот URL-адрес вступает в силу, только если локальная реплика работает во вторичной роли. URL-адрес маршрутизации только для чтения должен быть указан для каждой реплики отдельно (если для реплики требуется подобная маршрутизация). Все URL-адреса маршрутизации только для чтения используются для направления запросов на соединение с намерением чтения к определенной доступной для чтения вторичной реплике. Как правило, каждой доступной для чтения вторичной реплике назначается URL-адрес маршрутизации только для чтения.

    Сведения о вычислении URL-адреса маршрутизации только для чтения для реплики доступности см. в разделе Вычисление значения read_only_routing_url для AlwaysOn

  • Для каждой реплики доступности, которая должна поддерживать маршрутизацию только для чтения и при этом является первичной, необходимо задать список маршрутизации только для чтения. Указанный список маршрутизации для доступа только на чтение применяется только тогда, когда локальная реплика работает в роли первичной. Этот список должен указываться отдельно для каждой реплики, по мере необходимости. Как правило, каждый список маршрутизации только для чтения будет содержать все URL-адреса маршрутизации только для чтения, причем URL-адрес локальной реплики будет идти в конце списка.

    Примечание.

    Запросы на подключение с намерением чтения направляются к первой доступной записи в списке маршрутизации только для чтения текущей первичной реплики. Однако для реплик только для чтения поддерживается балансировка нагрузки. Дополнительные сведения см. в разделе Настройка балансировки нагрузки между репликами только для чтения.

Примечание.

Сведения о прослушивателях групп доступности и дополнительные сведения о маршрутизации только для чтения см. в статье о прослушивателях групп доступности, возможности подключения клиентов и отработке отказа приложений (SQL Server).

Разрешения

Задача Разрешения
Настройка реплик при создании группы доступности Требуется членство в предопределенной роли сервера sysadmin, а также одно из следующих разрешений: CREATE AVAILABILITY GROUP на уровне сервера, ALTER ANY AVAILABILITY GROUP или CONTROL SERVER.
Изменение реплики доступности Требуется разрешение ALTER AVAILABILITY GROUP для группы доступности, разрешение CONTROL AVAILABILITY GROUP, разрешение ALTER ANY AVAILABILITY GROUP или разрешение CONTROL SERVER.

Использование Transact-SQL

Настройка списка маршрутизации только для чтения

Выполните действия ниже, чтобы настроить маршрутизацию только для чтения с помощью Transact-SQL. Пример кода см. в подразделе Пример (Transact-SQL)далее в этом разделе.

  1. Подключитесь к экземпляру сервера, на котором находится первичная реплика.

  2. Если вы указываете реплику для новой группы доступности, используйте CREATE AVAILABILITY GROUP инструкцию Transact-SQL. Если вы добавляете или изменяете реплику для существующей группы доступности, используйте ALTER AVAILABILITY GROUP инструкцию Transact-SQL.

    • Чтобы настроить маршрутизацию только для чтения для вторичной роли, укажите в предложении ADD REPLICA или MODIFY REPLICA WITH параметр SECONDARY_ROLE следующим образом:

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

      Существуют следующие параметры URL-адреса маршрутизации только для чтения.

      системный адрес
      Это строка, такая как адрес системы, полное доменное имя или IP-адрес, однозначно идентифицирующий целевую компьютерную систему.

      порт
      Номер порта, используемый ядром СУБД экземпляра SQL Server.

      Например: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      В предложении MODIFY REPLICA параметр ALLOW_CONNECTIONS не является обязательным, если реплика уже настроена для соединений только для чтения.

      Дополнительные сведения см. в разделе Вычисление значения read_only_routing_url для AlwaysOn.

    • Чтобы настроить маршрутизацию только для чтения для первичной роли, в предложении ADD REPLICA или MODIFY REPLICA WITH укажите параметр PRIMARY_ROLE следующим образом:

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

      где server обозначает экземпляр сервера, на котором размещена вторичная реплика, доступная только для чтения, в группе доступности.

      Например: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      Примечание.

      Необходимо настроить URL-адрес маршрутизации только для чтения перед настройкой списка маршрутизации только для чтения.

Настройка балансировки нагрузки между репликами только для чтения

Начиная с SQL Server 2016 (13.x), можно настроить балансировку нагрузки в наборе реплик только для чтения. Раньше при маршрутизации только для чтения трафик всегда направлялся на первую доступную реплику в списке маршрутизации. Чтобы воспользоваться преимуществами этой функции, используйте один уровень вложенных скобок вокруг экземпляров сервера READ_ONLY_ROUTING_LIST в CREATE AVAILABILITY GROUP или ALTER AVAILABILITY GROUP командах.

Например, следующий список маршрутизации распределяет нагрузку запросов на подключение с намерением чтения между двумя репликами только для чтения: Server1 и Server2. Вложенные скобки вокруг этих серверов обозначают группу серверов с балансировкой нагрузки. Если в этом наборе ни одна из реплик не доступна, будет предпринята попытка последовательно подключиться к другим репликам, Server3 и Server4, в списке маршрутизации только для чтения.

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

Обратите внимание, что каждая запись в списке маршрутизации сама по себе может представлять собой набор реплик, доступных только для чтения, с балансировкой нагрузки. Это продемонстрировано в следующем примере.

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

Поддерживается только один уровень вложенных скобок.

Пример (Transact-SQL)

В следующем примере изменяются две реплики доступности существующей группы доступности AG1 для поддержки маршрутизации только для чтения в том случае, если одна из этих реплик в настоящий момент обладает первичной ролью. Чтобы определить экземпляры сервера, на которых размещена реплика доступности, в этом примере указаны имена экземпляров —COMPUTER01 и 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

Настройка списка маршрутизации только для чтения

Выполните действия ниже, чтобы настроить маршрутизацию только для чтения с помощью PowerShell. Пример кода см. в подразделе Пример (PowerShell)далее в этом разделе.

  1. Установите значение по умолчанию (cd) равным экземпляру сервера, на котором размещена первичная реплика.

  2. При добавлении реплики доступности в группу доступности воспользуйтесь командлетом New-SqlAvailabilityReplica . При изменении существующей реплики доступности воспользуйтесь командлетом Set-SqlAvailabilityReplica . Соответствующие параметры:

    • Чтобы настроить маршрутизацию только для чтения для вторичной роли, укажите параметр ReadonlyRoutingConnectionUrl"url".

      где URL-адрес — это полное доменное имя подключения (FQDN) и порт, используемый при маршрутизации в реплику для подключений только для чтения. Например: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      Дополнительные сведения см. в разделе Вычисление значения read_only_routing_url для AlwaysOn.

    • Чтобы настроить доступ соединения для первичной роли, укажите ReadonlyRoutingList"server" [ ,...n ], где server обозначает экземпляр сервера, на котором размещена вторичная реплика только для чтения в группе доступности. Например: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      Примечание.

      Необходимо настроить URL-адрес маршрутизации только для чтения для реплики перед тем, как перейти к настройке ее списка маршрутизации.

    Примечание.

    Чтобы просмотреть синтаксис командлета, используйте командлет Get-Help в среде SQL Server PowerShell. Дополнительные сведения см. в разделе Get Help SQL Server PowerShell.

Настройка и использование поставщика SQL Server PowerShell

Пример (PowerShell)

В следующем примере выполняется настройка первичной реплики и одной вторичной реплики в группе доступности с использованием маршрутизации только для чтения. Сначала в этом примере каждой реплике назначается URL-адрес для маршрутизации запросов только для чтения. Затем он задает список маршрутизации только для чтения для первичной реплики. Соединения со свойством «ReadOnly» в строке подключения будут перенаправляться на вторичную реплику. Если такая вторичная реплика недоступна для чтения (в соответствии со значением параметра ConnectionModeInSecondaryRole ), подключение направляется обратно в первичную реплику.

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

Продолжение: после настройки маршрутизации только для чтения

Как только текущая первичная реплика и вторичные реплики, доступные для чтения, будут настроены в обеих ролях для поддержки маршрутизации подключений только для чтения, вторичные реплики, доступные для чтения, смогут принимать от клиентов, подключающихся через прослушиватель группы доступности, запросы на подключение с намерением только чтения.

Совет

При использовании bcp Utility или sqlcmd Utilityможно указать доступ только для чтения к любой вторичной реплике, которой разрешен доступ только для чтения. Для этого нужно указать параметр -K ReadOnly .

Требования и рекомендации для клиентских строк подключения

В случае если клиентское приложение использует маршрутизацию только для чтения, его строка подключения должна удовлетворять следующим требованиям.

  • Используйте протокол TCP.

  • Задайте атрибут/свойство намерения приложения как «только для чтения».

  • Укажите прослушиватель группы доступности, настроенный для поддержки маршрутизации только для чтения.

  • Укажите базу данных в этой группе доступности.

Кроме того, мы рекомендуем включить в строках подключения отработку отказа между несколькими подсетями, что обеспечивает отдельный параллельный клиентский поток для каждой реплики в каждой подсети. Это сводит к минимуму время повторного подключения клиента после переключения при отказе.

Синтаксис строки подключения зависит от поставщика SQL Server, который использует приложение. Следующий пример строки подключения для поставщика данных .NET Framework 4.0.2 для SQL Server демонстрирует фрагменты строки подключения, которые необходимы и рекомендуются для работы с маршрутизацией только для чтения.

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

Дополнительные сведения о намерении приложения на доступ только для чтения и маршрутизации подключений только для чтения см. в статье Прослушиватели групп доступности, подключение клиентов и отработка отказа приложения (SQL Server).

Если маршрутизация только для чтения работает неправильно

Дополнительные сведения об устранении неполадок с конфигурацией маршрутизации только для чтения см. в разделе Маршрутизация только для чтения работает неправильно.

Возврат к поведению маршрутизации по умолчанию

Начиная с SQL Server 2025 (17.x), можно указать NONE как READ_WRITE_ROUTING_URL или READ_ONLY_ROUTING_URL место назначения, чтобы отменить указанную маршрутизацию для реплики доступности и направить трафик в соответствии с поведением по умолчанию. Дополнительные сведения см. в команде ALTER AVAILABILITY GROUP Transact-SQL.

Дальнейшие шаги

Просмотр конфигурации маршрутизации только для чтения

Настройка доступа соединения клиентов

Использование строк подключения в приложениях

Блоги

Дополнительное содержимое

  • Last updated on