Минимальные требования к размещению файлов пользовательских баз данных

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

Файлы пользовательских баз данных, журналов транзакций, журналов планов обслуживания и других журналов располагаются в стандартной структуре каталогов, используемой вендором. При наличии нескольких дисков (дисковых массивов), системные и серверные каталоги располагаются на диске C (в предлагаемых программой установке каталогах по умолчанию - C:\Program Files\Microsoft SQL Server), файлы данных располагаются на диске D, а файл журнала транзакций (который рекомендуется иметь один для каждой БД) располагаются на диске E. В корне каждого диска создаётся каталог MSSQL, в котором создаются подкаталоги для размещения соответствующих файлов. Для файлов данных создаётся подкаталог DATA, для файлов журналов подкаталог LOG, для файлов резервных копий подкаталог BACKUP. Нежелательно располагать на одном диске файлы с разным типом доступа, например, файлы данных и файл журнала транзакций. Для повышения живучести приложения баз данных, нежелательно располагать файлы резервных копий на одном диске с файлами данных. Например, для трёх дисков возможна такая, типовая конфигурация размещения файлов баз данных:

  • C:\Program Files\Microsoft SQL Server\ - фалы сервера баз данных, системные базы данных, журналы планов обслуживания и т.п.
  • D:\MSSQL\DATA\ - Файлы данных пользовательских баз данных.
  • E:\MSSQL\LOG\ - Файл журнала регистрации транзакций пользовательской базы данных.
  • E:\MSSQL\BACKUP\ - Файлы резервных копий пользовательских и системных баз данных.

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

Для размещения пользовательских данных создаётся отдельная файловая группа (группы), которая делается файловой группой по умолчанию. Предлагается называть такую группу: DATAGROUP. Делается это для того, чтобы разнести по разным файлам системные и пользовательские объекты базы данных.

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

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

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

Как правило, SQL Server балансирует нагрузку ввода-вывода эффективнее аппаратных решений. Поэтому предпочтительно размещать базу данных на нескольких файлах и помещать файлы на собственные диски, чем размещать базу в одном файле данных и помещать его на массивы типа RAID10 или SAN. Рекомендуется, для принятия решения о целесообразности использования RAID10 или SAN проводить тестирование разных вариантов конфигурации дисковой подсистемы.

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

