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

По умолчанию и доступ для чтения и записи, и доступ только для чтения разрешены в первичной реплике, а подключения к вторичным репликам группы доступности AlwaysOn запрещены. В этом разделе описывается настройка доступа к подключению в реплика доступности группы доступности AlwaysOn в SQL Server 2014 с помощью 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 } )

     где

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

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

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

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.

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

Пример (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. Соответствующие параметры:

   • Чтобы настроить доступ к подключению для вторичной роли, укажите ConnectionModeInSecondaryRole параметр secondary_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 командлет в среде PowerShell SQL Server 2014. Дополнительные сведения см. в разделе Get Help SQL Server PowerShell.

Сведения о настройке и использовании поставщика SQL Server PowerShell см. в статье SQL Server поставщик 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 или программы sqlcmd можно указать доступ только для чтения к любой вторичной реплика, которая включена для доступа только для чтения, указав -K ReadOnly параметр .

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

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

Факторы, которые могут повлиять на триггеры и задания после отработки отказа.

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

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

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

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

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

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

См. также

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

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

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

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

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

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

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

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

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

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

См. также:

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