В стандарте SQL определены два оператора: GRANT и REVOKE пре­доставления и отмены привилегий соответственно.

Оператор предоставления привилегий имеет следующий формат:

GRANT {<список действий> | ALL PRIVILEGES }

ON <имя_объекта>

ТО {<имя_пользователя> | PUBLIC }

Здесь список действий определяет набор действий из обще допустимого перечня действий над объектом данного типа.

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

<имя_объекта> - задает имя конкретного объекта: таблицы, представления, хра­нимой процедуры, триггера.

<имя_пользователя> или PUBLIC определяет, кому предоставляются данные приви­легии.

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

Рассмотрим пример, пусть у нас существуют три пользователя с абсолютно уни­кальными именами user1, user2 и user3. Все они являются пользователями од­ной БД.

Userl создал объект Tab1, он является владельцем этого объекта и может пере­дать права на работу с эти объектом другим пользователям. Допустим, что поль­зователь user2 является оператором, который должен вводить данные в Tab1 (например, таблицу новых заказов), а пользователь user3 является менеджером отдела, который должен регулярно про­сматривать введенные данные.

Для объекта типа таблица полным допустимым перечнем действий является на­бор из четырех операций: SELECT, INSERT, DELETE, UPDATE. При этом операция об­новления может быть ограничена несколькими столбцами.

Общий формат оператора назначения привилегий для объекта типа таблица бу­дет иметь следующий синтаксис:

GRANT {[.INSERT][.DELETE][.UPDATE

(<список столбцов>)]} ON <имя_таблицы>

TO {<имя_пользователя> | PUBLIC }

В рассматриваемом случае необходимо выполнить следующие назначения:

Эти назначения означают, что пользователь user2 имеет право только вводить новые строки в отношение Tab1, а пользователь user3 имеет право просматри­вать все строки в таблице Tab1.

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

GRANT SELECT, UPDATE (SENA)

Если наш пользователь user1 предполагает, что пользователь user4 может его за­мещать в случае его отсутствия, то он может предоставить этому пользователю все права по работе с созданной таблицей Tab1.

GRANT ALL PRIVILEGES

WITH GRANT OPTION

В этом случае пользователь user4 может сам назначать привилегии по работе с таблицей Tab1 в отсутствие владельца объекта пользователя user1. Поэтому в случае появления нового оператора пользователя user5 он может назначить ему права на ввод новых строк в таблицу командой:

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

GRANT SELECT, UPDATE, DELETE

WITH GRANT OPTION

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

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

Так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, и, следовательно, для таких представлений набор допустимых действий ограничивается операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для та­кого представления допустимыми будут все 4 операции- SELECT, INSERT, UPDATE и DELETE.

Для отмены ранее назначенных привилегий в стандарте SQL определен опера­тор REVOKE. Оператор отмены привилегий имеет следующий синтаксис:

REVOKE {<список операций>| ALL PRIVILEGES}

ON <имя_объекта>

FROM {<список пользователей | PUBLIC }

(CASCADE | RESTRICT }

Параметры CASCADE или RESTRICT определяют, каким образом должна произво­диться отмена привилегий. Параметр CASCADE отменяет привилегии не только пользователя, который непосредственно упоминался в операторе GRANT при пре­доставлении ему привилегий, но и всем пользователям, которым этот пользова­тель присвоил привилегии, воспользовавшись параметром WITH GRANT OPTION.

Например, при использовании операции

REVOKE ALL PRIVILEGES

TO user4 CASCADE

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

Параметр RESTRICT ограничивает отмену привилегий только пользователю, не­посредственно упомянутому в операторе REVOKE. Но при наличии делегирован­ных привилегий этот оператор не будет выполнен.Так, например, операция:

REVOKE ALL PRIVILEGES

TO user4 RESTRICT

не будет выполнена, потому что пользователь user4 передал часть своих полно­мочий пользователю user5.

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

Поэтому корректным будет следующее использование оператора REVOKE

TO user2, user4 CASCADE

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

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

Если необходимо изменить это условие, то после создания хранимой процедуры необходимо записать оператор REVOKE

TO PUBLIC CASCADE

И теперь можно назначить новые права пользователю user4

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

GRANT CREATE TABLE ALTER TABLE DROP TABLE

