Тюнинг SQL Server 2012 под SharePoint 2013-2016. Часть 2

Здравствуйте! Сегодня с вами снова я — Любовь Волкова, системный архитектор департамента разработки бизнес-решений. В предыдущей статье мы начали обсуждение темы тюнинга SQL-серверов для работы с базами данных SharePoint 2013/2016. В материале были подробно рассмотрены вопросы выбора между физическим и виртуальным сервером, планирование размера, размещения баз данных, подготовка дисковой подсистемы и оптимизация передачи данных по сети.

Сегодня мы продолжаем рассказ, останавливая свое внимание на особенностях настройки параметров SQL-сервера в ходе процесса инсталляции с учетом последующей работы с базами данных SharePoint, а также настройках перед развертыванием SharePoint и в процессе обслуживания баз данных корпоративного портала.

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

Установка SQL Server 2012

Экран выбора функциональных компонентов

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

Пути по умолчанию указывают на папки, размещенные на диске C:. Настоятельно рекомендуется изменить этот путь, обеспечив инсталляцию компонентов на другой диск.

Конфигурация экземпляра SQL-сервера

На странице конфигурации экземпляра SQL-сервера предлагается ввести данные о имени экземпляра и расположении файлов в файловой системе, связанных с ним.

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

Сервисные учетные записи

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

Рекомендуется установить отдельные учетные записи для служб SQL Server Agent и SQL Database Engine.

Рекомендуемый минимальный перечень учетных записей:

• Sql_installation – учетная запись Active Directory (AD), используем для инсталляции SQL, которая на время инсталляции необходимо включить в группу локальных администраторов. После инсталляции учетную запись Sql_installation заблокировать. Она нужна будет только на время инсталляции обновлений.
• Sql_engine – учетная запись Active Directory (AD), администратор отдельного экземпляра SQL Server 2012 (для каждого экземпляра свой), которая не входит в группу локальных администраторов. Под этой учетной записью будет работать сервисы SQL Server для устанавливаемого экземпляра (служба SQL Server Database Engine). Эта учетная запись не должна входить в группы локальных, доменных администраторов или администраторов предприятий.
• SQL_agent – учетная запись службы агента SQL Server, позволяющей автоматизировать некоторые административные задачи. Агент SQL-сервера выполняет задания, контролирует SQL Server и обрабатывает предупреждения. Служба агента SQL Server должна быть запущена для автоматического запуска локальных или много серверных административных заданий. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов.
• SQL_browser – учетная запись браузера SQL-сервера, которая прослушивает входящие запросы на ресурсы SQL-сервера и предоставляет сведения об экземплярах, установленных на этом компьютере. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов. В случае, если на сервере инсталлируется экземпляр, имя которого отличается от имени по умолчанию или количество экземпляров более одного, включение службы является обязательным.
• SQL_admin – учетная запись администратора SQL-сервера.

Параметры сортировки

Как правило, во время инсталляции SQL-сервера настройки параметров сортировки оставляют по умолчанию, что не соответствует рекомендуемым настройкам для SharePoint-фермы. В общем случае SharePoint позволят работать с любыми встроенными параметрами сортировки, но для системных баз данных SQL Server 2012 (master и tempdb) настоятельно рекомендуется использовать параметры, непосредственно адаптированные под SharePoint:

• Англоязычная версия SharePoint 2013 использует Latin1_General_CI_AS_KS_WS;
• Русскоязычная версия SharePoint 2013 использует Cyrillic_General_CI_AS.

Каждая аббревиатура в названии параметров сортировки имеет строго определенное значение:

• CI, Case Insensitive (нечувствительность к регистру). «A» и «а» рассматриваются как один и тот же символ.
• AS, Accent Sensitive (чувствительность диакритическим знакам, — знакам акцента). Символы «a» и «á» рассматриваются как разные символы.
  KS, Kana Sensitive (чувствительность к символам японского алфавита). Символы Japanese Hirakana и Katakana, которые выглядят одинаково, рассматриваются как разные символы.
