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

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

По умолчанию к первичной реплике разрешён как доступ для чтения и записи, так и доступ с намерением чтения, а подключения к вторичным репликам группы доступности Always On не разрешены. В этом разделе описывается, как настроить доступ к подключениям на реплике доступности группы доступности Always On в SQL Server с помощью SQL Server Management Studio, Transact-SQL или PowerShell.

Сведения о последствиях включения доступа только для чтения для вторичной реплики и введение в доступ к подключениям см. в статьях О доступе клиентских подключений к репликам доступности (SQL Server) и Активные вторичные реплики: доступные для чтения вторичные реплики (группы доступности Always On).

Требования и ограничения

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

Разрешения

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

Использование среды SQL Server Management Studio

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

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

2. Разверните узел Высокий уровень доступности AlwaysOn и узел Группы доступности .

3. Щелкните группу доступности, реплику которой нужно изменить.

4. Щелкните правой кнопкой мыши реплику доступности и выберите пункт Свойства.

5. В диалоговом окне Свойства реплики доступности можно изменить доступ к соединению для первичной и вторичной роли следующим образом:

   • Для вторичной роли выберите новое значение в раскрывающемся списке Доступная для чтения вторичная следующим образом.

     Нет
     Подключения пользователей ко вторичным базам данных этой реплики не допускаются. Они недоступны для чтения. Этот параметр принимается по умолчанию.

     Только для чтения
     Для вторичных баз данных этой реплики разрешены только подключения для чтения. Все вторичные базы данных доступны для чтения.

     Да
     Разрешены любые подключения к вторичным базам данных этой реплики, но только для доступа на чтение. Все вторичные базы данных доступны для чтения.

   • Для первичной роли выберите новое значение в раскрывающемся списке Соединения в первичной роли следующим образом:

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

     Разрешить подключения для чтения и записи
     Если свойство «Назначение приложения» имеет значение ReadWrite или не задано, то соединение разрешено. Соединения, у которых свойство соединения «Назначение приложения» равно ReadOnly , не разрешены. Это может помочь предотвратить ошибочное подключение клиентами рабочей нагрузки, предназначенной для чтения, к основной реплике. Дополнительные сведения о свойстве подключения Application Intent см. в разделе Using Connection String Keywords with SQL Server Native Client.

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

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

Примечание.

Пример этой процедуры см. в подразделе Примеры (Transact-SQL)далее в этом разделе.

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

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

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

     ВТОРИЧНАЯ_РОЛЬ ( РАЗРЕШИТЬ_ПОДКЛЮЧЕНИЯ = { НЕТ | ТОЛЬКО_ЧТЕНИЕ | ВСЕ } )

     где:

     Нет
     Прямые подключения к вторичным базам данных этой реплики не допускаются. Они недоступны для чтения. Этот параметр принимается по умолчанию.

     READ_ONLY
     Для вторичных баз данных этой реплики разрешены только подключения для чтения. Все вторичные базы данных доступны для чтения.

     ВСЕ
     Разрешены любые подключения к вторичным базам данных этой реплики, но только для доступа на чтение. Все вторичные базы данных доступны для чтения.

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

   PRIMARY_ROLE ( ALLOW_CONNECTIONS { READ_WRITE = | ALL } )

   где:

   READ_WRITE
   Соединения, у которых свойство "Назначение приложения" равно ReadOnly , не разрешены. Если свойство «Назначение приложения» имеет значение ReadWrite или не задано, то соединение разрешено. Дополнительные сведения о свойстве подключения Application Intent см. в разделе Using Connection String Keywords with SQL Server Native Client.

   ВСЕ
   Разрешаются все соединения с базами данных в первичной реплике. Этот параметр принимается по умолчанию.

Пример (Transact-SQL)

В следующем примере вторичная реплика добавляется в группу доступности с именем AG2. Автономный экземпляр сервера COMPUTER03\HADR_INSTANCE указан для размещения новой реплики доступности. Эта реплика настроена так, чтобы разрешать только подключения на чтение и запись при выполнении первичной роли и только подключения с намерением чтения при выполнении вторичной роли.

ALTER AVAILABILITY GROUP AG2   
   ADD REPLICA ON   
      'COMPUTER03\HADR_INSTANCE' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',  
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )  
         );   