В этом случае пользователь user1 может создавать, изменять или удалять табли­цы в БД DB_LIB, однако он не может разрешить создавать или изменять таблицы в этой БД другим пользователям, потому что ему дано разрешение без права де­легирования своих возможностей.

В некоторых СУБД пользователь может получить права создавать БД. Напри­мер, в MS SQL Server системный администратор может предоставить пользова­телю main_user право на создание своей БД на данном сервере. Это может быть сделано следующей командой:

GRANT CREATE DATABASE

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

В СУБД, которые поддерживают однобазовую архитектуру, такие разрешения недопустимы. Например, в СУБД Oracle на сервере создается только одна БД, но пользователи могут работать на уровне подсхемы (части таблиц БД и свя­занных с ними объектов). Поэтому там вводится понятие системных привиле­гий. Их очень много, 80 различных привилегий.

Для того чтобы разрешить пользователю создавать объекты внутри этой БД, используется понятие системной привилегии, которая может быть назначена од­ному или нескольким пользователям. Они выдаются только на действия и кон­кретный тип объекта. Поэтому если системный администратор предо­ставил пользователю право создания таблиц (CREATE TABLE), то для того чтобы он мог создать триггер для таблицы, ему необходимо предоставить еще одну системную привилегию CREATE TRIGGER. Система защиты в Oracle считается од­ной из самых мощных, но это имеет и обратную сторону - она весьма сложная. Поэтому задача администрирования в Oracle требует хорошего знания как се­мантики принципов поддержки прав доступа, так и физической реализации этих возможностей.

«» — это единая универсальная среда для доступа, настройки и администрирования всех компонентов MS SQL Server, а также для разработки компонентов системы, редактирования текстов запросов, создания скриптов и пр. Благодаря наличию большого количества визуальных средств управления, «Среда SQL Server Management Studio » позволяет выполнять множество типовых операций по администрированию MS SQL Server администраторам с любым уровнем знаний SQL Server. Удобная среда разработки, встроенный веб-браузер для быстрого обращения к библиотеке MSDN или получения справки в сети, подробный учебник, облегчающий освоение многих новых возможностей, встроенная справка от сообществ в Интернете и многое другое позволяют максимально облегчить процесс разработки в среде SQL Server, а также дает богатые возможности для создания различных сценариев SQL Server. Об установке и запуске программы «Среда SQL Server Management Studio » и пойдет речь в данной статье.

1. Установка программы «Среда SQL Server Management Studio»

Программа «Среда SQL Server Management Studio » поставляется вместе с дистрибутивом MS SQL Server. Для ее установки необходимо отметить компоненты

  • Средства управления - основные (Management Tools - Basic)
    • Средства управления - полный набор (Management Tools - Complete)

на странице выбора компонент программы установки MS SQL Server.

Подробно про установку компонент MS SQL Server я писал в статье .

2. Запуск программы «Среда SQL Server Management Studio»

По умолчанию файлы программы «SQL Server Management Studio » устанавливаются в «C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\Management Studio\». Для запуска «SQL Server Management Studio » следует запустить исходный файл Ssms.exe , находящийся в данной директории.

Также в Microsoft Windows Server 2012 (R2) ярлык для запуска «SQL Server Management Studio » можно найти в списке всех программ.

А в Microsoft Windows Server 2008 (R2) в меню «Пуск » (Start) — «Microsoft SQL Server 2012 » — «Среда SQL Server Management Studio ».

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

  • <АдресСервера > — имя или IP адрес сервера для экземпляра по умолчанию
  • <АдресСервера >\<ИмяЭкземпляра > — для именованного экземпляра SQL Server
  • <ИмяПсевдонима > — если применяются SQL Server

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

Введя имя экземпляра сервера и данные для авторизация необходимо нажать «Соединить » (Connect) для подключения к выбранному SQL серверу.

Среда SQL Server Management Studio имеет два основных назначения: администрирование серверов баз данных и управление объектами баз данных. Эти функции рассматриваются далее.

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

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают, среди прочих, следующие:

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

    подключение к серверу;

    создание новых групп серверов;

    управление множественными серверами;

    пуск и остановка серверов.

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

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

Среда SQL Server Management Studio отделяет деятельность по регистрации серверов от деятельности по исследованию баз данных и их объектов. (Действия этих обоих типов можно выполнять посредством обозревателя объектов.) Прежде чем можно использовать базы данных и объекты любого сервера, будь то локального или удаленного, его нужно зарегистрировать.