• WS, Width Sensitive (чувствительность к объему данных). Символ, требующий для хранения в таблице SQL один байт и тот же символ, требующий для хранения два байта рассматриваются системой как два разных символа.

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

После инсталляции SQL-сервера параметры сортировки, установленные для экземпляра, невозможно изменить. В связи с этим на странице конфигурационных настроек сервера мастера инсталляции рекомендуется выставить значения, рекомендованные для SharePoint.

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

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

Конфигурационные настройки ядра SQL-сервера

Настройки сервера

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

Настоятельно рекомендуется выбрать смешенный режим аутентификации, который позволяет пользователям подключаться с помощью проверки подлинности Windows или SQL Server. На странице ввода конфигурационных параметров сервера необходимо ввести данные о пароле системной учетной записи sa, а также выбрать администратора SQL из числа учетных записей Active Directory. Учетная запись sa всегда присутствует в качестве имени входа в компонент ядра SQL-сервер и является членом предопределенной роли сервера sysadmin.

Если компонент ядра SQL-сервера установлен с использованием только проверки подлинности Windows (то есть проверка подлинности SQL Server не включена), имя входа sa все равно будет присутствовать, но будет отключена.

Расположение файлов баз данных по умолчанию

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

1. Файлов данных системной базы tempdb;
2. Журнала транзакций системной базы tempdb;
3. Файлов данных баз контента SharePoint;
4. Журналов транзакций баз контента SharePoint;
5. Резервный копий баз данных SharePoint.

Общей рекомендацией является размещение всех перечисленных категорий файлов на разных дисках:

Конфигурирование после установки SQL-сервера

После завершения инсталляции SQL-сервера необходимо выполнить ряд важных настроек перед тем, как будет запущен процесс установки SharePoint Server 2013. Если же установка SharePoint уже выполнена, вы тоже можете внести изменения для того, чтобы повысить производительность работы портала.

С целью оптимизации производительности SharePoint рекомендуется настроить следующие параметры сервера SQL:

• Установить максимальный и минимальный объем памяти, коэффициент заполнения для индекса, максимальный уровень параллелизма для SQL-сервера;
• Задать пути для сохранения по умолчанию файлов данных, журналов транзакций и резервных копий по умолчанию;
• Настроить сжатие при резервном копировании и проверку страниц при восстановлении;
• Для каждой из баз данных SharePoint и базы данных tempdb установить исходный размер файлов данных и журналов транзакций, параметры автоматического увеличения размера файлов данных и журналов
• Настроить параметры автоматического создания и обновления статистики
• Сконфигурировать мгновенную инициализацию файлов
• Выполнить настройку параметров блокировки страниц в памяти

Экспресс-аудит использования ОЗУ

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

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

SELECT 
total_physical_memory_kb
, available_physical_memory_kb
, total_page_file_kb, available_page_file_kb
, system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

Наилучшим результатом, свидетельствующим о том, что система не испытывает проблем с ОЗУ является получение значения «Available physical memory is high» в поле «system_memory_state_desc». Получение значения «Available physical memory is low» будет свидетельствовать об обратном.

Пример результата:

Получить данные о текущих параметрах сервера, связанных с настройкой использования ОЗУ, можно при помощи следующего скрипта:

SELECT name, value, minimum, maximum, value_in_use
FROM sys.configurations 
WHERE name IN (
	'min server memory (MB)'
	,'max server memory (MB)'
	)

Пример результата:

Максимальный объем памяти

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

SQL максимальный размер ОЗУ = TotalPhyMem — (NumOfSQLThreads * ThreadStackSize) — (1GB * ОКРУГЛВНИЗ(NumOfCores/4)) — RAMOSReserved — RAMForOtherApps, где:

• TotalPhyMem – общий физический размер ОЗУ на сервере.
• NumOfCores – кол-во ядер процессоров.
• NumOfSQLThreads – кол-во потоков, использующихся на сервере для обработки запросов к базам данных. При кол-ве ядер до 4 значение NumOfSQLThreads всегда постоянно и равно 256. При кол-ве ядер свыше 4 расчет выполняется по формуле: NumOfSQLThreads = 256 + (NumOfCores- 4) * 8.
• ThreadStackSize = 2Мб для серверов x64. Для серверов IA64 ThreadStackSize=4Мб.
• RAMOSReserved – ОЗУ для операционной системы. 20% для серверов с TotalPhyMem не более 15 Гб и 12,5% для большего объема.
• RAMForOtherApps – ОЗУ для других экземпляров SQL-сервера и приложений;

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

