Поделиться через

Рекомендации по проектированию SQL Server

  • Статья

System Center Operations Manager использует Microsoft SQL Server для поддержки операционных баз данных, хранилища данных и баз данных аудита ACS. Эти базы данных являются важными и создаются во время развертывания первого сервера управления или сборщика ACS в группе управления.

В лабораторной среде или в маломасштабном развертывании в Operations Manager SQL Server можно разместить на первом сервере управления в группе управления.

В развертывании от среднего до корпоративного масштаба экземпляр SQL Server должен располагаться на выделенном автономном сервере или в конфигурации высокой доступности SQL Server. В любом случае SQL Server уже должен существовать и доступен перед началом установки первого сервера управления или сборщика ACS.

Мы не рекомендуем использовать базы данных Operations Manager из экземпляра SQL с другими базами данных приложений, чтобы избежать потенциальных проблем с ограничениями на операции ввода-вывода и других аппаратных ресурсов.

Внимание

Operations Manager не поддерживает экземпляры SQL платформы как службы (PaaS), включая такие продукты, как Управляемый экземпляр SQL Azure или Amazon Relational Database Service (AWS RDS). Используйте экземпляр SQL Server, установленный на компьютере с Windows. Единственным исключением из этого является Управляемый экземпляр SCOM в Azure Monitor, который использует Azure SQL MI и не настраивается повторно.

требования SQL Server

Следующие версии SQL Server Enterprise и SQL Server Standard Edition поддерживаются для существующей установки System Center Operations Manager версии для размещения сервера отчетов, операционной базы данных, хранилища данных и базы данных ACS:

  • SQL Server 2019 с минимальным накопительным обновлением 8 (CU8) или более поздним обновлением, как показано здесь.
  • SQL Server 2016 и последние обновления, доступные здесь
  • SQL Server 2022 с минимальным накопительным обновлением 11 (CU11) или более поздним обновлением, как показано здесь.
  • SQL Server 2019 с минимальным накопительным обновлением 8 (CU8) или более поздним обновлением, как показано здесь.
  • SQL Server 2017 с последним доступным обновлением, как доступно здесь
  • SQL Server 2017 и накопительные обновления, как описано здесь
  • Sql Server 2016 и пакеты обновления, как описано здесь

Перед тем как обновить SQL Server, сначала смотрите сведения об обновлении для версии 2017+ и сведения об обновлении для SQL 2019.

Следующие версии SQL Server Enterprise и Standard Edition поддерживаются для новой или существующей установки System Center 2016: Operations Manager для размещения сервера отчетов, операционной базы данных, хранилища данных и базы данных ACS.

  • SQL Server 2016 и последние обновления, доступные здесь
  • SQL Server 2014 и последние обновления, доступные здесь
  • SQL Server 2012 и последние обновления, доступные здесь

Драйверы SQL Server

Драйверы OLE DB и ODBC SQL Server должны быть установлены на всех серверах управления и сервере веб-консоли, так как эти компоненты напрямую взаимодействует с базами данных и этими драйверами разрешают доступ на уровне API к SQL.

Рекомендуется использовать зашифрованное подключение SQL Server; При этом необходимо установить последние версии драйверов SQL:

Дополнительные сведения о настройке шифрования подключений SQL можно найти здесь: Настройка SQL Server СУБД для шифрования подключений

Если не используются зашифрованные подключения SQL, используйте предыдущие выпуски драйверов SQL, которые не требуют шифрования.

Обновления SQL Server

Каждый из следующих компонентов SQL Server, поддерживающих инфраструктуру Operations Manager, должен находиться в одной основной версии SQL Server:

  • Экземпляры модулей СУБД SQL Server, в которых размещается любая из баз данных Operations Manager, включая:
    • Менеджер операций
    • OperationManagerDW
    • Базы данных SSRS ReportServer и ReportServerTempDB
  • экземпляр SQL Server Reporting Services (SSRS).

Режим проверки подлинности SQL Server

По умолчанию SQL работает в конфигурации проверки подлинности в смешанном режиме. Однако Operations Manager использует только проверку подлинности Windows для общения с SQL Server. Если оставлено по умолчанию, настройка проверки подлинности в смешанном режиме SQL по-прежнему будет работать, если у локальной учетной записи нет роли db_owner. Локальные учетные записи с ролью db_owner, как известно, вызывают проблемы с Operations Manager.

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

Другие вопросы

Другие рекомендации по оборудованию и программному обеспечению применяются в планировании проектирования:

  • Рекомендуется использовать диски SQL в формате ФАЙЛОВ NTFS.
  • Необходимо иметь не менее 1 ГБ свободного места на диске для операционной базы данных и базы данных хранилища данных. Это применяется во время создания базы данных. Имейте в виду, что использование дисков баз данных значительно увеличится после установки, убедитесь, что достаточно свободного места на диске выше этого базового требования.
  • Требуется платформа .NET Framework 4.
  • Платформа .NET Framework 4.8 поддерживается в Operations Manager 2022.
  • Сервер отчетов не поддерживается в Windows Server Core.
  • Параметр сортировки SQL Server должен быть одним из поддерживаемых типов, как описано в разделе: параметр сортировки SQL Server.
  • Поиск полнотекстового текста SQL Server требуется для всех экземпляров ядра СУБД SQL Server, в котором размещается любой из баз данных Operations Manager.
  • Параметры установки Windows Server (Server Core, Server with Desktop Experience и Nano Server), поддерживаемые компонентами базы данных Operations Manager, зависят от того, какие параметры установки поддерживаются SQL Server.

