Будь умным!


У вас вопросы?
У нас ответы:) SamZan.ru

СанктПетербургский государственный инженерноэкономический университет ПРОЕКТИРОВАНИЕ БАЗ

Работа добавлена на сайт samzan.ru: 2015-07-10

Бесплатно
Узнать стоимость работы
Рассчитаем за 1 минуту, онлайн

Федеральное агентство по образованию

Государственное образовательное учреждение

высшего профессионального образования

«Санкт-Петербургский государственный

инженерно-экономический университет»

ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ В СРЕДЕ SQL SERVER 2000

Учебное пособие

Специальности 080801 – Прикладная информатика в экономике

230201 – Информационные системы и технологии

Санкт-Петербург

2006

Допущено

редакционно-издательским советом СПбГИЭУ

в качестве методического издания

Составитель

канд. экон. наук, доц. С.П. Кузьмина

Рецензенты

доктор техн. наук, проф. В.А. Дятлов

кан. экон. наук, проф. кафедры информатики СПбГФЭУ О.П. Ильина

Подготовлено на кафедре

информационных систем в экономике

Одобрено научно-методическим советом

факультета информационных систем в экономике и управлении

Отпечатано в авторской редакции с оригинал-макета,

представленного составителем

СПбГИЭУ, 2006


                  Стр.

Ведение            4

Часть I. Общие сведения о СУБД Microsoft SQL Server 2000  5

  1.  Возможность SQL Server 2000      5
    1.  Компоненты SQL Server 2000      15
    2.  Архитектура БД в среде SQL Server 2000    34
    3.  Инструменты программирования SQL Server 2000  69
    4.  Язык Transact SQL        79

Часть II. Администрирование SQL Server 2000    116

  1.  Создание и управление базой данных    116
    1.  Импорт и экспорт данных     126
    2.  Копирование в среде SQL Server 2000   144
    3.  Восстановление SQL Server 2000    156
    4.  Репликация        158
    5.  Проверка подлинности SQL Server 2000   179
    6.  Разрешение уровня сервера и БД    189
    7.  Оптимизация работы сервера     198
      1.  Конфигурация сети     200
      2.  Индексы       201
      3.  Триггеры       212
      4.  Хранимые процедуры     220
      5.  Представления      229
      6.  Мониторинг       237
    8.  Система безопасности      263

Список литературы         280


ВВЕДЕНИЕ

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

Такой системой является рассматриваемая в пособии СУБД SQL Server 2000.

Пособие состоит из двух частей.

В первой части представлены общие сведения о СУБД SQL Server 2000. Рассматриваются возможности, компоненты СУБД, использование языка Transact SQL, управление и манипулирование данными, вопросы оптимизации работы СУБД.

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


ЧАСТЬ I.

Общие сведения о СУБД Microsoft SQL server 2000

1.1. ВОЗМОЖНОСТИ SQL SERVER 2000

Microsoft SQL Server 2000 — это законченное решение для управления и анализа данных, позволяющее оперативно развертывать масштабируемые Web-приложения нового поколения. SQL Server 2000 — ключевой компонент поддержки электронной коммерции, интерактивных деловых приложений и хранилищ данных, обеспечивающий масштабируемость, необходимую для поддержки растущих, динамических сред. В SQL Server 2000 предусмотрена широчайшая поддержка XML (Extensible Markup Language) и других форматов, используемых в Интернете, функций производительности и доступности, гарантирующих своевременное решение поставленных задач, а также развитой функциональности управления и настройки, позволяющей автоматизировать выполнение рутинных задач и снизить совокупную стоимость владения. Кроме того, SQL Server 2000 в полном объеме использует преимущества Windows 2000, обеспечивая интеграцию с Active Directory Services и поддерживая до 32 процессоров и до 64 Гигабайт (Гб) оперативной памяти.

SQL Server 2000 — это реляционная СУБД, которая использует язык Transact-SQL для пересылки сообщений между компьютером клиента и компьютером, на котором работает SQL Server 2000. Реляционная СУБД состоит из механизма баз данных, собственно баз данных и приложений, необходимых для управления данными и компонентами реляционной СУБД. Реляционная СУБД организует данные в виде связанных строк и столбцов, составляющих базу данных. Реляционная СУБД отвечает за поддержку структуры базы данных и решает следующие задачи:

поддерживает связи между данными в базе;

гарантирует корректное хранение данных и выполнение правил, регламентирующих связи между ними;

восстанавливает данные после аварии системы, переводя их в согласованное состояние, зафиксированное до сбоя.

База данных (БД) SQL Server 2000 представляет собой реляционную базу данных, совместимую с SQL (Structured Query Language) с интегрированной поддержкой XML для Интернет-приложений. SQL Server 2000 создан на основе современного расширяемого сервера SQL Server 7.0

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

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

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

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

Таблица описывает некоторый класс объектов, важных для организации. Например, в базе данных какой-либо компании одна таблица может быть предназначена для сведений о работниках, другая — для сведений о покупателях и еще одна — для сведений о магазинах. Любая таблица состоит из столбцов и строк (в теории реляционных баз данных это атрибуты и кортежи соответственно). Каждый столбец определяет атрибут класса объектов, представленных таблицей. Например, в таблице для сведений о работниках могут быть столбцы, определяющие такие атрибуты, как имя, фамилия, идентификационный номер, отдел, тарифный разряд и название должности работника. А каждая строка описывает отдельный экземпляр объекта, представленного таблицей.

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

Для работы с информацией из базы данных необходим набор команд и операторов (язык), который поддерживается СУБД. С реляционными базами данных можно использовать различные языки, но наиболее распространен язык SQL. Стандарты на ПО, в том числе стандарты SQL, определяются организациями ANSI (American National Standards Institute) и ISO (International Organization for Standardization). SQL Server 2000 поддерживает Entry Level SQL-92 — стандарт SQL, опубликованный ANSI и ISO в 1992 году. Диалект SQL, который поддерживает SQL Server, называется Transact-SQL.

XML (Extensible Markup Language) — это стандарт форматирования данных, принятый в Интернете. Он представляет набор тэгов, которые включают в документ, чтобы определить его структуру.

Хотя большинство операторов SQL возвращают результаты в виде реляционного набора данных (в табличном виде), SQL Server 2000 поддерживает оператор FOR XML, который задает возврат результатов в виде документа XML. SQL Server 2000 также поддерживает запросы XPath из приложений для Интернета и интрасетей. В базы данных SQL Server можно добавлять информацию из документов XML, а оператор OPENXML позволяет выводить данные из документа в виде реляционного результирующего набора.

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

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

Установка или обновление SQL Server 2000 происходит под управлением приложения с графическим интерфейсом (GUI-приложения), которое направляет действия пользователя при вводе сведений, необходимых программе установки. Программа установки автоматически определяет наличие ранней версии SQL Server. После завершения установки SQL Server 2000 она спрашивает пользователя, не желает ли он запустить мастер обновления SQL Server 2000 (SQL Server 2000 Upgrade wizard), под руководством которого будет быстро выполнен процесс обновления. Таким образом, весь процесс установки или обновления завершается быстро, причем пользователю приходится вводить минимум информации.

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

SQL Server 2000 предлагает администраторам баз данных такие инструменты управления системами, как Enterprise Manager и SQL Profiler.

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

Некоторые функции SQL Server 2000 увеличивают масштабируемость системы. Например, SQL Server 2000 динамически регулирует степень дробления блокировок для каждой таблицы, на которую ссылается запрос, в него также входит оптимизированная поддержка высокоскоростных операций в средах VLDB (Very Large Database). Кроме того, SQL Server 2000 способен планировать параллельное исполнение, при котором обработка оператора SQL разделяется на несколько частей. Каждая часть может быть выполнена на отдельном процессоре, в этом случае формирование полного результирующего набора осуществляется быстрее, чем в том случае, когда отдельные части операторов выполняются последовательно.

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

Data Warehousing FVamework. Набор компонентов и интерфейсов прикладного программирования (API), реализующих функции хранилищ данных SQL Server 2000.

Data Transformation Services (DTS). Набор служб для создания хранилищ данных и киосков данных.

Meta Data Services. Набор интерфейсов ActiveX и информационных моделей, определяющих схему базы данных и преобразований данных, которые реализует Data Ware housing Framework. Схема, или метаданные, — это метод определения и организации данных.

Analysis Services. Набор служб, обеспечивающих возможности OLAP-обработки данных из гетерогенных источников OLE DB.

English Query. Продукт для разработки приложений, который дает пользователям возможность вместо использования компьютерного языка запросов (например SQL) за давать вопросы по-английски.

SQL Server 2000 работает с другими программными продуктами, образуя стабильное и безопасное хранилище информации для Интернета и интрасетей:

SQL Server 2000 работает с механизмами безопасности и шифрования Windows 2000 Server и Windows NT Server, реализуя безопасное хранилище информации;

SQL Server 2000 является высокопроизводительной службой хранения данных для Web приложений, работающих по управлением Microsoft Internet Information Services;

SQL Server 2000 можно использовать вместе с Site Server для обслуживания больших и сложных Web-сайтов электронной коммерции;

поддержка TCP/IP Sockets позволяет интегрировать SQL Server 2000 с Microsoft Proxy Server для реализации безопасной связи через Интернет и в интрасетях.

Производительность SQL Server 2000 можно довести до уровня, необходимого для работы огромных Интернет – узлов. Кроме того, в механизме баз данных SQL Server 2000 есть встроенная поддержка XML, а мастер Web Assistant помогает генерировать страницы HTML (Hypertext Markup Language) на основе данных SQL Server 2000 и публиковать эти данные для доступа по протоколам HTTP (Hypertext Transport Protocol) и FTP (File Transfer Protocol).

SQL Server поддерживает аутентификацию Windows, что позволяет применять в качестве учетных записей SQL Server 2000 пользовательские и доменные учетные записи Windows NT и Windows 2000. Аутентификацию пользователей при подключении к сети осуществляет Windows 2000. При соединении с SQL Server, клиентское ПО запрашивает доверенное соединение, которое может быть предоставлено, только если пользователи прошли аутентификацию Windows NT или Windows 2000. Таким образом, SQL Server сам не выполняет проверку пользователей, а пользователям не требуются отдельные имена и пароли для подключения к каждой системе SQL Server.

SQL Server 2000 может посылать и получать электронную почту и пейджинговые сообщения от Microsoft Exchange или других почтовых серверов, совместимых с MAPI (Message Application Programming Interface). Эта функция обеспечивает отсылку почты с помощью пакетов, хранимых процедур и триггеров SQL Server 2000. События и уведомления SQL Server 2000 можно настроить так, чтобы в случае возникновения серьезных проблем или даже при риске их возникновения администратор сервера автоматически получал уведомления по электронной почте или на пейджер.

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

Enterprise Edition. Эта редакция — полный вариант SQL Server, наиболее часто предлагаемый организациям. Enterprise Edition отличается развитыми возможностями масштабируемости и надежности, необходимыми для решения важных задач интерактивного ведения бизнеса и Интернет - приложений, в том числе распределенными секционированными представлениями, портированием журнала и улучшенными возможностями кластеризации. Эта редакция также в полном объеме использует преимущества наиболее совершенного аппаратного обеспечения, поддерживая до 32 процессоров и 64 Гб ОЗУ. Кроме того, SQL Server 2000 Enterprise Edition включает дополнительные функции анализа.

Standard Edition. Этот вариант могут позволить себе средние и небольшие организации, которым не требуются сложные возможности масштабируемости и доступности, а также полный набор функций анализа, которые имеются в SQL Server 2000 Enterprise Edition. Standard Edition применяют в симметричных многопроцессорных системах, в которых установлено до 4 процессоров и до 2 Гб ОЗУ.

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

1.2. КОМПОНЕНТЫ SQL SERVER 2000

SQL Server 2000 состоит из ряда компонентов, таких, как механизм реляционных баз данных, Analysis Services и English Query. Все эти компоненты, каждый из которых играет определенную роль, работая совместно, формируют полнофункциональную реляционную СУБД

Механизм реляционных баз данных SQL Server 2000 — это современное ядро с высокой степенью масштабируемости, предназначенное для хранения данных. Механизм баз данных сохраняет данные в таблицах. Каждая таблица представляет определенный класс объектов, в зависимости от интересов конкретной организации (например, сведения о транспортных средствах, работниках или клиентах). Таблица состоит из столбцов, каждый из которых представляет атрибут объекта (например, вес, имя или стоимость), который она моделирует, и строк. Каждая строка представляет один экземпляр объекта, моделируемого таблицей (например, автомобиль с номером ABC-123 или работник с идентификационным номером ID 123456). Приложение передает механизму баз данных оператор SQL, механизм возвращает результат в виде набора данных в табличной форме. Интернет - приложение передает механизму баз данных оператор SQL или запрос XPath, а тот возвращает результат в виде документа XML. Механизм реляционных баз данных обеспечивает поддержку стандартных интерфейсов доступа к данным, таких, как ADO (ActiveX Data Objects), OLE DB и ODBC (Open Database Connectivity).

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

рис. 1.1. СУБД Microsoft SQL Server

Механизм реляционных баз данных также обладает высокой степенью защиты. Аутентификацию при регистрации допустимо интегрировать с проверкой подлинности Windows, поэтому SQL Server не хранит никаких паролей и не пересылает их по сети (где их могут прочесть, используя анализаторы пакетов). На узлах разрешается задавать аудит всех пользователей, обращающихся к базе данных, соответствующий требованиям безопасности уровня С2, и применять протокол SSL (Secure Sockets Layer) для шифрования всех данных, передаваемых между приложением и базой данных.

DTS

Многим организациям для более эффективного принятия решений требуется централизация данных. Однако данные можно хранить в самых разнообразных форматах и в нескольких различных местах. DTS (Data Transformation Services) в SQL Server позволяет создавать хранилища и киоски данных путем интерактивного или автоматического импорта и передачи данных из нескольких гетерогенных источников по расписанию.

DTS SQL Server 2000 существенно повышает эффективность процесса создания хранилищ данных для оперативной аналитической обработки (Online Analytical Processing, OLAP). Кроме того, он предоставляет средства для тонкой настройки обширных баз данных для оперативной обработки транзакций (Online Transaction Processing, OLTP), в результате чего можно увеличить число одновременно работающих пользователей, активно добавляющих и модифицирующих данные. Структура баз данных OLTP такова, что они регистрируют подробности каждой транзакции. Попытка выполнить сложный анализ для определения трендов продаж за несколько месяцев или лет потребует просмотра огромного числа записей, а большая загруженность обработкой информации при этом снижает производительность баз данных OLTP.

Хранилища и киоски данных создаются в системе OLTP на основе данных, извлеченных и преобразованных в форму, которая лучше подходит для OLAP-обработки. Периодически осуществляется сбор строк с подробными данными OLTP в промежуточную базу данных, где они обобщаются, а итоговые данные помещаются в хранилище или киоск. DTS поддерживает извлечение данных из одного источника и выполнение сложных преобразований с последующим сохранением итоговых преобразованных данных в другом источнике данных. Этот компонент в значительной степени упрощает процесс извлечения данных из нескольких систем OLTP и создания на основе извлеченных данных хранилища или киоска данных для OLAP.

Analysis Services

Analysis Services предоставляет инструменты для анализа данных, которые находятся в хранилищах и киосках данных, где итоговая информация содержится в таблицах фактов. Таблица фактов — центральная таблица в схеме хранилища данных, в ней хранятся численные меры и ключи, связывающие факты с таблицами измерений. Как правило, базовая таблица фактов содержит сведения, описывающие некоторые события в бизнесе, например банковские транзакции или факты продажи продукции. Приложения работают с данными Analysis Services с помощью многомерных расширений ADO и OLE DB. Обработка запросов OLAP посредством многомерных кубов Analysis Services выполняется существенно быстрее, чем с использованием подробной информации из баз данных OLTP. В систему Analysis Services входит сервер, управляющий многомерными кубами, предназначенными для анализа. Он обеспечивает клиенту быстрый доступ к данным куба. Чтобы быстро выдавать ответы на сложные аналитические запросы, Analysis Services организует данные из хранилища в кубические массивы с помощью предварительно вычисленных агрегированных данных. Analysis Services также облегчает создание моделей извлечения информации для данных как из многомерных, так и из реляционных источников. Можно применять модели извлечения информации к обоим типам данных. Посредством службы PivotTable — компонента доступа, совместимого с OLE DB, Microsoft Excel и приложения других производителей могут получать данные с сервера и представлять их пользователю или создавать локальные кубические массивы для автономного анализа.

English Query

SQL Server 2000 English Query помогает создавать приложения, способные автоматически настраиваться в соответствии со специальными вопросами, которые задают пользователи. Администратор English Query определяет для обработчика English Query все логические связи между таблицами и столбцами базы данных или между кубическими массивами в хранилище или киоске данных. Затем пользовательское приложение может вывести специальное окно, в котором пользователю достаточно набрать символьную строку с вопросом (записанным по-английски), касающимся данных в базе или хранилище. Приложение передает эту строку обработчику English Query, который анализирует ее с учетом связей, определенных между таблицами и кубами. После этого English Query возвращает приложению оператор SQL или запрос MDX (Multi-Dimensional Expression), при исполнении которого будет получен ответ на заданный пользователем вопрос.

Посредством English Query разработчики могут преобразовывать реляционные базы данных в приложения English Query, которые позволяют конечным пользователям вместо формирования запроса с помощью оператора SQL задавать вопросы по-английски. Редактор моделей English Query находится в среде разработки Microsoft Visual Studio 6.0. При применении Visual Studio информация о базе данных задается таким образом, что English Query без труда обрабатывает вопросы относительно конкретных таблиц, полей и данных, введенные по-английски.

В English Query входят мастера, автоматически создающие проект (.eqp-файл) или модель (.eqm-файл) для приложения. После проверки проект компилируют в приложение English Query (.eqd-файл), после чего можно осуществлять его развертывание. Мастер SQL Project ускоряет процесс создания проектов English Query путем автоматического создания объектов и связей для выбранных баз данных.

Перед развертыванием приложения English Query следует выполнить его полное тестирование, чтобы убедиться, что все существующие объекты и связи возвращают точные результаты. Окно Model Test поддерживает встроенные отладочные средства, позволяющие вносить дальнейшие изменения и улучшения в проект EQ.

MetaData Services

Службы Meta Data Services из SQL Server обеспечивают хранение и управление метаданными информационных систем и приложений. Эта технология выполняет функции концентратора определений данных и компонентов, моделей разработки и развертывания, программных компонентов, предназначенных для повторного использования, и описаний хранилищ данных. Этот продукт состоит из следующих компонентов: ядра репозито-рия, инструментов, API, стандартных информационных моделей, браузера и SDK (Software Development Kit).

Meta Data Services представляет собой набор служб для управления метаданными. Для работы с Microsoft Meta Data Services необходимо разбираться в характеристиках метаданных. Метаданные описывают структуру и значение данных, а также структуру и значение приложений и процессов. Следует помнить, что метаданные — это абстракция, что у них есть контекст и они способны выполнять различные функции в среде разработки.

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