SELECT cpu_count AS [Logical CPU Count] 
, hyperthread_ratio AS [Hyperthread Ratio] 
, cpu_count / hyperthread_ratio AS [Physical CPU Count] 
, osi.physical_memory_kb / 1024 AS [Physical Memory (MB)] 
, sqlserver_start_time
FROM sys.dm_os_sys_info as osi;

Пример полученных результатов:

Приведем пример расчета максимального объема ОЗУ для SQL-сервера. Исходные данные для выполнения вычислений:

• TotalPhyMem = 8191 Мб;
• NumOfCores = 4;
• Сервер x64;
• RAMForOtherApps = 2000 Мб.

SQL максимальный размер ОЗУ = ОКРУГЛВНИЗ(8191 Мб — (256 * 2 Мб) — (1024 Мб * ОКРУГЛВНИЗ(4/4)) – 0,2*8191 Мб – 2000 Мб) = ОКРУГЛВНИЗ(8191 Мб – 512 Мб – 1024 Мб – 1638,2 Мб – 2000 Мб)= 3017 Мб.

Для автоматизации выполнения расчетов можно использовать SQL MAX MEMORY CALCULATOR.
Начиная с версии SQL 2008 R2, в случае наличия только одного экземпляра SQL на сервере, нет необходимости устанавливать вручную значение максимального объема выделяемой памяти. Это значение рассчитывается автоматически компонентом управления памятью Microsoft SQL Server на основе данных о текущем использовании ОЗУ операционной системой и другими приложениями и динамически изменяется в случае уменьшения/увеличения нагрузки на эту подсистему.

Минимальный объем памяти

Установка значения минимально выделяемого кол-ва оперативной памяти для SQL-сервера позволяет гарантировать, что при расчете системой выделяемой памяти под нужды операционной системы и других приложений для SQL-сервера будут зарезервировано не менее указанного объема ОЗУ. Значение «0» по умолчанию допускает ситуации, при которых на нужды сервера баз данных будет выделено минимально возможное кол-во ресурсов, что может очень негативно сказаться на производительности SharePoint.

Отметим, что установка ненулевого значения для параметра «Min Server Memory» не означает, что SQL автоматически получит весь указанный объем ОЗУ и будет постоянно его использовать. Выделение памяти будет всегда зависеть от текущей нагрузки. Текущие показатели могут отличаться от указанного параметра как в меньшую, так и в большую сторону.
Рекомендуемыми значениями параметра «Min Server Memory» являются следующие:

• SharePoint используется главным образом для совместной работы, активно выполняется редактирование контента.
• 60% от значения параметра «Max Server Memory» в случае, если корпоративный портал SharePoint в основном используется для поиска и просмотра контента.
• В остальных случаях устанавливается значение между 25% и 60% пропорционально процентному соотношению между чтением/изменением данных на корпоративном портале.

Для рассматриваемого примера значение параметра «Min Server Memory» должно быть установлено в диапазоне от 754 Мб до 1810 Мб.

Коэффициент заполнения для индекса

Коэффициент заполнения (параметр «fill factor») служит для точной настройки хранения и производительности индекса. При создании или перестроении индекса коэффициент заполнения отображает процент заполнения пространства каждой страницы конечного уровня, что позволяет зарезервировать для будущего расширения оставшееся на каждой странице пространство как свободное. Например, при указании для коэффициента заполнения значения 80 на каждой странице конечного уровня будет зарезервировано 20 процентов занимаемого ею дискового пространства. Данное дисковое пространство будет использовано для расширения индекса при добавлении в базовую таблицу новых данных. Пустое место резервируется не в конце индекса, а между строками индекса.