Дополнительные сведения см. в разделе "Требования к оборудованию и программному обеспечению" в документации по установке и планированию SQL Server: планирование установки SQL Server

Настройка сортировки SQL Server

Следующие колляции SQL Server и Windows поддерживаются в System Center Operations Manager.

Примечание.

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

Сопоставление SQL Server

  • SQL_Latin1_General_CP1_CI_AS

сортировка Windows

  • Latin1_General_100_CI_AS
  • French_CI_AS
  • French_100_CI_AS
  • Cyrillic_General_CI_AS
  • Chinese_PRC_CI_AS
  • Chinese_Simplified_Pinyin_100_CI_AS
  • Chinese_Traditional_Stroke_Count_100_CI_AS (Китайский_Традиционный_Подсчет_Черточек_100_CI_AS)
  • Japanese_CI_AS
  • Japanese_XJIS_100_CI_AS
  • Traditional_Spanish_CI_AS
  • Modern_Spanish_100_CI_AS
  • Latin1_General_CI_AS
  • Cyrillic_General_100_CI_AS
  • Korean_100_CI_AS
  • Czech_100_CI_AS
  • Hungarian_100_CI_AS
  • Polish_100_CI_AS
  • Finnish_Swedish_100_CI_AS

Если экземпляр SQL Server не настроен с использованием одного из поддерживаемых параметров сортировки, перечисленных ранее, установка новой версии Operations Manager завершится сбоем. Однако локальное обновление успешно завершается.

Настройка брандмауэра

Operations Manager зависит от SQL Server для размещения баз данных и платформы отчетов для анализа и представления исторических операционных данных. Роли сервера управления, операций и веб-консоли должны быть в состоянии успешно взаимодействовать с SQL Server, и важно понимать путь связи и порты для правильной настройки среды.

Если вы разрабатываете распределенное развертывание, использующее группы доступности SQL AlwaysOn, существуют дополнительные параметры конфигурации брандмауэра, которые необходимо включить в стратегию безопасности брандмауэра.

В следующей таблице определены порты брандмауэра, необходимые SQL Server, чтобы серверы управления взаимодействовали с базами данных:

Сценарий Порт Направление Роль Operations Manager
SQL Server, в котором размещаются базы данных Operations Manager TCP 1433 * Входящий трафик сервер управления и веб-консоль (для помощника по приложениям и диагностики приложений)
служба браузера SQL Server UDP 1434 Входящий трафик сервер управления
Выделенное подключение администратора SQL Server TCP 1434 Входящий трафик сервер управления
Другие порты, используемые SQL Server
— Вызовы удаленной процедуры Майкрософт (MS RPC)
— Инструментарий управления Windows (WMI)
— Координатор распределенных транзакций Майкрософт (MS DTC)		 TCP 135 Входящий трафик сервер управления
Прослушиватель группы доступности AlwaysOn SQL Server Настроенный администратором порт Входящий трафик сервер управления
Службы SQL Server Reporting Services, используемые для размещения сервера отчетности Operations Manager. TCP 80 (по умолчанию)/443 (SSL) Входящий трафик сервер управления и консоль управления

Примечание.

Хотя TCP 1433 является стандартным портом для экземпляра Ядра Базы Данных по умолчанию, при создании именованного экземпляра на автономном сервере SQL Server или развертывании группы доступности SQL Always On определяется настраиваемый порт, который должен быть задокументирован в справочных целях, чтобы вы могли правильно настроить брандмауэры и ввести эту информацию во время установки.

Дополнительные сведения о требованиях к брандмауэру для SQL Server см. в разделе "Настройка брандмауэра Windows для разрешения доступа к SQL Server".

Рекомендации по емкости и хранилищу

база данных Operations Manager

База данных Operations Manager — это база данных SQL Server, содержащая все данные, необходимые Operations Manager для повседневного мониторинга. Размер и настройка сервера базы данных критически важны для общей производительности группы управления. Наиболее критически важный ресурс, используемый базой данных Operations Manager, является подсистемой хранения, но ЦП и ОЗУ также являются значительными.