Для метаданных существует несколько уровней абстрагирования. Можно описать экземпляр данных, затем это описание и так далее — последовательные описания описаний, пока не будет достигнут некий практический предел. Как правило, для описания метаданных, используемого в разработке ПО, имеется три уровня абстрагирования. Например, экземпляр данных «таблица займов» можно представить в виде таблицы базы данных, ту, в свою очередь — как объект «таблица», принадлежащий базе данных, и наконец, объект базы данных «таблица» — как абстрактный класс, формализующий фиксированный набор характеристик, которым должны соответствовать все производные объекты.

Отличие между данными и метаданными часто приравнивается к отличию между типом и экземпляром. Разработчик модели определяет тип (например, класс или связь), а разработчик ПО определяет экземпляр (например, класс «таблица» или связь «таблица — столбцы»).

Различие между экземпляром и типом зависит от контекста. То, что в одном сценарии является метаданными, в другом сценарии может стать данными. Например, в типичной реляционной СУБД системный каталог описывает таблицы и столбцы, содержащие данные. Можно рассматривать данные системного каталога как метаданные, поскольку они представляют собой описания определений данных. Однако, используя соответствующий программный инструмент, удается манипулировать ими, как и любыми другими данными. Так, при просмотре связей между данными, сведений о версии таблицы или идентификации всех таблиц с финансовой информацией путем поиска столбцов с типом данных «валюта» типичные метаданные (представителем которых является системный каталог) играют роль данных, которыми можно манипулировать.

Books Online

SQL Server Books Online — это электронная документация, которая поставляется с SQL Server 2000. Чтобы найти нужную информацию в SQL Server Books Online, достаточно:

просмотреть оглавление;

выполнить поиск в указателе по ключевому слову;

выполнить поиск по слову или целой фразе.

В состав SQL Server Books Online также входят справочные файлы, которые открываются при щелчке кнопки Help в диалоговом окне или при нажатии клавиши F1.

Документацию SQL Server Books Online нельзя открыть в полном объеме с компакт-диска SQL Server 2000 — прежде необходимо установить ее на локальный жесткий диск. Раздел вводного руководства по Microsoft SQL Server 2000, посвященный установке, не включает сведения о некоторых изменениях, произошедших после того, как руководство уже было отпечатано. Они внесены в SQL Server Books Online в справочном файле программы установки SQL Server.

Чтобы установить на жесткий диск только документацию SQL Server Books Online, надо выполнить выборочную установку компонентов SQL Server 2000 и выделить только компонент Books Online. Он представляет собой набор справочных файлов в формате HTML, для просмотра которых необходим Microsoft Internet Explorer версии 5.0 или более поздней. Его можно загрузить с узла http://www.microsoft.com/windows/ie/.

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

администрировать и конфигурировать SQL Server;

определять информацию каталога для копии SQL Server;

конструировать и тестировать запросы;

копировать, импортировать, экспортировать и преобразовывать данные;

выводить диагностическую информацию;

запускать и останавливать SQL Server.

Утилиты командной строки автоматически устанавливаются во время установки SQL Server 2000 на компьютер под управлением Windows 2000, Windows NT, Windows 95 или Windows 98. В следующей таблице перечислены утилиты командной строки SQL Server и указаны папки, в которые они устанавливаются по умолчанию. Обратите внимание, что копия SQL Server 2000 по умолчанию устанавливается в подкаталог с именем MSSQL. Каждый именованный экземпляр SQL Server 2000 будет установлен в соответствующую папку с именем М?>?>0\Л<имя_экземпляра>.

Утилиты

console

sqlagent

sqldiag

sqlmaint

sqlservr

vswitch  x :\Program Files\Microsoft SQL Server\MSSQL\Binn

bcp

dtsran

dtswiz

isql

isqlw

itwiz

odbccmpt

osql

rebuildm

sqlftwiz  x:\Program Files\Microsoft SQL Server\80\Tools\Binn

distrib

logread

replmergsnapshot  x:\Program Files\Microsoft SQL Server\80\Com

Во время установки путь к папке x:\Program Files\Microsoft SQL Server\80\Tools\Binn добавляется к системному пути. Утилиты, расположенные в этой папке, можно запускать из командной строки, находясь в любом месте дерева каталогов. Утилиту, расположенную вне этой папки, необходимо запускать либо из того каталога, в котором она установлена, либо добавить путь к этому каталогу к пути поиска ОС.

Три указанные ниже утилиты больше не устанавливаются программой установки SQL Server 2000:

makepipe;

odbcping;

readpipe.

При необходимости их можно запустить из папки \x86\Binn на установочном компакт-диске SQL Server 2000 или вручную скопировать на компьютер. Описание любой утилиты командной строки из SQL Server 2000 вы найдете в SQL Server Books Online.

В состав SQL Server 2000 входит богатый набор инструментов с графическим интерфейсом, предназначенных для работы с SQL Server и его администрирования. В этом разделе кратко описаны многие из них. Кроме того, информация обо всех инструментах с графическим интерфейсом есть в Books Online.

Enterprise Manager

SQL Server Enterprise Manager — основной инструмент администрирования SQL Server 2000, поддерживающий пользовательский интерфейс, совместимый с ММС (Microsoft Management Console) и позволяющий решать ряд административных задач:

определять группы серверов, работающих под управлением SQL Server;

регистрировать отдельные серверы в группе;

настраивать любые параметры SQL Server для всех зарегистрированных серверов;

создавать и администрировать любые базы данных, объекты, идентификаторы пользователей, учетные имена и права доступа к SQL Server на каждом из зарегистрированных серверов;

определять и исполнять все административные задачи SQL Server на каждом зарегистрированном сервере;

интерактивно конструировать и тестировать операторы SQL, пакеты и сценарии, вызывая SQL Query Analyzer;

вызывать различные мастера SQL Server.

ММС поддерживает общий интерфейс для управления различными серверными приложениями в сети Microsoft Windows. В состав серверных приложений входит такой компонент, как оснастка, который предоставляет пользователям ММС интерфейс для управления серверным приложением. SQL Server Enterprise Manager является оснасткой ММС для Microsoft SQL Server 2000.

SQL Server Agent

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

запуск заданий SQL Server, запланированных для исполнения в определенное время или по истечении определенного периода времени;

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

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

SQL Profiler

SQL Profiler — это инструмент для записи событий SQL Server 2000. События сохраняются в файле трассировки, который впоследствии можно проанализировать или использовать для повтора некоторой последовательности действий при диагностировании возникшей проблемы. SQL Profiler применяемся для:

• пошагового исполнения проблемах запросов и определения источника проблемы;

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

записи последовательностей SQL-операторов, приводящих к возникновению проблем;

мониторинга производительности SQL Server и регулирования его загруженности.

SQL Profiler также поддерживает аудит действий, выполненных с экземплярами SQL Server. Информация о действиях, имеющих отношение к безопасности, сохраняется для последующего просмотра администратором, отвечающим за безопасность. Утилита Client Network

Client Network используется для управления клиентскими библиотеками Net-Libraries и определения псевдонимов серверов. Кроме того, эта утилита позволяет устанавливать параметры по умолчанию, которые используются приложениями DB-Library.

Большинству пользователей утилита Client Network никогда не понадобится. Для подключения к SQL Server 2000 им достаточно указать сетевое имя сервера, на котором работает SQL Server, и (что не обязательно) имя экземпляра SQL Server.

Утилита Server Network

Утилита Server Network применяется для управления серверными библиотеками Net-Libraries, а также позволяет задавать:

стеки сетевых протоколов, используемые экземпляром SQL Server 2000 для прослушивания клиентских запросов;

последовательность, в которой серверные библиотеки Net-Libraries определяют, не устанавливает ли приложение соединение;

новые сетевые адреса для прослушивания запросов экземпляром SQL Server 2000.

Большинству администраторов утилита Server Network также никогда не понадобится. Они могут задать серверные библиотеки Net-Libraries во время установки сервера.

Service Manager

SQL Server Service Manager предназначен для запуска, останова и приостановки серверных компонентов SQL Server 2000. Эти компоненты работают как службы в Microsoft Windows NT или Windows 2000, а в Windows 95 и Windows 98 — как отдельные исполняемые программы.

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

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

Microsoft Search (только для Windows NT и Windows 2000). Реализует механизм полнотекстового поиска. Существует в единственном экземпляре, независимо от числа экземпляров SQL Server на компьютере.

MSDTC (только для Windows NT и Windows 2000). Управляет распределенными транзакциями. Существует в единственном экземпляре, независимо от числа экземпляров SQL Server на компьютере.

 MSSQLServerOLAPService (только для Windows NT и Windows 2000). Реализует Analysis Services. Существует в единственном экземпляре, независимо от числа экземпляров SQL Server на компьютере.

Окно Service Manager может быть скрыто и представлено значком в системной области панели задач. Чтобы вывести мую со списком задач, которые поддерживает Service Manager, щелкните правой кнопкой значок на панели задач.

SQL Query Analyzer

SQL Query Analyzer — это инструмент с графическим интерфейсом, предназначенный для решения множества различных задач:

создания запросов и сценариев SQL, а также исполнения их с базами данных SQL Server;

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

копирования существующих объектов баз данных;

исполнения хранимых процедур без задания их параметров;

отладки хранимых процедур;

отладки запросов, имеющих проблемы с производительностью;

поиска объектов в базах данных, а также просмотра и работы с объектами;

добавления, обновления и удаления строк в таблице;

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

добавления часто используемых команд в меню Tools.

SQL Query Analyzer запускают непосредственно из меню Start или в SQL Server Enterprise Manager. Его также можно запустить, введя в командной строке команду isqlw.

Встроенные мастера SQL Server 2000

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

1.3. АРХИТЕКТУР БДВ СРЕДЕ SQL Server 2000

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

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

У каждого экземпляра SQL Server есть четыре системных базы данных (master, tempdb, msdb и model) и одна или несколько пользовательских. В одних организациях все данные содержатся в единственной пользовательской базе данных, а в других для каждой группы создана собственная база данных. Также у каждой базы данных может быть свое приложение, использующее ее. Например, в организации иногда отдельная база данных предназначена для учета продаж, другая — для платежных ведомостей, третья — для работы приложения управления делопроизводством и т. д. Некоторые приложения используют только одну базу данных, а другие могут обращаться к нескольким.

База данных XYZ

Какой она представляется пользователю

Рис. 1.2. Вид базы данных для пользователя и ее физическая реализация

Сервер

Рис. 1.3. Системные и пользовательские базы данных

Нет необходимости запускать несколько копий механизма баз данных SQL Server, чтобы предоставить доступ к базе данных на сервере нескольким пользователям. Единственный экземпляр SQL Server Standard Edition или Enterprise Edition способен обрабатывать запросы тысяч пользователей, одновременно работающих с разными базами данных. Каждый экземпляр SQL Server делает все свои базы данных доступными всем, кто подключается к нему (в зависимости от определенных для них прав доступа).

При подключении к экземпляру SQL Server соединение ассоциируется с определенной базой данных на сервере. Эта БД называется текущей. Обычно соединение устанавливается с базой данных по умолчанию, которую определяет системный администратор. Но, настроив параметры соединения из API баз данных, удается задать и другую БД. Можно переключаться с одной базы данных на другую с помощью оператора Transact-SQL USE <имя_БД> или функции API, которая меняет текущий контекст базы данных.

SQL Server 2000 позволяет отключить базу данных от одного экземпляра SQL Server, a затем подключить ее к другому экземпляру или вернуть обратно. При наличии файла с базой данных можно дать SQL Server указание подключать этот файл при установлении соединения под определенным именем.

Логические компоненты базы данных

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

Объекты баз данных

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

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

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

Тип данных  Атрибут, задающий тип информации, которая может храниться в столбце,

параметре или переменной. SQL Server поддерживает несколько системных типов данных; в дополнение к ним разрешается создавать пользовательские типы данных

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

Хранимая Откомпилированный набор операторов Transact-SQL, процедура хранимый

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

Функция Фрагмент кода, действующий как единая логическая сущность. Функцию

можно вызвать по имени, при этом разрешается задать ряд необязательных входных параметров. Она возвращает сведения о состоянии и необязательные выходные значения. Функции поддерживаются многими языками программирования, в том числе С, Visual Basic и Transact-SQL. В Transact-SQL имеется ряд встроенных функций, которые изменить невозможно, а также поддерживаются функции, которые создают и корректируют пользователи

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

Ограничение Свойство, назначаемое столбцу таблицы, которое позволяет предотвратить занесение недопустимых данных в столбец. Например, ограничения UNIQUE или PRIMARY_KEY предотвращают занесение значений, дублирующих существующие. Ограничение CHECK предотвращает занесение значения, не соответствующего критерию поиска, a NOT NULL — пустого значения

Правило  Объект БД, связанный со столбцами или с пользовательскими типами данных,

который задает значения данных, приемлемые в данном столбце. Лучше использовать ограничения CHECK, которые предоставляют ту же самую функциональность и соответствуют стандарту SQL-92

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

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

Режимы сопоставления

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

Различные объекты одной и той же базы данных SQL Server 2000 могут использовать разные режимы сопоставления. В SQL Server 2000 разрешается задавать отдельные режимы сопоставления вплоть до уровня столбцов, а каждому столбцу таблицы — присваивать различные режимы сопоставления. Более ранние версии SQL Server поддерживают только один режим сопоставления для каждого экземпляра SQL Server. У всех баз данных и их объектов, которые создаются в экземпляре SQL Server 7.0 или более ранней версии, режим сопоставления одинаков.

SQL Server 2000 поддерживает несколько режимов сопоставления, которые определяют правила использования символов для языка (например, македонского или польского) или для алфавита (например, Latin l_General — для латинского алфавита, который составляет основу письменности народов Западной Европы).

Каждый режим сопоставления SQL Server определяет три свойства:

порядок сортировки данных Unicode-типов (nchar, nvarchar и ntext);

порядок сортировки данных не-Unicode (char, varchar и text);

кодовую страницу для хранения символьных данных в формате, отличном от Unicode.

Примечание Для типов данных Unicode (nchar, nvarchar и ntext) невозможно задать эквивалент кодовой страницы. Двухбайтовые комбинации, используемые для кодировки символов Unicode, определены стандартом Unicode и не могут быть изменены.

Режимы сопоставления SQL Server 2000 задаются на любом уровне. При установке для экземпляра SQL Server 2000 можно задать режимы сопоставления по умолчанию. Во время создания базы данных стоит задать для нее режимы сопоставления по умолчанию; если этого не сделать, то режимами сопоставления по умолчанию для базы данных станут те, что определены для экземпляра. При определении каждого символьного столбца, переменной или параметра разрешается задать режимы сопоставления по умолчанию. Если это не сделано, при создании объекта будут взяты режимы сопоставления по умолчанию

для базы данных.

Идентификаторы пользователей, учетные имена, роли и группы

В основе механизмов безопасности SQL Server 2000 лежат идентификаторы пользователей, учетные имена, роли и группы. Пользователи, которые подключаются к SQL Server, должны указать свой идентификатор. Затем они смогут просматривать только те таблицы и запускать лишь те хранимые процедуры и административные функции, для которых у них есть соответствующие права. Такая система безопасности основана на использовании идентификаторов пользователей. Все типы механизмов безопасности описаны в таблице 1.1.

Таблица 1.1

Механизмы безопасности.

Таблица 1.1.Механизм безопасности

Описание

Идентификаторы пользователей

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

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

Пользователь не сможет подключиться к SQL Server, не указав сначала

допустимый идентификатор. Идентификаторы задаются членами

фиксированной серверной роли sysadmin

Учетные имена

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

доступа и собственности на объекты БД контролируются на основе учетного имени. Пользовательские учетные имена уникальны для базы данных, например учетное имя xyz в БД продаж отличается от учетного имени xyz в складской БД, хотя у обоих учетных имен один и тот же идентификатор. Учетные имена определяется членами фиксированной роли базы данных db_owner

Роли

Роль напоминает группу пользователей домена Windows 2000.

Она позволяет объединять пользователей в группу, а следовательно, применять права доступа к этим пользователям как к единому целому. Предоставление прав доступа, отказ в их предоставлении, отзыв прав доступа также осуществляется в отношении всех членов роли. Можно установить роль, описывающую задания, которые должны выполнять сотрудники вашей организации, занимающие определенную должность, и предоставить этой роли соответствующие права доступа. При подключении к этому заданию новый работник просто добавляются к членам роли. Когда работник перестает выполнять это задание, его можно удалить из роли. Если их несколько, то вам не придется многократно предоставлять, отказывать и отзывать права доступа для каждого из них. Права доступа применяются автоматически, как только пользователь становится членом роли. Роль похожа на группу в системе безопасности Windows

Группы

В SQL Server 2000 и SQL Server 7.0 группы отсутствуют. Однако

предусмотрено управление безопасностью SQL Server на уровне целой группы Windows NT или Windows 2000

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

Страницы и экстенты

Основной единицей хранения данных в SQL Server является страница. В SQL Server 2000 размер страницы составляет 8 кб. Другими словами, в базах данных SQL Server 2000 содержится 128 страниц на 1 Мб.

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

Страницы содержат строки данных (кроме данных типа text, ntext и image, которые хранятся в отдельных страницах). Данные размещаются на странице последовательно и начинаются сразу же после заголовка. В конце страницы расположена таблица смещений строк. Она содержит по одному элементу для каждой строки, размещенной на странице; в каждом элементе записано, как далеко первый байт строки расположен от начала страницы. Последовательность элементов таблицы смещений строк является обратной относительно последовательности строк страницы.

Экстенты представляют собой единицу выделения памяти для таблиц и индексов. Размер экстента составляет восемь последовательных страниц, или 64 кб. Другими словами, в базах данных SQL Server 2000 приходится 16 экстентов на 1 Мб.

Страница данных Microsoft SQL Server

Рис. 1.4. Строки данных и элементы таблицы смещения строк на странице

Для повышения эффективности выделения памяти, SQL Server 2000 не выделяет для таблиц с небольшим объемом данных целые экстенты. В SQL Server 2000 имеется два типа экстентов:

однородные экстенты, принадлежащие одному объекту; лишь объект-владелец может
использовать все восемь страниц экстента;

смешанные экстенты, у которых может быть до восьми объектов-владельцев.

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

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

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

В базах данных SQL Server 2000 применяются файлы трех типов.

Основной файл данных.    База данных начинается с основного файла данных, который
указывает на другие файлы базы данных. У любой базы данных есть только один основной файл данных. Для основных файлов данных рекомендуется использовать расширение .
mdf.

Дополнительные файлы данных.    Это все файлы данных, кроме основного. У некоторых баз данных дополнительные файлы данных вообще отсутствуют, в то время как у других их несколько. Для дополнительных файлов данных рекомендуется использовать расширение .ndf.

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

Распределение и повторное использование памяти

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

В SQL Server для хранения сведений о распределении экстентов применяются карты распределения двух типов.

Глобальная карта распределения (Global Allocation Map, GAM). В страницах GAM регистрируются выделенные экстенты. Каждая GAM содержит сведения о более чем 64 000экстентах (или примерно о 4 Гб данных). В GAM каждому экстенту соответствует один бит. Если он равен 1, экстент свободен, а если 0 — занят.