Коэффициент заполнения — это значение в процентах от 1 до 100; значение по умолчанию на сервере — 0, что означает полное заполнение страниц конечного уровня.

Для SharePoint для поддержки роста баз данных и снижения уровня фрагментации индексов оптимальным является значение 80.

Следующий скрипт позволят получить информацию от текущих настройках коэффициента заполнения индекса по умолчанию на уровне SQL-сервера:

SELECT name,value,minimum, maximum,value_in_use
FROM sys.configurations
WHERE name IN (
	'fill factor (%)'
	)

Пример результата:

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

В SharePoint 2010 установка значения «1» была опциональна, но для SharePoint 2013 значение, отличное от 1 будет препятствовать запуску мастера конфигурации фермы SharePoint. Установка указанного значения для степени параллелизма гарантирует то, что SQL-сервер, на котором размещены базы данных SharePoint, каждый из запросов обрабатывается только одним единственным процессом. Любое другое значение может стать причиной выбора менее оптимального плана выполнения для запроса и может послужить снижению общей производительности SharePoint Server 2013.

Ниже приведен скрипт, позволяющий получить данные о текущих настройках сервера SQL:

SELECT name,value,minimum, maximum,value_in_use
FROM sys.configurations
WHERE name IN (
	'max degree of parallelism'
	)

Пример результата:

Расположение файлов по умолчанию и сжатие в ходе резервного копирования

Расположения файлов

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

--Получение данных о настройках сервера SQL:
--пути сохранения данных, журналов транзакций и резервных копий
Declare @DataDir nvarchar(4000),
        @LogDir nvarchar(4000),
        @BakDir nvarchar(4000),
        @Instance sysname

Set @Instance = IsNull('' + Cast(ServerProperty('InstanceName') as sysname), '')

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', 
        N'SoftwareMicrosoftMSSQLServerMSSQLServer', 
        N'DefaultData', 
        @DataDir output, 
        'no_output'

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', 
        N'SoftwareMicrosoftMSSQLServerMSSQLServer', 
        N'DefaultLog', 
        @LogDir output, 
        'no_output'

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', 
        N'SoftwareMicrosoftMSSQLServerMSSQLServer', 
        N'BackupDirectory', 
        @BakDir output, 
        'no_output'

Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir

Пример результата:

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

Сжатие в ходе резервного копирования

Сжатие резервных копий позволяет ускорить резервное копирование в SharePoint. Оно доступно в выпусках SQL Server Standard Edition и Enterprise Edition. Установив параметр сжатия в скрипте резервного копирования или настроив сервер SQL Server для сжатия по умолчанию, можно значительно сократить размер резервных копий базы данных и доставляемых журналов.
Ниже приведен скрипт для получения данных о текущих параметрах сжатия в ходе выполнения резервного копирования:

SELECT name,value,minimum, maximum,value_in_use
FROM sys.configurations
WHERE name IN (
	'backup compression default'
	)

Пример результата:

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

Настройка параметров базы данных model

Шаблон базы данных model используется для создания всех баз данных в экземпляре SQL-сервер. Настройки и контент этой базы данных копируются для всех новых пользовательских баз данных при их создании.

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

• Проверка страниц при восстановлении (Page Verify), значение «CHECKSUM»;
• Автоматическое создание статистики (Auto Create Statistics), значение «False»;
• Автоматическое обновление статистики (Auto Update Statistics), значение «False»;
• Автоматическое асинхронное обновление статистики (Auto Update Statistics Asynchronously), значение «False»;
• Автоматическое сжатие базы данных (Auto Shrink), значение «False».

По умолчанию настройки системной базы данных SQL не соответствуют рекомендуемым:

Если установка SharePoint выполнена ранее, необходимо внести изменения в параметры всех баз данных, настройки которых отличаются от рекомендуемых.
Следующий скрипт позволяет получить информацию о текущей конфигурации баз данных (на примере model и двух баз данных SharePoint):

SELECT 
	name
	, is_auto_create_stats_on
	, is_auto_update_stats_on
	, is_auto_update_stats_async_on
	, is_auto_shrink_on
	,page_verify_option_desc