Факторы, влияющие на нагрузку базы данных Operations Manager, включают:

  • Скорость сбора операционных данных.
    • Скорость сбора операционных данных зависит от таких факторов, как количество импортированных пакетов управления, количество добавленных агентов и тип отслеживаемого компьютера. Например, агент, отслеживающий критически важный для бизнеса компьютер, собирает меньше данных по сравнению с агентом мониторинга сервера под управлением SQL Server с несколькими базами данных.
  • Скорость изменения пространства экземпляра.
    • Обновление существующих данных в базе данных Operations Manager является ресурсоемким по сравнению с записью новых операционных данных. Кроме того, при изменении данных пространства экземпляра серверы управления должны создавать дополнительные запросы к базе данных для изменения конфигурации вычислений и группирования. Скорость изменения пространства экземпляра увеличивается при импорте новых пакетов управления или добавлении новых агентов в группу управления.
  • Количество консолей управления и других подключений пакета SDK, выполняемых одновременно, также влияет на нагрузку на базу данных.
    • Каждая консоль управления считывает данные из базы данных Operations Manager. Запросы к этим данным используют потенциально большие объемы ресурсов операций ввода-вывода хранилища, времени ЦП и ОЗУ. Консоли управления, отображающие большие объемы операционных данных в представлении событий, представлении состояния, представлении оповещений и представлении данных производительности, обычно вызывают большую нагрузку на базу данных.

База данных Operations Manager является одним источником сбоя для группы управления, поэтому ее можно сделать высокодоступной с помощью поддерживаемых конфигураций отработки отказа, таких как группы доступности SQL Server AlwaysOn или экземпляры отказоустойчивого кластера.

Вы можете настроить и обновить базы данных Operations Manager с помощью существующей настройки Always-On SQL без необходимости вносить изменения после конфигурации.

Включение SQL Broker в базе данных Operations Manager

System Center Operations Manager зависит от SQL Server Service Broker для выполнения всех задач. Если компонент SQL Server Service Broker отключен, все операции задачи затрагиваются. Результирующее поведение может отличаться в зависимости от задачи, инициированной. Поэтому важно проверить состояние SQL Server Service Broker всякий раз, когда наблюдается непредвиденное поведение задачи в System Center Operations Manager.

Чтобы включить sql Server Service Broker, выполните следующие действия.

  1. Выполните следующий SQL-запрос, чтобы проверить, включен ли брокер, указанный результатом 1 (один) в is_broker_enabled поле:

    SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

  2. Если значение, отображаемое в is_broker_enabled поле, равно 0 (ноль), выполните следующую инструкцию SQL, чтобы включить брокер:

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER

База данных хранилища данных Operations Manager

Примечание.

Хранилище данных Operations Manager также называется базой данных "Хранилище данных отчетов" или просто хранилищем данных в некоторых документах.

System Center — Operations Manager вставляет данные в хранилище данных практически в режиме реального времени, важно иметь достаточную емкость на этом сервере, который поддерживает запись всех данных, собираемых в хранилище данных. Как и в базе данных Operations Manager, наиболее критически важный ресурс в хранилище данных — это подсистема ввода-вывода хранилища. В большинстве систем нагрузки на хранилище данных похожи на базу данных Operations Manager, но могут отличаться. Кроме того, рабочая нагрузка на хранилище данных, связанная с отчетностью, отличается от нагрузки на базу данных Operations Manager, вызванной использованием консоли управления Operations Manager.

Факторы, влияющие на нагрузку на хранилище данных, включают:

  • Скорость сбора операционных данных.
    • Хранилище данных выполняет вычисления и сохраняет агрегированные данные, а также ограниченный объем необработанных данных, чтобы обеспечить более эффективное создание отчетов. В результате стоимость сбора операционных данных в хранилище данных немного выше по сравнению с базой данных Operations Manager. Однако эта стоимость компенсируется снижением затрат на обработку данных обнаружения в хранилище данных по сравнению с базой данных Operations Manager.
  • Количество пользователей, одновременно создающих отчеты, или запланированная генерация отчетов.
    • Каждый пользователь отчетов может добавить значительную нагрузку на систему, так как отчеты часто суммируют большие объемы данных. Общие потребности в емкости зависят от количества отчетов, выполняемых одновременно, и типа выполняемых отчетов. Отчеты, запрашивающие большие диапазоны дат или большое количество объектов, требуют дополнительных системных ресурсов.

На основе этих факторов рекомендуется учитывать при изменении размера хранилища данных несколько рекомендаций.

  • Выберите соответствующую подсистему хранения.
    • Так как хранилище данных является неотъемлемой частью общего потока данных через группу управления, важно выбрать соответствующую подсистему хранения для хранилища данных. Как и в базе данных Operations Manager, RAID 0 + 1 часто является лучшим выбором. Как правило, подсистема хранения для хранилища данных должна быть похожа на подсистему хранения для базы данных Operations Manager, а также рекомендации, применимые к базе данных Operations Manager, также применяются к хранилищу данных.
  • Рассмотрите возможность соответствующего размещения журналов данных и журналов транзакций.
    • Что касается базы данных Operations Manager, разделение данных SQL и журналов транзакций часто является подходящим выбором по мере увеличения числа агентов. Если база данных Operations Manager и хранилище данных находятся на одном и том же сервере, и вы хотите разделить журналы данных и транзакций, необходимо разместить журналы транзакций для базы данных Operations Manager на отдельном физическом томе и дисковых шпинделях от хранилища данных, чтобы это принесло какую-либо пользу. Файлы данных для базы данных Operations Manager и хранилища данных могут совместно использовать тот же физический том, если том обеспечивает достаточную емкость и производительность операций ввода-вывода диска не оказывает негативного влияния на функции мониторинга и отчетов.
  • Рассмотрите возможность размещения хранилища данных на отдельном сервере из базы данных Operations Manager.
    • Хотя развертывания с меньшим масштабом часто могут консолидировать базу данных Operations Manager и хранилище данных на одном сервере, можно разделить их по мере увеличения числа агентов и объема входящих операционных данных. Если хранилище данных и сервер отчетов находятся на отдельном сервере из базы данных Operations Manager, вы можете повысить производительность отчетов.