Сервер можно зарегистрировать при первом запуске среды SQL Server Management Studio или позже. Чтобы зарегистрировать сервер базы данных, щелкните правой кнопкой требуемый сервер в обозревателе объектов и в контекстном меню выберите пункт Register. Если панель обозревателя объектов скрыта, то откройте ее, выполнив команду меню View --> Object Explorer. Откроется диалоговое окно New Server Registration (Регистрация нового сервера), как это показано на рисунке ниже:

Выберите имя сервера, который нужно зарегистрировать, и тип проверки подлинности для этого сервера (т.е. проверка подлинности Windows или проверка подлинности SQL Server), после чего нажмите кнопку Save.

Подключение к серверу

Среда SQL Server Management Studio также разделяет задачи регистрации сервера и подключения к серверу. Это означает, что при регистрации сервера автоматического подключения этого сервера не происходит. Чтобы подключиться к зарегистрированному серверу, нужно щелкнуть правой кнопкой требуемый сервер в окне инспектора объектов и в появившемся контекстном меню выбрать пункт Connect (Подключиться).

Создание новой группы серверов

Чтобы создать новую группу серверов в панели зарегистрированных серверов, щелкните правой кнопкой узел Local Server Groups (Группы локальных серверов) в окне Registered Server и в контекстном меню выберите пункт New Server Group (Создание группы серверов). В открывшемся диалоговом окне New Server Group Properties (Свойства новой группа серверов) введите однозначное имя группы и, по выбору, ее описание.

Управление множественными серверами

Посредством обозревателя объектов среда SQL Server Management Studio позволяет администрировать множественные серверы баз данных (называемые экземплярами) на одном компьютере. Каждый экземпляр компонента Database Server имеет свой собственный набор объектов баз данных (системные и пользовательские базы данных), который не разделяется между экземплярами.

Для управления сервером и его конфигурацией щелкните правой кнопкой имя сервера в обозревателе объектов и в появившемся контекстном меню выберите пункт Properties (Свойства). Откроется диалоговое окно Server Properties (Свойства сервера), содержащее несколько страниц, таких как General (Общие), Security (Безопасность), Permissions (Разрешения) и т.п.

На странице General отображаются общие свойства сервера:

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

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

Запуск и останов серверов

Сервер Database Engine по умолчанию запускается автоматически при запуске операционной системы Windows. Чтобы запустить сервер с помощью среды SQL Server Management Studio, щелкните правой кнопкой требуемый сервер в инспекторе объектов и в контекстном меню выберите пункт Start (Запустить). Это меню также содержит пункты Stop (Остановить) и Pause (Приостановить) для выполнения соответствующих действий с сервером.

Управление базами данных посредством обозревателя объектов Object Explorer

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают:

    создание баз данных, не прибегая к использованию языка Transact-SQL;

    модифицирование баз данных, не прибегая к использованию языка Transact-SQL;

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

    создание и исполнение инструкций SQL (описывается в следующей статье).

Создание баз данных без использования T-SQL

Новую базу данных можно создать посредством обозревателя объектов Object Explorer . Как можно судить по его названию, обозреватель объектов также можно использовать для исследования объектов сервера. С панели этого инструмента можно просматривать все объекты сервера и управлять сервером и базами данных. Дерево иерархии объектов сервера содержит, среди прочих папок, папку Databases (Базы данных). Эта папка, в свою очередь, содержит несколько подпапок, включая папку для системных баз данных, и по папке для каждой базы данных, созданной пользователем.

Чтобы создать базу данных посредством обозревателя объектов, щелкните правой кнопкой узел Databases и выберите пункт меню New Database (Создать базу данных). В открывшемся диалоговом окне New Database в поле Database name введите имя новой базы данных, после чего нажмите кнопку ОК.

Каждая база данных обладает несколькими свойствами, такими как тип файла, начальный размер и т.п. Список страниц свойств базы данных расположен в левой панели диалогового окна New Database. Страница General (Общие) диалогового окна Database Properties содержит, среди прочего, такую информацию, как имя, владелец и параметры сортировки базы данных:

