Оптимизация среды SQL Server с помощью решения проверки работоспособности SQL Server в Azure Monitor

Статья
05/05/2020

Символ проверки работоспособности SQL

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

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

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

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

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

изображение плитки

изображение панели мониторинга

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

Для решения проверки работоспособности SQL на каждом компьютере с агентом Microsoft Monitoring Agent (MMA) должна быть установлена поддерживаемая версия .NET Framework 4.6.2. Агент MMA используется решением System Center 2016 Operations Manager и Operations Manager 2012 R2, а также Azure Monitor.
Решение поддерживает SQL Server версии 2012, 2014, 2016, 2017 и 2019.
Рабочая область Log Analytics для добавления решения проверки работоспособности SQL в Azure Marketplace на портале Azure. Чтобы установить решение, вы должны быть администратором или участником подписки Azure.

Примечание

После добавления решения на серверы с агентами добавляется файл AdvisorAssessment.exe. Данные конфигурации считываются и отправляются на обработку в Azure Monitor в облаке. К полученным данным применяется логика и облачная служба записывает данные.

Чтобы проверить работоспособность серверов SQL Server, требуется агент и подключение к Azure Monitor с помощью одного из следующих способов:

Установка агента Microsoft Monitoring Agent (MMA), если сервер уже не отслеживается с помощью решения System Center 2016 Operations Manager или Operations Manager 2012 R2.
Если сервер отслеживается решением System Center 2016 Operations Manager или Operations Manager 2012 R2 и группа управления не интегрирована с Azure Monitor, сервер может использоваться как многосетевой. С помощью Log Analytics данные будут собираться и пересылаться в службу, а сервер по-прежнему будет отслеживаться решением Operations Manager.
Если группа управления Operations Manager интегрирована со службой, после включения решения в рабочей области добавьте контроллеры домена для сбора данных службой. Для этого выполните инструкции по добавлению компьютеров под управлением агентов.

Агент в SQL Server, который отправляет отчеты в группу управления Operations Manager, собирает данные, перенаправляет их на назначенный сервер управления, а затем отправляет их с сервера управления непосредственно в Azure Monitor. Данные не записываются в базы данных Operations Manager.

Если сервер SQL Server отслеживается Operations Manager, необходимо настроить учетную запись запуска от имени Operations Manager. Дополнительные сведения см. в разделе ниже Учетные записи запуска от имени в Operations Manager для Azure Monitor.

Сведения о сборе данных для проверки работоспособности SQL

Для проверки работоспособности SQL данные собираются из следующих источников с помощью включенного агента:

Инструментарий управления Windows (WMI)
Реестр
Счетчики производительности
Результаты динамического административного представления SQL Server

Данные собираются на сервере SQL Server и перенаправляются в службу Log Analytics каждые семь дней.

Учетные записи запуска от имени в Operations Manager для службы Log Analytics

Log Analytics использует агент Operations Manager и группу управления для сбора и отправки данных в службу Log Analytics. Для предоставления качественных услуг служба Log Analytics строится на пакетах управления для рабочих нагрузок. Каждой рабочей нагрузке требуются отдельные права для запуска пакетов управления в другом контексте безопасности, например в учетной записи пользователя домена. Необходимо предоставить учетные данные, настроив учетную запись запуска от имени для Operations Manager.

Чтобы настроить учетную запись запуска от имени Operations Manager для проверки работоспособности SQL, используйте указанные ниже сведения.

Настройка учетной записи запуска от имени для проверки работоспособности SQL

Если пакет управления SQL Server уже используется, примените эту конфигурацию запуска от имени.

Настройка учетной записи SQL запуска от имени в консоли управления

Примечание

По умолчанию рабочие процессы в пакете управления выполняются в контексте безопасности учетной записи локальной системы. Если вы используете агент Microsoft Monitoring Agent, подключенный непосредственно к службе, вместо прямой отправки отчетов в группу управления Operations Manager, пропустите шаги 1–5 ниже и выполните пример T-SQL или PowerShell, указав NT AUTHORITY\SYSTEM в качестве имени пользователя.