База данных хранилища данных Operations Manager является значимым источником сбоя для группы управления, поэтому её можно сделать высокодоступной с помощью поддерживаемых конфигураций отработки отказа, таких как группы высокой доступности SQL Server Always On или отказоустойчивые экземпляры кластера.

Always On в SQL Serverе

Группы доступности SQL Server Always On поддерживают среды для отработки отказов для дискретного набора пользовательских баз данных (баз данных доступности). Каждая группа баз данных доступности размещается в реплике доступности.

При использовании System Center 2016 и более поздних версий Operations Manager, SQL Always On предпочтительнее отказоустойчивой кластеризации, чтобы обеспечить высокий уровень доступности для баз данных. Все базы данных, кроме установки служб Reporting Services в собственном режиме, которые используют две базы данных для разделения постоянного хранилища данных от требований к временному хранилищу, можно разместить в группе доступности AlwaysOn.

Чтобы настроить группу доступности, разверните кластер отказоустойчивой кластеризации Windows Server (WSFC) для размещения реплики доступности и включите AlwaysOn на узлах кластера. Затем можно добавить базу данных SQL Server Operations Manager в качестве базы данных доступности.

Подсказка

Начиная с Operations Manager 2022, можно настроить и обновить базы данных Operations Manager с помощью существующей установки Always-On SQL без каких-либо изменений после изменения конфигурации.

Чтобы настроить группу доступности, разверните кластер отказоустойчивой кластеризации Windows Server (WSFC) для размещения реплики доступности и включите AlwaysOn на узлах кластера. Затем можно добавить базу данных SQL Server Operations Manager в качестве базы данных доступности.

Примечание.

После развертывания Operations Manager на узлах SQL Server, участвующих в SQL Always On, чтобы включить строгую безопасность CLR, запустите SQL-скрипт в каждой базе данных Operations Manager.

Строка мультисубсети

Operations Manager не поддерживает ключевые слова строки подключения (MultiSubnetFailover=True). Поскольку группа доступности имеет имя прослушивателя (известное как сетевое имя или точка доступа клиента в диспетчере кластеров WSFC), которое зависит от нескольких IP-адресов из разных подсетей, например при развертывании в конфигурации отработки отказа между сайтами, запросы на подключение клиента с серверов управления к прослушивателю группы доступности приведут к тайм-ауту подключения.

Рекомендуемый подход к работе с этим ограничением для узлов сервера развернутой группы доступности в многоподсетевой среде:

  1. Задайте сетевое имя прослушивателя группы доступности, чтобы зарегистрировать только один активный IP-адрес в DNS.
  2. Настройте кластер для использования низкого значения TTL для зарегистрированной записи DNS.

Эти параметры позволяют ускорить восстановление и определение имени кластера с новым IP-адресом при переключении на узел в другой подсети.

Выполните следующие команды PowerShell на любом из узлов SQL, чтобы изменить эти параметры:

Import-Module FailoverClusters
Get-ClusterResource "Cluster Name"|Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource "Cluster Name"|Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource "Cluster Name"
Start-ClusterResource "Cluster Name"
Start-ClusterGroup "Cluster Name"

Если вы используете AlwaysOn с именем прослушивателя, необходимо также внести эти изменения в конфигурацию прослушивателя. Дополнительные сведения о настройке прослушивателя группы доступности см. в документации по настройке прослушивателя группы доступности — SQL Server AlwaysOn.

Следующие команды PowerShell можно запускать на узле SQL, где размещен прослушиватель, чтобы изменить его параметры:

Import-Module FailoverClusters
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource <Listener Cluster Resource name>
Start-ClusterResource <Listener Cluster Resource name>
Start-ClusterGroup <Listener Cluster Group name>

Если используется кластеризованный экземпляр SQL или Always On для обеспечения высокой доступности, необходимо включить функцию автоматического восстановления на серверах управления, чтобы избежать перезапуска службы доступа к данным Operations Manager при переключении между узлами в случае отказа. Сведения о конфигурации см. в следующей статье базы знаний: служба System Center Management перестает отвечать после того, как экземпляр SQL Server переходит в автономный режим.

Оптимизация SQL Server

