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

  • Статья

Применимо к:SQL Server

Чтобы настроить группу доступности Always On для поддержки маршрутизации только для чтения в SQL Server, можно использовать Transact-SQL или PowerShell. Маршрутизация только для чтения относится к способности SQL Server направлять соответствующие запросы на подключение только для чтения к доступной вторичной реплика AlwaysOn (т. е. реплика, которая настроена для разрешения рабочих нагрузок только для чтения при выполнении под вторичной ролью). Для поддержки маршрутизации только для чтения группа доступности должна иметь прослушиватель группы доступности. Клиенты, запрашивающие данные в режиме "Только для чтения", должны направлять свои запросы к данному прослушивателю, и в строке подключения клиента должно быть задано намерение приложения 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. Если вы указываете реплику для новой группы доступности, воспользуйтесь инструкцией Transact-SQL CREATE AVAILABILITY GROUP. Если вы добавляете или изменяете реплику существующей группы доступности, воспользуйтесь инструкцией Transact-SQL ALTER AVAILABILITY GROUP.

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

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

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

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

      port
      Номер порта, используемый ядро СУБД экземпляра 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 — это полное доменное имя и порт, которые используются для маршрутизации к реплике соединений только для чтения. Например: -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).

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

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

Next Steps

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

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

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

Блоги

Технические документы

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