Советы тем, кто собирается сдавать сертификационные экзамены по SQL Server

В данном выпуске рубрики рассмотрен раздел 70 - 28 экзамена по администрированию SQL Server 7.0, посвященный созданию баз данных и управлению ими. Как известно, в SQL Server 7.0 внедрен совершенно новый подход к хранению данных, что поднимает SQL Server на более высокий уровень. Больше не нужно до начала построения базы данных определять все устройства. Теперь можно просто перескочить через это рутинное занятие и приступить непосредственно к процедуре создания базы. Правда, придется иметь в виду новые возможности. Рассмотрим эти нововведения и посмотрим, как их можно применить для построения баз данных. В SQL Server желанной цели всегда можно достичь двояко: как с помощью графических средств, так и средствами команд Т-SQL (Transact SQL). В данной статье использованы операторы Т-SQL.

Прежде чем начать

В SQL Server 7.0, как и в версии SQL Server 6.5, размер базы данных определяется в мегабайтах. Но теперь не надо резервировать место в устройствах для размещения базы данных, в этой версии SQL Server она представляет собой один или несколько файлов, которые можно увидеть на жестком диске. Журнал транзакций тоже размещается в одном или нескольких файлах, причем желательно их разместить отдельно от базы данных. Нельзя далее размещать базу данных и журнал на одном устройстве, потому что они будут конкурировать между собой за место. По мере ввода новых данных база будет автоматически увеличиваться в размерах. То же самое происходит и с журналом транзакций. Можно заставить SQL Server периодически делать проверки, цель которых - установить, можно ли несколько сжать базу данных после удаления части информации.

Создание базы данных

Создавать базу данных имеет право только системный администратор, исполняющий роль sysadmin, или некто, кого системный администратор наделил полномочиями создателя баз данных - dbcreator. В SQL Server 7.0 база данных создается в помощью сценария, подобного приведенному в листинге Создание базы данных (Create Database), который размещен вместе со всеми процедурами и практическими упражнениями к данной статье в сети WEB по адресу http://www.sqlmag.com. Листинг Создание базы данных (Create Database) создает базу данных бюджета, budget. Первая строка сценария объявляет, что вы собираетесь создать базу, которая станет составной частью базы данных Master, входящей в комплект поставки SQL Server. Технически перенесение фокуса на базу данных Master не является обязательным, поскольку это единственное место, где вы можете создавать определение базы данных. Однако явная спецификация местоположения напомнит вам о том, что необходимо будет сделать резервную копию базы данных Master после создания своей базы. Имя базы данных следует за оператором CREATE DATABASE. После этого необходимо задать логическое имя базы, то есть имя по которому SQL Server будет обращаться к базе, а также физическое имя файла для данных, с указанием пути и директории. По принятому соглашению для первичного файла используется расширение .mdf. В приведенном примере для создаваемой базы данных budget файл с данными budgetdata.mdf помещен в директорию C:\mssql7\data. SQL Server строит директорию с данными под основной установочной директорией. SQL Server использует директорию данных по умолчанию, хотя вы вольны размещать свои файлы с данными где угодно на локальном диске (но не на сетевом). Начальный размер файла 100Мб.
Автоматическое увеличение файла звучит заманчиво, особенно для тех, кто хоть раз вручную расширял устройства и базы данных в SQL Server 6.5, когда отведенного места оказывалось недостаточно. Однако надо помнить об опасности появления того оператора INSERT, который станет «последней каплей» и приведет к тому, что база данных займет весь диск. (К счастью, база данных не может расшириться на другой диск.) Если вы хотите установить верхний предел размера базы данных примените опцию MAXSIZE. Если не установить предельный размер базы данных, то она будет расти до тех пор, пока не займет весь диск. Для предотвращения автоматического роста базы данных не забудьте присвоить нулевое значение параметру роста файла FILEGROWTH. Вы можете выбрать, на сколько будет увеличиваться база данных со временем - на фиксированное количество мегабайтов, или же размер присоединяемой памяти будет вычисляться как определенный процент от ее объема в момент расширения. В любом случае следует избегать приращения небольшими порциями, это только отнимет время. Лучше каждый раз прибавлять достаточно большой, но в разумных пределах, объем памяти. По умолчанию, если только вы не изменили настройку, предусмотрено увеличение на 10 процентов. Приращение базы данных всегда производится кратными 64 Кб порциями, поскольку пространство памяти распределяется в экстентах, которые представляют собой страницы по 8 Кб. (Более подробно об этом написано в статье Кэйлен Дилани «Новые методы управления пространством памяти».) В том случае, когда необходимо предоставить базе данных возможность расширяться за пределы одного диска, можно либо разместить базу в нескольких файлах на нескольких дисках, либо использовать для ее хранения дисковый массив RAID.
Журнал транзакций располагается в отдельном файле с расширением .ldf. В рассматриваемом примере журнал создается в используемой по умолчанию директории C:\mssql7\data, но обычно он размещается на другом диске. Как и для файла с данными, необходимо сообщить системе логическое и физическое имена файла. Все, что было сказано относительно стратегии расширения и выбора размера приращения для файла данных, остается справедливым и для файла журнала. Если файл журнала становится слишком большим для одного физического диска, его можно разместить в нескольких файлах или группах файлов. В синтаксисе создания базы данных имеется несколько несообразностей. Например, имя базы данных является символьной величиной, но ее не надо указывать в кавычках. Логическое имя файла также является символьным полем; его можно, но не нужно брать в кавычки. А вот физическое имя файла обязано быть в обрамлении кавычек. Правда, если вам больше нравятся квадратные скобки, то вместо кавычек можно использовать их.

Изменение базы данных

Даже при автоматическом росте файла может потребоваться расширить базу данных на другой диск. Для такого распространения можно применить команду ALTER DATABASE:
ALTER DATABASE Budget
ADD FILE
( NAME = .....
.....
SIZE = .....
По существующему соглашению для вторичных файлов используется расширение .ndf.

Файлы и группы файлов

Чтобы распространить базу данных на несколько дисков, необходимы и первичные и вторичные файлы. База данных может иметь только один первичный файл. В SQL Server 7.0 введена концепция группы файлов. Группа файлов представляет собой административное объединение файлов базы данных, используемое для размещения таблиц и индексов и распределения их по конкретным дискам. Группа файлов может содержать один или более файлов. Хотя в рассматриваемом примере это не было обязательным, но при создании базы данных можно было применить ключевое слово первичный (PRIMARY):
CREATE DATABASE Budget
ON PRIMARY .....
Разбиение базы данных на группы файлов дает возможность управлять ее распределением по нескольким дискам, что может быть использовано для улучшения производительности. Можно достичь такого же выигрыша в производительности, как и при применении лентообразного заполнения наборов дисков (то есть, массивов RAID). Но если добавление файла в группу файлов не представляет труда, то этого нельзя сказать о расширении набора RAID. Первичная группа файлов всегда содержит первичный файл и системные таблицы. В эту группу файлов также обязательно будут помещаться файлы, для которых группа файлов не указана явным образом. Таким образом, если ваша база данных содержит один первичный и несколько вторичных файлов, но вы не объявили ни одной группы файлов, то все файлы войдут в одну первичную группу. Файлы журналов транзакций не входят в группу файлов.
Листинг Определение групп файлов (Define File Groups), размещенный по адресу http://www.sqlmag.com содержит сценарий создания групп файлов в соответствии с Books Online (BOL). В коде создания базы использовался текст . Различие в этих методах состоит в том, что для применения групп файлов ключевое слово FILEGROUP и название группы употребляются раньше, чем будут определены один или несколько файлов, составляющих саму группу. Предположим, у вас возникло желание разделить базу данных Budget на несколько групп файлов. Вы могли бы воспользоваться кодом Разделение базы данных (Split Database), помещенным по адресу http://www.sqlmag.com. Отметим, что только первичный файл имеет расширение .mdf. Первичная группа файлов не нуждается в ключевом слове FILEGROUP. В рассматриваемом примере база данных была разбита на группы Первичная (Primary); Наброски (Projections), в которой находятся наметки бюджета; а также Архивная (History), содержащая хронологические данные. Для большей живучести системы журнал помещен на другой диск. Вы получаете все преимущества от отделения данных от группы файлов с системными таблицами и от разнесения данных по разным дискам. К таким преимуществам относятся улучшенная производительность и способность резервировать и восстанавливать лишь избранные группы файлов, что существенно ускоряет процесс восстановления.
При выполнении восстановления вы получаете еще одно преимущество. Данные с наметками бюджета, которые меняются сравнительно часто, размещаются на одном диске, в то время как хронологические данные, остающиеся практически неизменными, помещены на другой диск. Это дает возможность достаточно часто делать резервные копии изменяемых файлов, а статичные группы файлов трогать редко. Поскольку исторические данные, вероятно, составляют существенную часть от общего объема хранимой информации, то время резервирования заметно сокращается. (Более подробно о резервировании и восстановлении баз данных написано в статье «Подход к восстановлению баз данных» (Ensuring Database Recovery) Вэйна Снайдера.)

Другие возможности

Можно установить еще две опции. Одна из них связана с обеспечением обратной совместимости. Для SQL Server 6.5 необходима опция FOR LOAD, поскольку в ходе восстановления сначала заново строится база данных, а затем восстанавливается резервная копия. FOR LOAD переводит базу данных в режим, при котором никто, кроме системного администратора или администратора базы данных, не сможет получить доступ к базе в течение всего процесса восстановления. SQL Server 7.0 воссоздает структуру базы и загружает в нее данные за одну операцию, поэтому режим доступности только для администратора не является обязательным. Однако и в этой версии можно сначала с применением FOR LOAD построить структуру базы данных в режиме с ограничением доступа, а затем отдельным шагом перезагрузить в нее данные.
В SQL Server 7.0 появилась новая возможность - FOR ATTACH. Она применяется в тех случаях, когда набор файлов базы данных уже имеется. Надо просто сообщить SQL Server о том, что они существуют, тогда он добавит эту базу в список доступных баз данных.

Перемещение базы данных

Еще одно новшество SQL Server 7.0 состоит в том, что появилась возможность отсоединить базу данных и перенести ее на другой сервер. Команда выглядит очень просто:
EXEC sp_detach_db ‘Budget'
Текущий сервер больше не показывает эту базу данных, но файлы все еще находятся на диске. Теперь можно переместить базу данных на другой сервер. Для того чтобы передать в SQL Server информацию о том, что собой представляют эти файлы и где они размещены, следует запустить процедуру sp_attach_db. Предположим, вы переместили базу данных Budget на другой сервер, скопировав данные в новую директорию на диске E, а журнал транзакций в новую директорию на диске F. Тогда можно запустить sp_attach_db следующим образом:
EXEC sp_attach_db ‘Budget',
@filenzme1 = .....
@filenzme2 = .....
Новый сервер помещает запись о базе данных в таблицу sysdatabases, и на этом процесс переноса базы данных Budget завершается.

Удаление базы данных

Для удаления баз данных по-прежнему применятся старый синтаксис - DROP DATABASE с указанием названий базы или баз данных, подлежащих удалению. Нельзя удалить используемую базу данных, в том числе опубликованную для репликации. Но в SQL Server 7.0 больше не надо беспокоиться о том, чтобы удалить устройства - их просто не существует, - и SQL Server сам стирает файлы с жесткого диска.
Даже если вы всегда создавали базы данных, пользуясь графическим интерфейсом, знание кода Т-SQL дает вам понимание того, что происходит «за кулисами». А написание кода для любой базы данных - неплохая идея. (В Enterprise Manager следует щелкнуть правой кнопкой мыши на базе данных, выбрать из появившегося меню пуенкты Все задачи (All tasks), а затем Создать сценарий на SQL (Generate SQL Scripts), и можно приступать к написанию и сохранению кода.) Помните, что всегда после создания или удаления баз данных необходимо сделать резервную копию базы данных Master.
Майкл Д. Рейли (mdreilly@sqlmag.com) является одном из основателей и вице-президентом компании Mount Vernon Data Systems

Практические вопросы по созданию баз данных в SQL Server 7.0

1. Требуется в каждую новую базу данных вводить определенный пользователем тип данных. Простейшим способом сделать это является:

    a. Использовать код SQL, который следует запускать после создания каждой базы данных.
    b. Добавить этот тип данных в системную базу данных Model.
    c. Добавить этот тип данных в системную базу данных Master.
    d. Добавить этот тип данных в определенную пользователем базу данных и использовать ее в качестве шаблона при каждом построении новой базы данных.

2. Для восстановления базы данных в SQL Server 7.0 необходимо:

    a. Перестроить файл или группу файлов Primary в первоначальное состояние, а затем восстановить базу данных.
    b. Перестроить все файлы и группы файлов в их первоначальное состояние, а затем восстановить базу данных.
    c. Перестроить все устройства и базы данных в первоначальный порядок, а затем восстановить базу данных.
    d. Восстановить базу данных без перестройки файлов.

3. Вы хотите разместить таблицу и ее индексы на разных физических дисках. Это можно сделать путем (отметить все правильные ответы):

    a. Создания файла Primary для всех таблиц и файла Secondary для индексов.
    b. Создания таблиц в группе файлов Primary и создания группы файлов Secondary для индексов.
    c. Размещения индексов в том же файле, где находится журнал транзакций, поскольку журнал хранится на отдельном от таблиц физическом диске.
    d. Создания группы файлов Secondary для этой таблицы и другой группы файлов Secondary для индексов.

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

    a. Хранить файлы с образами в дисковом массиве, а указатели их местоположения - в базе данных.
    b. Включить этот дисковый массив в группу файлов Primary и разрешить базе данных расширяться на него. При этом образы будут храниться в группе файлов Primary.
    c. Сделать массив дисков группой файлов Secondary и предназначить его для хранения текста и образов с помощью оборота TEXTIMAGE_ON < группа файлов > оператора CREATE TABLE.
    d. Сделать массив дисков группой файлов Secondary и разместить данные образов в отдельной таблице этой группы файлов