Возможности поддержки показали, что проблемы с производительностью обычно не вызваны высоким уровнем использования ресурсов (т. е. процессором или памятью) с самим SQL Server; скорее проблема напрямую связана с конфигурацией подсистемы хранения. Узкие места производительности часто связаны с несоблюдением рекомендаций по настройке для выделенного хранилища, используемого экземпляром базы данных SQL Server. Ниже приведены примеры.

  • Недостаточно выделения спинделей для LUN для поддержки требований ввода-вывода Operations Manager.
  • Размещение журналов транзакций и файлов базы данных на одном томе. Эти две рабочие нагрузки имеют разные характеристики ввода-вывода и задержки.
  • Неправильная конфигурация TempDB связана с размещением, размером и т. д.
  • Несоответствие выравнивания разделов дисков, на которых размещены журналы транзакций базы данных, файлы базы данных и TempDB.
  • Не обращайте внимания на базовую конфигурацию SQL Server, такую как использование AUTOGROW для файлов базы данных и журналов транзакций, настройку MAXDOP для параллелизма запросов, создание нескольких файлов данных TempDB на каждое ядро ЦП и т. д.

Конфигурация хранилища является одним из критически важных компонентов развертывания SQL Server для Operations Manager. Серверы баз данных, как правило, сильно привязаны к ввода-выводам из-за строгой операции чтения и записи базы данных и обработки журналов транзакций. Шаблон поведения ввода-вывода Operations Manager обычно составляет 80 % операций записи и 20 % операций чтения. В результате неправильной настройки подсистем ввода-вывода может привести к низкой производительности и работе систем SQL Server и становится заметной в Operations Manager.

Перед развертыванием SQL Server важно проверить структуру SQL Server, выполнив тестирование пропускной способности подсистемы ввода-вывода. Убедитесь, что эти тесты могут достичь требований ввода-вывода с приемлемой задержкой. Используйте служебную программу Diskspd для оценки емкости ввода-вывода подсистемы хранилища, поддерживающей SQL Server. В следующей статье блога, написанной участником команды файловых серверов из группы разработки продукта, представлены подробные рекомендации по выполнению стресс-тестирования с использованием этого инструмента — DiskSpd, PowerShell и аналитики производительности хранилища: измерение операций ввода-вывода в секунду (IOPS), пропускной способности и задержки как для локальных дисков, так и для общих папок SMB.

Размер единицы выделения NTFS

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

При форматировании секции, используемой для файлов данных SQL Server, рекомендуется использовать размер единицы распределения 64 КБ (то есть 65 536 байт) для данных, журналов и TempDB. Тем не менее имейте в виду, что использование размеров единиц выделения более 4 КБ делает невозможным использование сжатия NTFS на томе. Хотя SQL Server поддерживает данные только для чтения на сжатых томах, это не рекомендуется.

Резервная память

Примечание.

Большая часть информации в этом разделе поступает от Джонатана Кехайяса в его блоге Сколько памяти на самом деле требуется моему SQL Server? (sqlskills.com).

Не всегда легко определить нужный объем физической памяти и процессоров, выделяемых для SQL Server в поддержке System Center Operations Manager (или для других рабочих нагрузок за пределами этого продукта). Калькулятор размера, предоставляемый группой продуктов, предоставляет рекомендации на основе масштаба рабочей нагрузки, но их рекомендации основаны на тестировании, выполняемом в лабораторной среде, которая может или может не соответствовать фактической рабочей нагрузке и конфигурации.

SQL Server позволяет настроить минимальный и максимальный объем памяти , который будет зарезервирован и использован в процессе. По умолчанию SQL Server может динамически изменять требования к памяти на основе доступных системных ресурсов. Значение по умолчанию для минимальной памяти сервера равно 0, а значение по умолчанию для максимальной памяти сервера — 2 147 483 647 МБ.

Проблемы, связанные с производительностью и памятью, могут возникнуть, если не задано соответствующее значение для максимальной памяти сервера. Многие факторы влияют на то, сколько памяти необходимо выделить SQL Server, чтобы обеспечить поддержку других процессов, выполняемых в этой системе, таких как карточка HBA, агенты управления и антивирусная проверка в режиме реального времени. Если объем установленной памяти недостаточен, операционная система и SQL будут использовать подкачку на диск. Это может привести к увеличению нагрузки на дисковый ввод-вывод, дальнейшему снижению производительности и созданию эффекта домино, когда эта проблема становится заметной в Operations Manager.

Рекомендуется указать не менее 4 ГБ ОЗУ для минимальной памяти сервера. Это необходимо сделать для каждого узла SQL, в котором размещена одна из баз данных Operations Manager (операционные, хранилище данных, ACS).

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

  • 1 ГБ ОЗУ для ОС
  • 1 ГБ ОЗУ на каждые 4 ГБ установленной ОЗУ (до 16 ГБ ОЗУ)
  • 1 ГБ ОЗУ на каждые 8 ГБ ОЗУ при установке более 16 ГБ ОЗУ.

После установки этих значений отслеживайте счетчик Memory\Available MBytes в Windows, чтобы определить, можно ли увеличить объем памяти, доступной для SQL Server. Windows сигнализирует о том, что доступная физическая память достигает 96 МБ, поэтому желательно, чтобы счетчик не опускался ниже 200–300 МБ, чтобы оставался запас. Для серверов с 256 ГБ ОЗУ или больше, убедитесь, что он не работает с объемом памяти менее 1 ГБ.