Свойства файлов данных определенной базы данных перечисляются на странице Files (Файлы) и содержат такую информацию, как имя и начальный размер файла, расположение базы данных, а также тип файла (например, primary). База данных может храниться в нескольких файлах. В SQL Server применяется динамическое управление дисковым пространством. Это означает, что можно сконфигурировать размер базы данных для автоматического увеличения и уменьшения по мере надобности.

Чтобы изменить свойство Autogrowth (Автоувеличение) на странице Files, в столбце Autogrowth нажмите значок троеточия (...) и внесите соответствующие изменения в диалоговом окне Change Autogrowth. Чтобы позволить автоматическое увеличение размера базы данных, нужно установить флажок Enable Autogrowth . Каждый раз, когда существующий размер файла недостаточен для хранения добавляемых данных, сервер автоматически запрашивает систему выделить файлу дополнительное дисковое пространство. Объем дополнительного дискового пространства (в процентах или мегабайтах) указывается в поле File Growth (Увеличение размера файла) в том же диалоговом окне. А в разделе Maximum File Size (Максимальный размер файла) можно или ограничить максимальный размер файла, установив переключатель Limited to (MB) (Ограничение (Мбайт)), или снять ограничения на размер, установив переключатель Unlimited (Без ограничений) (это настройка по умолчанию). При ограниченном размере файла нужно указать его допустимый максимальный размер.

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

На странице Options (Параметры) диалогового окна Database Properties можно просмотреть и модифицировать все параметры уровня базы данных. Существуют следующие группы параметров: Automatic (Автоматически), Containment (Включение), Cursor (Курсор), Miscellaneous (Вспомогательные), Recovery (Восстановление), Service Broker (Компонент Service Broker) и State (Состояние). Группа State содержит, например, следующие четыре параметра:

Database Read-Only (База данных доступна только для чтения)

Позволяет установить доступ к базе данных полный доступ или доступ только для чтения. В последнем случае пользователи не могут модифицировать данные. Значение по умолчанию этого параметра - False.

Restrict Access (Ограничение доступа)

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

Database State (Состояние базы данных)

Описывает состояние базы данных. Значение по умолчанию этого параметра - Normal.

Encryption Enabled (Шифрование включено)

Определяет режим шифрования базы данных. Значение по умолчанию этого параметра - False.

На странице Extended Properties (Расширенные свойства) отображаются дополнительные свойства текущей базы данных. На этой странице можно удалять существующие свойства и добавлять новые.

На странице Permissions (Разрешения) отображаются все пользователи, роли и соответствующие разрешения.

Остальные страницы Change Tracking (Отслеживание изменений), Mirroring (Зеркальное отображение) и Transaction Log Shipping (Доставка журналов транзакций) описывают возможности, связанные с доступностью данных.

Модифицирование баз данных

С помощью обозревателя объектов можно модифицировать существующие базы данных, изменяя файлы и файловые группы базы данных. Чтобы добавить новые файлы в базу данных, щелкните правой кнопкой требуемую базу данных и в контекстном меню выберите пункт Properties. В открывшемся диалоговом окне Database Properties выберите страницу Files и нажмите кнопку Add, расположенную внизу раздела Database files. В раздел будет добавлена новая строка, в поле Logical Name которой следует ввести имя добавляемого файла базы данных, а в других полях задать необходимые свойства этого файла. Также можно добавить и вторичную файловую группу для базы данных, выбрав страницу Filegroups (Файловые группы) и нажав кнопку Add.

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

Чтобы удалить базы данных с помощью обозревателя объектов, щелкните правой кнопкой имя требуемой базы данных и в открывшемся контекстном меню выберите пункт Delete (Удалить).

Управление таблицами

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

Для практики создания таблиц, в базе данных SampleDb создадим таблицу Department. Чтобы создать таблицу базы данных с помощью обозревателя объектов, разверните в нем узел Databases, а потом узел требуемой базы данных, щелкните правой кнопкой папку Tables и в открывшемся контекстном меню выберите пункт New Table. В верхней части с правой стороны окна средства Management Studio откроется окно для создания столбцов новой таблицы. Введите имена столбцов таблицы, их типы данных и разрешение значений null для каждого столбца, как это показано в правой верхней панели на рисунке ниже:

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

Тип данных существующего столбца можно изменить на вкладке Column Properties (Свойства столбца) (нижняя панель на рисунке). Для одних типов данных, таких как char, требуется указать длину в строке Length, а для других, таких как decimal, на вкладке Column Properties требуется указать масштаб и точность в соответствующих строках Scale (Масштаб) и Precision (Точность). Для некоторых других, таких как int, не требуется указывать ни одно из этих свойств. (Недействительные значения для конкретного типа данных выделены затененным шрифтом в списке всех возможных свойств столбца.)

