Службы Analysis Services с группами доступности AlwaysOn

Статья
04/26/2023

Применимо к:SQL Server — только Windows

Группа доступности AlwaysOn — это предопределенная коллекция реляционных баз данных SQL Server, которые при отработке отказа все вместе переключаются на другой ресурс, если создаются условия для отработки отказа в любой из них. Запросы перенаправляются в зеркальную базу данных на другом экземпляре в той же группе доступности. Если группы доступности используются для обеспечения высокой доступности, можно использовать базу данных в этой группе в качестве источника данных в табличном или многомерном решении служб Analysis Services. Если используется база данных доступности, все следующие операции службы Analysis Services работают, как ожидалось: обработка или импорт данных, прямые запросы к базе данных (с использованием хранилища ROLAP или режима DirectQuery) и обратная запись.

Обработка и запросы — это рабочие нагрузки только для чтения. Можно улучшить производительность, если перегрузить эти рабочие нагрузки на вторичную реплику, доступную для чтения. Для этого сценария требуется дополнительная настройка. Чтобы выполнить все шаги, используйте контрольный список в этом разделе.

Предварительные условия

Необходимо иметь учетные данные SQL Server на всех репликах. Для настройки группы доступности, прослушивателей и баз данных необходимо быть sysadmin , но для доступа к базе данных из клиента службы Analysis Service пользователям достаточно иметь разрешения на чтение db_datareader .

Необхродимо использовать поставщика данных, который поддерживает протокол потока табличных данных (TDS) версии 7.4 или более поздней, например SQL Server Native Client 11.0, или поставщика данных для SQL Server в .NET Framework 4.02.

(Рабочие нагрузки только для чтения) Роль вторичной реплики должна быть настроена на подключения только для чтения, группа доступности должна иметь список маршрутизации, а подключение в источнике данных служб Analysis Services должно указывать прослушиватель группы доступности. Инструкции на этот случай приведены в этом разделе.

Контрольный список: использование вторичной реплики только для операций чтения

Если решение служб Analysis Services включает обратную запись, можно настроить подключение к источнику данных для использования вторичной реплики, доступной для чтения. Если у вас быстрое сетевое подключение, задержка данных у вторичной реплики будет очень небольшая и вторичная реплика будет предоставлято почти те же данные, что и первичная. Используя вторичную реплику для операций служб Analysis Services, можно снизить состязание за чтение и запись на первичной реплике и повысить коэффициент использования вторичных реплик в группе доступности.

По умолчанию доступ для чтения и записи и доступ только для чтения разрешается только к первичной реплике, а подключения к вторичным репликам запрещены. Для клиентского подключения только для чтения к вторичной реплике требуется дополнительная настройка. Для такой настройки необходимо задать свойства на вторичной реплике и выполнить скрипт T-SQL, который определяет спискок маршрутизации только для чтения. Используйте следующие процедуры, чтобы выполнить оба шага.

Примечание

Следующие шаги предполагают, что группа доступности и базы данных AlwaysOn уже существуют. Если настраивается новая группа, используйте мастер создания групп доступности для создания группы и присоединения баз данных. Мастер проверяет выполнение предварительных условий, выдает инструкции для каждого шага и выполняет начальную синхронизацию. Дополнительные сведения см. в статье Использование мастера добавления базы данных в группу доступности (среда SQL Server Management Studio).

Шаг 1. Настройка доступа на реплике доступности

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

Примечание

Эти шаги взяты из статьи Настройка доступа только для чтения в реплике доступности (SQL Server), где содержатся дополнительные сведения и альтернативные инструкции по выполнению этой задачи.
Разверните узел Высокий уровень доступности AlwaysOn и узел Группы доступности .
Щелкните группу доступности, реплику которой нужно изменить. Разверните Реплики доступности.
Щелкните правой кнопкой мыши вторичную реплику и выберите пункт Свойства.
В диалоговом окне Свойства реплики доступности можно изменить доступ по подключению для вторичной роли следующим образом:
- В раскрывающемся списке Вторичная реплика для чтения выберите Только для чтения.
- В раскрывающемся списке Подключения в первичной роли выберите Разрешить все подключения. Это значение по умолчанию.
- Если потребуется, в раскрывающемся списке Режим доступности выберите пункт Синхронная фиксация. Этот шаг необязателен, но обеспечивает равенство данных в первичной и вторичной репликах.
  
  Кроме того, это свойство обязательно для плановой отработки отказа. Если нужно выполнить плановую отработку отказа вручную для тестирования, задайте для свойства Режим доступности значение Синхронная фиксация как для первичной, так и для вторичной реплики.

Шаг 2. Настройка маршрутизации только для чтения

Подключитесь к первичной реплике.

Примечание

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

