Разработка и сопровождение баз данных в MS SQL Server 2000

Содержание

  

Введение 

      Система управления базами данных SQL Server 2000 предоставляет  пользователям широкие возможности по разработке и сопровождению баз данных. Для этого в составе системы имеется набор графических средств (Enterprise Manager, Query Analyzer), языковых средств (язык Transact-SQL), набор хранимых процедур [1].

      Основными задачами в процессе разработки и сопровождения баз данных в среде SQL Server 2000 являются создание, модификация и удаление баз данных, таблиц, а также объектов баз данных, таких как индексы, представления, запросы, хранимые процедуры и триггеры. В курсовом проекте рассматриваются физическая и логическая организация баз данных в SQL Server 2000, структурный язык запросов Transact-SQL, шаблоны команд и примеры запросов [2].

      В первой главе («Архитектура баз данных SQL Server 2000») рассматриваются физическая и логическая организация баз данных, сотав и назначение системных баз данных сервера.

      Во  второй главе («Создание и сопровождение баз данных средствами Enterprise Manager») рассматривается создание, сопровождение, удаление и перенос базы данных на другие сервера. В качестве инструментария используется графическая утилита Enterprise Manager.

      Третья  глава («Основы программирования на языке Transact-SQL») включает в себя основы структурированного языка запросов Transact-SQL, способы выполнения основных действий по созданию и сопровождению баз данных и входящих в них объектов средствами языка Transact-SQL. В данной главе приводятся шаблоны и примеры применения соответствующих команд. Более полный набор примеров по командам языка Transact-SQL.

      В приложениях представлены рисунки  и таблицы.

 

      1. Архитектура базы  данных MS SQL Server 2000 

      Структурой  хранения данных в SQL Server 2000 является база данных (database). Вся работа SQL Server 2000 сводится к управлению базами данных. Системные данные сервера, отвечающие за его функционирование, также хранятся в базах данных. Базу данных SQL Server 2000 можно рассматривать с двух сторон: физической и логической. При работе с любой базой данных SQL Server 2000 – пользовательской или системной – действуют одни и те же механизмы.

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

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

      1.1 Физическая архитектура  базы данных SQL Server 2000 

      Физическая  база данных SQL Server 2000 хранится в самостоятельном, уникальном для каждой БД наборе файлов. Журнал транзакций и сами данные обязательно хранятся отдельно. Это повышает отказоустойчивость базы данных в случае сбоев системы. 

      1.1.1 Файлы данных и группы файлов 

      Для хранения базы данных предназначен набор  файлов, персональный для любой базы данных. Каждый файл может принадлежать только одной базе данных. В SQL Server 2000 существует два типа файлов базы данных:

• файлы данных;
• файлы журнала транзакций.

      Файлы данных (data file) предназначены для хранения информации, находящейся в таблицах базы данных. Кроме того, в этих файлах также размещены процедуры, ограничения, триггеры, индексы и другая информация;

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

      Любая база данных должна содержать как  минимум один файл данных и один файл журнала транзакций, т.е. минимальное  количество файлов, составляющих базу данных, равно 2. При необходимости администратор может добавлять новые файлы данных или файлы журнала транзакций.

      Файлы данных бывают двух типов:

• Primary File (основной, или главный, файл);
• Secondary File (вторичный, или дополнительный, файл).

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

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

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

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

• Logical File Name – логическое имя файла, которое используется в командах Transact-SQL при ссылке на конкретный файл;
• OS File Name – имя файла в операционной системе, которое используется для обращения к файлу в операционной системе.

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

• Primary File Group – основная группа файлов, которая включает первичный файл и все файлы, не включенные в другие группы, база данных может иметь только одну основную группу файлов;
• User-defined File Group – пользовательская группа файлов, создаваемая командой CREATE DATEBASE или командой ALTER DATABASE, если в них используется параметр FILEGROUP, в базе данных можно создать несколько пользовательских групп файлов с произвольным набором файлов;
• Default File Group – группа файлов по умолчанию, в качестве которой назначается одна из групп файлов, созданных в базе данных. Только одна группа файлов может быть группой по умолчанию. Если не указано явно, группой по умолчанию становится основная группа. Если при создании объекта базы данных не указано явно, к какой группе файлов он будет принадлежать, то этот объект создается в группе файлов по умолчанию.

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

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

      1.1.2 Страничная организация файлов данных 

      Основная  единица хранения данных на уровне файла базы данных – это страница, которая участвует в операциях ввода-вывода как единое целое даже тогда, когда требуется всего одна строка. Каждая страница файла базы данных имеет объем 8192 байт. Страницы объединяются в экстенты. Экстент состоит из 8 страниц (64 Кбайт).

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

• тип страницы;
• объем свободного места на странице;
• идентификационный номер таблицы или индекса – владельца страниц.

 

      

      Имеется шесть типов страниц:

• Data - в страницах этого типа хранятся собственно данные, исключая данные типа text, ntext и image;
• Index - страницы этого типа используются для хранения информации об

индексированных таблицах;

• Text/Image - в страницах этого типа хранятся данные типа text, ntext и

image;

• Global Allocation Map - в страницах данного типа хранится информация об использовании экстентов (групп страниц);
• Page Free Space - в страницах этого типа хранится информация о свободном пространстве на страницах;
• Index Allocation Map - страницы этого типа хранят информацию об экстентах, используемых таблицами или индексами.

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

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

      На  нулевой странице располагается  заголовок страницы, в которых  хранятся атрибуты файла. Следующая страница содержит PFC, в которой хранится информация об использовании страниц экстента. Затем располагаются битовые поля GAM и дополнительной таблицы SGAM (Shared GAM). Остальные страницы заняты данными или являются свободными. Страницы IAM могут быть созданы в любом месте файла. Для многофайловой базы данных экстенты выделяются на всех файлах в равных пропорциях. Все это обеспечивает высокую скорость создания, поиска и удаления строк данных [8].

      Файл  журнала транзакций не имеет страниц  и экстентов. Он содержит только последовательность записей транзакций, выполняемых в базе данных.

1.2 Логическая архитектура базы данных SQL Server 2000 

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