Разделяемая глобальная карта распределения (Shared Global Allocation Map, SGAM).    Встраницах SGAM регистрируются экстенты, которые в данный момент используются как смешанные и в которых есть по крайней мере одна свободная страница. Каждая SGAM содержит информацию о 64 000 экстентах (или около 4 Гб данных). В SGAM каждому экстенту соответствует один бит. Если он равен 1, экстент используется как смешанный и у него есть свободные страницы. Если бит равен 0, экстент не используется как смешанный или, если он используется как смешанный экстент, все его страницы заняты.

Структура таблиц и индексов

SQL Server 2000 поддерживает индексы представлений. Первый индекс представления должен быть кластерным. Во время исполнения оператора CREATE INDEX для представления результирующий набор для этого представления сохраняется в базе данных, при этом используется структура, аналогичная структуре таблицы с кластерным индексом.

Строки с данными каждой таблицы или индексированного представления хранятся в наборе 8-килобайтных страниц данных. У каждой страницы данных есть 96-байтный заголовок, который содержит системную информацию, например идентификатор таблицы, которой принадлежит страница. Если используются страницы, связанные в список, то в заголовке страницы также находятся указатели на предыдущую и последующую страни цы. В конце страницы располагается таблица смещений строк.

Остаток страницы заполнен строками данных как показано на рисунке на

Рис. 1-5.    Организация страниц данных

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

• Кластерные таблицы.    Это таблицы с кластерным индексом. Строки данных хранятся в порядке, который определяется ключом кластерного индекса. Индекс реализован в виде сбалансированного дерева (В-дерева), которое поддерживает быстрое получение строк на основе значений их ключа кластерного индекса. Страницы на каждом уровне индекса, в том числе страницы на уровне листьев дерева, связаны в двусвязный список, но переход с одного уровня на другой осуществляется посредством ключа.

• Кучи.    Это таблицы без кластерного индекса. Строки данных хранятся без какого-либо определенного порядка, и последовательность страниц данных также не упорядочена. Страницы данных не организованы в связный список. Структура индексированных представлений аналогична структуре кластерных таблиц. SQL Server также поддерживает до 249 некластерных индексов для любой таблицы или индексированного представления. Некластерные индексы также имеют структуру В-дерева, но используют ее иначе, чем кластерные. Отличие в том, что некластерные индексы не влияют на порядок строк. Кластерные таблицы и индексированные представления хранят свои строки данных в порядке, который определяется ключом кластерного индекса. Некластерные индексы, определенные для таблицы, не влияют на совокупность страниц данных кучи. Страницы данных остаются в куче до тех пор, пока не будет определен кластерный индекс.

Структура журнала транзакций

У любой базы данных SQL Server 2000 есть журнал транзакций, в котором регистрируются все транзакции и модификации базы данных, выполненные в ходе каждой транзакции. Регистрация транзакций и сделанных в их ходе модификаций нужна для выполнения трех операций:

восстановления отдельных транзакций;

восстановления незавершенных транзакций во время запуска SQL Server;

повтора операций над восстановленной базой данных непосредственно до моментасбоя.

Серверные компоненты SQL Server 2000 получают операторы SQL от клиентов и обрабатывают их. На рис. 1-6 показаны основные компоненты, принимающие участие в обработке оператора SQL, полученного от клиента SQL Server 

Рис. 1-6.   Обработка оператора SQL, полученного от клиента

Протокол TDS

Клиенты посылают операторы SQL, используя протокол прикладного уровня SQL Server под названием поток табличных данных (TDS, Tabular Data Stream). SQL Server 2000 работает со следующими версиями TDS:

TDS 8.0 — используется клиентами под управлением клиентских компонентов SQL Server 2000. Клиенты TDS 8.0 поддерживают все функции SQL Server 2000;

TDS 7.0 — используется клиентами под управлением клиентских компонентов SQL Server 7.0. Клиенты TDS 7.0 не поддерживают новые функции SQL Server 2000, что
иногда вынуждает сервер соответствующим образом корректировать данные, отсылаемые обратно таким клиентам;

TDS 4.2 — используется клиентами под управлением клиентских компонентов SQL Server версий 6.5, 6.0 и 4.21а. Клиенты TDS 4.2 не поддерживают новые функции, добавленные в SQL Server 2000 и  7.0. Это также вынуждает сервер иногда корректировать данные, отсылаемые обратно этим клиентам.

Пакеты TDS создаются компонентом доступа Microsoft OLE DB для SQL Server, драйвером ODBC (Open Database Connectivity) SQL Server или динамически подключаемой библиотекой (dynamic link library, DLL) DB-Library. После этого пакеты TDS передаются клиентской библиотеке Net-Library SQL Server, которая инкапсулирует их в пакеты сетевого протокола. На сервере серверная библиотека Net-Library получает сетевые пакеты, извлекает из них пакеты TDS и передает их механизму реляционных баз данных. При возврате результатов клиентов этот процесс протекает в обратном направлении. Каждый сервер способен отслеживать несколько сетевых протоколов одновременно и  поддерживать по одной библиотеке Net-Library для каждого отслеживаемого протокола.

Сервер баз данных обрабатывает все запросы, которые передают ему серверные библиотеки Net-Library. Затем сервер компилирует все операторы SQL в планы исполнения, которые он использует для доступа к запрошенным данным и построения результирующего набора, возвращаемого клиенту.

Механизм реляционных баз данных SQL Server 2000 состоит из двух основных частей: реляционного механизма и механизма хранения. Одно из самых важных архитектурных изменений, внесенных в SQL Server 7.0 (и перешедших к SQL Server 2000), заключается в строгом разделении таких компонентов сервера, как реляционный механизм и механизм хранения, и использовании ими API OLE DB для взаимодействия друг с другом, как показано на рис. 1-7.

Рис. 1.7.    Компоненты механизма реляционной базы данных

Архитектура обработчика запросов

Операторы SQL — единственный вид команд, которые приложения посылают SQL Server 2000. Вся работа, выполняемая экземпляром SQL Server, является результатом обработки им SQL-операторов (получение, интерпретация и непосредственно исполнение). SQL Server 2000 исполняет операторы SQL с помощью одного из следующих процессов:

обработки одиночного оператора SQL;

пакетной обработки;

исполнения хранимой процедуры или триггера;

кэширования и повторного использования плана исполнения;

параллельной обработки запросов.

Архитектура памяти

SQL Server 2000 динамически выделяет и освобождает память по мере необходимости. Как правило, администратору не приходится указывать, сколько памяти надо выделить для SQL Server, хотя такая возможность все равно предусмотрена и в некоторых средах она бывает необходима. При работе нескольких экземпляров SQL Server на одном компьютере каждый экземпляр может динамически выделять и освобождать память в соответствии со своей загруженностью.

SQL Server 2000 Enterprise Edition поддерживает использование Windows 2000 AWE (Address Windowing Extensions), что позволяет экземплярам, работающим под управлением Windows 2000 Advanced Server, адресовать приблизительно 8 Гб памяти, а работающим под управлением Windows 2000 Data Center — приблизительно 64 Гб. Однако каждый экземпляр, использующий эту дополнительную память, должен статически выделять необходимую память.

Архитектура ввода-вывода

Главным назначением базы данных является хранение и поиск данных, поэтому выполнение многочисленных дисковых операций чтения/записи является одним из неотъемлемых атрибутов механизма баз данных. Дисковые операции ввода-вывода потребляют большой объем ресурсов, а их выполнение занимает относительного много времени. Значительная часть логики ПО реляционных СУБД служит для повышения эффективности операций ввода-вывода.

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

Ввод-вывод, осуществляемый экземпляром SQL Server, подразделяется на логический и физический. Логическое чтение происходит каждый раз, когда механизм баз данных запрашивает страницу из кэш-буфера. Если в настоящий момент страница отсутствует в кэш-буфере, выполняется физическое чтение, чтобы прочитать страницу в кэш-буфер. Если страница в данный момент находится в кэше, физическая операция чтения не выполняется — кэш-буфер использует ту страницу, которая уже хранится в памяти. При модификации данных страницы, которая находится в памяти, происходит логическая операция записи. При записи страницы на диск выполняется физическая операция записи. Страница может оставаться в памяти достаточно долго; так, перед физической записью ее на диск иногда выполняется несколько логических операций записи.

Архитектура полнотекстовых запросов

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

Поддержка индексации.    Служба реализует полнотекстовые каталоги и индексы, определенные для базы данных. Microsoft Search принимает определения полнотекстовых каталогов, а также таблицы и столбцы, составляющие индексы каждого каталога. Этот инструмент также реализует запросы на заполнение полнотекстовых индексов.

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

Полнотекстовые каталоги и индексы хранятся вне базы данных SQL Server в отдельных I файлах, управляемых службой Microsoft Search. Файлы полнотекстового каталога не восстанавливаются во время операции восстановления SQL Server. Их также нельзя резервировать и восстанавливать с помощью операторов Transact-SQL BACKUP и RESTORE. По завершении операций восстановления необходимо отдельно синхронизировать полнотекстовые каталоги. Файлы полнотекстового каталога доступны только службе Microsoft Search или системному администратору Windows NT или Windows 2000.

Организация транзакций

SQL Server 2000 управляет согласованностью и целостностью каждой базы данных при появлении ошибок. Любое приложение, обновляющее информацию в базе данных SQL Server, делает это с помощью транзакций. Транзакция — логическая единица работы, состоящая из последовательности операторов (выборки, добавления, обновления или удаления). Если во время транзакции не возникли ошибки, все изменения базы данных, сделанные в результате выполнения транзакции, становятся постоянными. Если ошибки есть, то в базу данных не вносится никаких изменений. Транзакция имеет несколько фаз.

  1.  Перед началом транзакции база данных находится в согласованном состоянии.
  2.  Приложение объявляет о начале транзакции. Этот процесс может быть инициирован с помощью оператора BEGIN TRANSACTION. В качестве альтернативы приложение может установить параметры для работы в неявном режиме транзакций. Первый же оператор Transact-SQL, исполняемый после завершения предыдущей транзакции, автоматически запускает новую транзакцию. В начале транзакции в журнал не
    заносится никаких записей; первая запись в журнале появляется при модификации данных.
  3.  Приложение начинает модификацию данных. Модификации в таблицах производятся по очереди. Если выполняется серия модификаций, то в результате база данных может какое-то время оставаться в несогласованном промежуточном состоянии.
  4.  Когда приложение достигает этапа, на котором все модификации успешно завершены, и аза данных снова находится в согласованном состоянии, приложение фиксирует транакцию. На этом этапе все сделанные модификации базы данных становятся постоянными.
  5.  Если приложение обнаруживает ошибки, которые предотвращают завершение транзакции, оно выполняет отмену (откат) всех модификаций данных. Этот процесс возвращает базу данных к той точке согласованности, в которой она находилась до начала транзакции.

Приложения SQL Server также могут работать в режиме автоматической фиксации. В этом режиме исполнение каждого оператора Transact-SQL фиксируется, если оно успешно, или автоматически откатывается, если возникает ошибка. Приложению, работающему в режиме автофиксации, нет необходимости использовать специальные операторы для начала и завершения транзакции.

Все операторы Transact-SQL выполняются в составе транзакций: явных, неявных или с автоматической фиксацией. Все транзакции SQL Server, включающие в себя операции по модификации данных, достигают новой точки согласованности и фиксируются или откатываются к исходной точке согласованности. Транзакции не остаются в промежуточном состоянии, в котором база данных не согласована. Инфраструктура распределенного управления SQL (SQL Distributed Management Framework, SQL-DMF) интегрирована и состоит из объектов, служб и компонентов, используемых для управления SQL Server 2000. SQL-DMF — гибкая и масштабируемая модель, способная адаптироваться к требованиям организации. Этот инструмент снижает необходимость присутствия пользователя при выполнении некоторых задач, например при создании резервных копий базы данных и рассылки оповещений, поддерживая сервисы, взаимодействующие непосредственно с SQL Server 2000.

Ключевые компоненты SQL-DMF поддерживают профилактическое управление экземплярами SQL Server в сети, позволяя определять следующую информацию:

все объекты SQL Server и права доступа к ним;

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

корректирующие действия, которые следует выполнять при возникновении определенных условий.

Основные компоненты SQL-DMF показаны на рис. 1-8.

Рис. 1.8. Основные компоненты SQL - DMF

Хранилища данных и оперативная аналитическая обработка (OLAP)

SQL Server 2000 содержит компоненты, позволяющие создавать хранилища и киоски данных. Хранилища и киоски данных можно использовать в сложных промышленных системах искусственного интеллекта, обрабатывающих запросы, которые применяются для выявления трендов и анализа критических факторов. Эти системы называются системами оперативной аналитической обработки (Online Analytical Processing, OLAP). Данные в хранилищах и киосках данных организованы иначе, чем в традиционных базах данных, работающих с транзакциями.

ПО управления реляционными базами данных промышленного уровня, например SQL Server 2000, изначально разрабатывалось для централизованного хранения данных, генерируемых ежедневными транзакциями крупных компаний или правительственных организаций. За десятилетия эволюции эти СУБД превратились в высокоэффективные системы регистрации данных, необходимые для ежедневной работы предприятия. Такие системы получили название систем оперативной обработки транзакций (Online Transaction-Processing, OLTP).

OLTP-системы

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

регистрация заказа, введенного с кассового терминала или через Web-узел;

размещение заказа на комплектующие изделия, когда их количество на складе становится меньше определенного числа;

отслеживание компонентов во время сборки конечного продукта на производстве;

регистрация сведений о работниках;

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

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

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

OLAP-системы

Системы для обработки запросов, предназначенных для выявления трендов и критических факторов, называются OLAP-системами. Как правило, запросы OLAP извлекают большой объем данных. Например, начальнику правительственного отдела по выдаче водительских прав, может понадобиться отчет по маркам и моделям автомобилей, которые регистрировались ежегодно в течение последних 20 лет. При выполнении запроса такого типа в системе OLTP:

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

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

Другая проблема в том, что на многих больших предприятиях существует несколько OLTP-систем, регистрирующих данные всех транзакций. Многие из них создавались в разное время и на основе различного программного и аппаратного обеспечения. Очень часто коды и имена, которые используются для идентификации элементов одной системы, отличаются от таковых в другой системе. Менеджерам, выполняющим запросы OLAP, в общем случае необходимо предоставить возможность работы с данными из нескольких систем OLTP.

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

Архитектура разработки приложений

Для доступа к базе данных приложения используют два компонента: API или URL (Uniform Resource Locatorунифицированный указатель ресурса), а также язык баз данных.

API и URL

API определяет способ программирования приложения для подключения к базе данных и передаче ей команд. Объектная модель API обычно не зависит от языка и определяет набор объектов, свойств и интерфейсов. API С или Visual Basic определяет набор функций для приложений, написанных на С, C++ или Visual Basic.

URL представляет собой строку или поток, с помощью которого Интернет приложение получает доступ к ресурсам Интернетаили интрасети. Microsoft SQL Server 2000 поддерживает библиотеку IS API (Internet Server Application Programming Interface), которую приложения IIS (Microsoft Internet Information Services) используют для создания URL, указывающих на экземпляры SQL Server 2000.

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

ActiveX Data Objects (ADO);

OLEDB;

ODBC и объектные API, построенные на его основе — Remote Data Objects (RDO) и
Data-Access Objects (DAO);

Embedded SQL для С (ESQL);

библиотека DB-Library для API С, разработанная специально для использования с ранними версиями SQL Server, предшествовавшими стандарту SQL-92.

Интернет приложения также могут применять URL, указывающие на виртуальные корневые каталоги IIS, которые ссылаются на экземпляр SQL Server. Зачастую URL содержит XPath-запрос, оператор Transact-SQL или шаблон. В дополнение к использованию URL, Интернет-приложения способны работать с данными в форме документов XML с помощью ADO или OLE DB.

Язык баз данных

Синтаксис команд, которые посылаются базе данных, определяется языком баз данных. Эти команды пересылаются через API, что дает приложениям возможность обращаться к данным и модифицировать их. Они также позволяют приложениям создавать и модифицировать объекты базы данных. Исполнение всех команд зависит от прав доступа, предоставленных пользователю. SQL Server 2000 поддерживает два языка: Transact-SQL и запросы XPath со схемами соответствия, применяемые в Интернет-приложениях, работающих в IIS.

Transact-SQL — это язык баз данных, поддерживаемый SQL Server 2000. Transact-SQL соответствует стандарту SQL-92 начального уровня, а также поддерживает некоторые функции промежуточного и полного уровней. Transact-SQL также содержит некоторые мощные расширения по сравнению со стандартом SQL-92.

Расширения определены в спецификации ODBC и поддерживаются OLE DB. Transact-SQL поддерживает расширения ODBC в приложениях, использующих API ADO, OLE DB или ODBC, или API, расположенные поверх ODBC. Расширения ODBC SQL не поддерживаются в приложениях, использующих API DB-Library или Embedded SQL.

SQL Server 2000 поддерживает подмножество языка XPath, определенного World Wide Web Consortium (W3C). XPath — это язык навигации по графам, который нужен для выбора узлов из документов XML. Прежде всего, с помощью схемы соответствия определяется представление данных из одной или нескольких таблиц или представлений SQL Server в виде XML, а затем с помощью запросов XPath можно получать данные из этой схемы соответствия.

Запросы XPath обычно используются в URL или API ADO. API OLE DB также поддерживает запросы XPath.

1.4. ИНСТРУМЕНТЫ ПРОГРАММИРОВАНИЯ SQL SERVER

SQL Server 2000 предоставляет набор инструментальных средств, позволяющих с помощью языка Transact-SQL взаимодействовать с базами данных SQL и хранящейся в них информацией. В набор входят SQL Query Analyzer и утилиты командной строки isqlw, isql и osql. На этом занятии вы познакомитесь с каждой из этих утилит, узнаете, как и в каких случаях их следует применять. На следующих занятиях рассказано, как управлять базами данных и хранящейся в них информацией с помощью операторов языка Transact-SQL.

SQL Query Analyzer

SQL Query Analyzer представляет собой графический интерфейс пользователя (GUI), позволяющий интерактивно конструировать, тестировать и исполнять операторы Transact-SQL, хранимые процедуры, пакеты и сценарии. SQL Query Analyzer можно запустить в SQL Enterprise Manager или непосредственно из меню Start, а также из командной строки, выполнив команду isqlw (далее по ходу занятия утилита isqlw .

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

Окно Query

Окно Query разделено на две панели: Editor и Results. При первом запуске SQL Query Analyzer автоматически выводится только панель Editor, Панель Results выводится автоматически при запуске запроса Transact-SQL. Панель Results также можно открывать и закрывать вручную, щелкая кнопку Show Results Pane на панели инструментов.

Панель Editor в SQL Query Analyzer содержит различные инструменты, предназначенные для создания и редактирования операторов Transact-SQL, в том числе стандартные команды редактирования Undo, Cut, Copy, Paste и Select All. Также можно выполнять поиск текста с заменой, перемещать курсор в заданную строку, вставлять и удалять отступы абзацев и знаки комментария.

Кроме того, при просмотре разделов справки по языку Transact-SQL в SQL Server Books Online можно копировать оттуда на панель Editor примеры использования синтаксиса, что весьма полезно при создании операторов Transact-SQL. Также предусмотрены средства для сохранения определений запросов и других сценариев SQL — они пригодятся для повторного использования, а также для создания шаблонов (которые представляют собой ; стереотипы сценариев, создающих объекты в базе данных).

Цветовая кодировка в Query Analyzer

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

Цвет

Категория

Красный

Символьная строка

Темно - красны

Хранимая процедура

Зеленый

Системная таблица

Темно - зеленый

Комментарий

Фиолетовый

Системная функция

Синий

Ключевое слово

Серый

Оператор

Можно изменить цвета, заданные по умолчанию, выбрав в диалоговом окне Options вкладку Fonts.

При устранении ошибок в сценариях Transact-SQL следует руководствоваться цветовой кодировкой. Например, если набранное ключевое слово не подсвечивается синим цветом, возможно, в нем допущена орфографическая ошибка или набрано неверное ключевое слово (если вы оставили параметры по умолчанию). Или, когда слишком много исходного текста подсвечено красным, то в какой-то символьной строке, вероятно, не закрыты кавычки.

Исполнение операторов Transact-SQL

В SQL Query Analyzer разрешается исполнять как целый сценарий, так и отдельные операторы SQL:

Чтобы исполнить хранимую процедуру, следует ввести на панели Editor запускающий хранимую процедуру оператор и нажать F5. Если исполняющий процедуру оператор расположен в начале пакета, то оператор EXECUTE (или EXEC) можно опустить, в противном случае он необходим.

Панель Results

При исполнении оператора Transact-SQL, на панели Results выводится выходная информация запроса (результирующий набор). На панели Results могут располагаться различные вкладки. Отображаемые вкладки определяются параметрами, заданными для интерфейса. По умолчанию выводятся только вкладки Grids (которая является активной) и Messages.

Вкладка Grids

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

Одновременно с вкладкой Grids всегда присутствует вкладка Messages, где выводятся сообщения, сгенерированные при исполнении запроса. Вкладка Results

Как и на вкладке Grids, на вкладке Results выводится результирующий набор, сгенерированный в результате исполнения оператора Transact-SQL. Однако на вкладке Results он выводится не в виде сетки, а в виде текста.

При использовании вкладки Results вкладка Messages не выводится. Все сообщения, сгенерированные при исполнении данного запроса, отображаются на вкладке Results ниже результирующего набора (за исключением тех случаев, когда при исполнении запроса возникает ошибка; в этом случае вкладка Results содержит лишь сообщение об ошибках).

Можно вывести вкладку Results или Grids, но не обе одновременно. Поскольку по умолчанию отображается вкладка Grids, то, чтобы открылась вкладка Results, необходимо соответствующим образом сконфигурировать SQL Query Analyzer. После этого до конца сеанса работы или пока вы снова не измените конфигурацию SQL Query Analyzer для вывода результатов запросов будет использоваться вкладка Results. Если закрыть SQL Query Analyzer и снова запустить его, то на панели Results вновь откроется вкладка Grids.

Результаты можно не только выводить на вкладках Grids или Results, но и посылать в файл.

Окно Object Browser

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

Objects — содержит список объектов базы данных и список стандартных объектов таких как встроенные функции и базовые типы данных;

Templates — предоставляет доступ к папке шаблонов Templates.

Окно отладчика Transact-SQL

SQL Query Analyzer оснащен отладчиком Transact-SQL, который позволяет управлять, отслеживать исполнение хранимых процедур. Отладчик поддерживает традиционные функции, такие, как установка точек останова, определение просматриваемых Bbipaxet пошаговое исполнение процедур.

Отладчик Transact-SQL из SQL Query Analyzer поддерживает отладку в SQL Server SQL Server 7.0 и SQL Server 6.5 Service Pack 2.

Отладчик Transact-SQL разрешается запускать только из-под SQL Query Analyzer. После запуска отладчик располагается в окне этого приложения.

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

SQL Query Analyzer не поддерживает несколько экземпляров отладчика. При попытке отладки второй хранимой процедуры, SQL Query Analyzer попросит отменить сеанс отладки, активный в текущий момент.

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

Окно отладчика Transact-SQL Debugger состоит из панели инструментов, строки состояния и нескольких окон. Многие из этих компонентов выполняют двойные функции, их можно использовать и как средства управления и мониторинга.

Иногда после завершения или отмены процедуры функциональность некоторых из этих компонентов снижается. Например, налагается запрет на установку точки останова или просмотр содержимого окон переменных, когда процедура остановлена.

Окно Open Table

Окно Open Table выводит столбцы и строки таблицы с помощью сетчатой формы. Можно модифицировать данные формы, а также вставлять и удалять строки.

Окно Object Search

Средства окна Object Search позволяют осуществлять поиск объектов на текущем сервере баз данных. Окно Object Search появляется при нажатии кнопки на панели инструмента или выполнении команды Object Search из меню Tools.

Утилита командной строки isqlw

Утилита isqlw (SQL Query Analyzer) позволяет вводить операторы Transact-SQL, системные хранимые процедуры и сценарии. Для запуска SQL Query Analyzer с предварительно заданными параметрами можно настроить ярлык или создать пакетный файл.

С утилитой isqlw работают как посредством интерфейса, так и без него. Чтобы запустить isqlw без пользовательского интерфейса, задайте корректную регистрационную информацию (экземпляр SQL Server 2000 с доверенным соединением или корректный регистрационный идентификатор с паролем), входной и выходной файлы. Утилита isqlw обрабатывает содержимое входного файла и помещает результат в выходной файл.

Если входной и выходной файлы не заданы, isqlw запускается в интерактивном режиме (запускает SQL Query Analyzer). Если указана допустимая регистрационная информация, isqlw подключается непосредственно к экземпляру SQL Server 2000. Если указанной информации недостаточно для подключения, то выводится диалоговое окно Connect To SQL Server.

Утилита isqlw и SQL Query Analyzer применяют интерфейс прикладного программирования (API) ODBC (Open Database Connectivity), который использует параметры драйвера SQL Server ODBC, заданные по умолчанию (SQL-92).

Утилита командной строки isql

Утилита isql также позволяет вводить операторы Transact-SQL, системные процедуры и сценарии. Эта утилита взаимодействует с SQL Server 2000 посредством DB-Library.

Как и большинство приложений, использующих DB-Library, утилита isql не применяет никаких параметров соединения по умолчанию. Чтобы задать параметры соединения, необходимо ввести операторы SET интерактивно или в сценарии.

Утилита isql запускается непосредственно из ОС, при этом можно задать чувствительные к регистру переключатели. После запуска isql принимает операторы Transact-SQL и посылает их SQL Server 2000. Результаты форматируются и выводятся на стандартное устройство вывода (на экран). Для выхода из isql служат команды QUIT и EXIT.

Если при запуске isql не указать имя пользователя, SQL Server 2000 проверяет переменные окружения и использует их значения, например isqluser=(пoльзoвaтeль) или isqlserver=(cepBep). Если переменные окружения не установлены, применяется имя пользователя рабочей станции, если имя сервера не задано — имя рабочей станции.

Когда ни один из параметров (-U и -Р) не задан, SQL Server 2000 пытается установить соединение в режиме Windows Authentication. Аутентификация проводится на основе учетной записи Windows пользователя, запустившего isql. Результаты выводятся сразу после завершения исполнения. В случае isql в одной строке выводится максимум 1000 символов. Длинные операторы необходимо разбивать на несколько строк.

Утилита командной строки osql

Утилита osql служит для ввода операторов Transact-SQL, системных процедур и сценариев. Эта утилита взаимодействует с сервером с помощью ODBC.

Утилита osql запускается непосредственно из ОС с чувствительными к регистру переключателями. После запуска osql интерактивно принимает операторы Transact-SQL и посылает их SQL Server 2000. Результаты форматируются и выводятся на экран. Для выхода применяют команды QUIT и EXIT.

Как и в случае утилиты isql, если при запуске osql не задано имя пользователя, SQL Server 2000 проверяет значения переменных окружения и использует их. Утилита osql применяет API ODBC, а также параметры драйвера ODBC SQL Server, заданные по умолчанию для соединения SQL Server 2000 (SQL-92).

1.5.ЯЗЫК TRANSACT - SQL

Transact-SQL занимает центральное место в использовании SQL Server. Любое приложение взаимодействующее с SQL Server, независимо от его пользовательского интерфейса, посыла серверу операторы Transact-SQL.

В SQL Server Books Online входит полный справочник по операторам Transact-SQL. В данном пособии описан каждый элемент Transact-SQL и приводятся примеры, иллюстрирующие способ применения этого элемента в операторах Transact-SQL. Чтобы облегчить с здание операторов Transact-SQL, можно копировать примеры синтаксиса из справочки на панель редактора SQL Query Analyzer. На панели Editor можно выбрать оператор Transact-SQL, функцию, хранимую процедуру или другой элемент Transact-SQL и, нажать сочетание Shift+Fl, просмотреть информацию о выбранном фрагменте текста.

Операторы Transact-SQL

Оператор Transact-SQL представляет собой набор команд, который выполняет некоторые действия над объектами базы данных или хранящимися в ней данными. SQL Server поддерживает три типа операторов Transact-SQL: DDL, DCL и DML.

Язык определения данных

С помощью языка определения данных (Data Definition Language, DDL), который обычно является частью СУБД, определяются и управляются все атрибуты базы данных, в том числе структура строк, определения столбцов, ключевые столбцы, расположение файлов и структура хранения данных. Операторы DDL поддерживают определение (объявление) объектов данных, например баз данных, таблиц и представлений. Язык DDL Transact-SQL, который используется для управления объектами, основан на операторах DDL SQL-92 (с некоторыми расширениями). Для каждого класса объектов обычно поддерживаются операторы ALTER и DROP (например, CREATE TABLE, ALTER TABLE и DROP TABLE).

Большинство операторов DDL выглядят так:

CREATE имя_объекта

ALTER имя_объекта

DROP имя_объекта

Следующие три примера иллюстрируют создание, изменение и удаление таблиц с помощью ключевого слова Transact-SQL CREATE. Однако использование ключевого слова CREATE не ограничено объектами типа «таблица».

CREATE TABLE

Оператор CREATE TABLE создает таблицу в существующей базе данных. Следующий оператор создает в базе данных Northwind таблицу под названием Importers. В таблице будет три столбца: CompanylD, CompanyName и Contact.

USE Northwind

CREATE TABLE Importers

(

CompanylD int NOT NULL, CompanyName varchar(40) NOT NULL, Contact varchar(40) NOT NULL

ALTER TABLE

Оператор ALTER TABLE позволяет модифицировать определение таблицы посредством изменения, добавления или удаления столбцов и ограничений, а также активации и деактивации ограничений и триггеров. Следующий оператор изменит таблицу Importers в базе данных Northwind, добавив в нее столбец под названием ContactTitle.

USE Northwind

ALTER TABLE Importers

ADD ContactTitle varchar(20) NULL

DROP TABLE

Оператор DROP TABLE удаляет определение таблицы со всеми ее данными, индексами, триггерами, ограничениями и правами доступа. Любое представление или хранимую процедуру, которые ссылаются на удаленную таблицу, необходимо удалить в явном виде с помощью операторов DROP VIEW или DROP PROCEDURE. Следующий оператор удаляет таблицу Importers из базы данных Northwind.

USE Northwind

DROP TABLE Importers

Язык управления данными

Язык управления данными применяется для управления правами доступа к объектам базы данных. Управление правами доступа осуществляется с помощью операторов SQL-92 GRANT и REVOKE, а также оператора Transact-SQL DENY. GRANT

Оператор GRANT создает в системе безопасности запись, которая дает возможность пользователю текущей базы данных работать с хранящейся в базе информацией или исполнять определенные операторы Transact-SQL. Следующий оператор предоставляет роли Public право доступа SELECT для таблицы Customers из базы данных Northwind:

USE Northwind

GRANT SELECT'Л

ON Customers TO PUBLIC

REVOKE

Оператор REVOKE аннулирует ранее предоставленное или запрещенное пользователю разрешение для текущей базы данных. Следующий оператор аннулирует право доступа SELECT для таблицы Customers из базы данных Northwind у роли Public:

USE Northwind REVOKE SELECT ON Customers TO PUBLIC

DENY

Оператор DENY создает в системе безопасности элемент, который отказывает учетной записи в предоставлении права доступа в текущей базе данных и предотвращает наследование учетной записью этого права доступа посредством членства в группе или роли.

USE Northwind DENY SELECT ON Customers TO PUBLIC

Язык манипулирования данными

Язык манипулирования данными используется для выборки, добавления, обновления и

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

SELECT

Оператор SELECT осуществляет выборку строк в базе данных и позволяет выбрать одну

или несколько строк в одной или нескольких таблицах. Следующий оператор выбирает

значения CustomerlD, CompanyName и ContactName для компаний, значение CustomerlD

которых равно alfki или anatr. Результирующий набор упорядочивается по значению

ContactName:

USE Northwind

CompanyName,  ContactName

OR CustomerlD =  'anatr')

SELECT CustomerlD,

FROM Customers

WHERE (CustomerlD =  'alfki'

ORDER BY ContactName

INSERT

Оператор INSERT добавляет в таблицу или представление новую строку. Следующий оператор добавляет строку в таблицу Territories базы данных Northwind. Значение TerritorylD для новой строки равно 98101, TerritoryDescription равно Seattle, a RegionID2.

USE Northwind

INSERT INTO Territories

VALUES (98101,   'Seattle',   2)

Ключевое слово INTO является необязательным. Его можно указать между INSERT и именем целевой таблицы для улучшения читабельности.

UPDATE

Оператор UPDATE служит для изменения данных в таблицах. Следующий оператор обновляет в таблице Territories (в БД Northwind) строку, значение TerritorylD которой равно 98101. Значение TerritoryDescription будет изменено на Downtown Seattle.

USE Northwind

UPDATE Territories

SET TerritoryDescription = 'Downtown Seattle'

WHERE TerritorylD = 98101

DELETE

Оператор DELETE удаляет строки из таблицы. Следующий оператор удаляет из таблицы Territories (БД Northwind) строку со значением TerritorylD, равным 98101.

USE Northwind

DELETE FROM Territories

WHERE TerritorylD = 98101

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

Идентификаторы

Имя объекта базы данных называется идентификаторам. В SQL Server любой объект может иметь идентификатор: серверы, базы данных и их объекты (таблицы, представления, столбцы, индексы, триггеры, процедуры, ограничения и правила). Для большинства объектов идентификаторы необходимы, в то время как для некоторых объектов (например, для ограничений) они не обязательны.

Идентификатор объекта создается при определении объекта. После этого на объект ссылаются с помощью идентификатора.

Классы идентификаторов

Существуют два класса идентификаторов: обычные и с ограничителями.

Обычные идентификаторы

В обычных идентификаторах ограничители не применяются. В следующем операторе SELECT присутствует два идентификатора: ТаЫеХ и KeyCol (ни один из которых не является идентификатором с ограничителями).

SELECT *  FROM TableX WHERE KeyCol = 124

Более подробно о формате идентификаторов рассказано в SQL Server Books Online.

Идентификаторы с ограничителями

Идентификаторы с ограничителями заключаются в двойные кавычки (" ") или квадратные скобки ([ ]). Идентификаторы, соответствующие правилам форм атирования идентификаторов, бывают как с ограничителями, так и обычными идентификаторами. В следующем операторе SELECT ограничители (квадратные скобки) не обязательны: SELECT * FROM [TableX] WHERE [KeyCol] = 124

В операторах Transact-SQL идентификаторы, не полностью соответствующие правилам форматирования, необходимо помещать между ограничителями. В следующем операторе SELECT идентификатор My Table необходимо использовать с ограничителями, поскольку он содержит пробел. Идентификатор Order также следует использовать с ограничителями, поскольку он совпадает с зарезервированным ключевым словом.

SELECT * FROM [My Table] WHERE [Order] = 10

Переменные

Переменная языка Transact-SQL представляет собой объект в пакетах Transact-SQL и сценариях, который может иметь определенное значение. После того как переменная определена (объявлена), один оператор Transact-SQL из пакета может установить ее значение, а другой — позже получить значение этой переменной. Следующие операторы Transact-SQL объявляют переменную с именем EmpIDVar, устанавливают ее значение равным 3, а затем используют эту переменную в операторе SELECT:

USE Northwind

DECLARE ШирШаг INT

SET @EmpIDVar = 3

SELECT * FROM Employees

WHERE EmployeelD = @EmpIDVar + 1

Как правило, переменные в пакетах и сценариях выполняют следующие функции:

являются счетчиками циклов;

хранят значение, анализируемое операторами, управляющими ходом выполнения программы;

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

Часто переменные используются в качестве счетчиков в операторах WHILE и LOOP, a также блоках IF...ELSE в пакетах или процедурах. Переменные можно применять только в выражениях, они не должны совпадать с именами объектов и ключевыми словами.

Функции

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

SQL Server2000 поддерживает функции двух типов:

встроенные функции — их работа определена в Transact-SQL, и ее нельзя модифицировать. Эти функции разрешается вызывать только в операторах Transact-SQL;

пользовательские функции — позволяют пользователям определять собственные функции Transact-SQL с помощью оператора CREATE FUNCTION.

Встроенные функции

Язык программирования Transact-SQL содержит три типа встроенных функций: функции получения набора строк, агрегатные и скалярные функции.

Функции получения набора строк

Функции получения набора строк можно применять в операторах подобно ссылкам на : таблицу. Они возвращают объект, который разрешается использовать в операторе Transact-SQL вместо ссылки на таблицу. Например, функция OPENQUERY является функцией получения набора строк, которая исполняет транзитный запрос на данном связанном сервере, представляющем собой источник данных OLE DB. В конструкции FROM на функцию OPENQUERY можно ссылаться так же, как на имя таблицы.

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

Агрегатные функции

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

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

списке выбора оператора SELECT (в подзапросе или внешнем запросе);

COMPUTE или COMPUTE BY;

HAVING.

Все агрегатные функции, за исключением COUNT, игнорируют пустые значения. Агрегатные функции часто применяются с конструкцией GROUP BY в операторе SELECT.

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

Скалярные функции

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

Табл.1.2.

Скалярные функции

Категория скалярной функции

Описание

Функции конфигурации

Возвращает сведения о текущей конфигурации

Функции курсора

Возвращают сведения о курсорах

Функции времени и даты

Выполняют различные действия над входными значениями времени и даты и возвращает строковое, числовое значение или значения в формате даты и времени

Математические

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

Функции метаданных

Возвращают сведения о БД и ее объектах

Функции безопасности

Возвращают информацию о пользователях и ролях

Строковые функции

Выполняют операции над стоками (char или varchar) и возвращают строковые или числовые значения

Системные функции

Выполняют операции и возвращают сведения о значениях, объектах и настройках SQL server

Функции системной статистики

Возвращают статистические данные о системе

Функции для работы с текстом и изображениями

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

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

Пользовательские функции

Оператор CREATE FUNCTION позволяет создавать пользовательские функции, оператор ALTER FUNCTION — модифицировать их, а оператор DROP FUNCTION — удалять. Любое полное имя пользовательской функции (имя_БД.имя_владельца.имя_функции) должно быть уникальным.

Пользовательская функция способна принимать различное число параметров (от 0 до 1024) и возвращать как скалярное значение, так и таблицу. Если у параметра функции есть значение по умолчанию, то, чтобы задать его при вызове функции, следует ввести ключевое слово DEFAULT. Этим параметры пользовательских функций отличаются от параметров хранимых процедур, в которых при отсутствии параметра также подразумевается значение по умолчанию. Пользовательские функции не поддерживают выходные параметры.

Типы пользовательских функций

Язык программирования Transact-SQL поддерживает два типа пользовательских функций: скалярные и табличные.

Скалярные функции

Скалярные функции возвращают единственное значение, тип которого определяется конструкцией RETURNS. При этом можно использовать все скалярные типы данных, включая bigint и sql_variant.

Тип данных timestamp, пользовательские и нескалярные типы данных (например, «таблица» или «курсор») не поддерживаются. Тело функции, определенное в блоке BEGIN ...END, содержит набор операторов Transact-SQL, которые возвращают значение функции. Функция способна возвращать значение любого типа, кроме text, ntext, image, cursor и timestamp.

Табличные функции

Табличные функции возвращают таблицы. Существуют табличные функции двух типов: подставляемые и много операторные. У подставляемых табличных функций тело отсутствует, а возвращаемая таблица представляет собой результирующий набор единственного оператора SELECT. Тело многооператорных функций, определенное в блоке BEGIN...END, состоит из операторов Transact-SQL, выполняющих формирование строк и размещение их в возвращаемой таблице.

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

Детерминированность функций

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

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

Функции конфигурации, курсоров, метаданных, безопасности и системной статистики (а также другие встроенные функции) относятся к недетерминированными.

Типы данных

Тип данных — это атрибут, определяющий тип информации, которую может содержать объект. У всех столбцов, параметров, переменных, функций, возвращающих значения и хранимых процедур с кодами возврата есть определенные типы данных. В Transact-SQL имеется несколько базовых типов данных, например varchar, text и int. Все данные, хранящиеся в SQL Server, должны быть совместимыми с одним из этих базовых типов. Создаваемые пользовательские типы данных всегда определяются на основе базовых типов данных.

Выражения

Выражение — это комбинация идентификаторов, значений и операторов, которые SQL Server может обработать, чтобы получить результат. Известно несколько различных способов использования выражений при обращении к данным и при их модификации. Например, выражение может быть частью запроса или определять условие для поиска данных, соответствующих некоторому набору критериев.

Использование операторов в выражениях

Операторы позволяют выполнять различные операции: арифметические, сравнения, конкатенации или присваивания. Например, можно проверить данные, чтобы выяснить, не пусто ли поле, в котором содержатся сведения о национальной принадлежности ваших клиентов (или не равно ли оно NULL).

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

В SQL Server предусмотрены семь категорий операторов. Все они описаны в таблице 1.3.

Табл. 1.3.

Категории операторов

Категория оператора

Описание

Сравнение

Сравнивает значение с другим значением или выражением

Логический

Проверяют истинность условия AND, OR, NOT, LIKE, ANY, ALL или IN

Арифметический

Выполняют сложение, вычитание, умножение, деление и операции по модулю

Унарный

Выполняют операции лишь над одним выражением любого типа данных из категории числовых типов данных

Поразрядный

Временно преобразуют обычное числовое значение (например, 150) в целочисленное и выполняют над ним поразрядные арифметические операции

Временно или постоянно

Конкатенация строк

Временно или постоянно объединяет две строки (с символьными или двоичными данными) в одну

Присваивание

Присваивают значение переменной или ассоциируют столбец результирующего набора с псевдонимом

Следующий оператор SELECT с помощью арифметических операторов вычитает из суммарного годового дохода часть, которую следует выплатить автору в качестве гонорара (она равна: объем продаж * процент авторского гонорара/100). В результате получатся сумма, которую получит издатель. Сначала вычисляется произведение ytd_sales и royalty, поскольку первым идет оператор умножения, произведение делится на 100, а частное вычитается из ytd_sales.

USE pubs

SELECT titlejd,  ytd_sales - ytd_sales * royalty / 100

FROM titles

Элементы языка управления ходом выполнения

Язык управления ходом выполнения состоит из специальных слов, которые контролируют ход выполнения операторов Transact-SQL, блоков операторов и хранимых процедур. Эти слова можно использовать в операторах Transact-SQL, пакетах и хранимых процедурах.

Если язык управления ходом выполнения не используются, отдельные операторы Transact-SQL выполняются последовательно, в том порядке, в каком они расположены. Язык управления ходом выполнения допускает объединение связанных операторов. Он также позволяет сделать их взаимозависимыми. При этом один оператор языка управления ходом выполнения не может относиться сразу к нескольким пакетам или хранимым процедурам.

Ключевые слова языка управления ходом выполнения полезны, если необходимо заставить Transact-SQL предпринять определенного рода действие. Например, с помощью пары операторов BEGIN...END можно заключить несколько операторов Transact-SQL в один логический блок. Пара операторов IF...ELSE позволяет исполнить некоторый блок операторов, если выполняется определенное условие, а если это условие не выполняется (т.е. выполняется условие ELSE) — исполнить другой блок операторов.

В таблице 1.4. приводится описание ключевых слов языка управления ходом выполнения, которые входят в Transact-SQL.

Табл 1.4.

Ключевые слова языка управления программами

Ключевое слово

Описание

BEGIN...END      

Заключает в себе набор операторов Transact-SQL, позволяя исполнять операторы группами

BREAK

Выход из цикла WHILE

CONTINUE

Продолжает выполнение цикла WHILE

GOTO

При исполнении пакета Transact-SQL вызывает переход к определенной

метке, пропуская операторы, расположенные между оператором GOTO и меткой

IF..ELSE 

Ставит исполнение оператора Transact-SQL в зависимость от некоторых

условий. При наличии ELSEопределяет альтернативный оператор, который будет исполнен, если не выполняется условие IF

RETURN

Безусловное завершение запроса, хранимой процедуры или пакета

WAITFOR

Приостанавливает работу соединения в течение заданного периода

или до наступления определенного времени суток

WHILE

Повторно исполняет оператор или блок операторов до тех пор, пока

заданное условие истинно. Как правило, используется с операторами BREAK или CONTINUE

Комментарии

Комментарии (или примечания) — это неисполняемые строки программы. С их помощью документируют исходный текст программы или временно отключают часть операторов Transact-SQL и пакетов во время их диагностики. Документирование исходного текста программы комментариями облегчает сопровождение программы. В комментариях часто записывают название программы, имя автора и даты внесения в исходный текст серьезных изменений, кроме того, в них можно описать сложные вычисления или разъяснить метод программирования.

SQL Server поддерживает два типа знаков комментариев

• Двойной дефис ( --).    Этот знак комментария можно использовать как внутри, так и в
начале строки программы. Все символы от двойного дефиса до конца строки рассмат
ривается как часть комментария. В случае многострочных комментариев, в начале каж
дой строки комментария должен стоять двойной дефис, как показано на следующем
примере:

USE Northwind

GO

-- Первая строка многострочного комментария.

-- Вторая строка многострочного комментария.

SELECT * FROM Employees

GO

• Парные символы «слэш — звездочка» ( /*...*/ ).    Эти знаки комментария можно ис
пользовать посредине, и в начале строки, и даже внутри исполнимого кода. Всё между
знаками начала (/* ) и конца ( */ ) комментария считается частью комментария. Мно
гострочный комментарий должен начинаться с открывающей пары символов ( /* ) и
заканчиваться закрывающей парой символов ( */). В этом случае не надо ограничи
вать знаками комментария каждую строку.

USE Northwind

/* Первая строка многострочного комментария.

Вторая строка многострочного комментария.   */ SELECT * FROM Products GO

Многострочный комментарий не должен выходить за пределы пакета. Комментарийдолжен завершаться до его конца. Например, для SQL Query Analyzer и утилиты osql команда GO является признаком конца пакета. Если первые два байта в прочитанной утилитой строке являются символами GO, утилита посылает все прочитанные после поступления последней команды GO операторы на сервер как один пакет. Если команда GO окажется в начале строки между разделителями /* и */, то в обоих пакетах будут посланы незакрытые знаки комментария (что приведет к возникновению синтаксических ошибок). В комментарии можно использовать любые буквы и цифры. SQL Server игнорирует все символы внутри комментария (за исключением команды GO). Кроме того, длина ком ментария в пакете не ограничена. Комментарий может быть как однострочным, так и мно гострочным.

Исполнение операторов Transact-SQL

В SQL Server 2000 предусмотрено несколько методов исполнения операторов Transact-SQL. Можно исполнить одиночный оператор или несколько операторов в виде пакета (который является группой из одного или нескольких операторов Transact-SQL). Операторы Transact-SQL также можно исполнять в хранимых процедурах и триггерах. На этом занятии вы познакомитесь с различными методиками исполнения операторов, а также узнаете о способах исполнения в зависимости от применяемой методики и об исполнении операторов Transact-SQL с помощью сценариев.

Одиночные операторы Transact-SQL

Обработка одиночных операторов является основным способом исполнения операторов SQL в SQL Server 2000. Проще всего обработку оператора проиллюстрировать на примере оператора SELECT.

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

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

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

Обработка оператора SELECT

Базовый процесс исполнения основной массы операторов Transact-SQL можно проиллюстрировать на примере этапов обработки одиночного оператора SELECT, который ссылается только на локальные таблицы (и не использует представления или удаленные базы данных).

  1.  Синтаксический анализатор сканирует оператор SELECT и разбивает его на логические единицы, такие, как ключевые слова, выражения, операторы и идентификаторы.
  2.  Путем описания логических шагов, необходимых для трансформации исходных данных в формат результирующего набора, строится дерево запроса (иногда оно называется деревом последовательности).
  3.  Оптимизатор запросов анализирует все варианты обращения к исходным таблицам и выбирает тот, который позволит быстрее всего получить результат с наименьшей затратой ресурсов. Дерево запроса обновляется точным описанием набора шагов. Конечная, оптимизированная версия дерева запросов получает название «план исполнения».
  4.  Реляционный механизм приступает к реализации плана исполнения. При выполнении шагов, требующих данные из таблиц базы, реляционный механизм с помощью
    OLE DB запрашивает у механизма хранения передачу данных из наборов строк, запрошенных у реляционного механизма.
  5.  Реляционный механизм обрабатывает данные, которые вернул механизм хранения, преобразует их в формат, определенный для результирующего набора, и возвращает результаты клиенту.

Обработка других операторов

Основные этапы обработки оператора SELECT верны и для других операторов SQLINSERT, UPDATE и DELETE. Операторы UPDATE и DELETE определяют целевые наборы строк, предназначенные для модификации или удаления. Процесс идентификации этих строк аналогичен процессу идентификации исходных строк для формирования результирующего набора оператора SELECT. И UPDATE, и INSERT могут содержать встроенные операторы SELECT, которые предоставляют данные для модификации или добавления. Даже операторы DDL, например CREATE PROCEDURE или ALTER TABLE, в конечном счете представлены наборами реляционных операций над таблицами системного каталога и иногда (в случае операторов ALTER TABLE и ADD COLUMN) над таблицами данных.

Пакеты

Пакет — это группа из одного или нескольких операторов Transact-SQL, которые приложение одновременно посылает на SQL Server для исполнения. SQL Server компилирует операторы пакета в единую исполнимую единицу (план исполнения). После этого по очереди выполняются операторы этого плана.

Ошибка при компиляции, например синтаксическая, останавливает процесс компиляции плана исполнения. В этом случае ни один из операторов пакета исполнен не будет. У ошибки периода выполнения, например арифметического переполнения или нарушения ограничения, возможно одно из следующих последствий:

большинство ошибок периода выполнения останавливают исполнение текущего и последующих операторов пакета;

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

Операторы, исполненные до оператора, вызвавшего ошибку периода выполнения, не испытывают на себе влияния этой ошибки. Единственное исключение: если пакет является транзакцией, то при возникновении ошибки транзакция откатывается. В этом случае происходит отмена всех незафиксированных модификаций, сделанных до возникновения ошибки периода выполнения.

Например, пакет состоит из 10 операторов. Если в пятом операторе допущена синтаксическая ошибка, ни один из операторов пакета не будет выполнен. Если пакет уже скомпилирован и при исполнении второго оператора происходит сбой, то результат первого оператора остается без изменений (поскольку он уже получен).

При обработке пакетов действуют следующие правила:

операторы CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE
TRIGGER и CREATE VIEW не могут соседствовать в пакетах с другими операторами.
Пакет должен начинаться с оператора
CREATE. Все следующие за ним операторы будут интерпретированы как часть определения, созданного первым оператором CREATE;

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

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

Команда GO

SQL Query Analyzer, а также утилиты osql и isql считают команду GO признаком конца пакета. Команда GO интерпретируется не как оператор Transact-SQL, а как сигнал для утилит, определяющий число операторов Transact-SQL в пакете. SQL Query Analyzer и утилиты osql и isql помещают все операторы Transact-SQL, расположенные между двумя последовательными командами GO, в строку и отправляют ее SQLExecDirect. В isql все операторы Transact-SQL, которые находятся между последовательными командами GO, перед исполнением помещаются в буфер команд.

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

Следующий пример создает представление. Поскольку необходимо, чтобы оператор CREATE VIEW был единственным оператором пакета, его следует изолировать от окружающих операторов USE и SELECT командами GO.

USE pubs

GO

CREATE VIEW auth_titles

AS

SELECT *

FROM authors

GO

SELECT * FROM auth_titles

GO

Обработка пакета

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

Все операторы SQL, которые приложение отправляет на сервер как единицу исполнения, составляют единый пакет и генерируют один план исполнения.

Все операторы хранимой процедуры или триггера составляют единый пакет. При компиляции любой хранимой процедуры или триггера получается единственный план исполнения.

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

Строка, исполняемая системной хранимой процедурой sp_executesql, — это пакет, при компиляции которого получается один план исполнения.

Если приложение посылает пакет, в котором содержится оператор EXECUTE, план исполнения для обрабатываемой строки или хранимой процедуры выполняется отдельно от плана исполнения оператора EXECUTE. План исполнения, сгенерированный для строки, которую исполняет хранимая процедура sp_executesql, также будет отделен от плана исполнения остальной части пакета, в котором присутствует вызов процедуры sp_execute-sql. Если оператор пакета вызывает триггер, то план исполнения триггера выполняется отдельно от плана исполнения исходного пакета.

Например, пакет, который содержит следующие четыре оператора, генерирует пять планов исполнения:

оператор EXECUTE, исполняющий хранимую процедуру;

вызов процедуры sp_executesql для обработки строки;

оператор EXECUTE, обрабатывающий строку;

оператор UPDATE, ссылающийся на таблицу, у которой есть триггер на обновление.

План исполнения пакета

1

EXEC ProA

2

sp exectesql N' INSERT...1

3

EXEC ( 'sp who' )

4.

UPDATE Trigger-Table. ..

  1.  План  2. План испол- 3. План испол- 4. План исполне-

Испол- ненияl  нения исполня- ния триггера

нения sp_executesql  емой строки

SELECT

INSERT

SP_WHO

RAISERROR

Рис. 1.9. иллюстрирует процесс обработки четырех операторов.

Хранимые процедуры и триггеры

Хранимая процедура — это группа операторов Transact-SQL, которая компилируется один раз и после этого может выполняться многократно. Такая функциональность повышает производительность, поскольку отпадает необходимость в перекомпиляции операторов Transact-SQL.

Триггер — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно. При создании триггера определяются условия его исполнения: триггер должен быть исполнен при определенной модификации данных в заданной таблице или столбце. операторы CREATE PROCEDURE и CREATE TRIGGER не могут располагаться в нескольких пакетах. Другими словами, хранимая процедура или триггер всегда создаются в одном пакете и компилируются в план исполнения.

Исполнение хранимых процедур и триггеров

SQL Server 2000 хранит только исходный текст хранимых процедур и триггеров. Когда хранимая процедура или триггер исполняется первый раз, исходный текст компилируется в план исполнения. Если до того, как план исполнения устареет и будет удален из памяти, хранимая процедура или триггер исполняется снова, реляционный механизм обнаруживает существующий план и использует его повторно. Если план устарел и удален из памяти, создается новый план исполнения. Этот процесс напоминает обработку SQL Server 2000 всех операторов SQL. Увеличение производительности при применении хранимых процедур и триггеров объясняется постоянством их SQL-операторов, что позволяет SQL Server использовать для них существующие планы исполнения.

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

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

Сценарии Transact-SQL

Сценарий — это набор операторов Transact-SQL, сохраненных в файле. Этот файл можно ввести в SQL Query Analyzer или утилиты osql и isql. Они способны исполнить операторы SQL, хранящиеся в файле.

Сценарии Transact-SQL состоят из одного или нескольких пакетов. Конец пакета отмечается командой GO. Если в сценарии Transact-SQL нет ни одной команды GO, он исполняется как один пакет.

Сценарии Transact-SQL позволяют выполнять следующие задачи:

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

переносить операторы с компьютера на компьютер (при необходимости);

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


ЧАСТЬ
II. АДМИНИСТРИРОВАНИЕ SQL SERVER 2000

2.1. СОЗДАНИЕ И УПРАВЛЕНИЕ БД SQL SERVER 2000

Первым этапом реализации физической базы данных является создание объекта «база данных». Создают этот объект и определяют его характеристики на основе информации, полученной при определении требований к системе и деталей, определенных при проектировании базы данных. Характеристики объекта «база данных» можно изменить и после его создания.

Создание базы данных SQL Server

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

право на создание базы данных по умолчанию принадлежит членам стандартных ролей на сервере sysadmin и dbcreator, однако это право может быть предоставлено и другим пользователям;

пользователь, создавший базу данных, становится ее владельцем;

на сервере может быть создано до 32 767 баз данных;

имя базы данных должно соответствовать правилам, определенным для идентификаторов.

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

Во время создания базы данных составляющие ее файлы заполняются нулевыми значениями, чтобы уничтожить все данные, которые могли остаться на диске после удаленных ранее файлов. Хотя из-за этого файлы создаются дольше, во время обычной работы базы данных ОС освобождается от заполнения файлов нулевыми значениями при первой записи в них. Эта функция повышает производительность каждодневных операций.

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

SQL Server создает новую базу данных в два этапа:

  1.  используя копию базы Model, SQL Server инициализирует новую базу данных и ее ме
    таданные;
  2.  после этого SQL Server заполняет оставшуюся часть базы данных (кроме страниц с
    внутренними данными, отражающими использование дискового пространства, заня
    того базой данных) пустыми страницами.

Любые пользовательские объекты из базы Model копируются во все новые базы данных. К базе данных Model можно добавлять любые объекты: таблицы, представления, хранимые процедуры, типы данных и т. д., которые следует включить в новые базы данных. Каждая новая база данных наследует все настроечные параметры базы Model.

Методы создания баз данных SQL Server

SQL Server предоставляет несколько методов для создания базы данных: оператор Transact-SQL CREATE DATABASE, консоль SQL Server Enterprise Manager и мастер Create Database, который можно вызвать в SQL Server Enterprise Manager.

Оператор CREATE DATABASE

Средств оператора CREATE DATABASE достаточно, чтобы создать базу данных со всеми ее файлами. Оператор CREATE DATABASE позволяет задать несколько параметров, определяющих характеристики базы данных, например максимальный размер или инкремент роста файла. При исполнении простого оператора CREATE DATABASE имя_БД без параметров создается база данных того же размера, что и Model. Подробное описание параметров оператора CREATE DATABASE вы найдете в справочнике по языку Transact-SQL в SQL Server Books Online.

Оператор CREATE DATABASE можно исполнить в SQL Query Analyzer. Следующая программа-пример создает базу данных под названием Products и задает для нее один основной файл. Также автоматически создается файл журнала транзакций размером 1 Мб. Поскольку в параметре SIZE для основного файла не заданы ни мегабайты, ни килобайты, размер основного файла измеряется в мегабайтах. Поскольку не заданы параметры файла журнала транзакций, в том числе параметр MAXSIZE, этот файл может увеличиваться, пока не заполнит все место на диске.

USE master . ,

GO ,

CREATE DATABASE Products

NAME = prods_dat,

FILENAME =  'c:\program files\microsoTt SQL server\mssql\data\prods.mdf',

SIZE =4,

MAXSIZE = 10, ,..'•..

FILEGROWTH =1 '

Управление базой данных SQL Server

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

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

Если необходимо устранить неполадки и принять решения о внесении изменений в базу данных, то стоит просмотреть определение базы данных и параметры ее конфигурации. SQL Server предоставляет несколько методов просмотра сведений о базе данных: системную хранимую процедуру sp_helpdb, оператор DATABASEPROPERTYEX и SQL Server Enterprise Manager.

Системная хранимая процедура sp_helpdb выдает информацию обо всех базах данных или только о заданной. Оператор DATABASEPROPERTYEX возвращает текущее значение настроечного параметра или свойства только для заданной базы данных. За один раз этот оператор возвращает значение лишь одного свойства. Для просмотра настроечных параметров базы данных можно также использовать SQL Server Enterprise Manager. В консоли откройте диалоговое окно Properties для нужной базы данных. В диалоговом окне Properties имеется несколько вкладок со сведениями о конфигурации базы данных.

Модификация базы данных

Разрешается модифицировать исходное определение базы данных после ее создания. Однако иногда перед внесением изменений требуется вывести базу данных из обычного режима работы. В следующей таблице перечислены типы изменений свойств базы данных с указанием способа их внесения. Подробно решение каждой из задач описано в SQL Server Books Online.

Табл. 2.1.

Изменение свойств БД


Тип изменения

Методы внесения изменения

Увеличение размера базы данных

Оператор ALTER DATABASE,

Свойства БД в SQL Server Enterprise Manage

Изменение физического местоположения БД

Оператор Alter DATABASE

Уменьшение размера БД

Оператор DBCC SHRINKDATABASE

Опция SHRK DATABASE в SQL SERVER ENTERPRISE MANAGER доступная через узел БД

Задание автоматического уменьшения размера БД

Системная хранимая процедура sp_dboption

Свойства БД в SQL Server Enterprise Manage

Уменьшение размера файла БД

Оператор DBCC SHRINKFILE

Добавление файла данных или файла журнала

Оператор ALTER DATABASE

Удаление файла данных или файла журнала

Свойства БД в SQL Server Enterprise Manage

Оператор ALTER DATABASE

Добавление группы файлов

Свойства БД в SQL Server Enterprise Manage

Оператор ALTER DATABASE

Изменение группы файлов по умолчанию

Оператор ALTER DATABASE

Изменение параметров БД

Оператор ALTER DATABASE

Системная хранимая процедура sp_dboption

Свойства БД в SQL Server Enterprise Manage

Переименование

Системная хранимая процедура sp_renamedb

Изменение владельца БД

Системная хранимая процедура sp_changedbowner

Настройка параметров базы данных

Можно задать несколько параметров уровня базы данных, определяющих ее характеристики. Эти параметры доступны для изменения только системному администратору, владельцу базы данных, а также членам стандартных ролей на сервере sysadmin и dbcreator и фиксированной роли в базе данных db_owner. Для каждой базы данных эти параметры уникальны и не влияют на другие базы данных. Задать параметры базы данных можно в конструкции SET оператора ALTER DATABASE, системной хранимой процедуре sp_dbop-tion или в некоторых случаях — в SQL Server Enterprise Manager.

Параметры, общие для сервера, задаются с помощью системной хранимой процедуры sp_configure или SQL Server Enterprise Manager. Параметры уровня соединения определяют посредством оператора SET.

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

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

Табл. 2.2

Категории параметров базы данных

ТИП ПАРАМЕТРА

ОПИСАНИЕ

Auto

Управляют некоторыми автоматическими режимами и функциями

Cursor

Управляют поведением и сферой действия курсора

Recover

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

SQL

Управляют параметрами совместимости с ANSI

State

Управляют состоянием базы данных (подключена она или отключена), определяют, кто может подключиться к базе данных, а также находится ли она в режиме «только для чтения» (для контроля за завершением соединений при переходе базы данных из одного состояния в другое могут использоваться специальные конструкции)

Удаление базы данных SQL Server

Если несистемная БД больше не нужна (или она перемещена в другую базу данных или на другой сервер), ее можно удалить. При удалении базы файлы и их данные удаляются с диска на сервере. База данных удаляется навсегда и не может быть восстановлена иначе как со сделанной ранее резервной копии. Удалить системные базы данных (Msdb, Master, Model и Tempdb) невозможно.

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

Базу данных удаляют и с помощью .оператора DROP DATABASE или из консоли в SQL Server Enterprise Manager.

2.2. ИМПОРТ И ЭКСПОРТ ДАННЫХ

Скорее всего первое, что вам придется сделать после настройки базы данных, — импортировать в нее данные из внешнего источника. С импортированными данными можно работать при помощи операторов Transact-SQL и других инструментов для просмотра и модификации данных. Импорт — это процесс получения данных из внешних по отношению к SQL Server источников (например, из текстового файла ASCII) и размещение их в таблицах SQL Server. Обратный процесс — экспорт — это извлечение данных из экземпляра SQL Server в определенном заданном пользователем формате (например, копирование содержимого таблицы SQL Server в базу данных Microsoft Access). На этом занятии вы узнаете, как копировать данные из БД SQL Server с помощью утилиты командной строки Ьер, а также как средствами оператора BULK INSERT импортировать в базу данные из файла. Служба DTS (Data Transformation Services) также позволяет извлекать, трансформировать и объединять данные из самых различных источников.

Использование утилиты Ьср и оператора BULK INSERT

Утилита командной строки Ьср копирует данные из БД SQL Server в файл и обратно. Чаще ее применяют для переноса больших объемов данных из другой программы (как правило, из другой СУБД) в таблицу SQL Server. При использовании утилиты Ьср сначала выполняется экспорт данных из программы-источника в файл, а затем импорт данных из файла в таблицу SQL Server. Кроме того, Ьср позволяет переносить данные из таблицы SQL Server в файл, предназначенный для использования другими программами, например Microsoft Excel.

Есть и другой способ переноса данных из файла в таблицу SQL Server — с помощью оператора BULK INSERT. Однако он не предназначен для копирования больших объемов данных из экземпляра SQL Server в файл. Чтобы выполнить такую процедуру средствами оператора BULK INSERT, применяют утилиту Ьср при работе оператора Transact-SQL (а не в командной строке).

Дабы использовать для добавления данных утилиту Ьср и оператор BULK INSERT, данные, которые содержатся в файле, следует разбить на строки и столбцы. SQL Server принимает данные в любом ASCII- или двоичном формате, если можно определить разделители (символы, которыми разделяются столбцы и строки). Структура файла иногда не идентична структуре таблицы SQL Server, поскольку Ьср и BULK INSERT допускают пропуск столбцов или изменение их последовательности во время копирования большого объема данных.

При этом данные добавляются к содержимому таблицы SQL Server. При копировании большого объема данных из SQL Server в файл данные записываются поверх предыдущего содержимого файла.

Нужно помнить о следующих правилах:

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

файл должен содержать данные в символьном формате или в формате, ранее сгенерированном Ьср (например, в собственном формате Ьср). Необходимо, чтобы каждое поле таблицы было совместимо с соответствующим полем копируемого файла. Например, нельзя скопировать поле типа int в поле типа datetime;

для копирования большого объема данных необходимы соответствующие права доступа к исходному и целевому файлам и таблицам: для копирования из файла в таблицу необходимо иметь права доступа INSERT и SELECT для таблицы, а для копирования из таблицы или представления в файл — право доступа SELECT для копируемой таблицы или представления.

Следующая команда Ьср копирует данные из таблицы Publishers в базе данных Pubs в файл Publishers.txt:

Ьср pubs.. publishers out publishers.txt -с -Т

В команде сначала указана база данных (Pubs) и таблица (Publishers), откуда будут извлечены данные. Ключевое слово out задает экспорт данных из таблицы в файл Publishers.txt; параметр -с задает символьный (char) формат данных, а параметр -Т определяет использование для подключения к SQL Server доверенного соединения. В содержимое файла Publishers.txt войдут все данные таблицы Publishers. Для просмотра данных годится текстовый редактор, например Блокнот.

Утилита командной строки Ьср также позволяет выполнить копирование большого объема данных из файла Publishers.txt в таблицу Publishers! базы данных Pubs:

Ьср pubs. . publishers2 in publishers.txt -с -Т

В этой команде вместо out использовано ключевое слово in. После того как эта команда будет исполнена, можно вставить в таблицу Publishers! все данные из файла Publishers.txt.

Большой объем данных в таблицу Publishers! также копируют с помощью оператора BULK INSERT и такого инструмента для работы с запросами, как, например, Query Analyzer:

Use Pubs

BULK INSERT Publishers2 FROM 'c:\publishers.txt' WITH (DATAFILETYPE =   'CHAR')

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

Использование различных форматов данных

Утилита Ьср способна создавать и читать файлы с данными, формат по умолчанию которых задают параметром командной строки. В таблице 2.3. описаны четыре формата данных по умолчанию.

Табл. 2.3

Форматы данных

Формат данных

Параметр Ьср

Конструкция BULK INSERT

Описание

Встроенный

-п

DATAFILETYPE

='native'

Использует встроенные типы данных (из базы данных). Хранение информации во встроенном формате полезно при копировании информации из одного экземпляра SQL Server в другой. Использование встроенного формата экономит время и место на диске, позволяя избежать излишнего преобразования типов данных в символьный формат и обратно. Однако файл с данными во встроенном формате не может прочитать ни одна программа, кроме Ьср

Символьный

DATAFILETYPE= 'char'

Использует символьный формат данных (char) для всех столбцов, при этом разделителями полей по умолчанию являются символы табуляции, а разделителями строк — символ перевода строки. В символьном формате полезно хранить данные, предназначенные для использования в других программах (например, в электронных таблицах), а также когда необходимо скопировать в SQL Server данные из другой СУБД. Символьный формат часто используется при копировании данных из других программ, поддерживающих экспорт и импорт данных в простом текстовом формате использование для всех столбцов символьного формата Unicode. При этом разделителями полей по умолчанию являются символы табуляции, а разделителями строк — символы перевода строки. Данные в этом формате могут быть скопированы с сервера (использующего кодовую страницу, отличную от той, которую применяет работающий с Ьср клиент) на другой сервер. Последний может обращаться к той же кодовой странице, что и исходный сервер (или к другой кодовой странице). Этот формат предотвращает любые потери символьных данных, если на исходном и целевом серверах используется тип данных Unicode. Даже если на одном из этих серверов не поддерживается тип данных Unicode, потери расширенных символов минимальны Использует встроенные типы данных (из базы данных) для данных всех не символьных данных, а для всех символьных данных (char, nchar, varchar, nvarchar, text и ntext) — формат Unicode

По умолчанию утилита Ьср работает в интерактивном режиме и запрашивает необходимую для определения формата данных информацию у SQL Server и у пользователя. Однако при использовании параметров -п, -с, -w или -N Ьср не запрашивает сведения о каждом из столбцов таблицы SQL Server по отдельности. Вместо этого SQL Server читает и записывает данные в заданном формате по умолчанию.

По умолчанию оператор BULK INSERT работает в символьном режиме (char), интерактивный режим не применяется.

При массовом копировании данных в интерактивном режиме утилита Ьср запрашивает сведения относительно типа хранилища, длины префикса, длины поля и разделителей полей и строк. Файл, в котором хранятся сведения о формате каждого поля файла с данными, называется файлом формата. Он предоставляет информацию, которая по умолчанию применяется как для копирования большого объема данных из файла в SQL Server, так и при повторном копировании данных из таблицы (при этом не требуется заново задавать формат данных).

Использование DTS

Служба SQL Server DTS (Data Transformation Services) — это набор графических инструментов и программируемых объектов, позволяющих извлекать, трансформировать и объединять данные из совершенно различных объектов в один или несколько целевых наборов данных. Средствами DTS генерируют DTS-пакеты, которые позволяют создавать пользовательские решения для переноса данных, соответствующие бизнес требованиям конкретной организации. В частности, DTS позволяет импортировать и экспортировать данные.

Инструменты DTS

В службу DTS входит набор инструментов, позволяющих создавать и исполнять DTS-пакеты. Инструменты DTS описаны в таблице 2.4.

Табл 2.4.

Инструменты DTS

Инструмент

Описание

Мастер импорта/ экспорта DTS

Предназначен для копирования данных в экземпляр SQL Server и из него, а также для определения способов преобразования данных. Считается самым простым из предоставляемых DTS методом копирования данных между источниками OLE DB

DTS Designer

Этот графический инструмент используется для построения сложных пакетов с потоками заданий и логикой, управляемой событиями. DTS Designer также позволяет редактировать и настраивать пакеты, созданные мастером импорта/экспорта DTS

DTS и Enterprise Manager

Реализует возможности манипулирования пакетами и доступа к данным пакета из Enterprise Manager

Утилиты исполнения пакетов DTS 

К ним относятся:

утилита командной строки dtswiz, которая позволяетзапустить мастер импорта/экспорта DTS из командной строки. Команда dtswiz может быть задана с набором параметров командной строки;

утилита командной строки dtsrun, которая позволяет исполнить существующий пакет в командной строке. Команду dtsrun также можно задать с набором параметров командной строки; утилита DTS Run, которая предоставляет набор диалоговых окон, используемых для исполнения существующего пакета. Можно запустить утилиту DTS Run, исполнив в командной строке программу dtsrunui без параметров

DTS Query Designer

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

DTS-пакет — это организованный набор соединений, задач DTS, преобразований DTS и ограничений потока работ, собранный с помощью утилиты DTS или программных методов и сохраненный в структурированном файле SQL Server, SQL Server 2000 Meta Data Services или в файле Microsoft Visual Basic.

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

Задачи DTS

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

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

Импорт и экспорт файлов. DTS позволяет импортировать в SQL Server данные из текстового файла или источника данных OLE DB (например, из базы данных Microsoft Access 2000). Также предусмотрен экспорт данных из SQL Server в приемник OLE DB (например, на лист Microsoft Excel 2000). DTS также обеспечивает высокоскоростную загрузку данных в таблицы SQL Server из текстовых файлов.

Трансформация данных. Задача Transform Data из DTS Designer позволяет выбрать данные из подключенного источника, определить преобразования и передать трансформированные данные в соединение-получатель. А задача Data-Driven Query предназначена для привязки данных к параметризованному запросу.

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

Обмен сообщениями между пользователями и пакетами. Задача Send Mail позволяет отсылать почтовые сообщения при успешном или неудачном завершении этапа пакета. Задача Execute Package предназначена для запуска одного пакета из другого в качестве этапа, а задача Message Queue позволяет пакетам обмениваться сообщениями средствами Message Queuing.

Исполнение набора операторов Transact-SQL или сценариев ActiveX для источника данных. В задачах Execute SQL и ActiveX Script предусмотрены инструменты для написания собственных операторов SQL и сценариев и последующего исполнения их в качестве этапов в потоке заданий пакета.

Поскольку DTS основана на расширяемой модели СОМ, она позволяет создавать пользовательские задачи. Пользовательские задачи разрешается интегрировать в интерфейс DTS Designer и сохранять как часть объектной модели DTS. Трансформации DTS

Трансформация DTS — это одна или несколько функций или операторов, которые применяются к некоторым данным до того, как они достигают получателя. При этом исходные данные (данные источника) не изменяются. Например, можно извлечь подстроку из поля с исходными данными и скопировать ее в таблицу - получатель. Конкретная функция для работы с подстрокой представляет собой трансформацию, привязанную к полю с исходными данными. Другая возможность — выполнять поиск строк с определенными характеристиками (например, определенными значениями полей) и применять функции только к ним. Трансформации облегчают реализацию сложной проверки и очистки данных, а также преобразования в процессе импорта и экспорта данных. Трансформации позволяют:

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

применять функции, написанные в виде сценариев ActiveX. Эти функции позволяют реализовать специализированные трансформации или включить анализ некоторых условий. Например, можно написать функцию на языке сценариев, которая будет искать в столбце значения больше 1000. Если такое значение обнаружено, то в целевой таблице оно заменяется на —1, а если значение поля меньше 1000, то оно копируется в целевую таблицу без изменений;

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

создавать собственные трансформации в виде объектов СОМ и применять их к данным
полей.

Соединения DTS

Служба Data Transformation Services основана на архитектуре OLE DB, которая позволяет копировать и трансформировать данные из самых различных источников:

SQL Server и Oracle (напрямую посредством встроенных компонентов доступа OLE DB);

ODBC (с помощью компонента доступа Microsoft OLE DB Provider для ODBC);

Access 2000, Excel 2000, Visual FoxPro, dBase, Paradox, HTML и других источников в виде файлов с данными;

текстовых файлов (с помощью встроенного компонента доступа OLE DB);

Microsoft Exchange Server, Microsoft Active Directory и других не реляционных источников данных;

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

Функциональность DTS иногда ограничивается возможностями конкретных баз данных, драйверов ODBC или компонентов доступа OLE DB. Поток заданий DTS-пакета

Рабочие элементы в DTS-пакетах упорядочены с помощью этапов и условий выполнения. Предусмотрены средства как визуального (с помощью DTS Designer), так и программного конструирования потока заданий DTS-пакета. Кроме того, исполнение этапов также настраивают с помощью сценариев ActiveX.

Этапы определяют порядок исполнения задач в составе DTS-пакета. В объектной модели DTS этапы представляют единицы исполнения, они указывают, какие задачи необходимо реализовать при исполнении пакета и в какой последовательности.

Последовательность реализации этапов пакета задают с помощью условий, которые связывают две задачи: одна выполняется в зависимости от удачного или неудачного исполнения другой. Условия выполнения позволяют организовать условное ветвление в потоке работы. Этапы без ограничений реализуются немедленно, по несколько этапов параллельно. Кроме того, поток заданий модифицируют с помощью сценариев ActiveX.


2.3. КОПИРОВАНИЕ В СРЕДЕ SQL SERVER 2000

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

Табл. 2.5.

Терминология резервного копирования

Термин

Описание

Резервная копия (Backup)

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

Устройство резервного копирования (Backup device)

Набор резервных копий записывается на носитель (диск или стример) с использованием устройства резервного копирования (имя стримера или физическое имя файла)

Файл резервной копии (Backup file)

Физический файл (например C:\SQLBackups\Full.bak) или ленточный накопитель (например \\.\TapeO), используемый при записи резервной копии на носитель

Носитель резервной копии (Backup media)

Файл с набором резервных копий

Набор резервных копий (Backup set)

Физический носитель (диск или стример), на который сохраняют набор резервных копий с использованием файла резервной копии. На носителе может храниться несколько наборов резервных копий (например несколько резервных копий из SQL Server 2000 и несколько из Windows 2000)

Семейство носителей (Media family)

Все носители (диски или ленты) в наборе носителей, записанные одним устройством резервного копирования для одного набора резервных копий

Заголовок носителя (Media header)

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

Набор носителей (media set)

Все носители, задействованные в операции резервного копирования (например лента, дисковый файл, устройство резервного копирования, записывающее набор лент, или же набор лент, записанный несколькими устройствами резервного копирования

Носителем резервной копии может быть диск или ленточный накопитель (стример). Для SQL Server 2000 подходят следующие виды носителей: локальные ленточные накопители, локальные жесткие диски, жесткие сетевые диски и именованные каналы (named pipes). Последние предоставляют интерфейс, который могут использовать решения резервного копирования сторонних фирм.

Традиционно администраторы БД использовали стример при создании резервных копий, так как это было дешевле использования жестких дисков. Однако ленточные накопители работают медленно и имеют ограниченный объем. Ограничением скорости резервного копирования для SQL Server 2000 обычно является сам стример. Вы можете, повысить производительность операций создания резервных копий, используя для этого два стримера одновременно. Это вдвое уменьшит время создания копии, так как запись на стримеры происходит параллельно. Проблема ограниченного объема стримера заключается в том, что ваша копия может не помещаться на одну ленту и кто-то должен их менять. В противном случае резервное копирование никогда не будет завершено. Одновременная запись на несколько накопителей позволяет решить проблему ограниченности объема одной ленты при записи больших БД.

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

Не так давно жесткий диск был слишком дорогим средством для хранения резервных копий БД. Но теперь ситуация изменилась, и в качестве способа резервного копирования часто выбирают запись резервной копии на диск, поскольку чаще это наиболее быстрый способ. Создание резервной копии БД размером 20 Гб занимает 8 минут. Если вы создаете резервную копию на локальном диске, убедитесь, что используете для этого не тот физический диск, на котором хранятся файлы данных и файлы журнала транзакций. После создания резервной копии на локальном диске ее файлы регулярно (допустим, каждую ночь) автоматически копируются на стример.

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

Вы можете создать одно или несколько постоянных устройств резервного копирования для регулярного создания резервных копий, или же создавать новый файл при каждой операции резервного копирования. Скорее всего, вы захотите создать устройство, которое можно будет использовать неоднократно и которое поможет вам в значительной степени автоматизировать процесс резервного копирования. Наличие постоянных устройств резервного копирования позволит ссылаться на них в командах резервного копирования и восстановления, используя логическое имя устройства, что предпочтительнее использования полного физического имени. Информация об устройства резервного копирования содержится в таблице sysdevices БД master. Информация о файлах резервного копирования, создаваемых непосредственно во время копирования, не записывается в таблицу sysdevices, поэтому их нельзя использовать повторно, к тому же их необходимо указывать при каждом обращении.

Резервное копирование с использованием Transact-SQL

Вы также можете создавать резервные копии, используя команды BACKUP DATABASE и BACKUP LOG Transact-SQL. Познакомиться с синтаксисом и необязательными параметрами этих команд вы можете, обратившись к SQL Server Books Online. Значения параметров для команд резервного копирования Transact-SQL те же, что и в SQL Server Enterprise Manager, но освоение синтаксиса требует практики. Кроме того, в Transact-SQL есть ряд дополнительных параметров, которые расширяют возможности создания резервных копий. Большинство из них используется при работе с ленточными накопителями, а некоторые из перечисленных ниже могут быть использованы со всеми типами носителей и доступны только в Transact-SQL.

Можно установить пароль для набора резервных копий. Вы должны будете ввести
его для восстановления резервной копии.

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

Можно начать заново прерванное резервное копирование. Это особенно полезно при создании резервных копий больших БД.

Полное резервное копирование базы данных

Команда BACKUP DATABASE может быть и очень простой, и очень сложной. Вы можете создать файл резервной копии «на лету» или же использовать уже созданное устройство резервного копирования.

Следующий оператор создает полную резервную копию БД Northwind в файле C:\SQLBackups\Temp.bak:

BACKUP DATABASE Northwind TO DISK =  'C:\SQLBackups\Temp.bak'

Следующий оператор создает резервную копию БД Northwind на устройстве резервного копирования NorthwindFullBackup и затем проверяет резервную копию:

DATABASE Northwind TO NorthwindFullBackup RESTORE VERIFYONLY FROM NorthwindFullBackup

В следующем примере создается резервная копия БД Northwind на устройстве резервного копирования NorthwindFullBackup. Он инициализирует носитель, создает набор носителей NorthwindBackups с описанием Media for Northwind Database Backups и создает набор резервных копий Full Northwind Database Backup # 1 с описанием Backup Set #1. Эта команда также выводит сообщение о ходе процесса при выполнении каждых 25% от общего объема операции.

BACKUP DATABASE Northwind TO NorthwindFullBackup

WITH FORMAT ,

MEDIANAME = 'NorthwindBackups1   ,

MEDIADESCRIPTION =  'Media for Northwind Database Backups'   ,

NAME = 'Full Northwind Database Backup #1'   ,

DESCRIPTION =  'BackupSet #1'   ,

STATS = 25

В следующем примере резервная копия БД Northwind создается параллельно на двух устройствах: NwindDevicel и NwindDevice2. Перед началом записи файла резервной копии на каждое устройство SQL Server 2000 проверяет, определена ли метка Media Set for Northwind Database Backups для данного набора носителей:

BACKUP DATABASE Northwind TO NWindDevicel,   NWindDevice2 .    •

WITH MEDIANAME =  'Media Set for Northwind Database Backups'

Дифференциальное резервное копирование базы данных

Чтобы создать дифференциальную резервную копию БД с помощью команды BACKUP DATABASE, необходимо добавить аргумент WITH DIFFERENTIAL в любой из вышеприведенных примеров (кроме того, сначала нужно создать полную резервную копию).

В следующем примере создается дифференциальная копия БД Northwind на устройстве резервного копирования NorthwindDiffBackup:

BACKUP DATABASE Northwind TO NorthwindDiffBackup WITH

DIFFERENTIAL »

Резервное копирование файла или группы файлов

Чтобы создать резервную копию файла или группы файлов, добавьте аргумент FILE = логическое_имя_файла или FILEGROUP = логическое_имя_группы в оператор BACKUP DATABASE.

Следующий оператор создает резервную копию группы файлов Second_FG БД Northwind на устройстве резервного копирования NorthwindFGBackup:

BACKUP DATABASE Northwind FILEGROUP =   'SECOND_FG'  TO NorthwindFGBackup

Резервное копирование журнала транзакций

Синтаксис команды BACKUP LOG, с помощью которой создается резервная копия журнала, очень похож на синтаксис BACKUP DATABASE.

В следующем примере создается резервная копия журнала транзакций БД North-wind на устройстве NorthwindTLogBackup:

BACKUP LOG Northwind TO NorthwindTLogBackup

В следующем примере создается резервная копия журнала транзакций БД North-wind на устройстве NorthwindTLogBackup, но журнал транзакций не усекается. Используйте эту команду при повреждении диска с файлами данных:

BACKUP LOG Northwind TO NorthwindTLogBackup WITH NOJTRUNCATE

Репликация

Репликация SQL Server 2000 позволяет поддерживать несколько копий данных на различных компьютерах с целью повышения общей производительности системы, а также обеспечивает поддержку синхронизации всех копий. Например, какой-либо отдел размещает данные о продажах на собственном сервере и посредством репликации обновляет эти сведения на корпоративном компьютере. Или другой пример: несколько мобильных пользователей работают весь день, отключившись от сети, а в конце рабочего дня они с помощью репликации сведением выполняют слияние своих записей с главной базой данных. Репликация — важная и мощная технология распределения данных и некоторых типов объектов баз данных (хранимых процедур, представлений и пользовательских функций) по всему предприятию. В репликации SQL Server используется принцип «публикации и подписки». Издатель (владелец) данных, подлежащих репликации, определяет статьи (аналогичные таблицам базы данных), которые надо сделать доступными для подписчиков (или для адресов, получающих копии оригинальной публикации).

Принципы репликации

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

Издателем является сервер-источник данных, подлежащих репликации. Для каждой таблицы или иного объекта базы данных, который предполагается использовать в качестве источника репликации, издатель определяет статью. Одна или несколько связанных статей из одной базы данных организуются в публикацию. Публикации представляют собой удобный способ группировки связанных данных и объектов, которые следует реплицировать.

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

Дистрибьютор — это сервер, выполняющий различные задачи по перемещению статей от издателей к подписчикам. Реальные задачи зависят от типа выполняемой репликации.

SQL Server 2000 также поддерживает репликацию между гетерогенными источниками данных. Источники данных OLE DB или ODBC могут подписываться на публикации SQL Server. Последний также способен получать данные, реплицируемые некоторыми другими источниками данных, в том числе Microsoft Exchange, Microsoft Access, Oracle и DB2.

Модель репликации сведением позволяет многим или даже всем подписчикам, а также издателю обновлять реплицируемые данные. Репликация сведением является идеальным решением для любого окружения, где подписчики часто отсоединены от сети (например, для удаленных продавцов). Подписчики получают возможность модифицировать или добавлять данные в дороге, а по возвращении в офис или при подключении к локальной сети через модем — выполнять слияние модифицированных данных с оригинальной копией (опубликованной издателем).

2.4. ВОССТАНОВЛЕНИЕ В СРЕДЕ SQL SERVER 2000

Определение последовательности восстановления данных

При восстановлении данных сначала необходимо определить оптимальную последовательность использования файлов резервных копий. SQL Server Enterprise Manager облегчает эту задачу. При выполнении резервного копирования или восстановления SQL Server 2000 записывает все детали, относящиеся к истории резервного копирования или восстановления в БД msdb. Для каждой резервной копии указано, какие файлы или устройства резервного копирования использовались, кто и когда проводил резервное копирование. Если необходимо восстановить данные с помощью SQL Server Enterprise Manager, он обращается к системным таблицам msdb и сообщает, какие файлы следует использовать, чтобы затратить на восстановление как можно меньше времени. Если БД msdb повреждена, ее надо восстановить до того, как вы приступите к восстановлению пользовательских БД. При этом вы восстановите всю историю о резервном копировании и восстановлении БД экземпляра SQL Server 2000 (конечно, при условии, что у вас есть резервная копия БД msdb).

Если нет достаточно свежей резервной копии БД msdb или вы хотите восстановить данные на другом экземпляре, SQL Server 2000 записывает всю необходимую информацию с каждым набором резервных копий, чтобы восстановить историю резервного копирования в БД msdb. Можноиспользовать SQL Server Enterprise Manager, чтобы прочитать каждый файл резервной копии и добавить информацию в БД msdb. После этого SQL Server Enterprise Manager сможет обращаться к восстановленной БД msdb, чтобы определить оптимальную последовательность восстановления.

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

Для просмотра содержимого устройства резервных копий с использованием Transact-SQL, используются команды, перечисленные в табл. 2.5., чтобы получить информацию о наборах носителей и резервных копий.

Табл. 2.5.

Команды Transact-SQL для восстановления информации

Команда Transact-SQL

Описание

RESTORE LABELONLY

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

RESTORE HEADERONLY

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

RESTORE FILELISTONLY

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

2.5. РЕПЛИКАЦИЯ

Процесс репликации

Репликация (replication) — это процесс автоматического распределения копий данных и объектов БД между экземплярами SQL Server с одновременной синхронизацией всей распространяемой информации.

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

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

Отделение OLTP-операций от функций средств поддержки принятия решений. Чтобы пользователи системы поддержки принятия решений не выполняли запросы на занятом OLTP-сервере, данные для запроса можно реплицировать на выделенный сервер поддержки принятия решений.

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

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

Расширение системы за пределы ЛВС. Данные, для доступа к которым используется Интернет, могут быть реплицированы на различные серверы в разных географических регионах для равномерного распределения нагрузки между серверами.

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

SQL Server 2000 поддерживает три типа репликации (replication types) — моментальных снимков, транзакций и сведением. Репликация моментальных снимков (snapshot replication) — это периодическая репликация целостного набора данных, зафиксированного по состоянию на определенный момент времени, с локального сервера на удаленные. Лучше использовать этот тип репликации в БД, где количество реплицируемых данных не велико, а источник данных статичен. Вы можете предоставлять удаленным серверам ограниченную возможность обновления реплицированных данных. Репликация транзакций (transactional replication) — это репликация начального моментального снимка данных на удаленные серверы, а также репликация отдельных транзакций, работающих на локальном сервере и выполняющих последовательные изменения данных в начальном моментальном снимке. Эти реплицированные транзакции выполняются над реплицируемыми данными на каждом удаленном сервере для синхронизации данных на удаленном сервере с данными локального сервера. Вы можете использовать этот тип репликации, если вам необходимо постоянное обновление данных на удаленных серверах. Вы можете предоставлять удаленным серверам ограниченную возможность обновления реплицированных данных. Репликация ведением (merge replication)— это репликация начального моментального снимка данных на удаленные серверы, а также репликация изменений, происходящих на каком-либо удаленном сервере, обратно на локальный сервер с целью синхронизации, разрешения конфликтов и повторной репликации на удаленные серверы. Вы можете использовать репликацию сведением в случае, когда многочисленным изменениям подвергаются одни и те же данные, либо когда удаленные независимые компьютеры работают автономно, например, как в случае автономного пользователя.

Средства репликации SQL Server используют терминологию издательской деятельности для названий процессов и компонентов репликации. Сервер, реплицирующий сохраненную информацию на другие серверы, называется издателем (publisher). Реплицируемая информация состоит из одной или нескольких публикаций (publications). Каждая публикация представляет собой логически согласованный набор данных отдельной БД и состоит из одной или нескольких статей (articles). Статья может быть одним или несколькими объектами следующего типа:

часть или целая таблица (с фильтрацией по столбцам и/или по строкам);

хранимая процедура или определение представления;

выполнение хранимой процедуры;

представление;

индексированное представление;

пользовательская функция.

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

Серверы, получающие реплицируемую информацию, называются подписчиками (subscribers). Они получают избранные публикации — подписки (subscriptions) — от одного или нескольких издателей. В зависимости от типа репликации, подписчикам может быть разрешено изменять реплицируемую информацию, а также реплицировать измененную информацию обратно издателю. Подписчики могут быть авторизованы, или могут быть анонимными (анонимная подписка используется при публикации данных в Интернете). Для больших публикаций использование анонимных серверов может повысить производительность системы.

Агенты репликации (replication agents) автоматизируют процесс репликации. Как правило, агент репликации — это задание службы SQL Server Agent, сконфигурированное администратором для выполнения специфических задач по расписанию. По умолчанию в среде Windows NT 4.0/2000 агенты репликации выполняются в контексте безопасности доменной учетной записи службы SQL Server Agent. В Windows 98/ Me они выполняются в контексте безопасности зарегистрированного в системе пользователя. Существует некоторое число агентов репликации для различных задач репликации. Каждый агент сконфигурирован так для запуска по определенному расписанию. Различные типы репликации используют один или несколько таких агентов.

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

Агент Distribution передает моментальный снимок данных и последующие изменения от распространителя подписчикам. Этот агент используется при репликации моментальных снимков и репликации транзакций. По умолчанию для всех подписок на отдельную публикацию используется один агент Distribution. Такой агент называется разделяемым (shared). Однако вы можете настроить систему так, чтобы у каждого подписчика был личный, независимый (independent), агент Distribution.

Агент Log Reader перемещает транзакции, помеченные для репликации, из журнала транзакций с сервера-издателя на сервер-распространитель. Этот агент используется при репликации транзакций. Каждая из помеченных для репликации БД будет иметь один агент Log Reader, запускающийся на распространителе и подключающийся к издателю.

Агент Queue Reader вносит в публикацию изменения, сделанные подписчиками в автономном режиме. Репликация мгновенных снимков и репликация транзакций используют этот агент в случае, если разрешена очередь обновлений. Агент запускается на распространителе, и существует только один экземпляр такого агента, обслуживающий всех издателей и публикации для конкретного распространителя.

Агент Merge передает моментальный снимок данных от распространителя подписчикам. Он также перемещает и контролирует изменения в реплицируемых данных между издателем и подписчиками. Этот агент дезактивирует подписки, информация которых не обновлялась в течение максимального срока хранения публикации (по умолчанию — 14 дней). Этот агент используется в случае репликации сведением. Каждая подписка на объединенную публикацию имеет свой объединяющий агент, который синхронизирует данные между сервером, публикующим данные, и серверами-подписчиками.

Агент History Clean Up удаляет журнал событий агента из БД распространения, и используется для управления размером этой БД. Все типы репликации используют этот агент. По умолчанию он запускается каждые 10 минут.

Агент Distribution Clean Up удаляет реплицированные транзакции из БД распространения, и отключает неактивных подписчиков, данные которых не обновлялись в течение максимального периода хранения распространяемых данных (по умолчанию — 72 часа). Если разрешены анонимные подписки, реплицированные транзакции не удаляются до истечения максимального периода хранения. Репликация моментальных снимков и репликация транзакций используют этот агент. По умолчанию он запускается каждые 10 минут.

Агент Expired Subscription Clean Up выявляет и удаляет подписки с истекшим сроком хранения. Все типы репликации используют этот агент. По умолчанию он запускается один раз в день.

Агент Reinitialize Subscriptions Having Data Validation Failures повторно инициализирует все подписки, имеющие ошибки при проверке согласованности данных. По умолчанию этот агент запускается вручную.

Агент Replication Agents Checkup являет неактивных агентов репликации и заносит соответствующие записи в журнал приложений Windows. По умолчанию он запускается каждые 10 минут.

Агенты Snapshot, Distribution и Merge можно встраивать в приложения с помощью элементов управления ActiveX.

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

Репликация моментальных снимков

При репликации моментальных снимков агент Snapshot периодически (по заданному расписанию) копирует все помеченные для репликации данные с сервера-издателя в папку моментальных снимков распространителя. Агент Distribution периодически копирует все данные из папки моментальных снимков на каждый сервер-подписчик и, используя эти данные, полностью обновляет на нем публикацию. Агент Snapshot выполняется на распространителе, а агент Distribution может выполняться как на распространителе, так и на каждом сервере-подписчике. Оба агента записывают информацию журналов событий и журнала ошибок в БД распространения (рис. 2.1.)

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

При репликации моментальных снимков подписчикам можно разрешить обновлять реплицированную информацию немедленно (Immediate Updating) и/или в порядке очереди (Queued Updating). Возможность обновления подписки (Updatable Subscription) полезна, когда подписчикам требуется изредка изменять последнюю. Если же подписку изменяют часто, лучше использовать репликацию сведением. Кроме того, в случае с обновляемыми подписками все обновления являются частью транзакции. Это означает, что обновление либо целиком подтверждается, либо откатывается, если происходит конфликт. При репликации сведением конфликты разрешаются построчно.

Рис. 2.1. Запись в журнал событий и ошибок.

Если используется немедленное обновление подписки, при любой попытке подписчика обновить реплицированные данные он сам или издатель инициируют транзакцию с двухэтапным подтверждением (two-phase commit, 2PC). 2РС-транзакция включает этап подготовки и этап подтверждения, и выполняется под управлением службы MS DTC, запущенной на подписчике и выступающей в качестве диспетчера транзакций. На подготовительном этапе MS DTC координирует действия служб SQL Server, запущенных на издателе и подписчике и играющих роль диспетчеров ресурсов, чтобы гарантировать успешное выполнение транзакции в обеих БД. На этапе подтверждения MS DTC получает от диспетчеров ресурсов уведомления об успешной подготовке, затем диспетчерам передается команда подтверждения и транзакция подтверждается на сервере-издателе и сервере-подписчике. Если на издателе имеется конфликт (конфликтующее обновление еще не было тиражировано на сервер-подписчик), транзакция, инициированная подписчиком, завершается неудачно. 2РС-транзакция гарантирует отсутствие конфликтов, поскольку издатель выявляет все конфликты до подтверждения транзакции.

Если используется очередь обновлений (Queued Updating), сделанные подписчиком изменения помещаются в очередь и периодически передаются издателю. Изменения могут быть выполнены при отсутствии соединения с издателем. Изменения, которые находятся в очереди, пересылаются на данный сервер, когда устанавливается соединение. Очередь может храниться либо в БД SQL Server, либо вы можете выбрать использование Microsoft Message Queuing, если работаете в среде Windows 2000. Подробнее об использовании Microsoft Message Queuing — в разделе «Queued Updating Components» справочной системы SQL Server Books Online. Так как обновления происходят не в реальном времени, то конфликты могут происходить, если другой подписчик или издатель изменили одни и те же данные. Конфликты разрешаются с использованием стратегии разрешения конфликтов, определяемой в момент создания публикации.

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

При репликации транзакций агент Snapshot создает исходный моментальный снимок данных, помеченных для репликации, и копирует его с сервера-издателя в папку моментальных снимков распространителя. Агент Distribution направляет полученный снимок каждому подписчику. Агент Log Reader следит за изменениями данных, участвующих в репликации, и фиксирует каждое изменение журнала транзакций в БД распространения на сервере-распространителе. Агент Distribution отправляет каждое изменение всем подписчикам в первоначальном порядке выполнения этих изменений. Если хранимая процедура используется для обновления большого количества записей, можно реплицировать эту процедуру, а не каждую обновленную строку. Все три этих агента репликации заносят информацию о событиях и ошибках в БД распространения. На рис. 2.2. показан процесс репликации транзакций.

Рис. 2.2. Процесс репликации транзакций

  •  Новые транзакции
  •   Записи журналов событий и ошибок

Агент Distribution может работать постоянно, чтобы минимизировать задержку в обновлении данных между издателем и подписчиками, или может выполняться по заданному расписанию. Подписчики при наличии сетевого соединения с издателем могут получать изменения почти в реальном времени. После того как все подписчики получат реплицированные транзакции, агент Distribution Clean Up удаляет эти транзакции из БД распространения. Если по окончании заданного периода хранения (по умолчанию — 72 часа) подписчик не получил реплицируемые транзакции, те удаляются из БД распространения и подписка дезактивируется. Это позволяет предотвратить чрезмерное увеличение размера БД распространения. Дезактивированная подписка может быть повторно активирована, и тогда подписчику с целью обновления его данных передается новый моментальный снимок.

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

При репликации сведением агент Snapshot передает начальный моментальный снимок данных, участвующих в репликации, от издателя в папку моментальных копий распространителя. Агент Merge направляет полученный снимок каждому подписчику. Также он анализирует и объединяет изменения реплицируемых данных, выполняемые издателем и подписчиками. Если при объединении изменений происходит конфликт на издателе, агент Merge разрешает его, используя указанный администратором способ. Вы можете выбрать одно из существующих средств обнаружения конфликтов или создавать свое собственное. Оба агента заносят информацию о событиях и ошибках в БД распространения (это единственная функция БД распространения в случае репликации сведением). Процесс репликации сведением показан на рис. 2.3.

Рис.2.3. Процесс репликации сведением.

Чтобы различать записи отдельных копий реплицируемой таблицы и выявлять конфликты между записями, агент Merge использует специальный уникальный столбец реплицируемых таблиц. Если такого столбца нет, агент Snapshot добавляет его при создании публикации. Кроме того, при создании публикации агент Snapshot создает на издателе триггеры. Они ведут мониторинг реплицированных записей и заносят информацию об изменениях в системные таблицы сведения. Агент Merge также создает идентичные триггеры на каждом сервере-подписчике, когда передает ему начальный моментальный снимок.

Агент Snapshot может работать постоянно, чтобы минимизировать задержку в обновлении данных между издателем и подписчиками, или может выполняться по заданному расписанию. Подписчики при наличии сетевого соединения с издателем могут получать изменения почти в реальном времени. Если по окончании заданного периода хранения (по умолчанию — 14 дней) подписчик не получил реплицируемые транзакции, подписка дезактивируется. Дезактивированная подписка может быть повторно активирована, и тогда подписчику с целью обновления его данных передается новый моментальный снимок.

Существует несколько моделей репликации (replication topologies), которые вы можете использовать в соответствии с вашими задачами репликации. Если вы используете репликацию моментальных снимков или репликацию транзакций, вам часто придется использовать удаленного распространителя. Он может предоставлять службы репликации одновременно нескольким издателям и подписчикам. Если объем реплицируемых данных невелик, распространителя и издателей нередко размещают на одном и том же компьютере.

Вместо репликации данных нескольким подписчикам через подключение, имеющее низкую пропускную способность или высокую стоимость использования, можно опубликовать данные на удаленном подписчике, который распространит их другим подписчикам в своей области. Такой подписчик называется переиздающим (publishing subscriber, republisher).

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

Консоль SQL Server Enterprise Manager — основное средство организации и мониторинга репликации. Контейнер Replication дерева консоли SQL Server Enterprise Manager содержит средства, необходимые для организации и администрирования публикаций и подписки. Узел Replication Monitor контейнера Replication используется для доступа к агентам репликации и управления их работой. Replication Monitor также позволяет определить оповещения о событиях репликации.

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

Элементы управления ActiveX применяются в пользовательских приложениях, написанных на Visual Basic или Visual C++. Они позволяют программно управлять работой агентов Shaphot, Merge и Distribution. Например, в приложении может присутствовать кнопка Synchronize, запускающая агент Merge для сведения и синхронизации данных.

SQL-DMO используется для создания пользовательских приложений, позволяющих конфигурировать, организовывать и обслуживать среду репликации.

Replication Distributor Interface обеспечивает возможность репликации данных из гетерогенных источников данных (например Access или Oracle).

Хранимые процедуры используются главным образом для создания сценариев репликации на нескольких серверах, основываясь на конфигурации репликации, за данной средствами SQL Server Enterprise Manager.

Windows Synchronization Manager — эта утилита из состава Windows 2000, находящаяся в группе программ Accessories. На компьютерах с Internet Explorer 5.0 ее можно вызвать из меню Tools браузера Internet Explorer 5.0. Это средство для управления и синхронизации публикаций SQL Server и других приложений (например Web-страницы и электронной почты).

Active Directory Services — вы можете поместить объекты репликации в каталог Active Directory, разрешая пользователям искать и подписываться на публикации (если у этих пользователей имеются соответствующие права).

Защита процесса репликации реализована на нескольких уровнях. Прежде всего, только члены роли сервера sysadmin, могут конфигурировать и администрировать распространителей, издателей и подписчиков, включая конфигурирование БД для репликации. На уровне БД только члены роли сервера sysadmin и фиксированной роли db_owner опубликованной БД могут создавать и конфигурировать публикации и подписки. Только члены роли сервера sysadmin и фиксированной роли replmonitor БД распространения могут отслеживать активность процесса репликации.

Если используется удаленный распространитель, можно организовать защищенное соединение между ним и издателем. При соединении используется учетная запись distributor_admin SQL Server (следует использовать смешанный режим проверки подлинности). На удаленном распространителе издателя можно сконфигурировать в качестве доверенного (пароль для доступа не нужен) или ненадежного (для доступа требуется пароль). Рекомендуется использовать второй вариант.

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

Статические фильтры вводят ограничения на публикацию определенных строк или столбцов; и все подписчики получают одинаковые данные (за исключением трансформируемой подписки). Все типы репликации могут использовать статические фильтры. Чтобы опубликовать отдельные наборы данных для разных подписчиков при помощи статических фильтров, следует либо создать отдельные публикации, либо использовать трансформируемую подписку (transformable subscription). Горизонтальный фильтр может значительно снизить производительность репликации транзакций, поскольку просматриваются все записи журнала транзакций БД публикаций.

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

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

По умолчанию файлы начальных моментальных снимков копируются в папку Repl-data распространителя. Однако вы можете хранить оригиналы или копии файлов мгновенных копий в другом месте, например на сетевом диске или на компакт-диске. Файлы моментальных снимков, сохраненные в резервном каталоге, могут быть сжаты (для сжатия используется формат Microsoft CAB), чтобы файлы могли уместиться на съемном носителе информации или для ускорения передачи данных при использовании соединения с низкой скоростью передачи данных. На сжатие файлов моментальных снимков потребуется дополнительное время.

По умолчанию либо агент Distribution, либо агент Merge вносят моментальный снимок в БД подписки. Если объем данных в публикации достаточно велик, считывание исходной мгновенной копии вручную с компакт-диска или другого запоминающего устройства (например, с ленты) может выполняться быстрее, чем пересылка файла по сети.

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

2.6. ПРОВЕРКА ПОДЛИННОСТИ В SQL SERVER 2000

Чтобы пользователь мог выполнять какие-либо действия или получить доступ к БД в системе SQL Server 2000, администратор должен создать регистрационную запись, которая предоставит пользователю доступ к SQL Server 2000, и назначить ей соответствующие разрешения. Регистрационная запись может быть связана с определенной группой или пользователем Windows NT 4.0/2000 или с записью пользователя SQL Server 2000 (учетная запись SQL Server 2000).

Подключаясь к SQL Server 2000, пользователь выбирает вид проверки подлинности (проверка средствами Windows или средствами SQL Server 2000) и вводит имя и пароль. Чтобы пользователь мог войти в систему, SQL Server 2000 должен проверить, является ли он зарегистрированным пользователем и имеет ли право устанавливать соединение с SQL Server, т. е. должны быть проверены регистрационные реквизиты пользователя.

Проверка подлинности средствами Windows

Если пользователь прошел проверку подлинности в домене и является зарегистрированным пользователем Windows, ОС предложит SQL Server 2000 доверять результатам этой проверки и предоставить доступ на основании указанных имени и пароля. При этом для подтверждения личности пользователя передается билет KerberosWindows 2000) или маркер доступа (в Windows NT 4.0). SQL Server 2000 проверяет аутентичность билета Kerberos или маркера доступа, и сравнивает полученные данные со списком пользователей и групп, которым разрешен доступ. На основе этой информации SQL Server 2000 предоставляет или отказывает в доступе.

Подключение, использующее средства проверки подлинности Windows, называется доверенным (trusted connection).

Проверка подлинности средствами SQL Server 2000

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

Если клиентские и серверные сетевые библиотеки не используют для всего сеанса SSL-шифрование, имя и пароль пользователя передаются SQL Server 2000 открытым текстом.

Сравнение типов проверки подлинности

В табл. 2.4. перечислены различные возможности защиты, предоставляемые при проверке подлинности средствами Windows и средствами SQL Server 2000.

Табл. 2.4.

Возможности защиты при проверке подлинности средствами Windows и SQL Server 2000

Проверка подлинности средствами Windows

Проверка подлинности средствами SQL Server 2000

Имя и пароль, указываемые пользователем для входа в домен, передаются контроллеру домена Windows в зашифрованном виде

Windows не проверяет подлинность пользователя

Когда пользователь, прошедший проверку подлинности, передает имя и пароль SQL Server 2000, билет Kerberos или маркер доступа зашифровываются

Имя и пароль, которые вводит пользователь, передаются в незашифрованном виде (если не включен протокол SSL)

В Windows NT 4.0/2000 действует политика паролей (требуется сложный пароль, срок действия пароля ограничен)

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

В Windows NT 4.0/2000 действует политика блокировки учетных записей (при нескольких попытках ввода неправильного пароля

SQL Server 2000 не блокирует учетные записи: пароль можно вводить сколько угодно раз

Клиентские сетевые библиотеки и проверка подлинности.

Чтобы подключиться к серверной сетевой библиотеке, клиенты SQL Server 2000 используют аналогичную клиентскую библиотеку. Для поддержки нужного сетевого протокола соответствующая пара сетевых библиотек должна быть активна на клиенте и на сервере. На компьютерах с Windows NT 4.0/2000 клиентскими библиотеками по умолчанию являются TCP/IP Sockets и Named Pipes; они используются в той очередности, в какой перечислены здесь. В большинстве случаев вам не придется изменять настройки сетевых библиотек. Однако если возникают проблемы с подключением к SQL Server 2000, необходимо проверить настройки сетевых библиотек. Подключить новые сетевые библиотеки, задать для них нестандартные параметры соединения и определить очередность использования библиотек на клиентском компьютере можно с помощью утилиты SQL Server Client Network Utility.

Запустить утилиту SQL Server Client Network Utility можно из меню Start\Programs\Microsoft SQL Server.

Можно реализовать принудительное SSL-шифрование. Если вы пометите соответствующий флажок, система SQL Server 2000, к которой вы подключаетесь, также должна будет использовать шифрование. С помощью SQL Server Client Network Utility вы можете установить шифрование в серверных библиотеках Net-Libraries.

При использовании сетевых библиотек Named Pipes и Multiprotocol требуется, чтобы пользователь прошел проверку подлинности в домене Windows, и только потом он может подключиться к SQL Server 2000, используя средства проверки подлинности Windows или SQL Server 2000. Это не создает проблем, если вы используете Windows-клиент и уже зарегистрировались в том же самом домене (или доверенном домене), к которому принадлежит компьютер с SQL Server 2000. Если же вы пытаетесь подключиться к SQL Server 2000 с компьютера, не имеющего доверенного соединения с доменом, то при использовании библиотек Named Pipes и Multiprotocol вы не сможете этого сделать (однако, используя библиотеку TCP/IP Sockets, вы можете подключиться, если введете правильное имя и пароль).

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

Ни библиотека TCP/IP Sockets, ни любая другая не требует начальной проверки подлинности в домене Windows. Пользователь (или приложение) может попытаться подключиться к SQL Server 2000, используя сетевой протокол TCP/IP и библиотеку TCP/IP Sockets, с любого компьютера, который может установить соединение с SQL Server 2000. Поэтому вам необходимо знать, как обезопасить домен Windows и свой экземпляр SQL Server 2000, чтобы защитить данные.

Выбор режима проверки подлинности для SQL Server 2000

При установке SQL Server 2000 вы выбираете режим проверки подлинности, который будет использовать ваша система: режим проверки подлинности Windows или смешанный.

Если ваш экземпляр SQL Server 2000 работает под управлением Windows 98/Ме, вы можете использовать только проверку подлинности средствами SQL Server

Проверка подлинности Windows

Если установлен режим проверки подлинности Windows (Windows Authentication Mode), пользователь может подключиться к SQL Server 2000 только с помощью средств проверки подлинности Windows (такое соединение называется доверенным). Windows NT 4.0/2000 обеспечивает различные методы для защиты среды Windows, которые невозможно перечислить в этой книге, но которые очень важны для защиты вашего экземпляра SQL Server 2000. К ним относятся политики учетных записей и групп, прокси-серверы, брандмауэры, маршрутизаторы и протокол IPSec (Internet Protocol Security). Благодаря этим механизмам использование режима проверки подлинности Windows (доверенного соединения) обеспечивает лучшую защиту, чем проверка подлинности средствами SQL Server 2000.

Смешанный режим проверки подлинности

При смешанном режиме проверки подлинности (Mixed Authentication Mode) пользователь может подключиться к SQL Server 2000 с помощью средств проверки подлинности Windows или SQL Server 2000. Этот режим необходим для прямого подключения к SQL Server 2000 клиентов Novell NetWare, Apple Macintosh, Banyan Vines, UNIX и Linux. Однако при использовании этого режима данные защищены меньше, чем при режиме проверки подлинности Windows, и поэтому его следует использовать только в случае крайней необходимости.

Делегирование учетной записи пользователя

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

При делегировании учетной записи пользователя требуется, чтобы на всех участвующих серверах была установлена ОС Windows 2000, поддерживался протокол Kerberos и использовалась служба Active Directory. При использовании Active Directory для передачи соответствующих разрешений пользователь должен иметь право делегирования. Чтобы предоставить пользователю эти разрешения, пометьте соответствующий флажок в диалоговом окне свойств учетной записи, доступном из оснастки Active Directory Users And Computers

Кроме того, на компьютере, где работает система SQL Server 2000, должно быть разрешено делегирование. Для выполнения распределенных запросов на двух SQL Server 2000 на различных компьютерах оба компьютера должны использовать делегирование. Это свойство учетной записи устанавливается в диалоговом окне Properties, доступном из Active Directory Users And Computers.

И наконец, чтобы использовать делегирование учетной записи пользователя, у системы SQL Server 2000 должно быть основное имя службы (Service Principal Name, SPN). SPN определяется с помощью утилиты Setspn из состава Windows 2000 Resource Kit. Чтобы задать постоянное имя SPN, назначаемое администратором домена Windows учетной записи, используемой службой SQL Server, необходимо воспользоваться утилитой Setspn. Постоянное имя SPN назначается на основании номера используемого порта. Разным экземплярам SQL Server и разным портам присваиваются разные SPN. Чтобы разрешить делегирование, используется сетевая библиотека TCP/IP, а не Named Pipes, поскольку SPN соответствует определенному сокету TCP/IP (TCP/IP socket).

В следующем примере SPN назначается для экземпляра SQL Server 2000 с именем SelfPacedCPU.SelfPacedSQL.MSFT, использующего порт 1433 и учетную запись пользователя домена SelfPacedSQL\ SQLService:

Setspn -a MSSQLSvc/SelfPacedCPU.Se