Пример сценария создания пользовательской базы данных:

    USE [master]
    GO
    CREATE DATABASE [ИмяБазыДанных] ON PRIMARY
    (      NAME = N'ЛогическоеИмяФайлаСистемныхДанных'
        ,  FILENAME = N'D:\MSSQL\DATA\ИмяФайлаСистемныхДанных.mdf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = UNLIMITED
        ,  FILEGROWTH = 131072KB
    ),
    FILEGROUP [DATAGROUP]
    (      NAME = N'ЛогическоеИмяФайлаДанных'
        ,  FILENAME = N'D:\MSSQL\DATA\ИмяФайлаДанных.ndf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = UNLIMITED
        ,  FILEGROWTH = 131072KB
    )
    LOG ON
    (      NAME = N'ЛогическоеИмяФайлаЖурнала'
        ,  FILENAME = N'E:\MSSQL\LOG\ИмяФайлаЖурнала.ldf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = 2048GB
        ,  FILEGROWTH = 131072KB
    )
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'ИмяБазыДанных', @new_cmptlevel=90
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [ИмяБазыДанных].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    GO
    ALTER DATABASE [ИмяБазыДанных] MODIFY FILEGROUP [DATAGROUP] DEFAULT
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULL_DEFAULT OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULLS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_PADDING OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_WARNINGS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ARITHABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_CLOSE OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_CREATE_STATISTICS ON
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_SHRINK OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_UPDATE_STATISTICS ON
    ALTER DATABASE [ИмяБазыДанных] SET CURSOR_CLOSE_ON_COMMIT OFF
    ALTER DATABASE [ИмяБазыДанных] SET CURSOR_DEFAULT GLOBAL
    ALTER DATABASE [ИмяБазыДанных] SET CONCAT_NULL_YIELDS_NULL OFF
    ALTER DATABASE [ИмяБазыДанных] SET NUMERIC_ROUNDABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET QUOTED_IDENTIFIER OFF
    ALTER DATABASE [ИмяБазыДанных] SET RECURSIVE_TRIGGERS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ENABLE_BROKER
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_UPDATE_STATISTICS_ASYNC ON
    ALTER DATABASE [ИмяБазыДанных] SET DATE_CORRELATION_OPTIMIZATION OFF
    ALTER DATABASE [ИмяБазыДанных] SET TRUSTWORTHY OFF
    ALTER DATABASE [ИмяБазыДанных] SET ALLOW_SNAPSHOT_ISOLATION OFF
    ALTER DATABASE [ИмяБазыДанных] SET PARAMETERIZATION SIMPLE
    ALTER DATABASE [ИмяБазыДанных] SET READ_WRITE
    ALTER DATABASE [ИмяБазыДанных] SET RECOVERY SIMPLE
    ALTER DATABASE [ИмяБазыДанных] SET MULTI_USER
    ALTER DATABASE [ИмяБазыДанных] SET PAGE_VERIFY NONE
    ALTER DATABASE [ИмяБазыДанных] SET DB_CHAINING OFF
    GO
    USE [ИмяБазыДанных]
    GO
    EXEC sp_changedbowner 'sa'
    GO
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET AUTO_UPDATE_STATISTICS_ASYNC ON'
    , @value=N'Стандартное значение изменено для снижения необходимости частого обновления статистики в рамках планов обслуживания БД.'
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET PAGE_VERIFY NONE'
    , @value=N'Стандартное значение изменено для повышения производительности IO, включать нужно при обнаружении ошибок DBCC CHECKDB или для критических бизнесу приложений.'
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET RECOVERY SIMPLE'
    , @value=N'Для повышения производительности не критичных приложений. Если нужно восстанавливать данные на заданый момент времени, нужно выбрать другую модель и обеспечить резервное копирование журнала транзакций.'
    GO

     

Полезные ссылки:

FAQ:

Вопрос 1.

В представленном выше примере сценария создания БД, есть установка значений настроек по умолчанию:

    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULLS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_PADDING OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_WARNINGS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ARITHABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET CONCAT_NULL_YIELDS_NULL OFF
    ALTER DATABASE [ИмяБазыДанных] SET QUOTED_IDENTIFIER OFF

     

Тут значения по умолчанию выставляются в OFF. Чем это обусловлено? Вопрос возник, поскольку неясны причины отклонения от ANSI стандарта, к тому же в другом регламенте рекомендуется выставлять данные настройки в ON:

    SET transaction isolation level read committed;
    -- Если всё совсем уж плохо - uncommitted
    SET nocount -- меньше трафик
        ,quoted_identifier -- стандартизация кавычек
        ,ansi_nulls -- стандартизация сравнения с NULL
        ,ansi_warnings -- вывод ошибок агрегации NULL и деления на 0
        ,ansi_padding -- стандартизация оконечных пробелов и нулей
        ,arithabort -- стандартизация отката транзакций
        ,xact_abort -- стандартизация отката транзакций
        ,concat_null_yields_null -- сцепление с NULL
    ON;
    SET numeric_roundabort off; -- стандартизация потери точности
    GO

     

Ответ 1.

Это установки по умолчанию, которые делает для новой базы данных соответствующий мастер SQL Server 2005 Management Studio. В регламенте предлагается документировать все отклонения от стандартных настроек, которые показаны в примере и там же есть образец документирования. На самом деле, если проще изменять установки по умолчанию, а не добавлять их в каждый сценарий создания объектов, можно создать базу с желаемым набором умолчаний. Однако, в таком случае нужно чётко понимать, что изменение настроек не приведёт к конфликтам с системными объектами или при промежуточной материализации в tempdb... Добавление установок в сценарии является более простым, понятным и, главное, управляемым путём настройки.

Published Tue, Sep 2 2008 6:22 by gladchenko
Filed under:

Comments

# karlo4

Требования маленькие, но объем большой, скачал, дома буду изучать)))

Tuesday, September 16, 2008 8:32 AM by karlo4@mail.ru

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: