ЛАБОРАТОРНАЯ РАБОТА № 1

Изучение общих принципов работы в MS SQL Server 7.0

Цель работы: получить простейшие навыки работы с SQL Server 7.0, изучить принципы администрирования SQL Server 7.0, а также получить практические навыки по созданию, обслуживанию и защите пользовательских баз данных.

1. 1. Теоретическая часть

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

1.1. Использование SQL Server

Все работы с SQL Server делятся на две категории. Проектирование и реализация БД обычно относятся к компетенции разработчика, тогда как администрирование и сопровождение составляют обязанности администратора.

Разработка БД включает следующие задачи:

P проектирование с учетом требований оптимального использования аппаратного обеспечения и возможности расширения в процессе эксплуатации; выявление и моделирование объектов БД и логики приложения; определение типа информации для каждого объекта и типа отношений между объектами;

P создание БД и ее объектов, включая таблицы, механизмы обеспечения целостности данных, объекты ввода и извлечения данных, индексы и механизмы защиты;

P оптимизация приложений БД для достижения максимальной производительности;

P планирование развертывания, включая анализ нагрузки и выработку рекомендаций по созданию оптимального набора индексов БД;

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

1.2. Администрирование БД SQL Server

Администрирование БД SQL Server включает:

P установку и настройку SQL Server и сетевой защиты;

P модернизацию SQL Server при появлении новых версий;

P создание БД, включая выделение дискового пространства для БД и журналов транзакций, и управление использованием дискового пространства;

P импорт/экспорт данных;

P создание плана резервного копирования и восстановления и его выполнение;

P разработку плана защиты пользователей и приложений баз данных;

P автоматизацию решения повторяющихся задач с помощью заданий и использование предупреждений для сбора информации об ошибках;

P мониторинг и  оптимизацию баз данных и сервера;

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

В состав SQL Server включен административный инструментарий, позволяющий снизить затраты на решение рутинных задач администрирования и автоматизировать их выполнение.

SQL Server Enterprise Manager предназначен для администрирования сервера и управления базами данных с компьютера-клиента.

SQL Server Query Analyzer – инструмент для анализа и выполнения запросов на языке Transact-SQL. Анализатор запросов позволяет оптимизировать запросы и собирать статистическую информацию, а также работать с несколькими запросами в нескольких окнах.

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

1.3. Типы баз данных

Базы данных SQL Server бывают системные и пользовательские. Принципиальной разницы между ними нет. Системные БД хранят информацию о сервере SQL Server в целом, применяемую для управления системой.

Системные БД:

База данных	Описание
master	Служит для управления пользовательскими БД; хранит информацию об учетных записях, параметрах конфигурации, размещении данных и системных сообщениях об ошибках.
model	Шаблон для создания новых пользовательских БД.
tempbd	Хранилище для временных таблиц и других временных объектов.
msdb	Обеспечивает поддержку службы SQL Server Agent и предоставляет хранилище для расписания и журнала заданий.
distribution	Хранит протоколы и транзакции, используемые при репликации.

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

Пользовательские БД создают пользователи. Один сервер может управлять несколькими такими БД.

1.4. Планирование емкости

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

Основные факторы, учитываемые при оценке объема пространства, необходимого БД, следующие:

P размер БД model и системных таблиц (включая их предполагаемый рост), обычно эта цифра составляет небольшой процент размера БД;

P объем данных в таблицах БД пользователя (включая их предполагаемый рост);

P число и размер индексов – в частности, размер ключа, число записей и коэффициент заполнения;

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

В средах оперативной обработки транзакций журналу транзакций отводится 25 % пространства БД. В БД, используемых преимущественно для выполнения запросов, журналу можно выделять меньше места.

1.5. Планирование разрешений и системы защиты

Доступ к SQL Server и базам данных осуществляется на основе учетных записей и ролей.

Разрешения предоставляются (или отзываются) пользователям и ролям.

В SQL Server три типа разрешений: на выполнение операторов, на доступ к объектам и неявные разрешения.

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

Для работы с данными и выполнения процедур нужны разрешения на доступ к объектам. Например, разрешения на доступ к таблицам и представлениям дают пользователю возможность обращаться к данным таблиц и представлений при помощи операторов SELECT, INSERT, UPDATE и DELETE.

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

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

1. 2. Порядок выполнения работы

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

Для создания БД используется средство Enterprise Manager.

1. 1. Заpегистpиpоваться с пpавами администpатоpа или ввести любую учетную запись, входящую в локальную гpуппу Administrators.
2. 2. Запустить Enterprise Manager. Для создания БД в левой панели раскрыть группу SQL Server, к которой относится сервер. Дважды щелкнуть на имени сервера. Затем щелкнуть правой кнопкой мыши по папке Databases и выбрать в контекстном меню команду New Database.
3. 3. Откроется диалоговое окно Database Properties с вкладкой General. Ввести имя БД в текстовом поле Name. Enterprise Manager автоматически создаст основной файл данных, используя введенное имя БД как префикс, и группу файлов с именем PRIMARY. Изменить группу для основного файла данных нельзя. Ввести имя файла (логическое имя), местоположение (физическое имя), размер и группу для каждого файла.

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

1. 4. Задать начальный размер файла в мегабайтах: выделить ячейку с текущим значением и ввести нужное значение.

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

1. 5. В группе File Properties в нижней части окна определяются параметры роста для каждого файла. Выбрав имя файла, выделить параметр Automatically Grow File (размер данного файла будет увеличиваться автоматически). Также следует указать фиксированное приращение размера в процентах или в мегабайтах, в зависимости от объема свободного пространства в файле. Здесь же можно задать максимальный размер файла, выбрав параметр Restrict Filegrowth, и указать предельное значение (в Мб) либо оставить размер файла неограниченным. После создания БД заданные параметры можно модифицировать. Для этого необходимо открыть в Enterprise Manager диалоговое окно Database Properties. Для удаления файла из списка следует выделить его имя и нажать клавишу Delete.
2. 6. Настроить параметры журнала транзакций. Выбрать вкладку Transaction Log в окне Database Properties. Для файлов журнала нельзя указать группу (поскольку они не могут быть отнесены ни к одной из них). Ввести имя файла (логическое имя), местоположение (физическое имя) и размер для каждого из файлов. Указать все параметры автоматического роста файлов журнала, выполнив последовательность действий, описанную в п.5.
3. 7. После настройки параметров всех файлов нажать кнопку OK. SQL Server создаст БД. Вернуться в окно Enterprise Manager и выделить папку Databases того сервера, где создана новая БД. В правой панели Enterprise Manager появится значок для созданной БД.

2.2. Просмотр сведений о базе данных

Просмотр информации о БД осуществляется средствами Enterprise Manager.

1. 1. Раскрыть (щелкнуть на значке «+») нужную группу SQL Server, затем – папку Databases.
2. 2. Выделить имя БД для просмотра сведений о ней. На правой панели находятся три заголовка: General, Tables&Indexes и Space Allocated. На странице General содержится общая информация о БД, дате ее архивирования и параметрах обслуживания. Для каждого заголовка предусмотрен еще ряд пунктов.
3. 3. Выделить заголовок Tables&Indexes для просмотра информации о таблицах и индексах. Здесь содержится информация о числе строк и размере каждой таблицы.
4. 4. Выделить заголовок Space Allocated для просмотра информации о свободном и заполненном пространстве каждого файла данных и файла транзакций.
5. 5. Для удаления БД выделить удаляемую базу и выбрать в контекстном меню команду Delete. Откроется окно сообщения Delete Database. Для подтверждения удаления нажать кнопку Yes.

2.3. Создание таблиц БД

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

1. 1. В Enterprise Manager открыть группу SQL Server,  затем – сервер.
2. 2. Открыть папку Databases и просмотреть доступные БД.
3. 3. Открыть нужную БД.
4. 4. Выделить папку Tables, выбрать в контекстном меню команду New Table. Откроется диалоговое окно Choose Name. Ввести имя таблицы и нажать кнопку OK.
5. 5. Откроется окно New Table, напоминающее электронную таблицу. Каждый ряд сетки соответствует полю таблицы, каждый столбец – одному из атрибутов поля.
6. 6. Определить все поля таблицы БД.

Записи в таблице физически хранятся в том порядке, который определен в полях. Для вставки нового поля следует выделить нижнее поле и в контекстном меню выбрать команду Insert Column. Для удаления поля следует в контекстном меню выбрать Delete Column. Для задания полю признака первичного ключа в контекстном меню выбрать команду Primary Key.

1. 7. Для задания других ограничений и индексов следует в контекстном меню выбрать команду Properties. Также можно воспользоваться значком Table And Index Properties на панели инструментов. Для обоих вариантов откроется диалоговое окно Properties.
2. 8. Для сохранения результатов работы выбрать значок Save на панели инструментов SQL Server Enterprise Manager.

2.4. Заполнение БД

Существуют разнообразные способы загрузки данных в SQL Server 7.0. Наиболее простой - непосредственное занесение информации в таблицы.

В Enterprise Manager открыть группу SQL Server, сервер, папку Databases, в которую необходимо занести информацию, выделить поле Tables. На правой панели Enterprise Manager появится список таблиц. Выбрать таблицу для загрузки информации, в контекстном меню выбрать команду Open Table и далее Return all Rows. Активизируя нужное поле, можно занести в него информацию.

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

Задание 1

Создать базу данных MyDB, состоящую из нескольких таблиц, содержащую один основной файл, три дополнительных файла и один файл журнала транзакций. Использовать варианты задания (без учета политики безопасности) из лабораторной работы №3 (с. 29).

2.5. Создание и использование триггеров

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

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

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

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

Для каждого триггера SQL Server создает две временные таблицы (deleted и inserted), на которые можно ссылаться в описании триггера. Эти таблицы хранятся в памяти и являются локальными по отношению к триггеру, то есть триггер имеет доступ только к своей собственной версии таблиц. Временные таблицы применяются для сравнения состояния таблицы «до» и «после» внесения изменений.

Создание триггера средствами Enterprise Manager

1. 1. В Enterprise Manager выделить имя таблицы, для которой должен быть создан триггер, выбрать в контекстном меню команду All Tasks, затем команду Manager Triggers. Откроется диалоговое окно Triggers Properties.
2. 2. В поле Text ввести команды T-SQL, описывающие триггер. Например, следующий триггер с именем Print_Update будет выдавать сообщение при обновлении таблицы:

CREATE TRIGGER Print_Update ON My Table

FOR UPDATE

AS

PRINT “My table was updated

GO

1. 3. Для проверки синтаксиса следует щелкнуть по кнопке Check Syntax. Исправить ошибки, если они есть. Для создания триггера щелкнуть по кнопке Apply. Созданный триггер будет добавлен в список Name.
2. 4. Окно Trigger Properties останется открытым, позволяя создать другие триггеры для заданной таблицы. После описания необходимых триггеров щелкнуть по кнопке Close.
3. 5. Для удаления триггера следует выбрать его из списка Name и нажать клавишу Delete.

Задание 2

В созданной БД выбрать таблицу для создания на ней триггера. Создать триггер по указанию преподавателя и проверить его выполнение.

Примечание. Для проверки работоспособности триггера рекомендуется использовать программу SQL Server Query Analyzer. Для этого после создания триггера запустите эту программу и выполните необходимые действия для его срабатывания операторов T-SQL.

2.6. Создание и использование представлений

Представление можно определить как некоторую виртуальную таблицу, содержащую результаты выполнения запроса (оператора SELECT) к одной или нескольким реальным таблицам.

Для конечного пользователя представление выглядит как обычная таблица в БД, для которой можно применить операторы SELECT, INSERT, UPDATE и DELETE.

Ограничения представлений

• Представление может ссылаться не более чем на 1024 поля в таблице. Если необходимо сослаться на большее число полей, следует использовать другой метод.
• Представление может быть создано только на основе таблицы в текущей БД.
• Разработчик представления должен обладать правами доступа ко всем полям представления.
• Любые обновления, модификации и прочие действия не могут нарушить целостность данных. Например, если в исходной таблице не допускаются значения NULL, то в представлении они также недопустимы.
• Представления могут основываться на других представлениях. Можно создать представление, которое будет извлекать информацию из других представлений, вплоть до 32-х уровней вложенности.
• В исходном запросе представления не допускается содержание разделов ORDER BY, COMPUTE, COMPUTE BY и ключевого слова INTO.
• Нельзя строить индексы на основе представлений. Тем не менее, исходный запрос представления способен реализовать доступ к индексам.

Создание представления средствами Enterprise Manager

1. 1. В Enterprise Manager открыть папку Databases и выбрать базу данных. Откроется окно БД.
2. 2. В левой панели выделить базу данных и в контекстном меню выбрать команду New, далеем – View. Откроется окно New View. Указать имя представления и выбрать его поля исходных таблиц.
3. 3. Ввести в панели оператор SQL :

SELECT <поле1> [, <поле2>,  <поле3> …]

FROM <таблица1> [, <таблица2> ,…].

Допустимо использование других разделов оператора SELECT. После ввода оператора щелкнуть на кнопке Verify SQL, чтобы проверить корректность запроса. Если запрос верен, щелкнуть на кнопке OK в следующем диалоговом окне, чтобы Enterprise Manager заполнил панель диаграммы и полей запроса окна New View.

1. 4. Для изменения или удаления представления в Enterprise Manager открыть папку Databases, затем БД и щелкнуть по значку Views. Далее выделить имя представления, которое надо изменить или удалить. В появившемся окне выбрать Design View или Delete.

Задание 3

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

<< Первая < Предыдущая 1 2 3 4 5 6 7 8 9 10 Следующая > Последняя >>