Откройте окно запроса и вставьте следующий скрипт. Этот сценарий делает три действия: включает подключения для чтения к вторичной реплике (что по умолчанию отключено), задает URL-адрес маршрутизации только для чтения и создает список маршрутизации, согласно которому назначаются приоритеты запросам на подключение. Первая инструкция, позволяющая подключения для чтения, избыточна, если вы уже задали свойства в Среда Management Studio, но включена для полноты.

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

Измените скрипт, заменив заполнители значениями для вашей установки.
- Замените заполнитель Computer01 именем экземпляра сервера, на котором находится первичная реплика.
- Замените заполнитель Computer02 именем экземпляра сервера, на котором находится вторичная реплика.
- Замените contoso.com именем домена или удалите его из скрипта, если все компьютеры находятся в одном и том же домене. Сохраните номер порта, если прослушиватель использует порт по умолчанию. Порт, фактически используемый прослушивателем, указан на странице свойств Среда Management Studio.
Выполните скрипт.

Теперь создайте в модели служб Analysis Services источник данных, который использует только что настроенную вами базу данных.

Создайте источник данных служб Analysis Services, который использует базу данных доступности AlwaysOn

В этом разделе описывается создание источника данных служб Analysis Services, который подключается к базе данных в группе доступности. Эти инструкции можно использовать для настройки подключения к первичной реплике (по умолчанию) или подключения для чтения к вторичной реплике, которую вы настроили согласно шагам в предыдущем разделе. Параметры конфигурации AlwaysOn, а также свойства подключения, заданные на клиенте, определяют, какая реплика используется — первичная или вторичная.

В SQL Server Data Tools, многомерном проекте служб Analysis Services и многомерном проекте модели интеллектуального анализа данных щелкните правой кнопкой мыши Источники данных и выберите Создать новый источник данных. Нажмите кнопку Создать , чтобы создать новый источник данных.

Или для проекта табличной модели щелкните меню «Модель» и выберите Импорт из источника данных.
В диспетчере подключений на странице «Поставщик» выберите поставщика, который поддерживает протокол TDS. Собственный клиент SQL Server 11.0 поддерживает этот протокол.
В диспетчере подключений для свойства «Имя сервере» введите имя прослушивателя группы доступности, а затем выберите базу данных, доступную в группе.

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

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

Задайте свойству Назначение приложения значение READONLY , если вы настраиваете клиентское подключение ко вторичной реплике только для чтения. В противном случае оставьте значение по умолчанию, READWRITE , чтобы направить подключение на первичную реплику.
В данных об олицетворении выберите Использовать указанные имя пользователя и парольи введите учетную запись пользователя домена Windows, имеющего минимум разрешений db_datareader для базы данных.

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

Завершите создание источника данных и закройте мастер источников данных.
Добавьте MultiSubnetFailover=Yes в строку подключения, для ускорения обнаружения и подключения к активному серверу. Дополнительные сведения об этом свойстве см. в разделе Поддержка высокого уровня доступности и аварийного восстановления собственного клиента SQL Server.

Это свойство не отображается в сетке свойств. Чтобы добавить это свойство, щелкните источник данных правой кнопкой мыши и выберите Просмотр кода. Добавьте MultiSubnetFailover=Yes в строку подключения.

Источник данных определен. Теперь можно перейти к созданию модели и начать с создания представления источников данных или, если это табличные модели, с создания связей. Если вы находитесь на этапе, когда необходимо извлечь данные из базы данных доступности (например, когда вы готовы к обработке или развертыванию решения), можно протестировать конфигурацию, чтобы проверить, что обращение к данным происходит из вторичной реплики.

Тестирование конфигурации

После настройки вторичной реплики и создания подключения к источнику данных в службах Analysis Services, можно убедиться в том, что команды обработки и запросов перенаправляются на вторичную реплику. Можно также выполнить плановую отработку отказа вручную, чтобы проверить план восстановления для этого сценария.

Шаг 1. Проверка перенаправления подключения к источнику данных на вторичную реплику

Запустите приложение SQL Server Profiler и подключитесь к экземпляру SQL Server, на котором находится вторичная реплика.

Когда выполняется трассировка, события SQL:BatchStarting и SQL:BatchCompleting показывают запросы из служб Analysis Services, работающих на экземпляре компонента Database Engine. Эти события выбираются по умолчанию, поэтому все, что требуется, — это запустить трассировку.
В SQL Server Data Toolsоткройте проект служб Analysis Services или решение, содержащее подключение к источнику данных, котороенужно протестировать. Убедитесь в том, что источник данных указывает прослушиватель группы доступност, а не экземпляр в группе.

Это важный шаг. Если указано имя экземпляра сервера, маршрутизация к вторичной реплике не выполняется.
Организуйте окна приложений так, чтобы видеть SQL Server Profiler и SQL Server Data Tools рядом.
Разверните решение, а когда оно закончит работу, остановите трассировку.

