Администрирование SQL Server 2000 и расширенные свойства объектов

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

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

Первое и наиболее очевидное применение расширенных свойств объектов - это, безусловно, документирование. Если администратор создает таблицу в Enterprise Manager и для ее полей дает описание (Description), то описание для каждого поля автоматически превращается в расширенное свойство с именем MS_ Description.

В Query Analyzer контекстные меню объектов содержат команду Extended properties. Первый из таких объектов - это сама база данных. Далее следуют расположенные по уровням иерархии сверху вниз объекты:

0 - User, Type;
1 - Table, View, Procedure, Function, Default, Rule;
2 - Column, Parameter, Index, Constraint, Trigger.

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

Use MyDB
exec sp_addextendedproperty N?Property1?, N?test?

добавят к базе MyDB свойство Property1 со значением test.

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

Введенные тем или иным способом расширенные свойства можно сохранить при генерации сценариев. Для этого перед запуском генерации нужно выбрать на закладке Formatting параметр Include extended properties, и все расширенные свойства будут занесены в сценарий. В SQL Server 2000 также можно перенести объекты вместе с расширенными свойствами в другую базу, если отметить аналогичный параметр в диалоговом окне мастера импорта/экспорта или в пакете DTS.

Расширенные свойства объектов базы данных можно тиражировать или, напротив, зафиксировать их принадлежность к узлу. При тиражировании расширенные свойства, как одна из составных частей реплики, будут перенесены на сервер-подписчик. Для обеспечения переноса расширенных свойств объектов в составе реплики в свойствах публикации на закладке Articles необходимо выбирать нужную статью, и уже для ее свойств на закладке Snapshot отметить параметр Extended properties.

Все расширенные свойства объектов базы хранятся в системной таблице Sysproperties. Для поиска объектов по расширенным свойствам и их значениям очень удобно использовать Query Analyzer. Нужно выбрать из меню Tools команду Object Search и заполнить соответствующие поля.

Для ускорения написания сценариев в Query Analyzer можно использовать специальные шаблоны для работы с расширенными свойствами объектов. Эти шаблоны находятся в папке Manage Extended Property и соответствуют различным действиям, выполняемым над расширенными свойствами.

А теперь перейдем к задачам администрирования.

Аудит хранимых процедур

Механизм расширенных свойств можно использовать для аудита вызова хранимых процедур, т. е. для получения сведений о том, кто вызвал хранимую процедуру, когда и с какими параметрами. Можно также в значении расширенного свойства процедуры запоминать порядковый номер ее вызова. Для этого нужно создать расширенные свойства для процедуры или пользователя, например, в Query Analyzer, а текст процедуры дополнить вызовом обновления соответствующих расширенных свойств. Информацию, связанную с аудитом, можно получать с помощью SQL Profiler. Преимущество использования расширенных свойств в том, что эта информация становится принадлежностью самого объекта и хранится в нем самом. Хотя такой механизм не заменяет возможностей SQL Profiler.

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

Листинг 1 содержит текст хранимой процедуры Showind, выдающей информацию об индексах для данной таблицы и запоминающей дату и время своего вызова в значении расширенного свойства Calldate.

Аудит и маркировка таблиц

Расширенные свойства таблиц можно использовать для их аудита. В Листинге 2 дан пример триггера audit на таблицу example. Данный триггер не только запрещает изменение таблицы, но и записывает в значение расширенного свойства table_audit таблицы example имя пользователя, который пытался ее изменить, а в значение свойства пользователя записывает имя таблицы example. Предполагается, что у таблицы было заранее создано расширенное свойство table_audit, а у всех пользователей базы - свойство user_audit. Можно пойти дальше, подсчитать число попыток и при достижении заданного порога наложить на пользователя санкции.

Администратору приходится работать с системными таблицами и представлениями. Для системных таблиц нельзя создавать расширенные свойства, а для системных представлений можно. Однако в базе Master часть таблиц отнесена к пользовательским - это таблица MSReplication_options и таблицы с именами, которые начинаются на SPT_. В базе Distribution все таблицы с именами на MS тоже пользовательские. В отдельных базах значительная часть служебных таблиц, участвующих в тиражировании, тоже отнесена к пользовательским. Для них мы можем применять расширенные свойства.

Расширенные свойства можно использовать и в качестве маркеров для различных характеристик таблиц. Например, можно создать расширенное свойство Text_in_Row и применять его для хранения значения аналогичного свойства таблицы и последующего поиска. Листинг 3 содержит процедуру, устанавливающую значение Yes для расширенного свойства, если свойство Text in row для таблицы доступно.

Сопровождение индексов

В SQL Server 2000 можно устранять фрагментацию индексов с помощью команды DBCC INDEXDEFRAG. Допустим, у администратора есть хранимая процедура, которая перестраивает все индексы с заданным значением фрагментации. Эта процедура может устанавливать дату и время дефрагментации индекса в качестве значения расширенного свойства Defrag_Date для данного индекса. Параметр LogicalFrag принимает при этом значение первоначальной фрагментации таблицы. Листинг 4 содержит пример текста такой хранимой процедуры.

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

КРАМАРСКАЯ ТАТЬЯНА АЛЕКСЕЕВНА