FROM sys.databases
WHERE name IN ( --перечислить все базы данных, для которых будет выполнена проверка
	'model'
	,'WSS_Content'
	,'SharePoint_Config'
	)

Пример результата:

Cоздание/обновление статистики

Автоматическое создание/обновление статистики для SharePoint Server не поддерживается, поэтому оно должно быть отключено для всех баз. Необходимо установить значение «False» для параметров «Auto Create Statistics», «Auto Update Statistics» и «Auto Update Statistics Asynchronously».

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

Рекомендуем ежедневно обновлять статистику базы данных контента SharePoint, используя параметр FULLSCAN на сервере SQL Server. Хотя в SharePoint есть системное задание таймера для обновления статистики, настоятельно рекомендуется установить график планового обслуживания на сервере SQL Server, чтобы статистика базы данных обновлялась ежедневно. Подробнее о настройке плана обслуживания и обновления статистики можно прочитать здесь.

Проверка страниц при восстановлении

Настоятельно рекомендуется установить значение «CHECKSUM» для параметра проверки страниц при восстановлении (Page Verify), как минимум для системных баз данных SharePoint и баз контента. В этом случае SQL-сервер вычисляет контрольную сумму для контента всей страницы и сохраняет это значение в ее заголовке при записи на диск. При считывании страницы данных с диска выполняется пересчет контрольной суммы данных и сравнение полученного значения с тем, которое записано в заголовке. Это обеспечивает постоянную поддержку высокого уровня целостности данных.

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

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

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

Настройка параметров базы данных tempdb

Системная база данных tempdb — это глобальный ресурс, доступный всем пользователям, подключенным к данному экземпляру SQL-сервер, в котором хранятся следующие объекты:

• временные объекты, созданные явно, такие как глобальные или локальные временные таблицы, временные хранимые процедуры, табличные переменные и курсоры;
• внутренние объекты, создаваемые компонентом SQL Server Database Engine, например, рабочие таблицы, хранящие промежуточные результаты буферов или сортировки;
• версии строк, сформированные транзакциями изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;
• версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в сети, функции режима MARS и триггеры AFTER.

По умолчанию база данных tempdb настроена на работу в простом режиме восстановления.

Количество, первоначальный размер и автоматический рост файлов

База данных tempdb является одной из наиболее интенсивно использующихся баз данных и должна всегда располагаться на наиболее скоростных дисках. В случае, если создается несколько файлов данных для этой базы, SQL-сервер обеспечивает одновременную запись данных в каждый из них, тем самым увеличивая производительность выполнения указанной операции.
Общей рекомендацией является создание по одному файлу на каждое ядро процессора. Майкрософт также говорит о допустимости создания одного дополнительного файла данных из расчета на 2/4 ядра.

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

Пример записи в журнал о частых событиях автоматического приращения файла базы данных контента (для базы данных tempdb возможна аналогичная ситуация):

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

Размер каждого из файлов должен совпадать, при этом суммарный размер всех исходный размеров фалов данных должен быть настроен на 10-25% от размера самой большой базы данных контента. Рекомендации специалистов Майкрософт для размер автоматического роста файлов – 10% от размера файла данных.

Размер файла журнала транзакций – 50% от исходного размера файла данных. Автоматический рост файла журнала транзакций – 50% от его исходного размера.

Пример:

• память — 8Гб;
• процессоров 4-ядра;
• база контента 95 Гб.

Исходный размер файла журналов транзакций – около 25% от суммарного исходного размера всех файлов данных, следовательно:

• количество файлов – 4шт, суммарный размер 24 Гб, то есть каждый файл по 6Гб;
• автоматический прирост – 614 Мб;
• исходный размер файла журналов транзакций – 3 Гб;
• автоматически прирост файлов журналов – 1,5 Гб.

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

Проверка страниц

Необходимо убедиться в том, что установлено значение CHECKSUM для параметра Проверка страниц (Page Veriety).

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

Перечислим дополнительные рекомендации для tempdb, использующейся на экземпляре SQL-сервера, обеспечивающего хранение баз данных SharePoint:

• избегайте сжатия базы данных tempdb, выполняйте эту операцию только если вы абсолютно уверены в ее необходимости, а размер свободного места составляет около 50%;
• параметры сортировки должны совпадать с настройками этого параметра для SQL-сервера;
• не изменяйте владельца базы данных со значения sa на другое;
• не удаляйте базу данных tempdb;
• не удаляйте гостевую учетную запись из списка пользователей базы данных;

  

• не заменяйте режим восстановления Простой на любой другое;
• убедитесь в том, что база данных tempdb использует для хранения файлов данных и журналов RAID 1, RAID 1+0 или RAID 5 с целью предотвращения сбоя в работе SQL-сервера из-за проблем одного из дисков. Необходимо помнить о том, что в случае сбоя в работе базы данных tempdb весь экземпляр SQL-сервера прекращает свою работу.

Дополнительную информацию по настройке tempdb можно получить здесь.

Индивидуальные настройки для баз данных SharePoint

Важно знать, что SharePoint при создании баз данных контента или баз данных приложений-служб копирует не все настройки базы данных model.

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

• Первоначальный размер файлов данных и файлов журналов транзакций;
• Размер автоматического приращения для файлов баз данных SharePoint;
• Автоматическое создание статистики

Первоначальный размер файлов данных и журналов транзакций

Для каждой из баз данных SharePoint следуйте следующим рекомендациям:

1. Оцените ожидаемый размер базы данных с учетом роста в течение ближайшего года.
2. В качестве исходного суммарного размера файлов данных установите 25% от значения, полученного в п.1.
3. в качестве исходного размера файла журналов транзакций установите 25% от значения в п.2.

Ниже приведена таблица с типовыми значениями размеров баз данных SharePoint и комментариями относительно ожидаемого роста их размеров.

База данных	Общие сведения о размере	Коэффициенты роста	Ожидания по росту
База данных контента Центра администрирования	Малый	Если в ферме SharePoint инсталлированны компоненты и настроено использование PowerPivot, все рабочие книги Excel, и файлы данных PowerPivot, использующиеся в панелях мониторинга, хранятся в базе данных контента Центра администрирования. При использовании PowerPivot для SharePoint 2013 с параметрами по умолчанию, которые задают хранение журнала сбора данных об использовании и обновлении данных в течение 365 дней, объем базы данных контента центра Центр администрирования будет увеличиваться в течение одного года. Увеличению объема способствует создание дополнительного контента на веб-узле Центра администирования (ссылки администрирования, дополнительные списки и библиотеки документов, контент дополнительных решений и др.)	<1Гб
База данных конфигурации	Малый	При полном режиме восстановления и частом изменении конфигурационных настроек фермы файлы журнала транзакций, хранящиеся в базе данных конфигурации, могут значительно увеличиваться	<1Gb

Типовые размеры баз данных SharePoint Server

