Открытие существующего пакета DTS и его корректировка

Пакет New_data.dts хранится вне SQL Server. Он предназначен для импорта данных о новых продуктах из текстового файла в таблицу newprods базы данных Nortwind.

1. Предварительно создайте таблицу newprods. Для этого выполните программу C:\MOC\2072a\labfiles\L09\newprods.sql. Запишите ее текст в отчет.

2. Откройте пакет DTS. Для этого вызовите команду контекстного меню Open Package на рубрике Data Transformation Services и выберите файл C:\MOC\2072a\labfiles\L09\New_data.dts (самую последнюю версию).

3. Поместите копию пакета, свойства его объектов и задания в отчет.

4. Выполните пакет. В результате вы должны увидеть, что 1343 строки были успешно импортированы в таблицу newprods.

5. Отредактируйте пакет таким образом, чтобы вначале удалялась существующая таблица newprods, на следующем шаге таблица Newprods создавалась с помощью файла сценария C:\MOC\2072a\labfiles\L09\newprods.sql, а затем заполнялась данными из текстового файла. Для этого выполните следующие действия:

5.1. В окне перетащите пиктограмму Execute SQL Task из раздела Tasks панели инструментов в рабочее пространство DTS Designer.

5.2. В окне диалога Execute SQL Task Properties введите информацию, представленную в табл. 2.

Таблица 2

5.3. Перетащите вторую пиктограмму Execute SQL Task из раздела Tasks панели инструментов в рабочее пространство DTS Designer.

5.4. В окне диалога Execute SQL Task Properties введите информацию, представленную в табл. 3.

Таблица 3

5.5. Задайте правильный порядок выполнения шагов задания в пакете DTS. Для этого в рабочем пространстве DTS Designer выделите задачу, которая удаляет таблицу Newprods, затем при нажатой клавише CTRL щелкните по пиктограмме задания, которое создает таблицу Newprods. Обе пиктограммы будут выделены.

5.6. В меню Workflow выберите позицию On Completion. Дважды щелкните по синей стрелке, чтобы просмотреть свойства последовательности выполняемых действий. Отметьте в отчете. Закройте окно диалога.

5.7. В рабочем пространстве выделите задачу, которая создает таблицу newprods. Нажмите и удерживайте клавишу CTRL и щелкните по текстовому файлу New products. При этом обе пиктограммы останутся выделенными.

5.8. В меню Workflow выберите позицию On success. Дважды щелкните по зеленой стрелке, чтобы просмотреть свойства последовательности выполняемых действий.

5.9. Закройте окно диалога, сохраните пакет DTS, а затем выполните его.

Перенос объектов SQL Server в новую базу данных

Импортирование объектов из одной базы данных в другую

Ваша задача перенести часть объектов базы данных Northwind в новую базу данных. Для выполнения данной задачи необходимо сначала создать новую базу данных Nwind2.

Переключитесь в Enterprise Manager , выделите свой сервер, в меню TOOLS раскройте список мастеров Wizard, и в рубрике Data Transformation Service выберите DTS Import Wizard.

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

Таблица 4

Переключитесь в Query Analyzer. Выполните следующие команды для просмотра результата передачи в базу данных Nwind2.

Use Nwind2

Select * from information_schema.tables where table_type in (‘BASE TABLE’,’VIEW’)

Просмотрите содержание каталога сценариев

C:\MOC\2072a\Labfiles\L09\DB_xref. Откройте и просмотрите файлы типа .tab, используя Блокнот или Query Analyzer. Что за команды там находятся? В файле c каким расширением записано создание представлений в новой базе данных?

Экспорт файлов в текстовый файл

Ваша задача состоит в том, чтобы перекачать данные из таблицы Customers базы данных Northwind в текстовый файл. Для этого воспользуйтесь мастером DTS Export Wizard. Введите информацию в соответствующих окнах мастера из табл.5.

Таблица 5

Откройте программу Блокнот и просмотрите созданный текстовый файл.

Проверьте, что для пакета создано расписание

Для этого в Enterprise Manager раскройте список Jobs в SQL Server Agent и найдите в нем задание SouthAmericanCustomers и просмотрите его свойства. Команду шага задания вы просмотреть не сможете, т.к. она зашифрована.

Импорт данных с помощью задания массовой вставки Bulk Insert

Создайте самостоятельно в конструкторе пакетов DTS пакет для импорта данных из текстового файла C:\MOC\Labfile\L09\Newprods.txt в таблицу Products базы данных Northwindcopy. Предварительно просмотрите исходную таблицу, подсчитайте число записей в ней. Будьте внимательны, когда будете описывать разделители полей. Предусмотрите в пакете DTS шаг, который бы не позволил разрастаться таблице Products при повторном выполнении задания.

Отобразите в отчете копию пакета и свойства его соединений и заданий.

Перенос данных из таблицы Excel в таблицу базы данных Pubs

В конструкторе пакетов DTS создайте самостоятельно (используя материалы лекции) задание для переноса данных из таблицы reclama.xls с листа Заказы в таблицу Zakaz базы данных Pubs. Используйте VB скрипт для преобразования поля Месяц из поля в числовом формате в источнике данных в поле символьного формата так, чтобы 1-му месяцу соответствовало значение «Январь», второму – «Февраль» и т.д.

10. Лабораторная работа по теме «Репликация»

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

В этой лабораторной работе вам необходимо объединиться с партнером, в упражнении 2 один из компьютеров будет выступать в роли издателя и дистрибьютора, а другой в роли подписчика, а в упражнении 3 – наоборот, второй компьютер будет сконфигурирован как издатель и дистрибьютор, а первый - как подписчик.

Регистрация второго сервера

В Enterprise Manager раскройте SQL Server Group и, вызвав контекстное меню, выполните команду New SQL Server Registration. При регистрации сервера Вашего партнера не забудьте, что Вы можете это сделать только с помощью SQL Server аутентификации, указав Login sa и пароль sa.

Настройка репликации транзакций

Используя мастер, Вы должны создать публикацию на основе таблицы Products базы данных Northwind. Данная таблица будет реплицирована в таблицу ReplProducts на сервере-подписчике. Вы должны обновить данные на сервере-издателе и проверить, что они обновились в репликации.

Создание публикации (на сервере издателе и дистрибьюторе)

1. В Enterprise Manager выберите локальный сервер в качестве издателя и дистрибьютора, щелкните по его имени и вызовите мастер для создания публикации (Tools|Wizards|Replication|Create Publication Wizard).

2. В окне диалога Create and Manage Publication выберите Northwind, затем щелкните по кнопке Create Publication.

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

Таблица 1

После завершения установки свойств публикации подождите завершения процесса создания дистрибьютора, издателя, публикации и ее статей.

4. Закройте окно диалога по созданию публикаций.

5. В дереве консоли раскройте окно Replication Monitor.

6. Задайте режим автоматического обновления Replication Monitor.

7. Раскройте папку Publishers, в ней – свой сервер, после чего щелкните по созданной Вами публикации. В окне детализации просмотрите список агентов репликации, отметьте в отчете их свойства и хронологию.

Ответьте на следующие вопросы:

Когда намечен запуск Log Reader Agent?

Когда намечен запуск Snapshort Agent?

Создание подписки (на сервере-подписчике)

На втором сервере создайте подписку типа Pull на публикацию, созданную на сервере-издателе.

1. В Enterprise Manager раскройте свой сервер на втором компьютере, щелкните по его имени, и запустите мастер по созданию подписки типа Pull (Tools|Wizards|Replication|Create a Pull Sibscription Wizard).

2. Используйте в окнах мастера информацию табл.2

Таблица 2

После нажатия на кнопку Finish подождите выполнение мастером всех необходимых шагов для создания подписки, ее инициализации и запуска агента Distribution Agent.

3. Раскройте ветвь Management, в ней SQL Server Agent, после чего раскройте рубрику Jobs. Просмотрите задание из категории Repl-Distribution и обратите внимание на его расписание. Ресурсы какого сервера будут использованы в этом задании. Изменятся ли эти ресурсы в случае Push-подписки?

3. Создание публикации слиянием

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

На компьютере, обозначенном как издатель и дистрибьютор, создайте папку общего доступа с именем repldata по адресу: C:\program files\Microsoft SQL Server\MSSQL\Repldata.

Задайте для данной папки разрешения: для всех – чтение, для

Сконфигурируйте на данном компьютере свойства дистрибьютора (можно для этого использовать либо команду TOOLS|Replication|Configure Publishing, Subscribers and Distribution, либо соответствующий мастер, предварительно выделив нужный сервер).

Установите свойства в соответствии с табл. 3.

Таблица 3

Создайте публикацию для репликации слиянием на издателе

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

Таблица 4

Просмотрите хронологию работы агента Snapshot

Раскройте папку Replication Monitor, раскройте в ней папку Publishers, после чего щелкните по рубрике Northwind:Northwind, в окне детализации просмотрите столбец Last Action (там должна появиться запись “A snapsort of 6 articles was generated” (сформирован моментальный снимок 6 статей). Вызовите контекстное меню на Snapshot и выполните команду Agent History. Щелкните по кнопке Session details. Просмотрите подробную информацию о создании моментального снимка, зафиксируйте ее в отчете.

Создание подписки по запросу и разрешение конфликтов

Создание Pull подписки

На сервере, который теперь должен служить подписчиком с помощью мастера Pull Subscription создайте подписку типа Pull. Используйте при этом данные табл.5.

Таблица 5

После создания подписки в дереве консоли Enterprise Manager разверните сервер-подписчик, разверните базу данных NorthwindMergeDB, после чего щелкните по Pull Subscriptions. В окне детализации, после того, как стобец Status покажет, что Merge Agent успешно работает, просмотрите Job History.

Ресурсы какого сервера использует этот агент?

Обновление исходной таблицы на издателе

Перейдите на сервер дистрибьютор и издатель, откройте на нем окно запроса.

Введите и выполните следующие команды:

Use Northwind

SELECT * from Customers

Просмотрите результат, запишите, какое значение находится в первой строке в столбце ContactName.

Введите и выполните следующие команды:

Use Northwind

Update Customers

Set ContactName==’Maria Anders-Smith’ Where CustomerID=’ALFKI’

SELECT * from Customers

Сколько времени займет репликация этого обновления данных?

На сервере-подписчике переключитесь в SQL Server, после чего откройте новое соединение со своим сервером.

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

На сервере-издателе и дистрибьторе переключитесь в Enterprise Manager, раскройте Replication Monitor, затем Agents, после чего щелкните по Merge Agent (Возможно Вам придется обновить его), в окне детализации щелкните правой кнопкой мыши по Northwund и выберите Agent History. Щелкните по кнопке Session Details.

Просмотрите и зафиксируйте в отчете, какие действия были выполнены во время сеанса.

Можете ли вы запустить задание Merge Agent с сервера дистрибьютора и издателя?

Одновременное обновление на издателе и подписчике

На сервере издателя и дистрибьютора переключитесь в Query Analyzer , введите и выполните следующие команды:

Use Northwind

Update Customers

Set ContactName=’Maria Anders-Smyth’ where CustomerID=’ALFKI’

Select * from customers

На сервере –подписчике откройте окно запроса и введите следующие команды:

Use NorthwindMergeDB

Update Customers

Set ContactName=’Maria Anders-Smythe’ where CustomerID=’ALFKI’

Select * from customers

На сервере дистрибьютора и издателя переключитесь в Enterprise Manager, в окне детализации Agent details убедитесь, что изменения были раплицированы, и что конфликт был разрешен. Это займет несколько минут.

Какое обновление было применено?

11. Лабораторная работа по теме «Индексирование таблиц в SQL Server»

Цель данной работы научиться планировать и создавать индексы.

1. Установка базы данных CREDIT

В главном меню системы выберите пункт «Выполнить» и запустите командный файл, который находится по адресу: "C:\MOC\2073A\Labfiles\Credit Database\Install.bat". После этого обновите данные Вашей рубрики Databases и проверьте, что в списке баз данных появилась база данных Credit.

2.Создание индексов в таблице Corporation

Создайте кластерный индекс в таблице Corporation по полю Corp_no c именем Corporation_ident,

Создайте некластерный индекс по полю region_no, назовите индекс corporation_region_link

Общий вид команды индексирования (без дополнительных параметров):

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX имя_индекса ON { имя_таблицы| имя_представления } (имя_поля[ ASC | DESC ] [ ,...n ] )

3. Использование системной хранимой процедуры Sp_help для идентификации индексов

Подключитесь к Query Analyzer и напишите скрипт, в котором откройте базу данных creditи запустите процедуру sp_help для таблицы corporation (sp_help имя_таблицы). Выполните скрипт. Просмотрите в результате 6-ю таблицу. Какие сведения об индексах дает процедура sp_help ?

Ответы зафиксируйте в отчете.

4. Использование информации из таблицы sysindexes

В Qwery Analyzer наберите следующие команды, а затем выполните скрипт.

USE credit

GO

SELECT t.name AS [имя_таблицы], i.name AS [имя_индекса], i.*

FROM sysobjects AS t JOIN sysindexes AS i ON t.id = i.id

WHERE t.id > 100

ORDER BY t.name

Просмотрите внимательно скрипт, скопируйте его в отчет. Проанализируйте результат.

Какие таблицы имеют индексы? Какого типа?

5. Создание индексов таблиц базы данных Northwind

Наберите следующие команды, проанализируйте их и выполните скрипт.

USE Northwind

GO

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'Orders_Customers_link')

DROP INDEX Orders.Orders_Customers_link

GO

CREATE NONCLUSTERED INDEX Orders_Customers_link ON Orders(CustomerID)

WITH FILLFACTOR = 75

GO

Сохраните текст скрипта в отчете.

Что должно стать результатом его выполнения?

Выполните процедуру sp_help имя_таблицы, sp_helpindex имя_таблицы, чтобы проверить создание индекса.

5.2. Создайте два индекса для таблицы Products базы данных Northwind в соответствии с данными табл.1.

Таблица 1

Сделайте запрос к таблице sysindexes, чтобы проверить, какие индексы были созданы.

5.3. Проверьте наличие индексов с помощью системной хранимой процедуры sp_helpindex для таблиц Orders и Products.

Зафиксируйте результаты. Проанализируйте их.

6. Проверка индексных структур

В этом упражнении перед созданием индексов будет проверяться структура таблицы базы данных Credit. Затем Вы создадите различные типы индексов с различными значениями FillFactor и выявите их влияние на структуру таблицы.

6.1. Обследование первоначальной структуры таблицы.

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

USE Credit

GO

EXEC sp_spaceused member

SELECT * FROM sysindexes WHERE id = OBJECT_ID('member')

DBCC SHOWCONTIG ('member')

GO

Результаты запишите в табл.2

Таблица 2

6.2. Создание кластерного индекса. После создания индекса по полю Member_no с помощью команды

CREATE UNIQUE CLUSTERED INDEX mem_no_CL ON member (member_no).

Затем с помощью команд, показанных в предыдущем упражнении заполните следующую таблицу (табл.3)

Таблица 3

Сравните показатели табл. 2 и табл.3.

Остались страницы заполненными после создания кластерного индекса?

Сделал ли кластерный индекс таблицу более компактной. Почему да или почему нет?

6.3. Создание некластерного индекса.

Создайте некластерный индекс следующей командой:

CREATE NONCLUSTERED INDEX indx_fname ON member(firstname)

Выполните команду для просмотра таблицы sysindexes для таблицы member.

Запишите полученную информацию в табл.4

Таблица 4

6.4. Создание некластерного индекса с параметром FillFactor

Удалите существующий некластерный индекс командой

DROP INDEX member.indx_fname,

а затем создайте его заново с параметром FillFactor равным 25.

Используя таблицу sysindexes, как в предыдущих примерах, заполните таблицу 5, аналогичную по составу табл.4.

Как влияет параметр FillFactor на степень заполнения страниц на листьевом уровне?

7. Проверка полезности индексов

7.1. Создайте четыре некластерные в таблице Charge базы данных Credit в соответствии с табл. 6.

Таблица 6

7.2. Сделайте запросы к таблице Charge, чтобы получить максимальные и минимальные значения полей Charge_no, Member_number, Provider_no, category_no. Запишите полученные значения в отчет в виде табл.7.

Таблица 7

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

Для этого запишите следующие запросы, но не выполняйте их!

1. SELECT * FROM charge

WHERE charge_no BETWEEN 1 AND m, где m-соответствующее максимальное значение

2. SELECT * FROM charge

WHERE member_no BETWEEN 1 AND m

3. SELECT * FROM charge

WHERE provider_no BETWEEN 1 AND m

4. SELECT * FROM charge

WHERE category_no between 1 AND m.

Выделите первый запрос, и щелкните в меню Query по команде Display Estimated Plan.

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

Изменяя верхнюю границу в предложении BETWEEN, подберите такое ее максимальное значение, при котором оптимизатор запросов начнет использовать индексы. Результат зафиксируйте в табл.8. И так проделайте с каждым запросом.

Таблица 8

На основании полученных данных сделайте выводы, по какому ключу имеется наибольшая селективность.

8. Просмотр статистики индексов и оценка индексной селективности

Откройте базу данных Credit, удалите указанные в команде DROP индексы таблицы member и выполните следующие команды для создания индексов.

DROP INDEX member.indx_member_no, member.indx_corp_lname, member.indx_lastname

CREATE UNIQUE INDEX indx_member_no ON member (member_no)

CREATE INDEX indx_corp_lname ON member (corp_no,lastname)

CREATE INDEX indx_lastname ON member (lastname)

GO

Для просмотра статистики индекса indx_member_no выполните следующую команду:

DBCC SHOW_STATISTICS (member,indx_member_no)

Запишите результаты в табл.9.

Таблица 9

Аналогичные действия проделайте и с другими созданными индексами.

Оцените селективность каждого индекса по полученным результатам.

12. Лабораторная работа по теме «Программирование»

1. Исправление ошибок в готовом скрипте

Откройте в Query Analyzer скрипт, расположенный по адресу:

C:\Moc\2073A\Labfiles\L02\Sample_Script.sql.

Просмотрите его и выполните. Просмотрите перечень ошибок. Обратите внимание, что сообщается номер строки (Line ….), в которой находится ошибка.

Основные намеки: в программе не проставлены символы комментариев и отсутствуют команды (GO), разделяющие программу на пакеты.

Запустите программу, сохраните ее на своем диске, сделайте распечатку для отчета.

2.Создание циклических программ

Создайте программу для вычисления суммы четных и нечетных чисел по отдельности для чисел натурального ряда, начиная от 1 до 20. Результаты выводить на каждом шаге цикла в следующем виде:

Текст программы и результаты привести в отчете.

3.Использование конструкции CASE

Разработайте скрипт для проверки наличия контрактов с авторами (таблица Authors базы данных Pubs) и выдайте сообщения в соответствии со следующим алгоритом:

Если поле contract=0 и поле state =’CA’, то текст сообщения должен быть ‘надо заключить!’,

Если поле contract=0 и поле state =’TN’, то текст сообщения – ‘тоже хороший человек, надо подумать’,

Если поле contract=1, то сообщить ‘ему и так хорошо’, во всех остальных случаях записать ‘пусть потерпит!!’.

Вывести в запросе поля au_lname, au_fname, state, contract, 'Наличие контракта'=.

Текст программы и результаты привести в отчете.

4.Использование динамически конструирования программ

Написать программу для открытия любой базы данных и выполнения запроса из любой ее таблицы. Используйте для проверки таблицы Orders, Products базы данных Northwind и таблицу Authors из базы данных Pubs.

Текст программы и результаты привести в отчете.

5. Разработать функцию для начисления стипендии в зависимости от среднего балла по следующему алгоритму:

если средний балл <3.5, то стипендия не начисляется, если средний балл>=3.5, но меньше 4.5, то стипендия должна быть 300 р, иначе 400.

Проверить результаты, задав соответствующие значения параметров. Текст функции и результаты привести в отчете.

6. Создать таблицу в базе данных Pubs со следующими полями: FIO студента, OC1, OC2, OC3, означающие оценки за экзамены. Внести в нее 3-4 записи.

7. Преобразовать ранее созданную функцию, добавив в нее необходимые параметры.

Используйте новый вариант функции в запросе, выводя в нем следующие поля: FIO, OC1, OC2, OC3, средний балл, значение стипендии.

8.Создать функцию, которая должна выбирать в таблицу фамилию, разряд сотрудника, должность из соответствующих таблиц базы данных kadry с разрядом, большим или равным заданного параметром функции @r. По умолчанию сделать этот параметр равным числу 13. Организовать проверку на правильность ввода данного параметра, (разряд должен быть в пределах от 1 до 18), выдать сообщение об ошибке. Можно для этого использовать поле fio. Таблицу объявить параметром функции. Организовать вызов функции т.о., чтобы проверить правильность ее работы при всех вариантах вводимого параметра.

9. Создайте программу для исправления номера телефона автора с определенным идентификационным номером (Pubs..authors)

Создайте новую пользовательскую ошибку со следующим текстом сообщения «Введен неправильный идентификационный код пользователя» с уровнем серьезности 16 (контекстное меню на имени сервера/Все задачи/Manage SQL Server Messages).

Задайте в программе два параметра: ID автора (@au_id) и номер телефона (@phone). По умолчанию дайте им значения NULL.

Предусмотрите в программе проверку ввода значений параметров, выдайте сообщение, если они отсутствует.

Если параметр @au_id введен, проверьте, существует ли такой идентификационный номер в таблице авторов. Если нет автора с введенным @au_id, возбудите созданную ранее ошибку. Если введенный параметр соответствует определенному автору, замените номер его телефона на введенный и выдайте об этом сообщение.

10. Создайте скрипт для объявления, открытия и вывода данных из курсора, который должен выбрать поля CustomerID, ContactName, City из таблицы Northwinds..Customers из записей, в которых поле City=London.

11. Создайте хранимую процедуру с параметром @city и выходным параметром в виде курсора, аналогичном созданному в примере 10.

Напишите процедуру обработки данного курсора и сохраните ее в виде отдельного скрипта. В этой процедуре подсчитайте количество проживающих в заданном городе и выведите результат под выводом данных из курсора в виде «Число проживающих в городе London= …».

13. Лабораторная работа по теме «Триггеры»

Цель работы: научиться создавать триггеры

¨ для поддержания целостности данных,

¨ для обеспечения сложных алгоритмов модификации данных.

1. Создание триггера на вставку новой записи

Запустите Query Analyzer и наберите программу, представленную ниже. Программа проверяет, существует ли триггер 'OrdDet_Insert', и удаляет его, а затем создает заново. Триггер создается на добавление новой записи в таблицу Order Details (заказанные товары).

/*

** Создание триггера на добавление записи в таблицу Order Details.

** Когда в таблицу Order Details записываются сведения о новом заказе,

** в таблице Products значение поля UnitsInStock должно уменьшаться на

** количество заказанного товара (Quantity).

*/

USE Northwind

/*

** Если триггер 'OrdDet_Insert' существует, удалим его командой DROP

** TRIGGER…

*/

IF EXISTS ( SELECT name FROM sysobjects

WHERE type = 'TR' AND name = 'OrdDet_Insert' )

DROP TRIGGER OrdDet_Insert

GO

/* Создадим триггер*/

CREATE TRIGGER OrdDet_Insert

ON [Order Details]

FOR INSERT

AS

UPDATE P SET

UnitsInStock = P.UnitsInStock - I.Quantity

FROM Products P INNER JOIN Inserted I

ON P.ProductID = I.ProductID

GO

/*

** Проверим наличие триггера :

** 1. С помощью запроса к таблице sysobjects

*/

SELECT name FROM sysobjects

WHERE type = 'TR'

ORDER BY type, name

GO

/*

**2. С помощью хранимой процедуры sp_helptrigger

*/

sp_helptrigger [Order Details]

Запустите части программы, в которых проверяется наличие триггера и создается триггер. Сохраните этот текст в своем отчете. Опишите, что делает данный триггер.

Запустите по очереди части программы, которые сообщают информацию о созданных триггерах.

Где хранятся сведения о триггерах?

Тестирование созданного триггера

1. Проверьте наличие запаса продукта (UnitInStock) с кодом (ProductID), равным 22. Запишите значение поля UnitInStock в отчет.

2. Добавьте строку в таблицу Order Details, которая соответствует заказу продукта с кодом 22 в количестве (Quantity) 50 штук командой

Insert into [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)

Values (11077,22,21.00,50,0.0)

3. Еще раз выполните запрос п.1. Запишите значение поля UnitInStock в отчет.

Проработал ваш триггер или нет?

Доработайте данный триггер таким образом, чтобы вначале в нем осуществлялась проверка, существует ли в таблице Products заказанный товар в нужном количестве, выдавалось сообщение, если товара нет совсем или его запас меньше требуемого, и операция добавления записи в таблицу Order Details откатывалась. Еще раз протестируйте триггер.

2. Создание триггера на обновление для обновления содержимого колонки

1. Создайте две новые таблицы NewCategories и NewProducts на основе соответствующих таблиц с помощью команд SELECT * INTO…, например,

Select * into newcategories from categories

При этом в новые таблицы перенесутся данные, но они не будут содержать ограничения типа Primary KEY и Foreign Key.

2. Создайте триггер на удаление записи из таблицы NewCategories такой, что при отсутствии соответствующей категории в этой таблице, в таблице Newproducts изменялось значение поля Discontinued на 1.

create trigger category_delete on newcategories

for delete

as

update p set discontinued=1 from newproducts as p

inner join deleted as d

on p.categoryid=d.categoryid

3. С помощью запроса выведите все записи из таблицы Products с кодом категории (CategoryID), равным 7 (показать поля ProductID, CategoryID, Discontinued). Сохраните результаты запроса в отчете.

4. Удалите из таблицы Categories запись с кодом категории =7.

5. Выполните еще раз запрос п.3. для тестирования триггера. Сделайте выводы.

3. Создание триггера, который поддерживает сложные бизнес-правила

Текст данного триггера приведен ниже.

USE Northwind

GO

CREATE TRIGGER Product_Delete

ON NewProducts FOR DELETE

AS

IF (Select Count (*)

FROM [Order Details] INNER JOIN deleted

ON [Order Details].ProductID = Deleted.ProductID

) > 0

BEGIN

RAISERROR(‘Транзакция не может быть выполнена, т.к. в таблице Order Details существуют заказы данного продукта’, 10, 1)

ROLLBACK TRANSACTION

END

go

/* тестирование триггера */

DELETE NewProducts WHERE ProductID = 6

Выполните сначала часть программы, которая создает триггер, а затем протестируйте его. Сохраните текст триггера в отчете.

Создайте системное сообщение об ошибке с указанием в нем кода продукта (ProductID). Откорректируйте приведенную выше программу, используя в команде возбуждения ошибки номер созданного Вами сообщения. Протестируйте триггер еще раз.

Тестирование порядка выполнения триггеров и ограничений

1. Создайте триггер с именем Product_Delete2 для таблицы Products, аналогичный тому, который Вы использовали в предыдущем упражнении. Можете подкорректировать текст предыдущей программы.

2. Протестируйте триггер с помощью команды DELETE (см. предыдущее упражнение) Проработал ли данный триггер для таблицы Products? Почему да или почему нет?

Приложения

Приложение 1

Управление файлами данных

Рис.1. Физическая архитектура базы данных

Рис.2. Пример размещения базы данных в разных группах файлов на разных дисках

Команды создания и изменения базы данных kadry_new1

Use master

Create database kadry_new1

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

C:\Program Files\Microsoft SQL Server\MSSQL\data\kadry_new1.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\data\kadry_new1_log.LDF

Exec sp_helpdb kadry_new1—просмотр сведений о базе данных

/* добавляем к базе данных файловую группу*/

ALTER DATABASE kadry­_new1

ADD FILEGROUP kadryFG1

GO

/* добавляем в группу два файла*/

ALTER DATABASE kadry­_new1

ADD FILE

( NAME =kadry_data2,

FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ kadry_data2.ndf ',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB),

( NAME= kadry_data3 ,

FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ kadry_data3.ndf’ ,

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB)

TO FILEGROUP kadryFG1

ALTER DATABASE kadry­_new1

/*назначаем группу группой по умолчанию*/

MODIFY FILEGROUP kadryFG1 DEFAULT

Приложение 2

Управление безопасностью SQL Server

Таблица 1

Фиксированные серверные роли SQL Server

Таблица 2

Фиксированные роли базы данных

Приложение 3

Таблица 1

Счетчики для мониторинга использования памяти

	Объект:Счетчик	Описание	Рекомендации
	Memory: Available Bytes	Отслеживает количество байт памяти, свободной для использования ее процессором	Значение счетчика должно быть больше 5000 КВ. Значения ниже данного показывают переполнение физической памяти, что означает, что память должна быть увеличена
	Memory: Pages/sec	Отслеживает число страниц, которые операционная система Windows 2000 читает или пишет на жесткий диск	Этот счетчик никогда не должен быть продолжительно больше нуля Если же значение продолжительное время больше нуля, это означает, что Windows 2000 постоянно подкачивает файлы, а чтобы выяснить, является ли виновником интенсивного свопинга SQL Server , следует понаблюдать за следующим счетчиком.
	Process: Page Faults/sec/SQL Server Instance	Отслеживает подкачку страниц, вызванную тем, что Windows выделяет память под данные процессы	Высокие значения данных счетчиков показывают высокую интенсивность свопинга. Проверьте, SQL Server ли или другой процесс порождает чрезмерный свопинг

Таблица 2

Счетчики для наблюдения за памятью для SQL Server

Таблица 3

Счетчики для наблюдения за работой процессора

Таблица 4

Счетчики для наблюдения за работой дисков

Приложение 4

Таблица 1

Перечень категорий событий в SQL Profiler

Таблица 2

Свойства событий