Имейте в виду, что эти вычисления предполагают, что SQL Server сможет использовать всю доступную память, если только вы не измените их на учет для других приложений. Рассмотрите конкретные требования к памяти для ОС, других приложений, стека потоков SQL Server и других многостраничных выделителей. Типичная формула — это ((total system memory) – (memory for thread stack) – (OS memory requirements) – (memory for other applications) – (memory for multipage allocators)), где память для стека потоков равна ((max worker threads) (stack size)). Размер стека составляет 512 КБ для систем x86, 2 МБ для систем x64 и 4 МБ для систем IA64, и вы можете найти значение для максимальных рабочих потоков в столбце max_worker_count столбца sys.dm_os_sys_info.

Эти рекомендации также применяются к требованиям к памяти для SQL Server для запуска в виртуальной машине. Так как SQL Server предназначен для кэширования данных в буферном пуле, и он использует как можно больше памяти, это может быть трудно определить идеальный объем ОЗУ. При уменьшении объема памяти, выделенной экземпляру SQL Server, можно достичь точки, в которой меньший объем памяти обменивается на более высокий доступ к дисковым операциям ввода-вывода.

Чтобы настроить память SQL Server в среде с избыточным выделением ресурсов, начните с мониторинга среды и текущих метрик производительности, включая продолжительность жизни страницы управляющего буфером SQL Server, чтения страниц в секунду и чтения с физического диска/с. Если в среде есть избыточный объем памяти, время жизни страницы увеличится на единицу каждую секунду без какого-либо уменьшения в рабочей нагрузке, из-за кэширования; значение чтений страниц/сек в диспетчере буферов SQL Server будет низким после увеличения объема кэша; и значение чтений/сек физического диска также останется низким.

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

Дополнительные сведения см. в разделе "Параметры конфигурации памяти сервера".

Дополнительные сведения см. в разделе "Параметры конфигурации памяти сервера".

Оптимизация TempDB

Размер и физическое размещение базы данных TempDB может повлиять на производительность Operations Manager. Например, если размер, заданный для TempDB, слишком мал, часть нагрузки на системную обработку может уйти на автоматическое увеличение TempDB до размера, необходимого для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL Server. Чтобы обеспечить оптимальную производительность TempDB, рекомендуется использовать следующую конфигурацию для TempDB в рабочей среде:

  • Задайте для модели восстановления TempDB значение SIMPLE.
    • Эта модель автоматически освобождает пространство журналов для уменьшения требований к пространству.
  • Чтобы заранее выделить место для всех файлов TempDB, задайте достаточно большое значение размера файлов для типичных рабочих нагрузок в среде. Он предотвращает слишком частое расширение TempDB, что может повлиять на производительность. База данных TempDB может быть настроена на автоматическое увеличение, но это необходимо использовать для увеличения места на диске для незапланированных исключений.
  • Создайте столько файлов, сколько необходимо для максимальной пропускной способности диска.
    • Использование нескольких файлов сокращает количество разных файлов в хранилище TempDB и обеспечивает улучшенную масштабируемость. Однако не создавайте слишком много файлов, так как это может снизить производительность и увеличить затраты на управление.
    • В качестве общего руководства создайте один файл данных для каждого логического процессора на сервере (учет любых параметров маски сходства), а затем настройте количество файлов вверх или вниз по мере необходимости.
    • Как правило, если число логических процессоров меньше или равно 8, используйте то же число файлов данных, что и логические процессоры.
      • Если число логических процессоров больше 8, используйте восемь файлов данных, а затем, если соперничество продолжается, увеличивайте число файлов данных на кратное 4 (до числа логических процессоров), пока уровень соперничества не снизится до приемлемого или внесите изменения в рабочую нагрузку или код.
      • Если соперничество не уменьшится, вам, возможно, потребуется дополнительно увеличить число файлов данных.
  • Сделайте каждый файл данных одинаковым размером, что позволяет обеспечить оптимальную производительность пропорциональной заливки.
    • Равный размер файлов данных имеет решающее значение, так как алгоритм пропорциональной заливки основан на размере файлов. Если файлы данных создаются с неравными размерами, алгоритм пропорциональной заливки старается использовать самый большой файл больше для распределения GAM, вместо равномерного распределения между всеми файлами, тем самым сводя на нет цель создания нескольких файлов данных.
  • Поместите базу данных TempDB в быструю подсистему ввода-вывода с помощью твердотельных дисков для оптимальной производительности.
    • Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков.
  • Размещайте TempDB на дисках, которые не используются пользовательскими базами данных.

Чтобы настроить TempDB, можно выполнить следующий запрос или изменить его свойства в Management Studio.

USE [TempDB]
GO
DBCC SHRINKFILE (N'tempdev' , 8)
GO
USE [master]
GO
ALTER DATABASE [TempDB] MODIFY FILE ( NAME = N'tempdev', NEWNAME = N'TempDB', SIZE = 2097152KB , FILEGROWTH = 512MB )
GO
ALTER DATABASE [TempDB] ADD FILE ( NAME = N'TempDB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB2.mdf' , SIZE = 2097152KB , FILEGROWTH = 512MB )
GO

