Планирование физической организации базы данных

Планирование физической организации базы данных – важнейшая часть административной работы с базами данных, файлами и файловыми группами. Плохо физически организованная БД будет работать с меньшей производительностью.
В MS SQL Server 2005 предусмотрены средства для создания БД как с помощью кода на языке T-SQL, так и с помощью графического интерфейса пользователя. Однако на практике нередко приходится использовать код на T-SQL для оптимизации работы БД. Конечно, нет необходимости каждый раз разрабатывать код создания и модификации объектов БД «с нуля», но в большинстве крупных проектов этот код приходится проверять и редактировать, а это означает, что необходимо разбираться в том, как он работает.
Поэтому сначала рассмотрим правила создания и модификации объектов БД на языке T-SQL.

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

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

CREATE DATABASE имя_базы_данных
[ ON [ PRIMARY ]
( [ NAME=’логическое_имя_файла’, ]
FILENAME=’физическое_имя_файла’
[ , SIZE=размер ]
[ , MAXSIZE={максимальный_размер | UNLIMITED} ]
[ , FILEGROWTH=шаг_приращения_размера [Mb | Kb | %] ) ]
[ , FILEGROUP имя_файловой_группы ]
[ LOG ON
( [ NAME=’логическое_имя_файла’, ]
FILENAME=’физическое_имя_файла’
[ , SIZE=размер ]
[ , MAXSIZE={максимальный_размер | UNLIMITED} ]
[ , FILEGROWTH=шаг_приращения_размера [Mb | Kb | %] ) ]
[ … ]

ON – используется для определения местонахождения файла, в котором будут храниться данные.
PRIMARY – определяет файл как первичный или как член первичной файловой группы, если опущено, то основным файлом становится первый файл в операторе и для хранения используется первичная файловая группа;
NAME – определяет логическое имя файла. По умолчанию совпадает с физическим именем файла, определенном в параметре FILENAME;
FILENAME – определяет физическое имя действительного файла операционной системы, то есть указывает полный путь и имя этого файла;
SIZE – указывает размер файла: в мегабайтах, килобайтах. Должен быть целым числом. Размер основного файла по умолчанию равен размеру БД model. Указанный размер не должен быть меньше размера БД model.
MAXSIZE – указывает максимальный размер, до которого может увеличиваться файл. Если этот параметр не указан, то устанавливается значение UNLIMITED, позволяющее увеличивать файлам размер без ограничений.
FILEGROWTH – задает шаг увеличения файла, причем ноль означает запрет увеличения размера. Значение указывается в мегабайтах, килобайтах или процентах. По умолчанию приращение – 10%, если не указаны единицы, то цифра воспринимается в мегабайтах.
FILEGROUP – определяет имя группы файлов, в которую помещается файл.
LOG ON – определяет расположение и характеристики файлов, в которые будет записываться информация журнала транзакций. Параметры файла журнала аналогичны параметрам файла данных.

Для просмотра информации о базе данных и файлах используются следующие хранимые процедуры:
sp_helpdb [‘имя_базы_данных’] – информация о базе данных и ее настройках. Если база данных не указана, то отображается отчет по всем базам данных, поддерживаемых данным SQL-Server.
sp_helpfile [‘имя_файла’] – информация о файлах, относящихся к текущей базе данных. Если имя файла не указано, то отображается информация обо всех файлах этой базы данных.
sp_helpfilegroup [‘имя_файловой_группы’] – информация обо всех файловых группах в текущей базе данных. Если указано имя файловой группы, то выводится информация по каждому файлу указанной группы.
sp_spaceused [‘имя_объекта’] – сведения о дисковом пространстве, используемом указанным объектом.

Задание 1. Создайте собственную базу данных, размер основного файла которой 5 Мб, размер журнала – 2 Мб. Основной файл может увеличиваться до 10 Мб c шагом 20%. Объем файла журнала увеличивается до 5 Мб с шагом 1 Мб. Расположение файлов – в Вашей папке.

Задание 2. Просмотрите информацию о
 созданной базе данных и ее настройках,
 всех файлах созданной БД,
 файловых группах созданной БД,
 дисковом пространстве, используемом всеми объектами созданной БД.

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

DROP DATABASE имя_базы_данных

В результате удаляются все файлы, используемые базой данных.

Переименование базы данных:

sp_renamedb [@dbname=] ‘старое_имя’, [@newname=] ‘новое_имя’

Задание 3. Переименуйте созданную Вами базу данных.

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

ALTER DATABASE база_данных
{ ADD FILE <описание_файла> [ TO FILEGROUP имя_файловой_группы ]
| ADD LOG FILE <описание_файла>
| REMOVE FILE логическое_имя_файла
| ADD FILEGROUP имя_файловой_группы
| REMOVE FILEGROUP имя_файловой_группы
| MODIFY FILE <описание_файла>
| MODIFY NAME новое_имя_базы_данных
| MODIFY FILEGROUP имя_файловой_группы свойтво_файловой_группы }
Здесь <описание_файла> означает следующую конструкцию:

( NAME=’логическое_имя_файла’,
[ FILENAME=’физическое_имя_файла’ ]
[ , SIZE=размер ]
[ , MAXSIZE={максимальный_размер | UNLIMITED} ]
[ , FILEGROWTH=шаг_приращения_размера [Mb | Kb | %] ] )

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

Задание 4
1. Увеличьте размер основного файла Вашей базы данных до 10 МВ, а максимальный размер до 15 МВ.
2. Добавьте в вашу базу данных новую файловую группу.
3. Для Вашей базы данных определите еще один файл размером 2 Мб и поместите его в созданную файловую группу.
4. Просмотрите сведения о всех файлах вашей базы данных.

Сжатие базы данных
Это процесс уменьшения размеров файлов базы данных за счет удаления неиспользуемых частей файла.
Если установлено автоматическое сжатие данных, то сжатие выполняется постоянно с определенными интервалами. При операциях автоматического сжатия нельзя определить, какую часть базы данных необходимо сжать. SQL-Server пытается освободить значительную часть базы данных самостоятельно. Эти операции выполняются в период наименьшей активности пользователей.
Автоматическое сжатие можно установить через свойства базы данных. Окно свойств БД можно вызвать, например, через контекстное меню в Обозревателе объектов в Management Studio. В окне свойств БД в разделе Параметры надо установить значение «True» для свойства Автоматическое сжатие (см. рис. 1)

Задание 5.
1. Просмотрите сведения о дисковом пространстве, занимаемом БД AdventureWorksDW, запомните объем дискового пространства.
2. Для базы данных AdventureWorksDW установите возможность автоматического сжатия данных.
3. Снова просмотрите сведения о дисковом пространстве, занимаемом БД AdventureWorksDW – сравните с предыдущим просмотром.

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

DBCC SHRINKDATABASE ( ‘имя_БД’, [‘процент’] [ , { NOTRUNCATE | TRUNCATEONLY ] } )

имя_БД – имя базы данных, которую необходимо сжать.
процент – количество процентов свободного пространства, которое желательно оставить после сжатия.
NOTRUNCATE – сводное пространство не возвращается операционной системе, а резервируется в файлах, т.е. физически уменьшения размера базы данных не происходит.
TRUNCATEONLY – свободное пространство удаляется за последним используемым в файле экстентом, при этом данные не перемещаются, а параметр процент игнорируется.

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

Задание 6
1. Определите сведения о дисковом пространстве, занимаемом вашей базой данных.
2. Выполните сжатие своей базы данных.
3. Проверьте получившийся размер своей базы данных.

Сжатие базы данных можно осуществить также и путем сжатия каждого файла с помощью следующей команды:

DBCC SHRINKFILE ( ‘имя_файла’, [‘конечный_размер’]
[ ,{ EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] )

имя_файла – логическое имя файла, который необходимо сжать.
конечный_размер – желательный размер (целое число в мегабайтах), который должен иметь файл после выполнения сжатия. Если этот параметр не указан или меньше минимально допустимого размера, то файл сжимается до максимально возможного размера.
EMPTYFILE – выполняется перенос данных из файла в другие файлы файловой группы.
NOTRUNCATE – освободившееся место не возвращается операционной системе, т.е. размер файла не уменьшается на самом деле. При этом данные располагаются более компактно и смещаются к началу файла.
TRUNCATEONLY – происходит обрезание файла, начиная с последней используемой страницы. Никакого перемещения данных не происходит.

Резервное копирование данных
Необходимо уделять особое внимание сохранности информации, с которой работает пользователь. SQL-Server предлагает следующие типы резервного копирования информации:
• полное копирование (Full), которая является отправной точкой при восстановлении базы данных после сбоя, однако в зависимости от объема данных этот процесс может занимать много времени, поэтому не рекомендуется выполнять его слишком часто. Полная копия содержит все данные, содержащиеся в базе данных на момент окончания резервирования;
• дифференциальное копирование (Differential) содержит изменения данных, произошедшие с момента последнего создания полной копии базы данных. При этом сохраняются только страницы подвергшиеся изменениям. Таким образом, для восстановления базы данных достаточно самой последней дифференциальной копии;
• копирование журнала транзакций (Transaction Log) необходима для фиксирования всех изменений данных, произошедших в системе с момента последнего резервного копирования. Сама копия журнала содержит сведения о транзакциях и лишь только вместе с копией базы данных позволяет вернуться к состоянию, предшествующему сбою.
Для выполнения резервного копирования необходимо выбрать носитель, т.е. определить устройство, которое будет использоваться для создания копий.

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

sp_addumpdevice ‘тип_устройства’, ‘логическое_имя’, ‘физическое_имя’

тип_устройства – тип устройства резервного копирования. Допустимые значения: TAPE (магнитная лента), DISK (магнитный диск).
логическое_имя, физическое_имя – логическое и физическое имя устройства резервного копирования соответственно.

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

BACKUP {LOG | DATABASE } имя_БД
[ FILE = ‘логическое_имя_файла’, … ]
[ FILEGROUP = ‘имя_файловой_группы’ ]
TO логическое_имя_устройства
[ WITH
[ DESCRIPTION = ‘комментарий’ ]
[ DIFFERENTIAL ]
[ EXPIREDATE = ‘дата’ ]
[ INIT | NOINIT ] … ]

DIFFERENTIAL – создается дифференциальная копия базы данных;
EXPIREDATE – определяется дата, после которой резервная копия считается устаревшей и может быть перезаписана.
INIT | NOINIT – система осуществляет или нет инициализацию устройства.

Задание 7
1. Создайте физическое имя устройства резервного копирования с физическим именем C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\db_backup.bak (вместо d лучше взять имя своей БД).
2. Создайте резервную копию своей базы данных на созданном устройстве.
3. Выполните резервное копирование журнала транзакций своей базы данных на созданное устройство.

Операцию резервного копирования БД можно совершить также в Обозревателе объектов в Management Studio. Здесь нужно вызвать контекстное меню на имени нужной БД, в котором выбрать пункт Задачи / Создать резервную копию… Откроется диалоговое окно Резервное копирование базы данных, которое позволяет задать почти все необходимые параметры резервного копирования. В разделе Общие этого окна задаются:
• имя БД;
• модель восстановления, о них – будет далее;
• тип резервной копии;
• компонент резервного копирования, т.е. вся БД или файлы и файловые группы;
• имя создаваемой резервной копии (параметр Имя в пункте Резервный набор данных);
• длительность хранения резервной копии (Срок действия резервного набора данных истекает) - количество дней или дата;
• место размещения резервной копии (область Назначение).
В разделе Параметры этого же окна задаются другие опции, среди которых имеется область Журнал транзакций для задания режимов ведения журнала транзакций.

Задание 8. Создайте еще одну резервную копию своей базы данных с помощью Обозревателя объектов в Management Studio, поместив ее в свою папку.

Восстановление базы данных
При восстановлении базы данных из резервной копии существующая база данных будет перезаписана.
В SQL-Server предусмотрены следующие модели восстановления:
• полное восстановление (Full). При его применении в журнал транзакций записывается информация обо всех операциях. Происходит восстановление БД из резервной копии с применением к ней всех операций, сохраненных в журнале. При использовании этой модели не происходит потери данных. То есть, когда важна сохранность информации, применяют именно эту модель;
• восстановление без массовых операций (Bulk-Logged). Эта модель может рассматриваться как упрощенный вариант полного восстановления. Запись в журнал информации об обычных транзакциях происходит так же, как в модели полного восстановления, а информация об операциях массового копирования (таких, как массовый импорт данных или создание индекса) записывается не в полном объеме. То есть восстановленная из резервной копии БД будет содержать информацию обо всех изменениях, не относящихся к массовым операциям, поэтому необходимо выполнить все массовые информации повторно;
• простое восстановление (Simple). В этой модели журнал транзакций применяется только для поддержки транзакций в ходе их осуществления. Регулярно производится очистка журнала, в ходе которой удаляется информация обо всех завершенных транзакциях. Такой журнал гораздо меньше и обеспечивает более высокую производительность, но он практически не пригоден для восстановления БД.

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

RESTORE { LOG | DATABASE } имя_БД
‘файл_или_файловая_ группа’
[ FROM логическое_имя_устройства ]
[ WITH
[ DBO_ONLY ]
[ MOVE ‘логическое_имя_файла’ TO ‘физическое_имя’ ] … ]

DBO_ONLY – разрешается доступ к восстановленной базе только владельцам;
MOVE – указывает какое физическое имя будет соответствовать восстанавливаемому файлу. По умолчанию файл восстанавливается с тем же физическим именем, которое было определено при резервном копировании.

Задание 9
1. Удалите свою базу данных.
2. Восстановите удаленную базу с устройства с созданного Вами физического устройства.
3. Проверьте результат.

Операцию восстановления БД можно совершить также в Обозревателе объектов в Management Studio. В нем нужно вызвать контекстное меню на имени восстанавливаемой БД, в котором выбрать пункт Задачи / Восстановить / База данных… Откроется диалоговое окно Восстановление базы данных. Если восстановление проводится в прежнее место, то достаточно согласиться со всеми параметрами и нажать ОК. Если надо восстановить БД в новое место (в другую БД), то нужно его указать в этом окне. Также особые параметры восстановления можно указать в разделе Параметры этого окна.

Задание 10
1. Удалите свою базу данных.
2. Восстановите удаленную базу через Обозреватель объектов в Management Studio.
3. Проверьте результат.

Шикарный Горнолыжный склон Новопеределкино - последний шанс прокатиться с ветерком на лыжах до осени!
Здесь можно купить игры: ролевые и стратегические, оффлайновые и онлайновые!