Изменения в настройках базы данных происходят не часто, но они происходят. В этой главе мы научимся вносить изменения в базе данных с помощью SQL. Для изменения используется оператор ALTER DATABASE. В общем виде этот оператор можно увидеть в листинге 1.8.
Листинг 1.8. Общий вид команды ALTER DATABASE
ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}
В первой строке параметр database – это имя, базы данных, которую надо изменить. Далее построчно идут параметры или свойства базы данных, которые можно, а иногда и нужно изменить. Давайте рассматривать возможности команды с одновременным рассмотрением примеров, потому что возможностей у ALTER DATABASE столько же, сколько и у команды создания базы данных CREATE DATABASE.
Когда данные увеличиваются или увеличивается активность изменения данных, вам может понадобиться увеличить размер файлов данных или журнала. Для увеличения размер файлов можно использовать программу управления базой данных SQL Server Enterprise Manager, которая обладает визуальным интерфейсом или воспользоваться оператором ALTER DATABASE.
Вы можете контролировать размер базы данных с помощью:
Вы можете установить автоматическое приращение с помощью оператора ALTER DATABASE. Использование автоматического приращения уменьшает администраторские задачи, заключенные в ручном увеличении размера базы данных, но сложнее контролировать количество используемого сервером MS SQL Server пространства.
Увеличение лучше всего контролировать самостоятельно. Да, это отнимет лишнее рабочее время и потребует некоторых усилий, но контроль никогда не бывает лишним. Спокойно спит только тот администратор, у которого все находиться под контролем.
Если вы не сконфигурировали существующие файлы на автоматическое приращение, вы все еще можете увеличить размер. Если указан ноль для значения автоматического увеличения, значит, автоматического увеличения нет.
Вы можете создать вторичные файлы базы данных, для расширения размера базы данных. Но лучше использовать дополнительные файлы базы данных, чтобы расположить файлы на отдельном физическом диске или выделить объект базы данных из общей массы.
Следующий пример увеличивает текущий размер журнала базы данных NewDB:
ALTER DATABASE NewDB MODIFY FILE ( NAME='NewDB', SIZE = 100mb )
Теперь попробуем добавить дополнительный файл. Да, и это возможно. Допустим, что свободное пространство на жестком диске, но у вас есть дисковый массив, который позволяет управлять массивом на горячую. Прямо во время работы вставляем новый диск, создаем на нем файл для базы данных и продолжаем спать спокойно. Следующий пример показывает, как можно добавить файл к базе данных Sample.
ALTER DATABASE Sample1 ADD FILE ( NAME='SampleData2', FILENAME='c:\Temp\SampleData2.mdf', SIZE = 15mb, )
Вот тут необходимо сделать одно замечание, если для переноса данных с одного сервера на другой захотите воспользоваться функциями отключения и подключения базы данных, то не забудьте скопировать в новое место все файлы.
Допустим, что вам необходимо произвести массовую загрузку данных. База данных на данный момент занимает 10 мегабайт, а приращение, указанное в настройках в случае нехватки места составляет 1 мегабайт. Если вы будете загружать еще 10МБ данных, то во время загрузки произойдет 10 увеличений по одному мегабайту файла данных. Это отнимет лишнее время, поэтому вполне логичным будет увеличить размер файла вручную.
Для ручного увеличения размера файла до 200Мб необходимо выполнить следующую команду:
ALTER DATABASE testdatabase MODIFY FILE ( NAME = testdatabase, SIZE = 200MB )
Вторая строка содержит команду MODIFY FILE, что означает изменения файла. В скобках необходимо указать имя файла, размер которого надо изменить в параметре NAME и новый размер. Новый размер обязательно должен быть больше текущего, потому что команда позволяет только увеличивать файл, но не уменьшать.
Если вы хотите узнать текущий размер файлов или не помните имена, выполните команду:
EXEC sp_helpdb имя
где имя - имя базы данных. Например, следующая команда отображает информацию о базе данных FlenovSQLBook, в которую входит информация и о файлах:
EXEC sp_helpdb FlenovSQLBook
Состояния базы данных можно увидеть выполнив команду: EXEC sp_helpdb TestDatabase.
Помните, что таким образом вы можете только увеличивать размер файлов, но не уменьшать. Уменьшение файлов данных и журнала – это отдельная тема. Итак, если вы создали базу данных с файлом в 1 Мб, и потом увеличили ее размер до 5 мегабайт то вернуть изначальный размер командой ALTER DATABASE невозможно. Это значит, что следующий запрос вернет ошибку, если размер файла FlenovSQLBook больше 2Мб:
ALTER DATABASE FlenovSQLBook MODIFY FILE ( NAME = FlenovSQLBook, SIZE = 1MB )
С помощью команды ALTER DATABASE можно изменять и размер файла журнала, только в параметре NAME нужно указать имя файла журнала, который нужно увеличить:
ALTER DATABASE TestDatabase MODIFY FILE ( NAME = testdatabase_log, SIZE = 2MB )
Для оптимальной производительности сервера и уменьшения количества увеличения файлов (это отнимает лишнее время и может затормозить работу сервера):
Когда база данных увеличивается или когда увеличивается активность изменений, вам может понадобиться расширить журнал транзакций. Внимательно следите, чтобы журнал транзакций не оказался слишком маленьким. Регулярное наблюдение за журналом поможет вам определить оптимальное увеличение журнала и не допустить его переполнения. Если ваш журнал транзакций не может быть увеличен, то SQL Server не сможет записывать транзакции и не позволить делать изменения в базе данных.
Вы можете следить за журналом транзакций с помощью SQL Server Enterprise Manager, оператора DBCC SQLPERF (LOGSPACE) (подробней о команде в разделе 4.2.5) или системный монитор Windows.
Если данные изменяются достаточно часто, то необходимо выделить максимально возможное (но разумное) пространство и следить, чтобы всегда было достаточно свободного места в журнале даже для выполнения самой массовой операции обновления данных. Если база используется редко, и изменения происходят время от незначительно, то журнал можно выделить небольшим. Помните, что журнал транзакций пополняется только во время добавления, изменения и удаления данных. Во время выборки журнал не используется.
Некоторые ситуации, которые увеличивают активность журнала транзакций:
Когда вы используете автоматическое увеличение, и база данных состоит из нескольких файлов, SQL Server использует стратегию пропорционального заполнения между всеми фалами каждой файловой группы. По мере записи данных в файловую группу, сервер записывает результат пропорционально свободному пространству в каждом файле файловой группы и после этого записывает в следующий файл. Тут есть и положительные и отрицательные моменты. Положительное видно сразу, ведь если диски подключены к разным контроллерам, то мы добиваемся параллельности, а жесткие диски являются самым слабым звеном. Но отрицательный момент может возникнуть при автоматическом увеличении файлов. Чаще всего размеры дисков разные, да и свободное пространство на них распределяется не равномерно (если на один диск установить пару игр, то свободного места будет меньше, чем на другом). Теперь, один диск будет заполнен раньше, чем другой. Нет, база данных в этом случае останется доступной, и запись будет только на один диск, но произойдет резкое падение производительности во время записи.
Допустим, что на вашем основном диске, где расположена база данных, заканчивается место на диске. В этом случае, если есть другие логические разделы или жесткие диски, можно добавить к серверу новый файл и расположить его на этом диске. Добавление чем-то похоже на определение файла при создании базы данных:
ALTER DATABASE TestDatabase ADD FILE ( NAME = dbFile2, FILENAME = 'c:\Data\dbfile2.ndf', SIZE = 5MB, FILEGROWTH = 5MB )
В данном примере изменяется уже существующая база данных, на которой мы тестировали не один пример - TestDatabase. Затем указывается ключевое слово ADD FILE, указывающее на необходимость добавить новый файл. После этого ключевого слова, в круглых скобках описываются параметры файла точно так же, как при описании файла во время создания базы данных.
В качестве расширения для файла выбрано .ndf. Такое расширение рекомендуется для всех вторичных файлов, но может быть и другим. Напоминаю, что для основного файла рекомендуется указывать расширение .mdf.
Для удаления файла из базы данных совместно с ALTER DATABASE используется ключевое слово REMOVE FILE. Если вы создали файл и поместили в него таблицу, то файл уже нельзя удалить, пока он не станет пустым, то есть, пока из файла не будут удалены все таблицы.
Нам в данной книге лишний файл не нужен, поэтому давайте удалим то, что создали. Для этого нужно указать оператор REMOVE FILE и имя удаляемого файла:
ALTER DATABASE TestDatabase REMOVE FILE dbFile2
Для добавления файловой группы используется оператор ADD FILEGROUP. Например, следующий сценарий добавляет группу с именем fgNewGroup:
ALTER DATABASE TestDatabase ADD FILEGROUP fgNewGroup
Теперь посмотрим, как можно добавить файл в эту группу:
ALTER DATABASE TestDatabase ADD FILE ( NAME = dbFile2, FILENAME = 'c:\Data\dbfile2.ndf', SIZE = 5MB, FILEGROWTH = 5MB ) TO FILEGROUP fgNewGroup
Принцип такой же, как и у добавления файла, но после скобок указывается TO FILEGROUP и имя группы, в которую добавляется файл.
Файловую группу нельзя удалять, если в ней есть файл. Сначала необходимо удалить этот файл, а потом уже удалять группу. Для удаления группы используется REMOVE FILEGROUP, например:
ALTER DATABASE TestDatabase REMOVE FILEGROUP fgNewGroup
Иногда бывает необходимость переименовать базу данных. В моей практике это очень редко приходилось делать, но все же. Переименовать можно с помощью оператора MODIFY NAME. Например, следующий сценарий изменяет имя базы данных TestDatabase на MyDatabase:
ALTER DATABASE TestDatabase MODIFY NAME = MyDatabase
При этом вы не должны быть подключены к этой базе данных, лучше всего, если подключение будет к базе данных master. Если к базе данных, которую необходимо переименовать будет подключен хоть один пользователь, то переименование не сможет быть выполнено.
Если вы попробовали выполнить этот сценарий, то верните ей старое имя TestDatabase, потому что в дальнейшем при тестировании сценариев мы будем ссылаться на него.
У базы данных существует множество свойств, которые мы не задавали во время создания, но которые можно изменить уже у существующей базы. К таким свойствам относятся уровень доступа, модель восстановления и т.д. Давайте рассмотрим, что и как можно изменять.
Для изменения свойства используется оператор SET. Команда будет выглядеть следующим образом:
ALTER DATABASE Имя_базы SET имя_свойства
После ALTER DATABASE указывается имя базы данных, свойства которой нужно изменить, а после оператора SET нужно указать имя свойства.
Давайте посмотрим имена свойств которые нужно подставить вместо параметра имя_свойства:
Это основные параметры, которые можно изменить. Более подробно о моделях восстановления можно узнать из файла Doc/BackupRestore.pdf на компакт диске.
Теперь давайте посмотрим на примеры использования этих свойств:
Следующий пример разрешает доступ только одному пользователю:
ALTER DATABASE TestDatabase SET SINGLE_USER
Доступ только только пользователям ролей db_owner, dbcreator или sysadmin:
ALTER DATABASE TestDatabase SET RESTRICTED_USER
Возвращаем нормальный многопользовательский режим:
ALTER DATABASE TestDatabase SET MULTI_USER
Вывести базу данных в off-line, т.е. доступ будет запрещен всем пользователям:
ALTER DATABASE TestDatabase SET OFFLINE
Возобновить доступ к базе данных:
ALTER DATABASE TestDatabase SET ONLINE
Перевести базу данных в режим только для чтения, любые изменения будут отклонены:
ALTER DATABASE TestDatabase SET READ_ONLY
Вернуть базе данных полный доступ на запись и чтение:
ALTER DATABASE TestDatabase SET READ_WRITE
По завершении транзакции (принятии или откате) все открытые курсоры будут закрываться:
ALTER DATABASE TestDatabase SET CURSOR_CLOSE_ON_COMMIT ON
Установить полную модель восстановления:
ALTER DATABASE TestDatabase SET RECOVERY FULL
Установить модель восстановления BULK_LOGGED:
ALTER DATABASE TestDatabase SET BULK_LOGGED
Установить простую модель восстановления:
ALTER DATABASE TestDatabase SET SIMPLE
И последнее, что нам предстоит узнать – это возможность изменения раскладки (кодировки) по умолчанию для базы данных. Для этого выполняется команда:
ALTER DATABASE Имя_базы COLLATE имя_кодировки
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.