В Operations Manager откройте консоль управления и щелкните Администрирование.
В разделе Run As Configuration (Настройка запуска от имени) щелкните Профили и откройте SQL Assessment Run As Profile (Профиль запуска от имени для Оценки SQL).
На странице Учетные записи запуска от имени нажмите кнопку Добавить.
Выберите учетную запись запуска от имени Windows, которая содержит учетные данные, необходимые для SQL Server, или нажмите кнопку Создать для ее создания.

Примечание

Тип учетной записи «Запуск от имени» должен быть указан как Windows. Учетная запись запуска от имени также должна входить в группу локальных администраторов на всех серверах Windows Server, на которых размещены экземпляры SQL Server.
Нажмите Сохранить.
Измените, а затем выполните приведенный ниже пример T-SQL на каждом экземпляре SQL Server. Это позволит предоставить минимальные разрешения, необходимые учетной записи запуска от имени для проверки работоспособности SQL. Но это не требуется делать в том случае, если учетная запись запуска от имени уже является частью серверной роли sysadmin в экземплярах SQL Server.

    ---
    -- Replace <UserName> with the actual user name being used as Run As Account.
    USE master

    -- Create login for the user, comment this line if login is already created.
    CREATE LOGIN [<UserName>] FROM WINDOWS

    -- Grant permissions to user.
    GRANT VIEW SERVER STATE TO [<UserName>]
    GRANT VIEW ANY DEFINITION TO [<UserName>]
    GRANT VIEW ANY DATABASE TO [<UserName>]

    -- Add database user for all the databases on SQL Server Instance, this is required for connecting to individual databases.
    -- NOTE: This command must be run anytime new databases are added to SQL Server instances.
    EXEC sp_msforeachdb N'USE [?]; CREATE USER [<UserName>] FOR LOGIN [<UserName>];'

Настройка учетной записи SQL запуска от имени с помощью Windows PowerShell

Откройте окно PowerShell и выполните следующий скрипт после его обновления с собственными данными.

    import-module OperationsManager
    New-SCOMManagementGroupConnection "<your management group name>"

    $profile = Get-SCOMRunAsProfile -DisplayName "SQL Assessment Run As Profile"
    $account = Get-SCOMrunAsAccount | Where-Object {$_.Name -eq "<your run as account name>"}
    Set-SCOMRunAsProfile -Action "Add" -Profile $Profile -Account $Account

Основные сведения о приоритизации рекомендаций

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

Процесс вычисления взвешенного значения

Взвешенные значения являются статистическими значениями, основанными на трех ключевых факторах.

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

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

Приоритетные области

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

Доступность и непрерывность бизнес-процессов. Эта область содержит рекомендации в отношении доступности служб, устойчивости инфраструктуры и защиты бизнеса.

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

Обновление, перенос и развертывание. Эта область содержит рекомендации, которые помогут вам обновить, перенести и развернуть SQL Server в существующую инфраструктуру.

Операции и мониторинг. В этой области содержатся рекомендации, которые позволят оптимизировать ИТ-операции, реализовать программу профилактического обслуживания и повысить эффективность бизнеса.

Управление изменениями и конфигурациями. Эта область содержит рекомендации, которые помогут защитить повседневные операции, предотвратить негативное воздействие изменений на инфраструктуру, создать процедуры управления изменениями, а также отслеживать конфигурации системы и проводить их аудит.

Следует ли стремиться к показателю 100 % в каждой приоритетной области?

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

В каждой рекомендации указано, почему она важна. Их следует использовать, чтобы определить целесообразность реализации рекомендации с учетом характера ИТ-служб и бизнес-потребностей организации.

Игнорирование рекомендаций

Если нужно проигнорировать определенные рекомендации, создайте текстовый файл, при помощи которого рекомендации будут удалены из результатов оценки в Azure Monitor.

Указание рекомендаций, которые нужно проигнорировать

В меню Azure Monitor выберите Журналы.
Выполните следующий запрос, чтобы получить список рекомендаций, не выполненных на компьютерах в вашей среде.
```
SQLAssessmentRecommendation | where RecommendationResult == "Failed" | sort by Computer asc | project Computer, RecommendationId, Recommendation
```
Ниже приведен снимок экрана с запросом по журналам:
Выберите рекомендации, которые нужно проигнорировать. Эти значения будут использоваться для параметра RecommendationId в следующей процедуре.

Создание и использование текстового файла IgnoreRecommendations.txt

Создайте файл с именем IgnoreRecommendations.txt.
Вставьте или введите значение RecommendationId для каждой рекомендации, которую служба Azure Monitor должна игнорировать, в отдельной строке, а затем сохраните и закройте файл.
Поместите файл в следующую папку на каждом компьютере, где служба Azure Monitor должна игнорировать указанные ниже рекомендации.
- На компьютерах с Microsoft Monitoring Agent (подключенных напрямую или через Operations Manager): системный диск:\Program Files\Microsoft Monitoring Agent\Agent.
- На сервере управления Operations Manager: системный диск:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server.
- На сервере управления Operations Manager 2016 — системный диск:\Program Files\Microsoft System Center 2016\Operations Manager\Server.

Контроль игнорирования рекомендаций

После выполнения следующей плановой оценки (по умолчанию выполняется раз в семь дней) указанные рекомендации помечаются как игнорируемые и больше не отображаются на панели мониторинга оценки.
Для получения списка всех игнорируемых рекомендаций можно использовать следующие запросы поиска по журналам.
```
SQLAssessmentRecommendation | where RecommendationResult == "Ignored" | sort by Computer asc | project Computer, RecommendationId, Recommendation
```
Если вы решите позже просмотреть игнорируемые рекомендации, удалите все файлы IgnoreRecommendations.txt или RecommendationIDs можно удалить из них.

Часто задаваемые вопросы

Какие проверки выполняет решение "Оценка SQL"?

В следующем запросе показано описание всех проверок, которые выполняются в настоящее время:

SQLAssessmentRecommendation
| distinct RecommendationId, FocusArea, ActionArea, Recommendation, Description
| sort by FocusArea,ActionArea, Recommendation

Результаты можно экспортировать в Excel для дальнейшего анализа.

Как часто выполняется проверка работоспособности?

Проверка выполняется каждые семь дней.

Можно ли настроить частоту проверки?

На данный момент нет.

Если добавлено решение проверки работоспособности SQL, а затем обнаружен другой сервер, будет ли он проверяться?

Да, после обнаружения он проверяется каждые 7 дней.

Если сервер выведен из эксплуатации, будет ли он удален из решения проверки работоспособности?

Если сервер не отправляет данные в течение 3 недель, то он удаляется.

Как называется процесс, который собирает данные?

AdvisorAssessment.exe

Сколько времени требуется для сбора данных?

Время сбора данных на сервере занимает примерно 1 час. На серверах, которые имеют большое количество экземпляров или баз данных SQL, процесс сбора может занять больше времени.

Какие типы данных собираются?

Собираются следующие типы данных:
- WMI
- Реестр
- Счетчики производительности
- Динамические административные представления (DMV) SQL

Можно ли настроить время сбора данных?

На данный момент нет.

Почему необходимо настроить учетную запись «Запуск от имени»?

Для SQL Server выполняется небольшое количество SQL-запросов. Для того чтобы их можно было выполнить, необходимо использовать учетную запись выполнения от имени с разрешениями VIEW SERVER STATE для SQL. Кроме того, чтобы выполнять запросы к WMI, потребуются учетные данные локального администратора.

Почему отображаются только первые 10 рекомендаций?

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

Можно ли игнорировать рекомендации?

Да. См. раздел Игнорирование рекомендаций выше в этой статье.

Дальнейшие действия

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