Администрирование SQL Server и дисковое пространство
Администратор SQL Server отвечает за использование дискового пространства базами данных. Он обязан, с одной стороны, обеспечить это пространство, с другой - контролировать его использование. Естественно, возникает необходимость в технологии и инструментах для управления механизмом выделения дискового пространства. С помощью таких инструментов администратор определяет, сколько и когда нужно добавить в базу дискового пространства, и при необходимости высвобождает избыточное пространство. В SQL Server 2000 предусмотрены средства для решения этой задачи как автоматически, так и вручную. В предлагаемой статье я хочу дать краткий анализ имеющихся средств и перечислить дополнительные возможности, непосредственно не предназначенные для управления файлами базы данных. Статья будет полезна тем администраторам, которые хотят контролировать использование дискового пространства не только посредством консоли Enterprise Manager, но и с помощью дополнительных средств.
Предварительный расчет
Еще до создания базы данных администратор всегда может рассчитать необходимый минимум дискового пространства. Зная длину записи каждой таблицы и размер страницы (8 Kбайт), определяем количество записей на странице. Исходя из предполагаемого числа записей в таблице, можно выяснить необходимое в будущем число страниц и байтов. Дополнительно следует подсчитать количество страниц для хранения полей типа Text и Image. Необходимо учесть и процент заполнения страниц, если планируется кластерный индекс. Размер некластерных индексов вычисляется аналогично размеру таблицы. Это рутинная работа, но расчеты в точности соответствуют структуре каждой таблицы. Они упрощаются, если использовать заранее подготовленную хранимую процедуру, которая анализирует каждую вновь созданную пустую таблицу. В версиях 6.0 и 6.5 внутреннее представление данных в SQL Server было проще, и на курсах по администрированию рассматривалась специальная хранимая процедура sp_estspace для предварительного расчета объема таблиц. После соответствующей переработки ее можно применять и в более поздних версиях. При создании базы размер файлов обычно устанавливают с большим запасом. Размер журнального файла зависит от расписания резервного копирования и характера работы с базой. Рекомендуемый размер журнального файла составляет 10-25% от размера файла данных.
Режимы роста и сжатия
Существует два пути расширения файлов базы данных: автоматический рост с заданным шагом и добавление дискового пространства вручную. Начальный размер автоматически растущих файлов должен быть большим и шаг приращения - тоже. Если база расположена в нескольких файлах, то SQL Server размещает в них данные сообразно их размеру. Автоматический рост должен быть явлением исключительным и редким. Автоматическое приращение файлов базы маленькими «порциями» приводит к ее сильной фрагментации и в неожиданные моменты может вызвать рост нагрузки на систему. Файлы базы промышленного уровня лучше контролировать вручную. Для журнального файла на всякий случай стоит оставить режим автоматического роста. Не будем рассматривать способы настройки этих режимов, они известны. Автоматическое сжатие базы также может создать незапланированную нагрузку на сервер, хотя выполняется в фоновом режиме и не должно препятствовать работе пользователей. Но и этот процесс лучше контролировать вручную.
С целью повышения производительности администраторы иногда вместо файлов используют для размещения баз данных неформатированные разделы. В NTFS 5.0 на динамических дисках разделы называются томами и могут использоваться либо непосредственно, либо путем монтирования к пустой папке. В этом случае режим автоматического роста недоступен. В ручном режиме к простому тому можно добавлять дисковое пространство в пределах жесткого диска.
При любом варианте у администратора появляются дополнительные задачи. Если для файлов базы данных установлен режим неограниченного автоматического роста, необходима оценка свободного места на диске. Если решено наращивать файлы вручную, нужно успеть сделать это вовремя. Для решения подобных задач в SQL Server 2000 нет готовых инструментов, но можно попытаться создать их.
Использование оповещений
В SQL Server 2000 предусмотрена возможность реагирования на события с помощью оповещений (Alerts). Событием может быть появление сообщения в журнале Windows Application Log или превышение пороговых значений счетчиков производительности (Performance Counters). Причем в последнем случае системный монитор запускать совсем необязательно.
Этот испытанный механизм можно задействовать, если исчерпан заданный лимит свободного места на диске, в файле и т. п. Если, конечно, администратор готов кое-что запрограммировать на языке Transact-SQL.
План реагирования может быть следующим. По составленному заранее расписанию запускается задание, выполняющее хранимую процедуру. Хранимая процедура определяет, например, сколько свободного места осталось на диске, и, если значение достигло указанного предела, производит запись в журнал Windows Application Log. При наличии такой записи в журнале можно задействовать средства реагирования, включающие запуск задания, посылку сообщения по электронной почте или на пейджер. При оповещении необходимо добавить соответствующее сообщение в таблицу сообщений сервера sysmessages.
Если администратору удобнее использовать оповещения на срабатывание порогов в системном мониторе, то можно из процедуры устанавливать значения счетчиков для объектов производительности. Значениями дополнительных (User Settable) объектов производительности в SQL Server 2000 можно управлять, присваивая соответствующие значения переменной типа int.
При использовании счетчиков производительности нужно после установки необходимого значения выполнить сброс, чтобы оповещение не посылалось повторно. В случае, когда оповещение используется для информирования о событии, записанном в журнал Application Log, запись в журнале появится столько раз, сколько выполнится хранимая процедура.
По поводу системного монитора следует сделать одно замечание. Если на компьютере установлены экземпляр SQL Server 7.0 и один или несколько экземпляров SQL Server 2000, то системный монитор показывает только счетчики экземпляров SQL Server 2000.
С помощью оповещений можно заставить систему отреагировать даже на появление на диске заданного файла или папки и запустить приложение на исполнение. Необходимым условием использования оповещений является работа службы SQLAgent.
Рассмотрим пример использования оповещения о свободном пространстве на диске.
Для начала заготовим в таблице sysmessages сообщение.
EXEC sp_addmessage @msgnum = 60001, @severity = 10, @msgtext = N`Свободное место на диске %s составляет %s Кб, допустимо %s Кб`, @lang = `Russian`
Информацию об объектах файловой системы (дисках, папках, файлах) будем получать, используя соответствующую объектную модель (FileSystem-Object, FSO). Для этого сначала с помощью хранимой процедуры sp_OACreate потребуется создать объект Scripting.FileSystemObject, затем с помощью хранимой процедуры sp_Oamethod и метода GetDrive выбрать нужный диск. Хранимая процедура sp_OAGetProperty вернет в байтах свойство диска FreeSpace. Если заданный лимит исчерпан, оператор RAISERROR запишет в журнал сообщение №60001, о появлении которого администратор будет оповещен. После окончания процедуры созданные объекты уничтожаются. Текст процедуры содержится в Листинге 1.
Аналогичным образом можно определить размер указанного файла базы данных. Объем (в страницах), занимаемый базой в указанном файле, возвращает функция Fileproperty. Например, выполнение select fileproperty (`mydb_data`, `SpaceUsed`) возвращает количество страниц, выделенных под данные в файле с именем mydb_data. Далее путем простых арифметических вычислений можно определить размер свободного пространства в файле. Пример процедуры, выполняющей описанные действия, приведен в Листинге 2. Если база расположена в нескольких файлах, придется проанализировать все.
Владение файлами базы данных
Для контроля использования дискового пространства в NTFS можно использовать механизм квотирования, учитывающий владение объектами. Для этого нужно знать, кто является владельцем файлов баз данных. По умолчанию файлы баз данных располагаются в папке, где установлен сам SQL Server, например C:Program Fi-lesMicrosoft SQL ServerMSSQLDA-TA, владельцем которой является встроенная локальная группа администраторов на отдельном сервере или встроенная локальная группа администраторов на контроллере домена. Полный доступ (Full Control) разрешен только этой группе. Если файлы базы данных расположены не в папке DATA, то разрешения наследуются, но владельцем всегда остается группа администраторов. Выбор владельца файла не зависит от учетной записи пользователя, создавшего базу, и типа регистрации на сервере (в Windows или в SQL Server). Поэтому механизм квотирования без дополнительных настроек использовать нельзя. Нужно каким-то образом изменить имя владельца для файлов, создаваемых пользователями. В NTFS субъект (учетная запись) может стать владельцем какого-либо объекта только при самостоятельной регистрации в системе и при наличии соответствующего разрешения. Чтобы использовать квотирование, придется создать специальную учетную запись, не принадлежащую группе администраторов, и от ее имени завладеть нужными файлами. Затем можно отслеживать использование данной учетной записью дискового пространства, вызвав через контекстное меню диска диалоговое окно записей квот. Как показывает практика, после таких настроек SQL Server 2000 продолжает нормально работать, хотя в документации это не гарантируется.
В заключение хочется выразить надежду, что изложенные выше приемы позволят добиться более качественного администрирования SQL Server 2000 и надежного управления использованием дискового пространства.
КРАМАРСКАЯ ТАТЬЯНА АЛЕКСЕЕВНА - к.т.н., MCSE, MCDBA, MCT. Специализируется на преподавании и консультировании по вопросам использования SQL Server и продуктов BackOffice. Старший консультант компании АЛЕСТА (MCSP,CTEC). Адрес: kram@alesta.ru.