База данных	Ожидаемый размер	Ожидаемый предел роста	Коэффициенты роста	Свойства чтения/записи
App Management Database	Малый	Малый		Высокая интенсивность операций записи при установке приложений и обновлении лицензий
База данных службы параметров подписки	Малый	Малый	Размер определяется количеством поддерживаемых клиентов, ферм и компонентов	База данных подписок отличается высокой интенсивностью операций чтения
Служба подключения к бизнес-данным	Малый	Малый	Размер определяется количеством подключений	Высокая интенсивность операций чтения
База данных приложения-службы управляемых метаданных	Малый	Средний	На увеличение размера влияет объем управляемых метаданных	Высокая интенсивность операций чтения
База данных приложения-службы перевода SharePoint	Малый	Малый		Высокая интенсивность операций чтения
Power Pivot Database	Малый	Малый	В процессе использования PowerPivot сохраняет дополнительные данные в базах данных контента и базе данных контента центра Центр администрирования (WSS_Content).	Высокая интенсивность операций чтения
База данных PerformancePoint Services	Малый	Малый		Высокая интенсивность операций чтения
База данных администрирования поиска	Малый	Средний	К факторам, влияющим на рост, относится число наиболее подходящих элементов, количество источников контента и правил обхода, описания безопасности и объем трафика	Одинаковое соотношение операций чтения/записи
База данных отчетов аналитики	Средний	Большой		Высокая интенсивность операций записи во время ночного обновления аналитики
База данных обхода	Средний	Средний		Высокая интенсивность операций чтения
База данных ссылок	Средний	Большой	Размер пространства, занимаемого базой данных ссылок на диске, увеличивается на 1 ГБ на каждый миллион документов. Объем данных о переходах по ссылкам растет линейно с увеличением трафика запросов — 1 ГБ на миллион запросов	Высокая интенсивность операций записи во время обработки контента
База данных Secure Store	Малый	Малый	Размер и интенсивность роста определяются количеством конечных приложений, полей учетных записей для конечного приложения и числом пользователей, хранящихся для каждого конечного приложения. Если включен аудит, на размер также влияет количество операций чтения и записи для конкретного конечного приложения	Одинаковое соотношение операций чтения/записи
База данных приложения-службы состояний	Малый	Большой	Зависимость от интенсивности использования компонентов, которые сохраняют данные в базе данных службы состояний	Высокая интенсивность операций чтения
База данных сбора данных об использовании и работоспособности	Большой	Сверхбольшой	Размер базы данных зависит от настройки хранения, числа элементов, для которых ведутся журналы и внешний мониторинг, количества веб-приложений, работающих в среде, числа пользователей, работающих в текущий момент, и включенных компонентов	Высокая интенсивность операций записи
База данных профилей	Малый	Большой	На увеличение размера влияют кол-во пользователей и использование каналов новостей. Каналы новостей увеличиваются с ростом активности пользователей. По умолчанию хранятся действия за последние две недели, после чего задание таймера удаляет элементы канала новостей старше двух недель	Высокая интенсивность операций чтения
База данных синхронизации профилей	Средний	Большой	На увеличение размера влияет количество пользователей и групп, а также соотношение пользователей и групп	Одинаковое соотношение операций чтения/записи
База данных социальных тегов	Очень малый	Сверхбольшой	На увеличение размера влияет количество созданных и используемых тегов, рейтингов и заметок	Высокая интенсивность операций чтения
База данных Word Automation	Малый			Высокая интенсивность операций чтения, однократно на элемент преобразования

Автоматический прирост файлов баз данных SharePoint

Рекомендуется установить точные значения прироста файлов баз данных – 50% от их исходного значения.

Автоматическое создание статистики

Проверьте, что для базы данных SharePoint установлены следующие параметры:

• Автоматическое создание статистики (Auto Create Statistics), значение «False»;
• Автоматическое обновление статистики (Auto Update Statistics), значение «False»;
• Автоматическое асинхронное обновление статистики (Auto Update Statistics Asynchronously), значение «False»;
• Автоматическое сжатие базы данных (Auto Shrink), значение «False».

Мгновенная инициализация файлов

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

1. Создание базы данных.
2. Добавление файлов, журналов или данных в существующую базу данных.
3. Увеличение размера существующего файла (включая операции автоприращения).
4. Восстановление базы данных или файловой группы.

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

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

1. Выясните данные об учетной записи, от имени которой запускаются службы SQL-сервера:
   • В данных о службах
     
   • Или в окне настройки конфигурации SQL-сервера.
     

2. Откройте приложение Локальные политики (secpol.msc).
3. Разверните на левой панели узел Локальные политики, а затем щелкните пункт Назначение прав пользователей.
4. На правой панели дважды щелкните Выполнение задач по обслуживанию томов.
5. Щелкните кнопку Добавить пользователя или группу и добавьте учетную запись, данные о которой получены в п.1.
6. Нажмите кнопку Применить и закройте все диалоговые окна Локальная политика безопасности.
   

   

7. Выполните перезагрузку сервиса SQL Server с целью применения политики.

Настройка параметров блокировки страниц в памяти

В 64-разрядной версии SQL режим AWE можно включить через групповую политику – включить привилегию «блокировки страниц в памяти» для SQL-сервера. Результат – по мере необходимости автоматическое включение работы с режимом AWE для осуществления доступа к памяти буферного пула.

Конфигурирование брандмауэра

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

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

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Включить брандмауэр можно с помощью следующей команды:

Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Настройка брандмауэра Windows для доступа к компоненту Database Engine

New-NetFirewallRule -DisplayName «SQL Server» -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow

New-NetFirewallRule -DisplayName «SQL Admin Connection» -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow

New-NetFirewallRule -DisplayName «SQL Database Management» -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow

New-NetFirewallRule -DisplayName «SQL Service Broker» -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow

New-NetFirewallRule -DisplayName «SQL Debugger/RPC» -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow

Если для БД SharePoint используется именованный экземпляр SQL, то также необходимо подключение по именованному порту. Номер порта назначается динамически, отображается в менеджере конфигурации SQL Server (Сетевая конфигурация | Протоколы | TCP/IP| Свойства).

New-NetFirewallRule -DisplayName «SQL NamePort» -Direction Inbound –Protocol TCP –LocalPort -Action allow

New-NetFirewallRule -DisplayName «SQLSERVR» -program " C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinnSqlservr.exe" -direction Inbound -Action Allow

Настройка брандмауэра Windows на разрешение доступа к службам Analysis Services

New-NetFirewallRule -DisplayName «SQL Analysis Services» -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow

New-NetFirewallRule -DisplayName «SQL Browser» -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

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

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

Исчерпывающий перечень номеров портов, используемых SQL можно найти здесь.

Псевдонимы для использования клиентами SQL

Чтобы создать псевдоним для SQL Server, необходимо выполнить следующую последовательность действий:

1. В диспетчере конфигурации SQL Server развернуть пункт Конфигурация клиента Native Client SQL, выбрать пункт Псевдонимы, нажать правой кнопкой мыши, в появившемся меню выбрать Создать псевдоним.
2. В поле Имя псевдонима указать имя. Клиентское приложение сможет использовать это имя вместо имени сервера для подключения.
3. В поле Сервер указать имя сервера или IP-адрес. Для именованного экземпляра добавить имя этого экземпляра.
4. В поле Протокол выбрать протокол, который будет использоваться для этого псевдонима.
   
5. Создать DNS-запись типа АA, которая разрешает имя псевдонима в IP-адрес SQL-сервера.

Настройка прав доступа перед установкой SharePoint

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

Параметры быстродействия

По умолчанию визуальные эффекты выбирается на основе производительности сервера. Для повышения производительности сервера рекомендуем выбрать режим Обеспечить наилучшее быстродействие (Свойства системы > вкладка Дополнительно > раздел Быстродействие > Параметры > вкладка Визуальные эффекты).

Управление виртуальной памятью

Рекомендуем определить размер и расположения файла подкачки. Размер файла подкачки должен быть равен выделенной оперативной памяти сервера с коэффициентом 1,5. Пример: 8192 MB RAM х 1,5 = 12288 MB. Рекомендуем размещать файл подкачки на отдельном диске — не системном.

(Свойства системы -> вкладка Дополнительно > раздел Быстродействие > Параметры > вкладка Дополнительно > раздел Виртуальная память).

Настройка схемы управления электропитанием

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

Откройте Панель управления> Система и безопасность > Электропитание и выберите схему питания Высокая производительность.

Флаг трассировки 1117

Установка TraceFlags 1117 обеспечивает равномерный рост всех файлов данных. Когда файл в файловой группе достигает порогового значения автоприращения, все файлы в файловой группе автоматически увеличиваются в размере на коэффициент приращения.

Для настройки автоматического приращения файлов в группе перейдите в Диспетчер конфигурации SQL Server > Службы SQL Server > SQL Server <имя экземпляра> > Свойства > Дополнительно > Параметры запуска, после чего добавьте в конце строки «;-T1117».

Начиная с SQL Server 2016 это поведение контролируется с помощью опции AUTOGROW_SINGLE_FILE и AUTOGROW_ALL_FILES в параметрах базы данных, а флаг трассировки 1117 не оказывает никакого влияния.

Если у вас остались вопросы, задавайте в комментариях!

Автор: Softline

Источник