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

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

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

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

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

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

Разрешения

Задача	Разрешения
Настройка реплик при создании группы доступности	Требуется членство в фиксированной роли сервера 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 , не разрешены. Таким образом, клиент не сможет по ошибке подключить рабочую нагрузку с намерением чтения к первичной реплике. Дополнительные сведения о свойстве соединения «Назначение приложения» см. в разделе Using Connection String Keywords with SQL Server Native Client.

Использование 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 ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

     где:

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

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

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

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

   PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   где:

   READ_WRITE
   Соединения, у которых свойство "Назначение приложения" равно ReadOnly , не разрешены. Если свойство «Назначение приложения» имеет значение ReadWrite или не задано, то соединение разрешено. Дополнительные сведения о свойстве соединения «Назначение приложения» см. в разделе 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 , не разрешены. Если свойство «Назначение приложения» имеет значение ReadWrite или не задано, то соединение разрешено. Дополнительные сведения о свойстве соединения «Назначение приложения» см. в разделе 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 при запуске рабочей нагрузки составления отчетов на вторичной реплике

• AlwaysOn: читаемая вторичная реплика и задержка данных

См. также

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