Использование Management Studio с Database Engine. Модифицирование баз данных

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

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

<блок_операторов>::=

Сгруппированные команды воспринимаются интерпретатором SQL как одна команда. Подобная группировка требуется для конструкций поливариантных ветвлений, условных и циклических конструкций. Блоки BEGIN...END могут быть вложенными.

Некоторые команды SQL не должны выполняться вместе с другими командами (речь идет о командах резервного копирования, изменения структуры таблиц, хранимых процедур и им подобных), поэтому их совместное включение в конструкцию BEGIN...END не допускается.

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

<условный_оператор>::=

IF лог_выражение

{ sql_оператор | блок_операторов }

{sql_оператор | блок_операторов } ]

Циклы организуются с помощью следующей конструкции:

<оператор_цикла>::=

WHILE лог_выражение

{ sql_оператор | блок_операторов }

{ sql_оператор | блок_операторов }

Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

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

<оператор_поливариантных_ветвлений>::=

CASE входное_значение

WHEN {значение_для_сравнения |

лог_выражение } THEN

вых_выражение [,...n]

[ ELSE иначе_вых_выражение ]

Если входное значение и значение для сравнения совпадают, то конструкция возвращает выходное значение. Если же значение входного параметра не найдено ни в одной из строк WHEN...THEN, то тогда будет возвращено значение, указанное после ключевого слова ELSE.

Основные объекты структуры базы данных SQL-сервера

Рассмотрим логическую структуру базы данных.

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

Логически данные в SQL Server организованы в виде объектов. К основным объектам базы данных SQL Server относятся следующие объекты.

Краткий обзор основных объектов баз данных.

Таблицы

Все данные в SQL содержатся в объектах, называемых таблицами . Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других СУБД и содержат:

· cтроки; каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;

· cтолбцы; каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер.

Представления

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

Хранимые процедуры

Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.

Триггеры

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

Функции

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

Индексы

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

©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-08

Если вы уже когда-либо писали схемы блокировок на других языках баз данных для преодоления недостатка блокировок (как я), то у вас могло остаться чувство, что обязательно нужно самому заниматься блокировками. Позвольте вас заверить, что диспетчеру блокировок можно полностью доверять. Тем не менее SQL Server предлагает несколько методов управления блокировками, о которых мы детально поговорим в этом разделе.

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

Установка уровня изоляции подключения

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

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Допустимыми уровнями изоляции являются:

Read uncommited ? serializable

Read commited ? snapshot

Repeatable read

Текущий уровень изоляции можно проверить с помощью команды проверки целостности базы данных (DBCC):

DBCC USEROPTIONS

Результаты будут следующими (сокращенно):

Set Option Value

isolation level repeatable read

Уровни изоляции могут быть также установлены на уровне запроса или таблицы с помощью параметров блокировки.

Использование изоляции уровня снимков базы данных

Существуют два варианта уровня изоляции снимков базы данных: snapshot и read commited snapshot. Изоляция snapshot работает подобно repeatable read, не занимаясь вопросами блокировки. Изоляция read commited snapshot имитирует установленный по умолчанию в SQL Server уровень read commited, так же снимая вопросы блокировки.

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

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

Использование изоляции Snapshot

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

ALTER DATABASE Aesop

SET ALLOW_SNAPSHOT_ISOLATION ON

| Для проверки того, включена ли в базе данных изоляция snapshot, выполните SVS следующий запрос: SELECT name, snapshot_isolation_state_desc FROM [ * sysdatabases.

Теперь первая транзакция начинает чтение и остается открытой (т.е. не подтвержденной): USE Aesop

BEGIN TRAN SELECT Title FROM FABLE WHERE FablelD = 2

Будет получен следующий результат:

В это время вторая транзакция начинает обновление той же строки, которая открыта первой транзакцией:

SET TRANSACTION ISOLATION LEVEL Snapshot;

BEGIN TRAN UPDATE Fable

SET Title = ‘Rocking with Snapshots’

WHERE FablelD = 2;

SELECT * FROM FABLE WHERE FablelD = 2

Результат следующий:

Rocking with Snapshots

He правда ли, удивительно? Вторая транзакция смогла обновить строку, даже несмотря на то, что первая транзакция осталась открытой. Вернемся к первой транзакции и увидим исходные данные:

SELECT Title FROM FABLE WHERE FablelD = 2

Результат следующий:

Если открыть третью и четвертую транзакции, то они увидят все то же исходное значение The Bald Knight:

Даже после того как вторая транзакция подтвердит изменения, первая будет по-прежнему видеть исходное значение, а все следующие транзакции - новое, Rocking with Snapshots.

Использование ИЗОЛЯЦИИ Read Commited Snapshot

Изоляция Read Commited Snapshot включается с помощью аналогичного синтаксиса:

ALTER DATABASE Aesop

SET READ_COMMITTED_SNAPSHOT ON

Подобно изоляции Snapshot, данный уровень для снятия вопросов блокировок также использует версионность строк. Если взять за основу пример, описанный в предыдущем разделе, то в данном случае первая транзакция увидит изменения, выполненные второй, как только они будут подтверждены.

Так как Read Commited является уровнем изоляции, принятым в SQL Server по умолчанию, требуется только установка параметров базы данных.

Разрешение конфликтов записи

Транзакции, записывающие данные при установленном уровне изоляции Snapshot, могут быть заблокированы предыдущими неподтвержденными транзакциями записи. Такая блокировка не заставит новую транзакцию ожидать - просто будет сгенерирована ошибка. Для обработки подобных ситуаций используйте выражение try. . . catch, выждите пару секунд и попробуйте повторить транзакцию снова.

Использование параметров блокировки

Параметры блокировки позволяют вносить временную коррекцию в статегию блокировки. В то время как уровень изоляции оказывает влияние на подключение в целом, параметры блокировки специфичны для каждой таблицы в конкретном запросе (табл. 51.5). Параметр WITH (параметр_блокировки) помещается после имени таблицы в предложении FROM запроса. Для каждой таблицы можно задать несколько параметров, разделяя их запятыми.

Таблица 51.5. Параметры блокировки

Параметр блокировки

Описание

Уровень изоляции. He устанавливает и не удерживает блокировку. Равносилен отсутствию блокировок

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

Уровень изоляции. Удерживает общую и эксклюзивную блокировки до момента подтверждения транзакции

Уровень изоляции. Удерживает общую блокировку до завершения транзакции

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

Включение блокировки на уровне строк вместо уровня страницы, экстента или таблицы

Включение блокировки на уровне страниц вместо уровня таблицы

Автоматическая эскалация блокировок уровня строк, страниц и экстента до гранулярности уровня таблицы

Параметр блокировки

Описание

Неприменение и неудержание блокировок. То же, что и ReadUnCommited

Включение эксклюзивной блокировки таблицы. Запрет другим транзакциям работать с таблицей

Удержание общей блокировки до подтверждения транзакции (аналогично Serializable)

Использование блокировки обновления вместо общей и ее удержание. Блокировка других записей в данные между изначальными операциями чтения и записи

Удержание эксклюзивной блокировки данных до подтверждения транзакции

В следующем примере в предложении FROM инструкции UPDATE использован параметр блокировки, запрещающий диспетчеру эскалировать гранулярность блокировки:

USE OBXKites UPDATE Product

FROM Product WITH (RowLock)

SET ProductName = ProductName + ‘ Updated 1

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

Ограничения блокировок уровня индексов

Уровни изоляции и параметры блокировки применяются на уровне подключений и запросов. Единственным способом управления блокировками на уровне таблицы является ограничение гранулярности блокировок на основе конкретных индексов. С помощью системной хранимой процедуры sp_indexoption блокировки строк и/или страниц можно отключить для конкретного индекса, используя следующий синтаксис: sp_indexoption ‘имя_индекса 1 ,

AllowRowlocks или AllowPagelocks,

Это может пригодиться в ряде особых случаев. Если таблица часто вызывает ожидания по причине блокировок страниц, то установка для параметра allowpagelocks значения off установит блокировку на уровне строк. Уменьшенная гранулярность блокировок положительно скажется на конкуренции. К тому же, если таблица редко обновляется, но часто считывается, блокировки на уровне строк и страниц нежелательны; в этом случае оптимальным является уровень блокировки на уровне таблиц. Если обновления выполняются нечасто, то эксклюзивная блокировка таблиц не приведет к большим проблемам.

Хранимая процедура Sp_indexoption предназначена для тонкой настройки схемы данных; именно поэтому в ней используется блокировка на уровне индексов. Для ограничения блокировок по первичному ключу таблицы используйте sp_help имя_ та блицы, чтобы найти имя индекса первичного ключа.

Следующая команда конфигурирует таблицу ProductCategory как редко обновляемый классификатор. Вначале команда sp_help выводит имя индекса первичного ключа таблицы: sp_help ProductCategory

Результат (усеченный) таков:

index index index

name description keys

PK_____________ ProductCategory 79A814 03 nonclustered, ProductCategorylD

unique, primary key located on PRIMARY

Имея в наличии реальное имя первичного ключа, системная хранимая процедура может установить параметры блокировки индекса:

EXEC sp_indexoption

‘ProductCategory.РК__ ProductCategory_______ 7 9А814 03′,

‘AllowRowlocks’, FALSE EXEC sp_indexoption

‘ProductCategory.PK__ ProductCategory_______ 79A81403′,

‘AllowPagelocks’, FALSE

Управление временем ожидания блокировок

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

К счастью, вы можете установить время ожидания блокировки с помощью параметра подключения set lock_timeout. Установите для этого параметра количество миллисекунд или, если хотите не ограничивать время, установите для него значение -1 (оно принято по умолчанию). Если для этого параметра установлено значение 0, то транзакция будет немедленно отклонена при наличии какой-либо блокировки. В этом случае приложение будет исключительно быстродействующим, но малоэффективным.

В следующем запросе время ожидания блокировки устанавливается в две секунды (2000 миллисекунд):

SET Lock_Timeout 2 00 0

Если транзакция выходит за пределы установленного предельного времени ожидания, то генерируется ошибка с номером 1222.

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

Оценка производительности конкуренции в базе данных

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

Тестирование конкуренции нужно правильно организовать. На одном уровне он должен содержать одновременное использование множеством пользователей одной и той же конечной формы. Может оказаться полезной и программа.NET, которая постоянно имитирует

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

Многопользовательскую конкуренцию лучше тестировать в процессе разработки несколько раз. Как говорится в экзаменационном руководстве MCSE, “не допускайте, чтобы тест в реальных условиях был первым”.

Блокировки приложения

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

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

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

Блокировка приложений может применяться в транзакциях; при этом может быть объявлен режим блокировки Shared, Update, Exclusive, IntentExclusice или IntentShared. Возвращаемое процедурой значение указывает, успешным ли было применение блокировки.

0. Блокировка установлена успешно.

1. Блокировка была установлена, когда другая процедура сняла свою блокировку.

999. Блокировка не была установлена по другой причине.

Хранимая процедура sp_ReleaseApLock снимает блокировку. В следующем примере продемонстрировано, как блокировка приложения может использоваться в пакете или процедуре: DECLARE @ShareOK INT EXEC @ShareOK = sp_GetAppLock

@Resource = ‘CableWorm’,

@LockMode = ‘Exclusive’

IF @ShareOK < 0

…Код обработки ошибки

… Программный код …

EXEC sp_ReleaseAppLock @Resource = ‘CableWorm’

Когда блокировки приложения просматриваются с помощью Management Studio или процедуры sp_Lock, они отображаются с типом АРР. В следующем листинге приведен сокращенный вывод процедуры sp_Lock, запущенной одновременно с приведенным выше кодом: spid dbid Objld Indld Type Resource Mode Status

57 8 0 0 APP Cabllf 94cl36 X GRANT

Следует обратить внимание на два небольших отличия в том, как блокировки приложения обрабатываются в SQL Server:

Взаимоблокировки не выявляются автоматически;

Если некоторая транзакция устанавливает блокировку несколько раз, она должна снять ее ровно такое же количество раз.

Взаимоблокировки

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

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

Раньше взаимоблокировки представляли собой серьезную проблему, но теперь SQL Server позволяет успешно разрешить ее.

Создание взаимоблокировки

Проще всего создать ситуацию взаимоблокировки в SQL Server с помощью двух подключений в редакторе запросов утилиты Management Studio (рис. 51.12). Первая и вторая транзакции пытаются обновить одни и те же строки, однако в противоположном порядке. Используя третье окно для запуска процедуры pGetLocks, можно выполнять мониторинг блокировок.

1. Создайте в редакторе запросов второе окно.

2. Поместите код блока Шаг 2 во второе окно.

3. В первое окно поместите код блока Шаг 1 и нажмите клавишу .

4. Во втором окне аналогично выполните код Шаг 2.

5. Вернитесь в первое окно и выполните код блока Шаг 3.

6. Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее.

Ниже приведен программный код примера.

– Транзакция 1 — Шаг 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact

SET LastName = ‘Jorgenson’

WHERE ContactCode = 401′

Puc. 51.12. Создание ситуации взаимоблокировки в Management Studio с помощью двух подключений (их окна расположены вверху)

Теперь первая транзакция установила эксклюзивную блокировку на запись со значением 101 в поле ContactCode. Вторая транзакция установит эксклюзивную блокировку строки со значением 1001 в поле ProductCode, а затем попытается эксклюзивно заблокировать запись, уже заблокированную первой транзакцией (ContactCode=101).

– Транзакция 2 — Шаг 2 USE OBXKites BEGIN TRANSACTION UPDATE Product SET ProductName

= ‘DeadLock Repair Kit’

WHERE ProductCode = ‘1001’

SET FirstName = ‘Neals’

WHERE ContactCode = ‘101’

COMMIT TRANSACTION

Пока еще взаимоблокировки не существует, поскольку транзакция 2 ожидает завершения транзакции 1, но транзакция 1 пока еще не ожидает завершения транзакции 2. В этой ситуации, если транзакция 1 завершит свою работу и выполнит инструкцию COMMIT TRANSACTION, ресурс данных будет освобожден, и транзакция 2 благополучно получит возможность необходимой ей блокировки и продолжит свои действия.

Проблема возникает, когда транзакция 1 попытается обновить строку с ProductCode=l. Однако необходимую для этого эксклюзивную блокировку она не получит, поскольку эта запись заблокирована транзакцией 2:

– Транзакция 1 — Шаг 3 UPDATE Product SET ProductName

= ‘DeadLock Identification Tester’

WHERE ProductCode = ‘1001’

COMMIT TRANSACTION

Транзакция 1 вернет следующее текстовое сообщение об ошибке спустя пару секунд. Возникшую взаимоблокировку можно также увидеть в SQL Server Profiler (рис. 51.13):

Server: Msg 1205, Level 13,

State 50, Line 1 Transaction (Process ID 51) was

deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Транзакция 2 завершит свою работу, будто бы проблемы и не существовало:

(1 row(s) affected)

(1 row(s) affected)

Рис. 51.13. SQL Server Profiler позволяет выполнять мониторинг взаимоблокировок с помощью события Locks:Deadlock Graph и выявлять ресурс, вызвавший взаимоблокировку

Автоматическое выявление взаимоблокировок

Как было продемонстрировано в приведенном выше коде, SQL Server автоматически выявляет ситуацию взаимоблокировки, проверяя блокирующие процессы и откатывая транзакции,

выполнившие наименьший объем работы. SQL Server постоянно проверяет существование перекрестных блокировок. Задержка выявления взаимоблокировок может варьироваться от нуля до двух секунд (на практике дольше всего мне приходилось ожидать этого пять секунд).

Обработка взаимоблокировок

Когда происходит взаимоблокировка, подключение, выбранное в качестве “жертвы” взаимоблокировки, должно повторить свою транзакцию. Так как работа должна быть переделана, хорошо, что откатывается именно та транзакция, которая успела выполнить наименьший объем работы, - именно она будет повторена сначала.

Ошибка с кодом 12 05 должна перехватываться клиентским приложением, которое и должно перезапускать транзакцию. Если все происходит как нужно, пользователь даже не заподозрит о том, что произошла взаимоблокировка.

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

SET DEADLOCKJPRIORITY LOW

Минимизация взаимоблокировок

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

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

Никогда не ставьте код транзакции в зависимость от ввода пользователя.

Старайтесь создавать пакеты и процедуры, устанавливающие блокировки, в одном и том же порядке. Например, вначале обрабатывается таблица А, затем таблицы Б, В и т.д. Таким образом, одна процедура будет ожидать второй и взаимоблокировки не смогут возникнуть по определению.

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

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

Автор: Майк Вайнер (Mike Weiner)
Соавтор: Бурцин Пэйтел (Burzin Patel)
Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)

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

Монитор активности

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

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.

Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio

Примечание. Монитор активности использует параметр периодичности обновления данных, который можно изменить щелчком правой кнопки мыши. При выборе частого обновления данных (раз в менее чем 10 секунд) производительность высоконагруженной рабочей системы может понизиться.

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

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

· Щелкните правой кнопкой мыши элемент строки, чтобы отобразить полный текст запроса или графический план выполнения с помощью пункта меню «Последние ресурсоемкие запросы».

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC :Completed , SQL :BatchStarting и SQL :BatchCompleted , а также Audit Login и Audit Logout .

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.

Аудит

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

В SQL Server 2008 (только выпуски Enterprise и Developer) SQL Server Audit реализует автоматизацию, позволяющую администратору и другим пользователям подготавливать, сохранять и просматривать аудиты различных компонентов серверов и баз данных. Функция предусматривает возможность аудита с детализацией уровня сервера или базы данных.

Существуют группы действий аудита на уровне сервера, например, следующие:

· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.

· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.

· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.

Группы действий аудита на уровне базы данных включают следующие:

· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.

Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server .

Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий - еще одной новой функции SQL Server 2008.

Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [ dbo .Payroll ] ?».

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

Сжатие резервных копий

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

При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией - Resource Governor .

Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных . Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера ; его также можно установить, выполнив хранимую процедуру sp _ configure со значением параметра backup compression default , равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.

Сжатие резервных копий - исключительно полезная функция, сберегающая дисковое пространство и время. Дополнительные сведения о настройке сжатия резервных копий см. в техническом примечанииНастройка производительности сжатия резервных копий в SQL Server 2008 . Примечание. Создание сжатых резервных копий поддерживается только в выпусках SQL Server 2008 Enterprise и Developer , однако все выпуски SQL Server 2008 позволяют восстанавливать сжатые резервные копии.

Серверы централизованного управления

Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.

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

· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008 .

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

· Управление службами и вызов диспетчера конфигурации SQL Server: инструмент «серверы централизованного управления» помогаает создать центр управления, в котором администратор БД может просмотреть и даже изменить (при наличии соответствующих разрешений) состояние служб.

· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.

Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 - знакомы ли вы с ними?

Сборщик данных и хранилище данных управления

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

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

Еще одна полезная функция хранилища данных управления - это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процессDCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.

Рис. 2: Вид отчета сборщика данных SQL Server 2008

В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQLServer в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQLServer 2008, кроме SQLServerExpress.

Сжатие данных

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

Сжатие данных, представленное в SQL Server 2008, помогает разрешить эти проблемы. Эта функция позволяет администратору избирательно сжимать любые таблицы, секции таблиц или индексы, благодаря чему уменьшается занимаемое пространство на диске и в памяти, а также размер операций ввода-вывода. Сжатие и распаковка данных нагружают процессор; тем не менее, во многих случаях дополнительная нагрузка на процессор более чем компенсируется выигрышем в объемах ввода-вывода. В конфигурациях, в которых ввод-вывод является «узким местом», сжатие данных также может обеспечить рост производительности.

В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.

SQLServer 2008 поддерживает два типа сжатия: сжатие строк , при котором сжимаются отдельные столбцы таблицы, и сжатие страниц , при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQLServer 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.

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

Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедуройsp _estimate _data _compression _savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQLServer 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.

Дополнительные сведения об использовании сжатия см. в документе Создание сжатых таблиц и индексов .

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

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

Управление на основе политик (PBM) предоставляет администратору широкий набор возможностей по управлению средой. Политики можно создавать и выполнять проверку на соответствие им. Если цель проверки (например, ядро базы данных, база данных, таблица или индекс SQLServer) не соответствует требованиям, администратор может автоматически перенастроить ее в соответствии с этими требованиями. Также существует ряд режимов определения политик (многие из которых автоматизированы), упрощающих проверку соответствия требованиям политик, регистрацию в журнале нарушений политики и отправку уведомлений, и даже выполняющих откат изменений для обеспечения соответствия требованиям политики. Дополнительные сведения о режимах определения и об их сопоставлении с аспектами (понятием управления на основе политик (PBM), также обсуждаемым в этом блоге) см. в разделе Блог об управлении SQL Server на основе политик .

Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQLServerManagement Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.

В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQLServer. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008 .

Прогнозируемая производительность и параллелизм

Многие администраторы сталкиваются со значительными трудностями при поддержке серверов SQLServers с постоянно изменяющимися рабочими нагрузками и обеспечении предсказуемого уровня производительности (или минимизации расхождений в планах запросов и производительности). Неожиданные изменения производительности при выполнении запросов, изменения планов запросов и/или общие связанные с производительностью проблемы могут быть вызваны рядом причин, в том числе повышением нагрузки от выполняющихся на сервере SQLServer приложений или обновлением версии самой базы данных. Предсказуемость выполняемых на сервере SQLServer запросов и операций значительно облегчает достижение и поддержание целей по уровню доступности, производительности и/или непрерывности бизнес-деятельности (выполнение соглашений об уровне обслуживания и уровне операционной поддержки).

В SQLServer 2008 некоторые функции изменены для увеличения прогнозируемости производительности. Так, внесены определенные изменения в структуры планов SQLServer 2005 (закрепление планов ) и добавлена возможность управления эскалацией блокировок на уровне таблиц. Оба улучшения способствуют более прогнозируемому и упорядоченному взаимодействию между приложением и базой данных.

Во-первых, структуры планов (Plan Guide):

В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции - «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документе Принудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.

В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов , позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.

sp_create_plan_guide_from_handle
@name = N’MyQueryPlan’,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;

У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides . Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide , для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.

Во-вторых, эскалация блокировок:

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

SQLServer 2008 предлагает решение обеих проблем. В нем реализован новый параметр, позволяющий управлять эскалацией блокировки на уровне таблиц. С помощью команды ALTERTABLE можно выбрать отключение эскалации или эскалацию до уровня секций для секционированных таблиц. Обе эти возможности улучшают масштабируемость и производительность без нежелательных побочных эффектов, затрагивающих другие объекты в экземпляре. Эскалация блокировки задается на уровне объекта базы данных и не требует внесения каких-либо изменений в приложения. Оно поддерживается во всех выпусках SQLServer 2008.

Resource Governor

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

Новая функция SQL Server 2008 - «Регулятор ресурсов»(Resource Governor) - помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.

На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.

Ниже приведены особенности, которые следует учитывать при использовании регулятора ресурсов.

— Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.

— Не поддерживается группировка объектов на уровне базы данных, при которой доступ к ресурсам регулируется на основе объектов базы данных, к которым осуществляется доступ.

— Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.

— Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.

— Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL ServerIntegration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.

Прозрачное шифрование данных (TDE)

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

Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных (TDE). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных (DEK). DEK - это симметричный ключ, защищаемый сертификатом, который хранится в базе данных >master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами (EKM).

Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов - это отдельная функция, позволяющая шифровать даже активные данные.

Шифрование базы данных - одноразовый процесс, который можно запустить командой Transact — SQL или из среды SQL Server Management Studio , после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys . Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES _128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB . Наконец, при одновременном использовании различных функций необходимо учитывать следующее:

• При использовании сжатия резервных копий для сжатия зашифрованной базы данных размер сжатой резервной копии будет большим, чем без использования шифрования, так как зашифрованные данные сжимаются плохо.
• Шифрование базы данных не влияет на сжатие данных (строчное или страничное).

TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer ; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Прозрачное шифрование данных .

Подводя итог, можно сказать, что в SQL Server 2008 предлагаются функции, усовершенствования и возможности, облегчающие работу администратора базы данных. 10 самых популярных из них описаны здесь, но в SQL Server 2008 есть и много других возможностей, упрощающих жизнь администраторов и других пользователей. Списки «10 лучших функций» по другим направлениям работы с SQL Server можно найти в других статьях «Лучшие 10 … в SQL Server 2008» на этом сайте. Полный список функций и их подробное описание см. в электронной документации по SQL Server и на обзорном веб-сайте SQL Server 2008 .

В стандарте SQL определены два оператора: GRANT и REVOKE пре­доставления и отмены привилегий соответственно.

Оператор предоставления привилегий имеет следующий формат:

GRANT {<список действий> | ALL PRIVILEGES }

ON <имя_объекта>

ТО {<имя_пользователя> | PUBLIC }

Здесь список действий определяет набор действий из обще допустимого перечня действий над объектом данного типа.

Параметр ALL PRIVILEGES указывает, что разрешены все действия из допустимых для объектов данного типа.

<имя_объекта> - задает имя конкретного объекта: таблицы, представления, хра­нимой процедуры, триггера.

<имя_пользователя> или PUBLIC определяет, кому предоставляются данные приви­легии.

Параметр WITH GRANT OPTION является необязательным и определяет режим, при котором передаются не только права на указанные действия, но и право переда­вать эти права другим пользователям. Передавать права в этом случае пользова­тель может только в рамках разрешенных ему действий.

Рассмотрим пример, пусть у нас существуют три пользователя с абсолютно уни­кальными именами user1, user2 и user3. Все они являются пользователями од­ной БД.

Userl создал объект Tab1, он является владельцем этого объекта и может пере­дать права на работу с эти объектом другим пользователям. Допустим, что поль­зователь user2 является оператором, который должен вводить данные в Tab1 (например, таблицу новых заказов), а пользователь user3 является менеджером отдела, который должен регулярно про­сматривать введенные данные.

Для объекта типа таблица полным допустимым перечнем действий является на­бор из четырех операций: SELECT, INSERT, DELETE, UPDATE. При этом операция об­новления может быть ограничена несколькими столбцами.

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

GRANT {[.INSERT][.DELETE][.UPDATE

(<список столбцов>)]} ON <имя_таблицы>

TO {<имя_пользователя> | PUBLIC }

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

Эти назначения означают, что пользователь user2 имеет право только вводить новые строки в отношение Tab1, а пользователь user3 имеет право просматри­вать все строки в таблице Tab1.

При назначении прав доступа на операцию модификации можно уточнить, зна­чение каких столбцов может изменять пользователь. Допустим, что менеджер отдела имеет право изменять цену на предоставляемые услуги. Предположим, что цена задается в столбце SENA таблицы Tab1. Тогда операция назначения при­вилегии пользователю user3 может измениться и выглядеть следующим образом:

GRANT SELECT, UPDATE (SENA)

Если наш пользователь user1 предполагает, что пользователь user4 может его за­мещать в случае его отсутствия, то он может предоставить этому пользователю все права по работе с созданной таблицей Tab1.

GRANT ALL PRIVILEGES

WITH GRANT OPTION

В этом случае пользователь user4 может сам назначать привилегии по работе с таблицей Tab1 в отсутствие владельца объекта пользователя user1. Поэтому в случае появления нового оператора пользователя user5 он может назначить ему права на ввод новых строк в таблицу командой:

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

GRANT SELECT, UPDATE, DELETE

WITH GRANT OPTION

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

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

Так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, и, следовательно, для таких представлений набор допустимых действий ограничивается операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для та­кого представления допустимыми будут все 4 операции- SELECT, INSERT, UPDATE и DELETE.

Для отмены ранее назначенных привилегий в стандарте SQL определен опера­тор REVOKE. Оператор отмены привилегий имеет следующий синтаксис:

REVOKE {<список операций>| ALL PRIVILEGES}

ON <имя_объекта>

FROM {<список пользователей | PUBLIC }

(CASCADE | RESTRICT }

Параметры CASCADE или RESTRICT определяют, каким образом должна произво­диться отмена привилегий. Параметр CASCADE отменяет привилегии не только пользователя, который непосредственно упоминался в операторе GRANT при пре­доставлении ему привилегий, но и всем пользователям, которым этот пользова­тель присвоил привилегии, воспользовавшись параметром WITH GRANT OPTION.

Например, при использовании операции

REVOKE ALL PRIVILEGES

TO user4 CASCADE

будут отменены привилегии и пользователя user5, которому пользователь user4 успел присвоить привилегии.

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

REVOKE ALL PRIVILEGES

TO user4 RESTRICT

не будет выполнена, потому что пользователь user4 передал часть своих полно­мочий пользователю user5.

Посредством оператора REVOKE можно отобрать все или только некоторые из ра­нее присвоенных привилегий по работе с конкретным объектом. При этом из описания синтаксиса оператора отмены привилегий видно, что можно отобрать привилегии одним оператором сразу у нескольких пользователей или у целой группы PUBLIC.

Поэтому корректным будет следующее использование оператора REVOKE

TO user2, user4 CASCADE

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

По умолчанию действие, соответствующее запуску (исполнению) хранимой про­цедуры, назначается всем членам группы PUBLIC.

Если необходимо изменить это условие, то после создания хранимой процедуры необходимо записать оператор REVOKE

TO PUBLIC CASCADE

И теперь можно назначить новые права пользователю user4

Системный администратор может разрешить некоторому пользователю созда­вать и изменять таблицы в некоторой БД. Тогда он может записать оператор предоставления прав следующим образом:

GRANT CREATE TABLE ALTER TABLE DROP TABLE

В этом случае пользователь user1 может создавать, изменять или удалять табли­цы в БД DB_LIB, однако он не может разрешить создавать или изменять таблицы в этой БД другим пользователям, потому что ему дано разрешение без права де­легирования своих возможностей.

В некоторых СУБД пользователь может получить права создавать БД. Напри­мер, в MS SQL Server системный администратор может предоставить пользова­телю main_user право на создание своей БД на данном сервере. Это может быть сделано следующей командой:

GRANT CREATE DATABASE

По принципу иерархии пользователь main_user, создав свою БД, теперь может предоставить права на создание или изменение любых объектов в этой БД дру­гим пользователям.

В СУБД, которые поддерживают однобазовую архитектуру, такие разрешения недопустимы. Например, в СУБД Oracle на сервере создается только одна БД, но пользователи могут работать на уровне подсхемы (части таблиц БД и свя­занных с ними объектов). Поэтому там вводится понятие системных привиле­гий. Их очень много, 80 различных привилегий.

Для того чтобы разрешить пользователю создавать объекты внутри этой БД, используется понятие системной привилегии, которая может быть назначена од­ному или нескольким пользователям. Они выдаются только на действия и кон­кретный тип объекта. Поэтому если системный администратор предо­ставил пользователю право создания таблиц (CREATE TABLE), то для того чтобы он мог создать триггер для таблицы, ему необходимо предоставить еще одну системную привилегию CREATE TRIGGER. Система защиты в Oracle считается од­ной из самых мощных, но это имеет и обратную сторону - она весьма сложная. Поэтому задача администрирования в Oracle требует хорошего знания как се­мантики принципов поддержки прав доступа, так и физической реализации этих возможностей.

С выходом SQL Server 2005 разработчики и администраторы баз данных (DBA) получат целый комплект новых средств управления. Версия SQL Server 2005 отличается не только набором добавленных в нее новых служб; при разработке этого продукта создание новых инструментов управления для DBA тоже было одной из важнейших задач. На смену SQL Server 2000 Enterprise Manager и Query Analyzer пришел новый программный инструмент - SQL Server Management Studio. Еще необходимо упомянуть о двух новых вспомогательных средствах: SQL Server Configuration Manager и Surface Area Configuration tool. Прежде чем приступать к рассмотрению возможностей SQL Server Management Studio, давайте познакомимся с каждым из этих инструментов.

Средства управления конфигурацией

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

SQL Server Configuration Manager. Этот инструмент помогает управлять учетными записями различных служб SQL Server 2005 на сервере, а также самими этими службами. Непосредственно из SQL Server Management Studio можно запускать и устанавливать службы SQL Server 2005, подобно тому как это раньше делалось с помощью Enterprise Manager, так что заново изучать SQL Server Configuration Manager не придется. Данная программа может быть вызвана через меню Start в Windows или через Computer Management в меню Administrative Tools. А чтобы запустить SQL Server Management Studio, который мы рассмотрим в следующем разделе, можно воспользоваться диалоговым окном Management Studio Registered Servers.

SQL Server Configuration Manager используется для управления службами SQL Server, сетевыми библиотеками и SQL Native Client (см. экран 1 ). Здесь можно устанавливать и изменять параметры учетных записей служб, а также включать или отключать эти службы. Данный инструмент частично перекрывает функции двух других средств, которые осуществляют управление этими службами и другими функциями с точки зрения управления защитой от возможных угроз.

Средства настройки. В SQL Server 2005 появилось много новых служб. Теперь, в дополнение к стандартным службам, таким как SQL Server, SQL Agent и Analysis Server, можно еще управлять параметрами службы SQL Server Integration Services (ранее Data Transformation Services, DTS), Reporting Services, Full-Text Search и SQL Browser.

При установке SQL Server 2005 эти службы представляют собой объект для потенциальных атак. Для принятия мер по минимизации поверхности атаки Microsoft предлагает два новых средства настройки, одно из которых предназначено для управления службами, а другое - для настройки параметров соединений. На конечной стадии процесса установки SQL Server 2005 имеются ссылки на эти программы, они также доступны через меню Start. С помощью этих инструментов можно контролировать те службы и функции, которые представляют собой потенциально уязвимые места для атак на SQL Server, любую из этих служб можно включать или отключать непосредственно из этих программ.

С помощью утилиты Surface Area Configuration for Services and Connections можно запускать и отключать различные службы (см. экран 2 ). Здесь же можно управлять статусом службы SQL Browser, которая работает через порт UDP 1434 и преобразует соединения в именованные объекты.

Программа Surface Area Configuration for Features, окно которой показано на экране 3 , позволяет управлять соединениями, в том числе специализированными удаленными запросами, настройками CLR, хранимыми процедурами Database Mail (SMTP), удаленными выделенными административными подключениями (Dedicated Administrator Connections), конечными точками служб Web (SOAP), OLE-автоматизацией расширенных хранимых процедур, конечными точками Service Broker, хранимыми процедурами SQL Mail (MAPI), xp_cmdshell и Web Assistant. Все эти функции по умолчанию отключены и должны активизироваться выборочно, по мере необходимости. Для включения многих из этих функций можно использовать системную хранимую процедуру sp_configure, с ее помощью также можно задействовать наиболее важные команды T-SQL на конечных точках.

Management Studio

В SQL Server 2005 появилось новое консолидированное средство управления - SQL Server Management Studio. Это всеобъемлющий инструмент, в котором объединены функции, актуальные для DBA и для разработчиков. Management Studio представляет собой комплексное программное средство, которое содержит столько возможностей, что для их подробного описания одной статьи явно недостаточно. Что касается наиболее значительных новшеств и изменений, имеющихся в данном продукте, то их можно описать тремя фразами: управление сервером, редактирование запросов и анализ производительности.

Management Studio - это огромный «ящик с инструментами»: разработчики Microsoft тщательно отобрали лучшие функции администрирования из Enterprise Manager и лучшие функции анализа и редактирования запросов из Query Analyzer, а затем соединили их в новом программном средстве. Далее в статье мы бросим беглый взгляд на эти новые возможности.

Запустив оболочку Management Studio в первый раз, вы обнаружите в ней сходство с Visual Studio. Но несмотря на то что Management Studio базируется на оболочке Visual Studio и имеет с ней некоторое внешнее сходство, это обманчивое впечатление: Management Studio представляет собой абсолютно новое программное средство, разработанное с использованием управляемого кода.

Сразу становится очевидно, что это два типа диалогов: центральная, не перемещаемая часть окна Management Studio Window называется областью окна документа, а объекты, размещаемые по краям, называются компонентами. Изначально в окне документа по умолчанию отображается страница сводки (Summary), содержащая некоторые отчетные данные о зарегистрированных серверах SQL Server. При первом запуске Management Studio в левой части будут отображаться диалоги компонентов Registered Servers и Object Explorer, а окно документа, справа, будет содержать страницу Summary (см. экран 4 ). Здесь отображается предлагаемый по умолчанию набор компонентов, но его можно изменить, добавив нужные компоненты в выбранные места. Страница Summary может содержать перечень компонентов узла Object Explorer или формировать отчет по выбранному узлу. Некоторое время поработав с компонентами, вы познакомитесь с третьим типом диалогов, так называемыми немодальными диалоговыми окнами, которые не связаны с основным окном Management Studio.

Как и в Visual Studio, каждое из окон компонентов является фиксируемым и скрываемым (hideable). С помощью значка канцелярской кнопки, имеющегося в панели кнопок каждого из окон, можно включить функцию автоматического скрытия (auto-hide), еще до нее можно добраться, щелкнув правой кнопкой мыши в самом верху окна. Скрываемые автоматически окна удобны в тех случаях, когда требуется дополнительное экранное пространство для расширения Object Browser, авторизующих запросов (authoring queries) и т. д. Характер представления этих окон можно изменить, нажав кнопку Window Position (кнопка с изображением небольшой треугольной стрелки, направленной вниз, которая имеется в правом верхнем углу каждого из окон компонентов).

Через меню View можно задействовать многие другие окна компонентов. Для целей администрирования наиболее актуальны активные по умолчанию окна Registered Servers и Object Explorer. Что касается окон компонентов Solution Explorer, Template Explorer и Properties, то они больше пригодятся для редактирования запросов и анализа. В Management Studio запоминается последнее отображение и параметры конфигурации окна, поэтому все они сохранятся при следующем запуске программы. Если нужно сбросить настройки окна в состояние по умолчанию, то в меню Window следует выбрать пункт Reset Window.

В Management Studio реализована поддержка администрирования и редактирования сценариев для многих элементов: экземпляров базы данных SQL Server, вместе с Analysis Services, Report Services, Integration Services и SQL Server Mobile. Все эти типы служб могут регистрироваться через Management Studio с помощью Register Servers, а затем их можно администрировать с помощью Object Explorer. Здесь можно редактировать сценарии T-SQL, сценарии Analysis Services и сценарии SQL Server Mobile, а также объединять их в проекты с помощью окна Solution Explorer, что будет показано в разделе, посвященном редактированию запросов.

Администрирование

Для того чтобы в полной мере оценить те новые возможности, которые получили администраторы баз данных с появлением Management Studio, сначала сравним Management Studio с Enterprise Manager, стандартным средством администрирования в SQL Server 2000. Программа Enterprise Manager представляет собой встроенную в SQL Server 2000 графическую утилиту, предназначенную для выполнения задач администрирования сервера баз данных. Данной утилите присущ ряд недостатков, в частности с ее помощью можно управлять только реляционным механизмом SQL Server и службой SQL Agent, но не службой Analysis Services. Кроме того, Enterprise Manager не может эффективно отображать большое количество серверов, баз данных или объектов баз данных. Вторая проблема заключается в том, что для решения многих задач здесь используются модальные диалоги, поэтому после запуска какой-либо процедуры, например резервного копирования, часто приходится запускать еще одну копию Enterprise Manager для продолжения наблюдения за сервером. И наконец, Enterprise Manager - это оснастка Microsoft Management Console (MMC), что обусловливает не слишком стабильную работу данного приложения и имеющие место весьма неприятные сбои, а порой оно просто перестает отвечать.

Все упомянутые ограничения в Management Studio устранены. Отсюда можно администрировать не только экземпляры реляционного механизма, но также и Analysis Services. Management Studio использует новый прикладной интерфейс SQL Management Objects (SMO) API, что обеспечивает эффективное управление большим количеством объектов баз данных. И наконец, везде, где это возможно, используются немодальные диалоги, разработанные с использованием стабильного управляемого кода.

Registered servers. В Management Studio, в отличие от Enterprise Manager, окно, предназначенное для регистрации серверов и их организации, отделено от окна просмотра серверных объектов. Информацию о зарегистрированных серверах или группах серверов можно экспортировать в конфигурационный файл формата XML и далее импортировать на тот же самый или какой-либо другой сервер, просто щелкнув правой кнопкой на соответствующем зарегистрированном сервере или группе серверов и выбрав пункт Export.

Кроме того, в SQL Server 2005 также разнесены функции управления службами SQL Server и функции управления сервером. Например, из Registered Servers нельзя запускать или останавливать службы - для этих целей предназначен компонент SQL Server Configuration Manager. Также в Management Studio разделены процедуры регистрации сервера, подключения к серверу и просмотра объектов сервера. Разумеется, в ходе регистрации сервера через окно Registered Servers устанавливается кратковременное соединение с сервером в целях проверки, но данное соединение не поддерживается постоянно. Тем не менее, если щелкнуть правой кнопкой мыши на зарегистрированном сервере, а затем выбрать пункт Object Explorer или New Query, то информация о соединении, сохраненная вместе с информацией о зарегистрированном сервере, будет использоваться SQL Server в дальнейшем для установления соединений с данным сервером.

Object Explorer. Это основной инструмент для организации объектов сервера. Object Explorer предоставляет не только функции администрирования, но и возможности редактирования запросов. Если поглубже заглянуть внутрь процессора баз данных SQL Server 2005, то можно, например, увидеть, что результирующее дерево параметров имеет сходство с Enterprise Manager. Однако здесь еще можно регистрировать экземпляры Analysis Services и управлять ими, что не было предусмотрено в классическом Enterprise Manager.

Для каждого из узлов, отображаемых в дереве Object Explorer, могут вызываться различные административные диалоги. Например, если щелкнуть правой кнопкой мыши на имени сервера баз данных, будет вызвано немодальное диалоговое окно Server Properties с несколькими закладками, с помощью которого можно просматривать и изменять конфигурацию параметров администрирования для данного сервера.

Когда будет развернут узел Object Explorer на уровне SQL Server database engine, обратите внимание, что SQL Agent имеет собственный узел и все объекты кода T-SQL подпадают под категорию узла Programmability, который имеет в своем составе узел для размещения сборок (assembly). Если развернуть дерево на уровне Analysis Server, то здесь вы также увидите новый узел, предназначенный для размещения хранимых процедур, которые тоже являются сборками.

Диалоговые окна Object Explorer базируются на SMO, который пришел на смену SQL-DMO, реализованному в SQL Server 2000, и представляет собой усовершенствованную объектную модель, обладающую большей гибкостью. SMO работает в асинхронном режиме, что позволяет открывать узел сервера и работать со многими объектами или методами, не оказывая влияния на другие функции. Допустим, мы открываем базу данных, содержащую сотни или даже тысячи таблиц, и в то время, пока она открывается, можем открыть еще один узел и выполнить какую-либо другую работу. Также следует упомянуть, что SMO содержит все необходимые механизмы для работы со сценариями, что позволяет автоматизировать с помощью сценариев практически любые действия, выполняемые в Object Explorer.

Выполнение задач администрирования

В Object Explorer системные объекты отделены от объектов пользовательской категории. Например, системные базы данных (master, model, msdb и tempdb) относятся к категории System Databases, в то время как новые учебные базы данных, AdventureWorks и AdventureWorksDW, являются пользовательскими. Здесь следует иметь в виду, что учебные базы данных по умолчанию не устанавливаются, поэтому, если в ходе установки продукта не был выбран соответствующий параметр, по ее окончании у вас не будет ни одной доступной пользовательской базы.

Как и в случае установки свойств сервера, можно настраивать параметры базы данных, вызвав окно Database Properties. В Management Studio также поддерживаются средства проектирования таблиц и представлений (известные в SQL Server 2000 как Visual Data Tools), в том числе инструмент Database Diagrams. В те диалоговые окна, которые предназначены для работы с объектами баз данных, теперь встроен механизм поддержки сценариев, так что любые действия здесь могут быть автоматизированы с помощью сценариев (см. экран 5 ).

Компонент Object Explorer в Management Studio содержит узлы, предназначенные для управления параметрами безопасности. Также можно управлять унаследованными от SQL Server 2000 пакетами DTS, службами Notification Services, триггерами уровня базы данных, Database Mail и триггерами DDL уровня сервера.

Как можно было ожидать, в Management Studio встроены механизмы управления SQL Server Agent, но при этом сюда добавлен новый узел для управления SQL Server Agent Proxy (посредник SQL Server Agent). Через SQL Server Agent Proxy для каждого отдельного шага задания может определяться соответствующий контекст безопасности, это достигается путем предоставления посредников безопасности пользователю Windows компонентом SQL Agent. Обычно в заданиях T-SQL на уровне шагов посредники не используются, и тем не менее с их помощью в заданиях можно пошагово назначать посредников для элементов управления Active X, операций xp_cmdshell, репликаций, а также служб Analysis Services, SQL Server и SQL Server Integration Services.

Редактирование запросов

Помимо большого количества новых административных функций, в Management Studio появилась полнофункциональная среда для работы с запросами, существенно превосходящая по функциональности Query Analyzer из SQL Server 2000.

SQL Server 2000 Query Analyzer больше всего подходит для редактирования кода T-SQL и сценариев. И хотя в Query Analyzer имеется прекрасный редактор T-SQL, тем не менее ему свойствен ряд ограничений. В частности, с его помощью нельзя редактировать другие типы сценариев SQL Server, например такие, как запросы MDX. Если в Query Analyzer выполняется редактирование запроса или сценария, то при этом окно редактирования должно иметь соединение с SQL Server, причем подключиться к другому серверу в данном сеансе редактирования нельзя. Что касается графика плана исполнения, то возможности Query Analyzer прекрасно подходят для анализа хода выполнения запроса, однако графическое представление выходных данных не переносимо. Кроме того, хотя в Query Analyzer можно редактировать сценарии T-SQL, находящиеся на диске в виде файлов, в SQL Server 2000 отсутствует интерфейс прямого доступа к программам контроля версий. И последнее. Хотя с помощью инструмента Object Browser, имеющегося в Query Analyzer, можно выполнять в базе данных поиск объектов кода T-SQL, его нельзя использовать ни для каких задач, связанных с управлением базой данных.

Что касается возможностей редактирования в Query Analyzer, то практически все они сохранились и в Management Studio, но в новом продукте все описанные выше ограничения устранены. С помощью Management Studio можно редактировать все типы сценариев SQL Server, причем не только написанные на T-SQL, но и XML-сценарии. При редактировании запросов необязательно иметь постоянное соединение с сервером, теперь это можно делать и в автономном режиме либо в ходе сеанса переключиться на другой сервер, на котором данный запрос должен быть выполнен. Полученное в Management Studio графическое отображение плана исполнения на выходе может быть передано в другой сеанс Management Studio, соответственно можно работать с полученными результатами без необходимости подключения к исходному серверу. Имеющийся в Management Studio компонент Object Explorer содержит всю функциональность Object Explorer из Query Analyzer, но здесь также в полном объеме реализована поддержка функций администрирования.

Работа в автономном режиме. Для запуска нового запроса в Management Studio существует несколько способов. При выборе из меню File пункта New можно создавать запросы трех типов: запрос T-SQL, запрос Analysis Services MDX, DMS или XMLA либо запрос SQL Server Mobile. Кроме того, созданные запросы можно запускать из Object Explorer или Solution Explorer, о чем мы поговорим далее.

Процесс редактирования запросов в Management Studio является независимым от соединения. Запросы можно запускать, имея подключение к серверу, не имея его сначала и установив в нужный момент либо переключаться на другой сервер для выполнения этого же запроса. Это дает возможность наиболее естественным образом использовать предусмотренную в SQL Server 2000 практику редактирования запросов из сценариев и текстовых файлов. При переходе в режим редактирования появится несколько дополнительных пиктограмм редактирования запросов, предназначенных, в частности, для установки, разрыва и изменения соединения. При редактировании запросов в Management Studio можно задействовать функцию auto-hide для окна Object Browser, тогда освободится дополнительное пространство экрана для редактирования.

Решения и проекты. Management Studio имеет расширенные возможности редактирования, он позволяет объединять созданные сценарии и текстовые файлы в приложения (solution) и проекты (project). Проект представляет собой именованную коллекцию сценариев и текстовых файлов, которые обычно размещаются в стандартном каталоге Windows, имеющем то же имя, что и сам проект. Приложение - это набор из одного или более проектов, при этом все проекты, входящие в состав данного приложения, могут быть включены в каталог с именем, соответствующим имени приложения, но могут быть и приложения, содержащие только один проект и, соответственно, имеющие один вариант размещения. Для создания нового проекта в Management Studio нужно выбрать меню File, затем New, Project, при этом запускается диалоговое окно New Project.

В Management Studio по умолчанию приложению присваивается то же имя, что и проекту, а размещаются приложение и проект в каталоге My Documents. В тех случаях когда необходимо объединить большое количество сценариев в один крупный проект, который желательно разбить на несколько подчиненных проектов (sub-project), можно собрать несколько проектов в одно приложение с осмысленным именем и задать для него необходимое размещение. При этом сначала создается новый проект, а затем указывается приложение, в состав которого он должен быть включен. Имя, присваиваемое решению по умолчанию, может быть изменено. После того как были созданы проект и соответствующее решение, можно осуществлять навигацию по приложению и входящим в него проектам с помощью компонента Solution Explorer, как показано на экране 6 .

Предположим, требуется модифицировать несколько объектов, относящихся к схеме AdventureWorks HumanResources. При этом часть сценариев будет модифицировать таблицы, часть - представления, а какие-то из них будут изменять код хранимых процедур. Сценарии каждого из этих типов могут быть объединены в соответствующие проекты, а проекты затем объединяются в приложение. Один из способов реализации описанного подхода показан на экране 6: здесь приложение с именем soln_HR содержит несколько проектов, каждый из которых соответствует определенному типу вносимых в схему HR изменений.

Используя функциональность приложений и проектов, можно создавать наборы сценариев для SQL Server Analysis Services или для SQL Mobile, группируя их в собственные проекты. Наборы, определяемые как проекты, будут помещаться в соответствующие каталоги на диске и отображаться в Solution Explorer в виде отдельных узлов. Каждому проекту можно назначить имя и указать папку для размещения на диске. По умолчанию каталог проекта размещается внутри каталога приложения, и все файлы данного проекта будут храниться в этом каталоге.

Возможность организации файлов в проекты и приложения - это только одно из преимуществ новой технологии работы со сценариями в SQL Server. Кроме этого, Management Studio имеет тесную взаимосвязь с программами проверки исходного кода, если данная система контроля исходного кода является подключаемым модулем. В пакете Visual SourceSafe (VSS) имеется однозначное соответствие между проектами VSS и проектами Management Studio, поэтому после создания проекта и приложения в Management Studio приложение целиком можно загрузить в VSS для выполнения соответствующей проверки, а затем выполнять проверку на уровне отдельных проектов или файлов. Для доступа к функциям загрузки в систему контроля кода следует щелкнуть правой кнопкой на любом узле в Solution Explorer. Настроить параметры проверки кода можно в диалоговом окне Options, которое вызывается из меню Tools программы Management Studio. В этом же окне (Tools, Options) можно включить некоторые другие функции редактирования, такие как нумерация строк и динамическая справка.

Интерактивная работа со сценариями баз данных. Для редактирования в базе данных объектов T-SQL можно использовать Object Explorer. Когда вы попадаете внутрь программируемого объекта базы данных, такого как хранимая процедура, функция или триггер, вы можете вызвать редактор запросов T-SQL, для чего требуется выбрать New или Modify. Если выбрано New, тогда будет использоваться шаблон, параметры которого можно просматривать и изменять с помощью компонента Template Explorer. При выборе варианта Modify генерируется сценарий ALTER, соответствующий выбранному объекту. Функции New и Modify заменили компоненты Assisted Editor, существовавшие в ранних бета-версиях Management Studio для SQL Server 2005.

Усовершенствования для плана исполнения. Management Studio имеет два существенных расширения, связанных с возможностями просмотра планов. Здесь, как и в Query Analyzer, можно просматривать графический вариант плана исполнения, однако и сами пиктограммы, и цветовая гамма претерпели изменения. Кроме того, теперь большая часть пиктограмм показывает детали той операции, которую SQL Server выполняет в настоящий момент. Если нажать на значок с символом «+» в правом нижнем углу окна запроса на план исполнения, то можно перейти к увеличенному отображению запроса. А если выбрать какой-либо из узлов запроса на план исполнения, то в диалоговом окне Properties будет отображена дополнительная информация об этом действии.

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

В целях графического просмотра данные XML можно экспортировать в переносимый (portable) формат. В полученном наборе данных нужно щелкнуть правой кнопкой на ссылке XML и сохранить данные в виде файла с расширением.sqlplan, как показано на экране 7 . Если открыть сохраненный файл через пункт Open меню File, то Management Studio отобразит XML-документ в виде графического изображения плана исполнения, как показано на экране 8 .

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

Соберем все вместе

Имеющийся в SQL Server 2005 инструментарий существенно расширяет возможности как управления, так и разработки. Например, интерфейс SQL Server Management Studio является настраиваемым, поэтому в каждом случае можно для себя решить, какие функции являются самыми необходимыми, и, соответственно, скрыть те функции, которые не будут использоваться в работе. Разумеется, в процессе освоения новых инструментов работы можно столкнуться с неизбежными в ходе обучения проблемами, но, как сказал поэт, «не пропадет ваш скорбный труд».

Кэлен Дилани - Независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги Inside SQL Server 2000 (Microsoft Press). [email protected]

Рон Талмадж - Владелец и руководитель Prospice LLC, консалтинговой компании в области баз данных из Сиэтла. Руководитель группы Pacific Northwest SQL Server Users Group, имеет сертификаты MCSD и MCP в SQL Server, ведет рассылку PASSnews и является SQL Server MVP. Написал Microsoft SQL Server 7.0 Administrator?s Guide (издательство Prima).