Запустите запрос T-SQL SELECT * from sys.sysprocesses, чтобы обнаружить конфликт распределения страниц для базы данных TempDB. В выходных данных системной таблицы ресурс ожидания может отображаться как "2:1:1" (страница PFS) или "2:1:3" (страница 'Глобальной карты распределения ресурсов'). В зависимости от степени состязания этот параметр может привести к тому, что SQL Server не отвечает на короткие периоды. Другим подходом является изучение динамических административных представлений [sys.dm_exec_request или sys.dm_os_waiting_tasks]. Результаты показывают, что эти запросы или задачи ожидают ресурсов TempDB и имеют аналогичные значения, как выделено ранее при выполнении sys.sysprocesses запроса.

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

Примечание.

Этот флаг трассировки влияет на каждую базу данных в экземпляре SQL Server.

Максимальная степень параллелизма

Подсказка

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

Конфигурация SQL Server по умолчанию для небольших и средних развертываний Operations Manager подходит для большинства потребностей. Однако, когда рабочая нагрузка группы управления масштабируется вверх к сценарию корпоративного класса (обычно 2000+ управляемых агентом систем и расширенной конфигурации мониторинга, которая включает мониторинг уровня обслуживания с расширенными синтетическими транзакциями, мониторинг сетевых устройств, кроссплатформенный и т. д.) необходимо оптимизировать конфигурацию SQL Server, описанную в этом разделе документа. Один из вариантов конфигурации, которые не рассматриваются в предыдущих рекомендациях, — MAXDOP.

Параметр конфигурации Microsoft SQL Server max degree of parallelism (MAXDOP) определяет количество процессоров, используемых для выполнения запроса в параллельном плане. Этот параметр определяет вычислительные и потоковые ресурсы, используемые для операторов плана запросов, которые выполняют работу параллельно. В зависимости от того, настроен ли SQL Server на симметричном многопроцессорном компьютере (SMP), компьютере с неравномерным доступом к памяти (NUMA) или процессорах с включенной технологией гиперпоточности, необходимо соответствующим образом настроить параметр максимальной степени параллелизма.

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

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

Примечание.

Максимальный уровень конфигурации параллелизма не ограничивает количество процессоров, которые использует SQL Server. Чтобы настроить количество процессоров, используемых SQL Server, используйте параметр конфигурации маски привязки.

  • Для серверов, использующих более восьми процессоров, используйте следующую конфигурацию: MAXDOP=8

  • Для серверов, использующих восемь или меньше процессоров, используйте следующую конфигурацию: MAXDOP=0 до N

    Подсказка

    В этой конфигурации N представлено количество процессоров.

  • Для серверов, на которых настроен NUMA, MAXDOP не должен превышать количество ЦП, назначенных каждому узлу NUMA.

  • Для серверов с поддержкой гиперпоточности значение MAXDOP не должно превышать количество физических процессоров.

  • Для серверов с поддержкой NUMA и гиперпоточности значение MAXDOP не должно превышать количество физических процессоров на узел NUMA.

Вы можете отслеживать количество параллельных рабочих, выполняя запрос select * from sys.dm_os_tasks.

В этом примере конфигурация оборудования сервера была hp Blade G6 с 24 ядрами процессоров и 196 ГБ ОЗУ. Экземпляр, на котором размещена база данных Operations Manager, имел параметр MAXMEM размером 64 ГБ. После выполнения предлагаемых оптимизаций в этом разделе производительность улучшилась. Однако узкие места параллелизма запросов по-прежнему сохраняются. После тестирования различных значений наиболее оптимальная производительность была найдена путем задания MAXDOP=4.

Начальное изменение размера базы данных

Попытка оценить будущий рост баз данных Operations Manager, в частности операционных и хранилищ данных, в течение первых нескольких месяцев после развертывания не является простым упражнением. Хотя Помощник по размеру для Operations Manager разумно оценивает потенциальный рост на основе формулы, полученной группой продуктов из их тестирования в лаборатории, он не учитывает несколько факторов, которые могут повлиять на рост в ближайшем будущем и в долгосрочной перспективе.

Начальный размер базы данных, рекомендуемый помощником по оценке размера, следует установить до прогнозируемого размера, что позволяет уменьшить фрагментацию и соответствующие издержки, которые можно указать во время установки для баз данных операционной системы и хранилища данных. Если во время установки недостаточно места на хранилище доступно, базы данных можно расширить позже с помощью SQL Management Studio, а затем переиндексировать после этого для дефрагментации и оптимизации соответствующим образом. Эта рекомендация также относится к базе данных ACS.

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

Автоматическое увеличение базы данных

Когда размер файла зарезервированных баз данных становится полным, SQL Server может автоматически увеличить размер в процентах или по фиксированной сумме. Кроме того, можно настроить максимальный размер базы данных, чтобы предотвратить заполнение всего свободного места на диске. По умолчанию база данных Operations Manager не настроена на автоматическое увеличение размеров; это применимо только к базам данных хранилища данных и ACS.

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

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

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

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

Политика переключения при отказе кластера

Отказоустойчивая кластеризация Windows Server — это платформа высокой доступности, которая постоянно отслеживает сетевые подключения и работоспособность узлов в кластере. Если узел недоступен по сети, действие восстановления выполняется для восстановления и подключения приложений и служб к сети на другом узле в кластере. Параметры по умолчанию оптимизированы для сбоев, когда происходит полная потеря сервера, который считается "жестким" сбоем. Это будут неустранимые сценарии сбоя, такие как сбой неисправного оборудования или питания. В таких ситуациях сервер теряется, и цель состоит в том, чтобы отказоустойчивая кластеризация оперативно обнаружила потерю сервера и быстро восстановилась на другом сервере в кластере. Чтобы выполнить это быстрое восстановление после сложных сбоев, параметры по умолчанию для мониторинга работоспособности кластера довольно агрессивны. Однако они полностью настраиваются, чтобы обеспечить гибкость для различных сценариев.

Эти параметры по умолчанию обеспечивают оптимальную работу для большинства клиентов; однако, поскольку кластеры растянуты от нескольких дюймов до, возможно, миль друг от друга, кластер может быть более подвержен сетевым компонентам, которые могут быть ненадежными, между узлами. Другой фактор заключается в том, что качество серверов общего назначения постоянно увеличивается, в сочетании с повышенной надежностью благодаря резервным компонентам (например, двойным источникам питания, объединению сетевых адаптеров и многопутевому вводу-выводу), количество ненадежных отказов оборудования может быть довольно редким. Так как жесткие сбои могут быть менее частыми, некоторые клиенты могут пожелать настроить кластер для временных сбоев, где кластер более устойчив к кратким сбоям сети между узлами. Увеличив пороговые значения сбоев по умолчанию, можно уменьшить чувствительность к кратким проблемам сети, которые длились короткий период времени.

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

Виртуализация SQL Server

В виртуальных средах по соображениям производительности рекомендуется хранить операционную базу данных и базу данных хранилища данных в прямом подключенном хранилище, а не на виртуальном диске. Чтобы оценить необходимые операции ввода-вывода в секунду и провести стресс-тестирование ваших дисков данных для проверки, можно использовать утилиту Operations Manager Sizing Helper, выпущенную для Operations Manager 2012. Производительность хранилища можно проверить с помощью служебной программы DiskSpd. См. также поддержку виртуализации Operations Manager для получения дополнительных рекомендаций по виртуализированной среде Operations Manager.

Режим "Always On" и модель восстановления

Хотя Always On Availability Group не является строго оптимизацией, важно учитывать, что изначально эта функция требует, чтобы базы данных находились в режиме полного восстановления. Это означает, что журналы транзакций никогда не удаляются до тех пор, пока не будет выполнена полная резервная копия или только журнал транзакций. По этой причине стратегия резервного копирования не является необязательной, но обязательной частью проектирования AlwaysOn для баз данных Operations Manager. В противном случае диски, содержащие журналы транзакций, заполняются.

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

Тип резервного копирования Расписание
Только журнал транзакций Каждые час
Полностью Еженедельно, воскресенье в 3:00 утра

Оптимизация служб отчетов SQL Server

Экземпляр служб Reporting Services выступает в качестве прокси-сервера для доступа к данным в базе данных хранилища данных. Он создает и отображает отчеты на основе шаблонов, хранящихся в пакетах управления.

Роль отчетов Operations Manager не может быть установлена параллельно с предыдущей версией роли отчетов и должна быть установлена только в собственном режиме (интегрированный режим SharePoint не поддерживается).

За кулисами служб Reporting Services есть экземпляр базы данных SQL Server, на котором размещаются базы данных ReportServer и ReportServerTempDB. Общие рекомендации по настройке производительности этого экземпляра применяются.

Примечание.

С SQL Server Reporting Services (SSRS) 2017 версии 14.0.600.1274 и более поздних версий параметры безопасности по умолчанию не разрешают отправку расширений ресурсов. Это приводит к исключениям ResourceFileFormatNotAllowedException в Operations Manager во время развертывания компонентов отчетов.

Чтобы устранить эту проблему, выполните следующие действия:

  1. Откройте SQL Management Studio.
  2. Подключитесь к экземпляру служб отчетности Reporting Services.
  3. Щелкните правой кнопкой мыши на экземпляре сервера в окне Обозреватель объектов.
  4. Выберите Свойства.
  5. Выберите "Дополнительно" на левой боковой панели.
  6. Добавьте *.* в список AllowedResourceExtensionsForUpload.

Кроме того, вы можете добавить полный список расширений отчетов Operations Manager в список разрешенных в SSRS. Список описан в разделе "Решение 2" здесь: отчеты Operations Manager не развертываются

Следующие шаги

Сведения о том, как настроить размещение хранилища данных (отчетов) за брандмауэром, см. в статье "Подключение хранилища данных (Отчеты) через брандмауэр.