В окне трассировки должны отображаться события из приложения Microsoft SQL Server Analysis Services. Вы должны видеть инструкции SELECT , которые извлекают данные из базы данных на экземпляре сервера, где находится вторичная реплика, если подключение было создано через прослушиватель для вторичной реплики.

Шаг 2. Плановая отработка отказа для проверки конфигурации

В Среда Management Studio проверьте первичную и вторичную реплики, чтобы убедиться в том, что они настроены на режим синхронной фиксации и на данный момент синхронизированы.

В следующих шагах предполагается, что вторичная реплика настроена для синхронной фиксации.

Чтобы проверить синхронизацию, откройте подключение к каждому экземпляру, на котором находятся первичная и вторичная реплики, откройте папку "Базы данных" и убедитесь в том, что в каждой реплике к имени базы данных добавлено состояние (Синхронизировано) и (Синхронизируется).

Примечание

Эти шаги взяты из статьи Выполнение запланированного перехода на другой ресурс вручную для группы доступности (SQL Server), где содержатся дополнительные сведения и альтернативные инструкции по выполнению этой задачи.
В приложении SQL Server Profiler запустите трассировку для каждой реплики и просматривайте трассировки параллельно. В следующих шагах нужно будет сравнить трассировки, чтобы убедиться в том, что запросы SQL, используемые для обработки или запросов из служб Analysis Services, переключаются с одной реплики на другую.
Выполните команду обработки или запроса из служб Analysis Services. Поскольку источник данных настроен на подключение только для чтения, убедитесь, что команда выполняется на вторичной реплике.
В Среда Management Studioподключитесь к вторичной реплике.
Разверните узел Высокий уровень доступности AlwaysOn и узел Группы доступности .
Щелкните правой кнопкой мыши группу доступности для выполнения отработки отказа и выберите команду Отработка отказа . Запустится мастер отработки отказа группы доступности. С помощью этого мастера выберите реплику, которая теперь станет первичной.
Убедитесь в том, что отработка отказа прошла успешно.
- В Среда Management Studioраскройте группы доступности и просмотрите обозначения «(первичный)» и «(вторичный)». Экземпляр, который был первичной репликой, теперь будет вторичной репликой.
- Посмотрите на панели мониторинга, обнаружены ли какие-либо неисправности. Щелкните правой кнопкой мыши группу доступности и выберите Показать панель мониторинга.
Подождите одну-две минуты, пока отработка отказа закончится на сервере.
Повторите команду обработки или запроса в решении Analysis Services и просматривайте трассировку параллельно в приложении SQL Server Profiler. Вы должны видеть свидетельство обработки на другом экземпляре, который стал новой вторичной репликой.

Что происходит после отработки отказа

Во время отработки отказа вторичная реплика принимает первичную роль, а прежняя первичная реплика — вторичную роль. Все клиентские соединения прерываются, владение прослушивателем группы доступности переходит к роли первичной реплики на новый экземпляр SQL Server, а конечная точка прослушивателя привязана к виртуальным IP-адресам и TCP-портам нового экземпляра. Дополнительные сведения см. в статье Типы клиентских подключений к репликам в группе доступности Always On.

Если отработка отказа происходит во время обработки, в файле журнала или выходном окне служб Analysis Services появляется следующая ошибка: "Ошибка OLE DB или ODBC: сбой связи; 08S01; поставщик TPC: существующее подключение было принудительно разорвано удаленным узлом. ; 08S01."

Эта ошибка может исчезнуть, если подождать минуту и попробовать еще раз. Если группа доступности настроена правильно для вторичной реплики, доступной для чтения, при повторной попытке обработка возобновится на новой вторичной реплике.

Ошибки, которые не исчезают, скорее всего вызваны ошибками конфигурации. Попробуйте перезапустить скрипт T-SQL для устранения проблем со списком маршрутизации, URL-адресами маршрутизации только для чтения и чтением на вторичной реплике. Необходимо также проверить, что на первичной реплике разрешены все подключения.

Обратная запись, если используется база данных доступности AlwaysOn

Обратная запись — это компонент служб Analysis Services для анализа вариантов в Excel. Она также часто используется для задач составления бюджета и прогнозирования в пользовательских приложениях.

Для поддержки обратной записи требуется клиентское подключение READWRITE. Если вы пытаетесь в Excel выполнить обратную запись через подключение только для чтения, появится следующая ошибка: "Не удалось получить данные из внешнего источника данных".

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

Для этого создайте дополнительный источник данных в модели служб Analysis Services для поддержки подключения для чтения и записи. Создавая вторичный источник данных, используйте то же имя прослушивателя и ту же базу данных, которые вы указали в подключении только для чтения, но не меняйте Назначение приложения, а оставьте значение по умолчанию, поддерживающее подключения READWRITE. Теперь можно добавить в представление источника данных новые таблицы фактов или измерений, основанные на источнике данных для чтения и записи, а затем включите обратную запись для новых таблиц.

См. также:

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