Чтобы разрешить значения null для данного столбца, следует установить для него соответствующий флажок поля. Также, если для столбца требуется значение по умолчанию, его следует ввести в строку Default Value or Binding (Значение по умолчанию или привязка) панели Column Properties. Значение по умолчанию присваивается ячейке столбца автоматически, если для нее явно не введено значение.

Столбец Number является первичным ключом таблицы Department. Чтобы сделать столбец первичным ключом таблицы, щелкните его правой кнопкой и в контекстном меню выберите пункт Set Primary Key (Задать первичный ключ).

Завершив все работы по созданию таблицы, щелкните крестик вкладки конструктора таблиц. Откроется диалоговое окно с запросом, сохранить ли сделанные изменения. Нажмите кнопку Yes, после чего откроется диалоговое окно Choose Name (Выбор имени) с запросом ввести имя таблицы. Введите требуемое имя таблицы и нажмите кнопку OK. Таблица будет сохранена под указанным именем. Чтобы отобразить новую таблицу в иерархии базы данных, в панели инструментов обозревателя объектов щелкните значок Renew (Обновить).

Для просмотра и изменения свойств существующей таблицы разверните узел базы данных, содержащей требуемую таблицу, разверните узел Tables в этой базе данных и щелкните правой кнопкой требуемую таблицу, а затем в контекстном меню выберите пункт Properties. В результате для данной таблицы откроется диалоговое окно Table Properties. Для примера, на рисунке ниже показано диалоговое окно Table Properties на вкладке General для таблицы Employee базы данных SampleDb.

Чтобы переименовать таблицу, в папке Tables щелкните ее правой кнопкой в списке таблиц и в контекстном меню выберите пункт Rename. А чтобы удалить таблицу, щелкните ее правой кнопкой и выберите пункт Delete.

Создав все четыре таблицы базы данных SampleDb (Employee, Department, Project и Works_on - подробную структуру таблиц вы можете найти в исходниках), можно использовать еще одну возможность среды SQL Server Management Studio, чтобы отобразить диаграмму типа "сущность - отношение" - диаграмму (ER) (entity-relationship) этой базы данных. (Процесс преобразования таблиц базы данных в диаграмму "сущность - отношение" (ER) называется обратным проектированием.)

Чтобы создать диаграмму ER для базы данных SampleDb, щелкните правой кнопкой ее подпапку Database Diagrams (Диаграммы баз данных) и в контекстном меню выберите пункт New Database Diagram (Создать диаграмму базы данных). Если откроется диалоговое окно, в котором спрашивается, создавать ли вспомогательные объекты, выберите ответ Yes.

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

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

Между таблицами базы данных SampleDb существует три отношения. Таблица Department имеет отношение типа 1:N с таблицей Employee, поскольку каждому значению первичного ключа таблицы Department (столбец Number) соответствует одно или более значений столбца DepartmentNumber таблицы Employee (в одном отделе может работать несколько сотрудников).

Аналогично существует отношение между таблицами Employee и Works_on, поскольку только значения, которые присутствуют в столбце первичного ключа таблицы Employee (Id) также имеются в столбце EmpId таблицы Works_on. Третье отношение существует между таблицами Project и Works_on, т.к. только значения, которые присутствуют в первичном ключе таблицы Project (Number) также присутствуют в столбце ProjectNumber таблицы Works_on.

Чтобы создать эти три отношения, диаграмму ER нужно реконструировать, указав для каждой таблицы столбцы, которые соответствуют ключевым столбцам других таблиц. Такой столбец называется внешним ключом (foreign key) . Чтобы увидеть, как это делается, определим столбец DepartmentNumber таблицы Employee, как внешний ключ таблицы Department. Для этого выполним следующие действия:


Подобным образом создаются и другие два отношения. На рисунке ниже показана диаграмма ER, отображающая все три отношения между таблицами базы данных SampleDb:

Автор: Майк Вайнер (Mike Weiner)
Соавтор: Бурцин Пэйтел (Burzin Patel)
Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)

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

Монитор активности

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

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.

Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio

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

