Настройка Read-Only доступа к реплике доступности (SQL Server)

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

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

Перед началом работы

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

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

Безопасность

Разрешения

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

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

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

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

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

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

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

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

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

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

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

   • Для основной роли выберите новое значение из раскрывающегося списка Connections in primary role, как показано ниже.

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

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

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

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

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

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

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

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

     где:

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

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

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

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

   PRIMARY_ROLE ( ALLOW_CONNECTIONS { READ_WRITE = | ALL } )

   где:

   Чтение и запись
   Соединения, у которых свойство "Назначение приложения" равно 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

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

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 командлет в среде SQL Server 2014 PowerShell. Дополнительные сведения см. в разделе 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

Продолжение действий: После настройки доступа Read-Only для технологической реплики.

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

• При использовании служебной программы 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: предоставление последней статистики на доступной для чтения вторичной базе данных, базе данных Read-Only и моментальном снимке базы данных

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

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

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

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

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

См. также

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