GO  

Использование PowerShell

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

Примечание.

Пример кода см. в подразделе Пример (PowerShell)далее в этом разделе.

1. Перейдите в каталог экземпляра сервера (cd), на котором размещена первичная реплика.

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

   • Чтобы настроить доступ к соединению для вторичной роли, укажите параметр ConnectionModeInSecondaryRolesecondary_role_keyword , где secondary_role_keyword равно одному из следующих значений:

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

     AllowReadIntentConnectionsOnly
     Разрешаются только соединения с базами данных во вторичной реплике, у которых свойство "Назначение приложения" равно ReadOnly. Дополнительные сведения об этом свойстве см. в разделе Using Connection String Keywords with SQL Server Native Client.

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

   • Чтобы настроить доступ к соединению для первичной роли, укажите параметр ConnectionModeInPrimaryRoleprimary_role_keyword, где primary_role_keyword равно одному из следующих значений:

     AllowReadWriteConnections
     Соединения, у которых свойство "Назначение приложения" равно ReadOnly , не разрешены. Если для свойства «Application Intent» установлено значение ReadWrite или свойство подключения «Application Intent» не задано, подключение разрешено. Дополнительные сведения о свойстве подключения Application Intent см. в разделе Using Connection String Keywords with SQL Server Native Client.

     AllowAllConnections
     Разрешаются все соединения с базами данных в первичной реплике. Этот параметр принимается по умолчанию.

   Примечание.

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

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

• Поставщик SQL Server PowerShell

Пример (PowerShell)

В следующем примере параметры ConnectionModeInSecondaryRole и ConnectionModeInPrimaryRole устанавливаются в значение AllowAllConnections.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" `   
-InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" `   
-InputObject $primaryReplica  
  

Дальнейшие действия: После настройки доступа только на чтение для реплики доступности

Доступ только для чтения к доступной для чтения вторичной реплике

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

• Обеспечение возможности подключения клиентских приложений к доступным для чтения вторичным репликам.

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

Факторы, влияющие на триггеры и задания после аварийного переключения

Если у вас есть триггеры и задания, которые завершаются сбоем при выполнении на недоступной для чтения вторичной базе данных или на доступной для чтения вторичной базе данных, необходимо в сценариях триггеров и заданий проверять для данной реплики, является ли база данных первичной или доступной для чтения вторичной базой данных. Для получения этих сведений используйте функцию DATABASEPROPERTYEX, возвращающую свойство Updateability базы данных. Чтобы определить базу данных, доступную только для чтения, задайте в качестве значения READ_ONLY, как в примере ниже:

DATABASEPROPERTYEX([db name],'UpdateAbility') = N'READ_ONLY'  

Чтобы определить базу данных для чтения и записи, укажите в качестве значения READ_WRITE.

Связанные задачи

• Настройка маршрутизации только для чтения в группе доступности (SQL Server)

• Создание или настройка прослушивателя группы доступности (SQL Server)

Связанные материалы

• AlwaysOn: ценностное предложение читаемой вторичной реплики

• AlwaysOn: почему есть два варианта включения вторичной реплики для читаемой рабочей нагрузки?

• Always On: настройка вторичной реплики, доступной для чтения

• AlwaysOn: я только что включил читаемую вторичную реплику, но мой запрос был заблокирован. В чем дело?

• AlwaysOn: обеспечение доступности последних статистических данных о читаемой вторичной реплике, базе данных только для чтения и моментальном снимке базы данных

• AlwaysOn: проблемы со статистическими данными о базе данных только для чтения, моментальном снимке базы данных и вторичной реплике

• AlwaysOn: влияние на основную рабочую нагрузку при запуске рабочей нагрузки составления отчетов на вторичной реплике

• AlwaysOn: влияние сопоставления рабочей нагрузки составления отчетов на вторичной реплике на изоляцию моментальных снимков

• AlwaysOn: минимизация блокировок потока REDO при запуске рабочей нагрузки составления отчетов на вторичной реплике

• Always On: доступная для чтения вторичная реплика и задержка данных

См. также

Обзор групп доступности Always On (SQL Server)
Активные вторичные реплики: вторичные реплики, доступные для чтения (группы доступности Always On)
Сведения о доступе клиента к репликам доступности (SQL Server)