С помощью монитора активности администратор также может выполнять следующие задачи:

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

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

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC :Completed , SQL :BatchStarting и SQL :BatchCompleted , а также Audit Login и Audit Logout .

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.

Аудит

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

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

Существуют группы действий аудита на уровне сервера, например, следующие:

· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.

· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.

· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.

Группы действий аудита на уровне базы данных включают следующие:

· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.

Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server .

Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий - еще одной новой функции SQL Server 2008.

Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [ dbo .Payroll ] ?».

Дополнительные сведения об использовании аудита SQL Server и примеры его реализации см. в разделе Руководство по обеспечению соответствия требованиям в SQL Server 2008 .

Сжатие резервных копий

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

При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией - Resource Governor .

Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных . Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера ; его также можно установить, выполнив хранимую процедуру sp _ configure со значением параметра backup compression default , равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.

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

Серверы централизованного управления

Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.

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

· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008 .

· Импорт и определение политик на многих серверах: в рамках функциональности Управления на основе политик (еще одной новой функции SQL Server 2008, также описанной в этой статье), SQL Server 2008 обеспечивает возможность импорта файлов политик в отдельные группы серверов централизованного управления и позволяет определять политики на всех серверах, зарегистрированных в определенной группе.

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

· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.

Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 - знакомы ли вы с ними?

Сборщик данных и хранилище данных управления

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

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

Еще одна полезная функция хранилища данных управления - это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процессDCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.

Рис. 2: Вид отчета сборщика данных SQL Server 2008

В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQLServer в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQLServer 2008, кроме SQLServerExpress.

Сжатие данных

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

Сжатие данных, представленное в SQL Server 2008, помогает разрешить эти проблемы. Эта функция позволяет администратору избирательно сжимать любые таблицы, секции таблиц или индексы, благодаря чему уменьшается занимаемое пространство на диске и в памяти, а также размер операций ввода-вывода. Сжатие и распаковка данных нагружают процессор; тем не менее, во многих случаях дополнительная нагрузка на процессор более чем компенсируется выигрышем в объемах ввода-вывода. В конфигурациях, в которых ввод-вывод является «узким местом», сжатие данных также может обеспечить рост производительности.

В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.

SQLServer 2008 поддерживает два типа сжатия: сжатие строк , при котором сжимаются отдельные столбцы таблицы, и сжатие страниц , при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQLServer 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.

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

Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедуройsp _estimate _data _compression _savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQLServer 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.

Дополнительные сведения об использовании сжатия см. в документе Создание сжатых таблиц и индексов .

Управление на основе политик

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

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

Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQLServerManagement Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.

В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQLServer. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008 .

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

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

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

Во-первых, структуры планов (Plan Guide):

В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции - «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документе Принудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.

В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов , позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.

sp_create_plan_guide_from_handle
@name = N’MyQueryPlan’,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;

У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides . Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide , для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.

Во-вторых, эскалация блокировок:

Эскалация блокировки часто вызывало проблемы блокировки, а иногда даже взаимоблокировку. Устранять эти проблемы приходилось администратору. В предыдущих версиях SQLServer можно было управлять эскалацией блокировок (флаги трассировки 1211 и 1224), но это было возможно только для детализации на уровне экземпляра. Для одних приложений это устраняло проблему, а для других вызывало еще большие проблемы. Другим недостатком алгоритма эскалации блокировок в SQL Server 2005 было то, что блокировки секционированных таблиц укрупнялись напрямую до уровня таблиц, а не до уровня секций.

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

Resource Governor

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

Новая функция SQL Server 2008 - «Регулятор ресурсов»(Resource Governor) - помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.

На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.

Ниже приведены особенности, которые следует учитывать при использовании регулятора ресурсов.

— Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.

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

— Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.

— Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.

— Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL ServerIntegration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.

Прозрачное шифрование данных (TDE)

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

Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных (TDE). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных (DEK). DEK - это симметричный ключ, защищаемый сертификатом, который хранится в базе данных >master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами (EKM).

Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов - это отдельная функция, позволяющая шифровать даже активные данные.

Шифрование базы данных - одноразовый процесс, который можно запустить командой Transact — SQL или из среды SQL Server Management Studio , после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys . Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES _128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB . Наконец, при одновременном использовании различных функций необходимо учитывать следующее:

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

TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer ; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Прозрачное шифрование данных .

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