Управление блокировками в SQL Server. Управление базами данных посредством обозревателя объектов Object Explorer
Rick Dobson, Ph.D. Выборочный перевод Леденева С. А.
Эта статья рассказывает о том, как управлять безопасностью SQL сервера, используя Access совместно с библиотекой SQL Distributed Management Objects (SQL-DMO) в Visual Basic for Applications (VBA)
Вступление
Эта статья в основном ориентирована на разработчиков ADP проектов. Возможно, Вы знакомы с 2-мя вещами относительно безопасности SQL сервера. Во-первых, безопасность - это не опция, как это было в Jet. Во-вторых, модель безопасности SQL сервера отличается от модели безопасности Access. Даже если у Вас имеются приблизительные понятия о безопасности SQL сервера, эта статья научит Вас, как настроить ее для защиты ресурсов вашего проекта.
Эта статья также может быть полезна администраторам SQL серверов. Решения, предлагаемые здесь, могут помочь им в управлении безопасностью сервера средствами Access. Из содержания этой статьи администраторы могут узнать об управлении безопасностью сервера без использования графических средств Enterprise Manager или запуска Transact SQL (T-SQL) скриптов из Query Analyzer. Изучив, как управлять безопасностью сервера, используя Access, администраторы получат альтернативу создания более быстрого по исполнению решения.
Cтатью можно условно разделить на две части о безопасности SQL Server 7.0 и SQL Server 2000. В начале рассматривается концепция безопасности SQL сервера, акцентируя внимание на том, как управлять безопасностью, используя Access. Этих сведений достаточно для разработки многопользовательских приложений с разными группами пользователей, имеющих четко определенные права на использование объектов сервера. Вторая часть статьи демонстрирует программные решения, основанные на управлении объектами SQL-DMO (SQL Distributed Management Objects) средствами Microsoft Visual Basic® for Applications (VBA). Поскольку SQL-DMO имеет иерархическую модель построения объектов, очень похожую на модель построения объектов Microsoft Office, то все, что необходимо, это больше узнать об объектах, свойствах, методах, и событиях SQL-DMO относительно безопасности. Ко всему прочему, программистам Access 2002 не обойтись без программирования функций настройки безопасности в VBA, поскольку они недоступны из меню Безопасность базы данных (Database Security).
Концепция безопасности SQL сервера
Этот раздел состоит из 4-х основных частей. Ознакомление начинается с описания аутентификации - процесса проверки пользователя на право доступа к базе данных сервера. Используя Enterprise Manager, SQL сервер предоставляет два вида аутентификации. Первая часть раздела показывает различия и сходства между ними.
Далее говорится о понятии авторизованного пользователя, служащего для осуществления соединения с сервером. Авторизованные пользователи называются логинами (logins). Два типа логинов соответствуют двум моделям аутентификации SQL сервера. Логины отвечают за права доступа к серверу.
Третья часть знакомит с понятием пользователей (users). SQL сервер резервирует понятие users, как понятие, характеризующее пользователей баз данных. Обычно на сервере существует несколько логинов, каждый логин ассоциируется с несколькими пользователями - одним на каждую базу данных, доступ к которой разрешен для данного логина. Логины и пользователи могут принадлежать фиксированным ролям, которые описывают набор разрешений. Вы можете управлять поведением логинов и пользователей, управляя их принадлежностью фиксированным ролям сервера и фиксированным ролям базы данных соответственно.
В четвертой части говорится об определяемых пользователем (user-defined) ролях баз данных.
Аутентификация
Аутентификация описывается как процесс авторизации пользователя на экземпляре SQL сервера и некоторой базе данных, ассоциированной с пользователем. Авторизация ассоциируется с логином, который описывает права пользователя на сервере. Авторизация работает подобно ключу от замка. Если ключ подходит к замку, человек использует его для доступа ко всему, что находится в комнате, дверь в которую была заперта на этот замок. SQL сервер обычно имеет несколько замков с различными ключами к ним. Каждый ключ дает доступ к различным наборам баз данных, находящимся на сервере.
SQL сервер поддерживает два вида аутентификации: SQL Server и Microsoft Windows® (или Windows NT® с SQL Server 7.0). Эти два вида определяют, кто осуществляет проверку логина: SQL сервер или Windows. При Windows аутентификации пользователи имеют доверительные учетные записи для доступа к SQL серверу. Эти учетные записи проверяет Windows, но SQL сервер "знает" имя учетной записи. Windows аутентификация дает пользователям возможность авторизоваться в Windows и на SQL сервере вводом только одного пароля. При аутентификации SQL Server SQL сервер сам осуществляет авторизацию. При любом типе аутентификации SQL сервер должен знать авторизованные логины.
Есть плюсы и минусы и у первого и у второго типа, однако предпочтительней Windows аутентификация. В Windows осуществляется более сложная процедура авторизации, и она освобождает администратора SQL сервера от необходимости управлять учетными записями. С другой стороны, не все операционные системы, поддерживающие SQL сервер, поддерживают Windows авторизацию. Например, Microsoft Data Engine (MSDE), установленный с Microsoft Office 2000 и Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) установленный с Office XP: и тот и другой вариант могут работать в системе Windows 98, которая не поддерживает Windows аутентификацию. Или SQL Server 7.0 и MSDE, работающие в системе Windows 95. Некоторые организации могут предпочесть возможность контролировать доступ к ресурсам базы данных на уровне администратора БД вместо более глобального уровня администратора Windows.
Любой экземпляр SQL сервера может иметь любой из двух типов аутентификации, которые можно установить в Enterprise Manager (я расскажу о третьем типе далее в этой статье). Документация SQL сервера определяет термин смешанного (mixed) режима подключения к SQL серверу, который поддерживает и Windows, и SQL Server аутентификацию. Если сервер поддерживает только Windows аутентификацию, тогда сервер имеет Windows (или Windows NT) тип аутентификации. По умолчанию MSDE устанавливается со смешанным типом аутентификации. MSDE 2000, напротив, по умолчанию устанавливается с Windows аутентификацией. Разработчики и администраторы, использующие Enterprise Manager, могут изменять тип аутентификации, используя графические средства. Так же любое приложение может использовать библиотеку SQL-DMO для изменения типа аутентификации сервера.
Логины и фиксированные серверные роли
Проект ADP содержит окно базы данных (Database window) похожее на традиционное для Jet решений. Однако проект ADP соединяется с сервером через OLE DB соединение в отличие от базы данных Jet. В проекте ADP тип логина указывается в диалоговом окне Параметры соединения (Data Link Properties). Для вызова этого диалога: из окна базы данных выберите меню Файл (File), в меню выберите Соединение (Connection).
Диалоговое окно Параметры соединения позволяет выбрать сервер баз данных, логин, и базу данных, с которой проект соединится через OLE DB. Подключаться к серверу можно двумя способами. При первом выбирается использование внутренней проверки безопасности Windows NT (use Windows NT integrated security). Это называется Windows аутентификацией. Выбрав эту опцию, не нужно указывать логин и пароль. Это потому, что Windows авторизует пользователя во время запуска Windows. Когда выбрана опция использования внутренней безопасности, проект ADP посылает логин Windows на SQL сервер при попытке создать с ним OLE DB соединение. Выбрав вторую опцию (use a specific user name and password), необходимо указать логин SQL сервера. В поле имени пользователя (user name) должно быть введено стандартное имя логина SQL сервера - логина, поддерживаемого данным SQL сервером. Пароль опционален, однако строго рекомендуется использовать пароль, поскольку совместно с логином, пароль предоставляет дополнительный уровень безопасности.
Хотя ввод логина является ключевым моментом при создании OLE DB соединения в проекте ADP, логин сам по себе не является достаточным для предоставления прав на выполнение каких-либо задач на сервере или с базой данных. Существует понятие членства логина в фиксированных серверных ролях, которое позволяет проекту ADP выполнять некоторые серверные функции, такие как, создание новых баз данных и управление логинами. В зависимости от версии SQL сервера, к которому подключается ADP проект, существует семь или восемь фиксированных серверных ролей. SQL Server 7.0 и MSDE предоставляют семь фиксированных серверных ролей, а SQL Server 2000 и MSDE 2000 - восемь. SQL Server Books Online (BOL) содержит исчерпывающую документацию о фиксированных серверных ролях, включая T-SQL выражения для назначения и удаления логинов из членства в ролях. К примеру, раздел Roles в BOL, рассказывает о фиксированных серверных полях и соответствующем наборе разрешений для доступа к базам данных, таких как просмотр и запись в таблицы.
Следующая таблица содержит краткий обзор имен фиксированных серверных ролей и краткое их описание. Выполнив системную хранимую процедуру sp_helpsrvrole можно получить список имен фиксированных серверных ролей. Исполнение системной хранимой процедуры sp_srvrolepermission выводит подробный перечень функций для каждой фиксированной серверной роли. Есть различия между фиксированными серверными ролями в 7.0 и 2000 версиях SQL сервера. Например, bulkadmin является новой ролью в SQL Server 2000. Дополнительно, выражение DROP DATABASE было доступно только для роли sysadmin в SQL Server 7.0, а SQL Server 2000 дает право на выполнение этой процедуры также и членам роли dbcreator.
|
Имя фиксированной серверной роли |
Описание фиксированной серверной роли |
| sysadmin | Выполнение любого выражения сервера или базы данных |
| serveradmin | Администрирование сервера, конфигурация, запуск, остановка. |
| setupadmin | Администрирование присоединенных (linked) серверов и право запуска хранимых процедур на этапе запуска сервера. |
| securityadmin | Управление логинами, паролями. Может давать право на создание новых баз данных. |
| processadmin | Выполнение команды KILL. |
| dbcreator | Создание, изменение, переименование и удаление баз данных. |
| diskadmin | Управление файлами на диске. |
| bulkadmin | Выполнение выражений BULK INSERT. |
Логин sa является специальным логином SQL сервера. Этот логин входит в группу sysadmin и предоставляет право на выполнение любых функций на сервере. SQL сервер создает этот логин на этапе установки и его нельзя удалить. Сразу после окончания установки логин sa не имеет пароля. Необходимо задать пароль для sa для обеспечения безопасности Вашего сервера баз данных, особенно для серверов со смешанным типом аутентификации. Помните: сервера с Windows аутентификацией не принимают и не обрабатывают логины SQL сервера.
При установке SQL сервера на Windows 98 или Windows ME сервер всегда устанавливается со смешанным типом аутентификации, потому он может принимать логины SQL сервера. Типы аутентификации по умолчанию различаются для SQL Server 7.0 и MSDE от SQL Server 2000 и MSDE 2000, устанавливаемых на Windows 2000 и Windows NT. Для SQL Server 7.0 и MSDE процесс установки по умолчанию устанавливает сервер со смешанным типом аутентификации. Напротив, SQL Server 2000 и MSDE 2000 по умолчанию устанавливаются с Windows аутентификацией. Кроме того, процесс установки версии 2000 назначает членов группы администраторов Windows членами фиксированной серверной роли sysadmin. Потому эти логины подобны логину sa, который имеет полный контроль над сервером.
Пользователи и фиксированные роли баз данных
Обычно пользователи получают доступ к SQL Server, чтобы работать с одной или несколькими базами данных. Для этой цели SQL сервер имеет механизм приведения в соответствие логинов с базами данных. Так же как SQL сервер может иметь один или больше логинов, каждый логин может иметь одну или больше учетных записей, с ним ассоциированных. Однако одна учетная запись соответствует только одному логину (за двумя исключениями). Документация SQL сервера определяет учетные записи как пользователей баз данных (или пользователей - users). Каждый пользователь базы данных имеет доступ к этой базе данных.
SQL сервер может предоставить пользователям баз данных право владения базой данных и разрешения на использование объектов базы данных. SQL сервер обеспечивает назначение разрешений внутри базы данных на двух независимых уровнях. На первом уровне Вы можете назначить один или несколько кластеров разрешений пользователю. Документация SQL сервера определяет эти кластеры как фиксированные роли базы данных. Я назвал их кластерами разрешений, поскольку они могут объединять в себе разрешения на многие объекты базы данных. Например, одна фиксированная роль (db_datareader) позволяет Вам читать данные из любой таблицы базы данных-не только из какой-то одной или двух определенных таблиц. На втором уровне Вы можете назначать разрешения на уровне конкретных объектов базы данных. SQL сервер позволяет сочетать оба способа: пользователь может быть членом одной или нескольких фиксированных ролей базы данных а также иметь специфические разрешения на конкретные объекты базы данных. Я расскажу об управлении разрешениями на конкретные объекты базы данных в следующем разделе.
Два специальных пользователя могут ассоциироваться с более чем одним логином. Это пользователи dbo и guest. Пользователь dbo является членом фиксированной серверной роли sysadmin, и может создавать объекты на сервере, такие как базы данных или таблицы базы данных. Пользователь, логин которого не принадлежит фиксированной серверной роли sysadmin, также может создавать объекты баз данных, такие, как таблицы.
Видимость объектов в Окне базы данных проекта ADP зависит от того, принадлежит ли логин проекта фиксированной серверной роли sysadmin. Если логин проекта ADP является членом роли sysadmin, в Окне базы данных показываются все объекты, владельцем которых является пользователь dbo, без круглых скобок после их имен. Для пользователей dbo все объекты, владельцами которых являются другие пользователи, кроме dbo, показываются с именем пользователя, заключенного в круглые скобки после имени объекта базы данных. В случае если логин проекта ADP не принадлежит роли sysadmin, имена объектов, владельцем которых является этот пользователь, показываются в окне базы данных без круглых скобок. Имена объектов, владелец которых пользователь dbo показываются с суффиксом (dbo). Объекты, владельцы которых не являются пользователями логина проекта ADP или пользователя dbo, не показываются в Окне базы данных.
Большинство логинов имеют пользователей для каждой базы данных, к которой они имеют доступ. Однако логин может получить доступ к базе данных даже без пользователя в этой базе данных, если база данных имеет специального пользователя. Имя этого пользователя - guest, это пользователь для логинов без учетных записей в базе данных. Пользователь guest не ассоциирован ни с каким логином. Вы можете назначать разрешения для пользователя guest так же как и для любого другого пользователя базы данных. Это позволяет настраивать права для логинов не имеющих специфических пользователей в конкретной базе данных.
Члены фиксированной серверной роли sysadmin имеют неограниченные права на любую базу данных сервера. Потому, члены этой роли могут обращаться к базе данных, даже если база данных не имеет пользователей.
Один из наиболее быстрых и простых способов предоставления пользователю прав на выполнение функций в базе данных это назначение пользователя членом фиксированных ролей баз данных. Существует девять фиксированных ролей баз данных на SQL сервере, они одинаковы для SQL Server 7.0 и SQL Server 2000. Имена и краткое описание фиксированных ролей баз данных приведены в следующей таблице. Выполнив системную хранимую процедуру sp_helpdbfixedrole можно получить список имен фиксированных ролей баз данных. Вызов системной хранимой процедуры sp_dbfixedrolepermission вернет табличный набор со специфическими разрешениями для каждой фиксированной роли базы данных.
|
Имя фиксированной роли базы данных |
Описание фиксированной роли базы данных |
| db_owner | Неограниченные полномочия в базе данных. |
| db_accessadmin | Для добавления и удаления пользователей базы данных. |
| db_datareader | Для чтения из таблиц и представлений (views) базы данных. |
| db_datawriter | Для добавления (insert), редактирования (update) и удаления (delete) записей таблиц и представлений базы данных. |
| db_ddladmin | Для выполнения любого оператора SQL Data Definition Language (или выполнения этих функций с помощью графического интерфейса) в базе данных. |
| db_securityadmin | Для управления членством пользователей в ролях, разрешением доступа к объектам и владением базой данных. |
| db_backupoperator | Для создания резервных копий (backing) и восстановления из них (restoring) базы данных. |
| db_denydatareader | Для запрета (или отмены) разрешения на любую выборку (SELECT) из конкретного объекта базы данных. |
| db_denydatawriter | Для запрета (или отмены) разрешения на любой оператор INSERT, UPDATE или DELETE, выполняемый с конкретным объектом базы данных. |
Назначение пользователям фиксированных ролей базы данных сказывается на функциях, которые пользователи смогут выполнять. Есть возможность изменять влияние членства пользователя в фиксированной роли баз данных назначением разрешений пользователю на конкретные объекты базы данных. Для простоты, в этом разделе об этом не говорится, однако следующий раздел посвящен назначению разрешений на конкретные объекты и определенным пользователем (user-defined) ролям базы данных. В Окне базы данных не показываются никакие таблицы, пока пользователь для логина проекта ADP не является членом фиксированной роли базы данных db_datareader. Пользователь без членства в роли db_backupoperator не может выполнять команды меню: Архивировать (Backup) или Восстановить (Restore) (меню Сервис (Tools), подменю Служебные программы (Database Utilities)). Соответственно, не могут создавать таблицы или другие объекты в базе данных пользователи без членства в фиксированной роли базы данных db_ddladmin. Средствами Окна базы данных, пользователь без членства в роли db_ddladmin может редактировать объекты сервера, такие как таблицы и представления, или создавать новые, однако, отредактированные и созданные объекты не будут сохранены в базе данных.
Члены фиксированной роли db_owner базы данных могут выполнять все функции предоставляемые членством в любой другой роли базы данных. Так как логины этой роли могут создавать базы данных без членства в фиксированной серверной роли sysadmin, члены группы db_owner не обязаны быть пользователями dbo. В дальнейшем, члены фиксированной серверной роли sysadmin могут выполнять любые функции в базе данных, которые может выполнить член группы db_owner. Дополнительно, таблицы и представления, созданные пользователем dbo, обладают улучшенной видимостью по сравнению с таблицами и представлениями, созданным членами роли db_owner, которые в то же время не являются членами фиксированной серверной роли sysadmin.
Определяемые пользователем (User-defined) роли базы данных и назначение разрешений
Спецификация безопасности SQL Server допускает шесть типов разрешений для конкретных объектов базы данных. Администраторы базы данных могут устанавливать разрешение на доступ к конкретным объектам созданием определяемых пользователем ролей базы данных и добавлением пользователей в эти группы. Затем они могут назначать разрешения для конкретных объектов в этих ролях. Все члены любой из этих специальных ролей наследуют разрешения этих ролей. Администраторы базы данных могут добавлять и удалять пользователей из ролей после их создания, а также изменять разрешения для ролей.
Шесть имен разрешений на объекты и их описание показано в следующей таблице. Вы можете назначать эти разрешения конкретным объектам базы данных. Если Вы хотите раздать разрешения на все объекты, Вам придется назначать разрешение для каждого объекта базы данных. Однако Вам лучше использовать фиксированные роли базы данных в случае, если необходимо дать разрешение на все объекты базы данных. Например, если Вы хотите дать разрешение на SELECT для всех таблиц и представлений (включая те, что еще не созданы), проще включить пользователя в фиксированную роль базы данных db_datareader. Если Ваша цель - ограничить разрешение на SELECT только для одного или двух существующих представлений, тогда создайте определяемую пользователем роль базы данных, дайте разрешение на SELECT для этих представлений, и включите в эту роль тех пользователей, которым нужно дать специальные разрешения.
|
Название разрешения |
Описание разрешения |
| SELECT | Просмотр записей в таблице или представлении. |
| INSERT | Добавление новых записей в таблицу или представление. |
| UPDATE | Изменение содержимого записей таблицы или представления. |
| DELETE | Удаление записей из таблиц или представлений. |
| REFERENCES | Позволяет создавать внешние ключи к первичному ключу или уникальному индексу таблицы или определяемой пользователем табличной функции (row-returning user-defined function). |
| EXECUTE | Выполнение хранимой процедуры или определяемой пользователем функции. |
Разрешения на конкретные объекты базы данных могут иметь три состояния (статуса): разрешено (granted), запрещено (denied), и отменено (revoked). Если Вы даете доступ к объекту, значение разрешения будет иметь статус granted. Соответственно, если Вы запрещаете доступ, разрешение будет denied. Если вы отменяете разрешение, поменяйте разрешения с granted или denied на revoked. Отмененные разрешения не разрешают и не запрещают доступ к объекту.
Поскольку допускается включать пользователя в несколько фиксированных и определяемых пользователем ролей базы данных, возможен конфликт разрешений между ролями. Вы можете использовать эти конфликты для тонкой настройки Вашей системы безопасности. Например, пользователь включен в фиксированную роль базы данных db_datareader, дающую ему право выполнять запросы SELECT из всех таблиц и представлений базы данных. Однако Вы можете запретить чтение из таблицы продаж пользователю, имеющему разрешение чтения данных из всех таблиц, чтобы скрыть от него конфиденциальную информацию в таблице продаж. Соответственно, члены фиксированной роли базы данных db_denydatareader не могут адресовать запросы SELECT к любой таблицы базы данных, даже если пользователи включены в определяемую пользователем роль базы данных, которая дает разрешение на чтение из некоторых конкретных таблиц. Разрешение denied всегда переопределяет разрешение granted.
SQL Server облегчает управление безопасностью назначением разрешений на группы пользователей через их членство в определяемых пользователем и фиксированных ролях базы данных. Помните, что каждый пользователь обычно соответствует конкретному логину. В свою очередь логины могут соответствовать конкретным пользователям Windows. Однако один логин может также входить в группу пользователей Windows. Поэтому, если у Вас уже есть группа Windows, для которой Вы хотите установить одинаковые настройки безопасности, тогда создайте логин для группы Windows. Затем создайте пользователя для этого логина. Любые разрешения, определяемые для этого пользователя, применяется ко всем членам соответствующей группы Windows.
Программирование безопасности SQL Server
SQL-DMO является контейнерным приложением (Automation application), назначение которого - администрирование SQL Server. Поскольку SQL-DMO является API для SQL Server Enterprise Manager, Вы можете программировать на SQL-DMO все, что могут предоставить Вам графические средства Enterprise Manager, включая управление всеми аспектами безопасности SQL Server. Этот аспект SQL-DMO особенно полезен для проектов, использующих MSDE и MSDE 2000, потому что в их состав не входит Enterprise Manager. Дополнительно, программистам Access 2002 необходимо изучить некоторые программные решения по управлению безопасностью, поскольку Access 2002 не имеет возможности управлять безопасностью с помощью меню (как это было в Access 2000).
В Access Вы можете программировать, используя SQL-DMO так же, как используются любые другие COM объекты. Ваш VBA проект должен при этом использовать библиотеку SQL-DMO. Microsoft SQLDMO Object Library - имя этой объектной библиотеки. Добавьте ссылку на библиотеку с помощью команды References (меню Tools). Файл DLL с библиотекой включен в MSDE и MSDE 2000. Другие версии SQL Server содержат DLL и файл справки, который Вы можете открыть прямо из окна Visual Basic Editor (VBE). Использующие Microsoft Office XP Developer Edition, могут установить версию SQL сервера, которая включает в себя файл справки по SQL-DMO.
На рисунке представлен фрагмент иерархической модели SQL-DMO с объектами для примеров кода, расположенных далее по тексту. Обратите внимание, что объект SQLServer расположен во главе иерархии. Многие приложения могут соединяться с SQL Server посредством объекта SQLServer. Наследники объекта SQLServer являются коллекциями объектов для логинов и баз данных, а также для индивидуальных объектов, таких как, IntegratedSecurity, которые предоставляют возможность управления безопасностью SQL Server. Последующий пример использует свойство SecurityMode объекта IntegratedSecurity для демонстрации того, как установить режим аутентификации SQL Server. Членами коллекции Databases являются индивидуальные базы данных, каждая из которых имеет собственные коллекции и объекты. Одна из этих коллекций - коллекция Users. Другая коллекция используется для фиксированных и определяемых пользователем ролей базы данных (DatabaseRoles). Членство отдельных пользователей в коллекции DatabaseRoles определяет способность пользователей читать и модифицировать объекты коллекций Tables и Views.
Рисунок. Фрагмент объектной модели SQL-DMO, относящийся к безопасности SQL Server
Перед началом рассказа об SQL-DMO важно знать, что DLL для SQL-DMO отличается для SQL Server 7 и SQL Server 2000. Две версии SQL-DMO различаются как минимум в двух аспектах. Во-первых, версия для SQL Server 2000 включает в себя новые объекты, появившиеся в SQL Server 2000, а также улучшенные традиционные. Также версия SQL-DMO для SQL Server 2000 дополнительно содержит традиционные объекты для управления SQL Server 7.0. Имена объектов для 2000 обычно оканчиваются 2 (например, SQLServer2 для новых объектов, вместо SQLServer для традиционных). Во-вторых, программами, использующими SQL-DMO для SQL Server 2000 нельзя управлять SQL Server 7.0 (даже если программы используют традиционные имена объектов SQL-DMO). Однако, используя библиотеку SQL-DMO для SQL Server 7.0 можно управлять SQL Server 2000. Это ограничение является результатом изменения файловых форматов библиотек при переходе к новой версии; синтаксис скрипта SQL-DMO не претерпел изменений.
Различие между версиями SQL-DMO требует внимательного отношения к тому, какую версию библиотеки Вы будете использовать в Вашем проекте. Если необходимо, чтобы приложение SQL-DMO работало с серверами SQL Server 7.0 и SQL Server 2000, необходимо писать программы, используя SQL-DMO для SQL Server 7.0. С другой стороны, если необходимо использовать преимущество SQL Server 2000, тогда Вам необходимо разрабатывать Ваше приложение, используя библиотеку для SQL Server 2000. Поскольку статья демонстрирует технику программирования безопасности, используя SQL-DMO, я использовал версию SQL-DMO для SQL Server 7.0. В любом случае, код выглядит одинаковым для разных версий, кроме случаев, когда Вы используете новые объекты, представленные в SQL Server 2000.
Подключение к SQL серверу
Подобно тому, как вы можете подключиться к SQL Server двумя способами, используя диалог Свойства соединения в проекте ADP, Вы можете подключиться к SQL Server двумя способами через SQL-DMO. Один способ соответствует аутентификации средствами SQL Server. Используя этот способ, Ваш код должен посылать имя сервера, логин, и пароль через SQL-DMO на сервер. Вы можете использовать параметр "имя сервера" для указания различных экземпляров SQL Server на локальной рабочей станции или на другой рабочей станции в сети. SQL-DMO также позволяет Вам соединяться, указав только имя сервера. В этом случае, SQL-DMO посылает идентификатор авторизованного в Windows пользователя на экземпляр SQL сервера. Для того чтобы использовать этот способ, следует установить свойство LoginSecure сервера в значение True.
На следующем листинге показана пара процедур, демонстрирующих синтаксис подключения к экземпляру SQL Server, используя логин SQL Server. Первая процедура определяет три строковых параметра для имени сервера (srvname), логина (suid) и пароля (pwd). Далее она посылает их второй процедуре, которая начинается инициализацией объекта SQLServer. Этот объект представляет экземпляр сервера. Затем вторая процедура вызывает метод Connect объекта SQLServer. Этот метод принимает на вход три параметра. Демонстрируется синтаксис передачи переменных имени сервера, логина и пароля.
| Sub CallSQLDMOSQLServerLogin() Dim srvname As String Dim suid As String Dim pwd As String "Определение аргументов для логина SQL сервера "Вызов процедуры подключения способом логина SQL сервера Sub SQLDMOSQLServerLogin(srvname As String, _ "Вызов метода Connect для подключения способом логина SQL сервера "Очистка переменных Следующий пример кода демонстрирует синтаксис подключения к экземпляру SQL Server, используя логин Windows, Sub CallSQLDMOWindowsLogin() SQLDMOWindowsLogin srvname Sub SQLDMOWindowsLogin(srvname As String) "Экземпляр сервера "Устанавливаем свойство LoginSecure перед вызовом "Очистка переменных |
Изменение режима аутентификации
Одним из основных преимуществ, которое дает SQL-DMO программистам, работающим с MSDE и MSDE 2000, является реализация этой возможности, которая иначе была бы им недоступна. Вызвано это тем, что Enterprise Manager не включен в установочный пакет MSDE или MSDE 2000. К примеру, клиентский компонент SQL Server Enterprise Manager дает возможность администраторам графическими средствами изменить режим аутентификации сервера:Windows аутентификация или смешанный режим. Проект ADP не предоставляет такой возможности. Однако следующая пара процедур позволит Вам изменить режим аутентификации сервера, даже не имея возможности использовать Enterprise Manager.
Первая процедура определяет значение одного параметра и посылает его второй процедуре. Этот параметр определяет один из двух режимов аутентификации. В комментариях к процедуре показаны имена двух предопределенных констант в соответствии с режимом аутентификации. Смешанный режим аутентификации является режимом по умолчанию. SQL-DMO позволяет установить режим аутентификации, который недоступен даже в Enterprise Manager. Другими словами, Вы можете использовать SQL-DMO для указания серверу принимать только логины SQL Server.
Вторая процедура выполняет подключение к серверу с использованием идентификатора пользователя Windows. Затем она устанавливает свойство SecurityMode объекта IntegratedSecurity для сервера в значение параметра, переданного ей из первой процедуры. Если значение этого свойства меняет режим аутентификации, режим не сменится, пока Вы не остановите и не перезапустите сервер. Однако вызов метода Stop объекта сервера не может моментально остановить сервер. Вы должны подождать, пока сервер не остановится. При помощи свойства Status объекта сервера Ваша программа может следить за сообщением, что сервер остановился. Процедура использует цикл, ожидая смены значения Status на SQLDMOSvc_Stopped. Далее процедура выполняет метод Start объекта SQLServer. Вызов этого метода установит новый режим аутентификации для сервера.
| Sub CallChangeServerAuthenticationMode() Dim constAuth As Byte "Устанавливаем constAuth в: "Установка значения по умолчанию для constAuth "вызов процедуры для изменения режима аутентификации Sub ChangeSeverAuthenticationMode(constAuth As Byte) "Устанавливаем имя сервера; "экземпляр объекта SQLServer для соединения "Устанавливаем свойство SecurityMode для Windows "Вызываем команду на останов и ждем "Рестартуем сервер со смешанным типом аутентификации "Clean up |
Открытие проекта ADP
Иногда бывает необходимо из Вашего приложения SQL-DMO программно открыть проект ADP. Технология открытия проекта ADP не требует использования SQL-DMO, однако, Ваше приложение SQL-DMO может использовать преимущество такой возможности. В последнем примере кода этой статьи (прямо перед заключительным разделом) демонстрируется процедура создания нового пользователя. Необходимость открытия ADP проекта основана на генерации строки соединения при установке приложения, основанного на ADP проекте.
Следующий пример кода демонстрирует, как открыть существующий ADP проект. В коде, представленном ниже, именем проекта ADP является msdn_test_security.adp. Пример кода позволяет открыть проект ADP в режиме аутентификации Windows или в режиме аутентификации SQL Server. Снова используется две процедуры. Список параметров в первой процедуре - CallOpenADPWindowsOrSQLServer - относительно большой, что связано с необходимостью указать имя сервера, базу данных, путь и название файла ADP проекта, логин SQL Server и пароль, а также логическую переменную. Логическая переменная определяет открытие проекта с Windows или с SQL Server аутентификацией. При указании Windows аутентификации нет необходимости в логине и пароле, поскольку проект ADP автоматически ссылается на идентификатор пользователя Windows. Вторая процедура использует эти параметры для открытия ADP проекта.
Перед описанием второй процедуры было бы полезным объяснить некоторые особенности кода. Во-первых, процедура, открывающая проект ADP уже работает в сессии Access. Поэтому необходимо создать новый экземпляр Access для проекта, который мы намерены открыть. Для создания сессии Access используется OLE класс.
Во-вторых, любой существующий проект ADP всегда открывается с сохраненными во время предыдущего открытия параметрами соединения с сервером. Поэтому программа должна открыть проект, используя старые параметры подключения, а потом эти параметры сменить. Новые параметры подключения можно назначить только открытому проекту. После закрытия проекта новые параметры соединения сохраняются, пока пользователь не изменит их в диалоге Параметры соединения. (Однако объектная модель офиса позволяет Вам скрыть и сделать недоступной команду меню Соединение в меню Файл).
В-третьих, сессия с открытым проектом ADP будет жить, пока живет переменная, представляющая эту сессию. Используйте описание Public в определении сессии для открываемого проекта. Объявляется переменная в разделе описаний модуля. Это позволит сессии жить, пока живет открывающий ее проект, то есть наш код. Для переменной сессии Access в примере используется имя appAccess, и следующее объявление в разделе описаний стандартного модуля. Поскольку объявление должно быть в верхней части модуля, Вы его не увидите в коде ни для первой, ни для второй процедуры.
Public appAccess as Access.Application
Вторая процедура - OpenADPWindowsOrSQLServer - начинается выводом сообщения, спрашивающим оставить ли сессию открытой после завершения процедуры. Если пользователь ответит "нет", процедура установит параметры соединения для проекта, а затем закроет сессию без шанса посмотреть изменения. Далее проект запускает новую сессию Access при помощи функции CreateObject. Аргументы этой функции различаются в зависимости от версии Access, которую Вы используете (в коде описаны особенности использования версий Access 2000 или Access 2002). После создания сессии для проекта процедура вызывает метод OpenAccessProject для добавления проекта ADP в сессию. Аргументом для этого метода служит путь вместе с именем файла проекта. Расширение.adp опционально. После открытия проекта процедура переопределяет его параметры соединения при помощи метода OpenConnection объекта CurrentProject. Процедура переопределяет соединения, используя две различные строки соединения в зависимости от значения переменной bolWindowsLogin. Эта Логическая переменная устанавливается первой процедурой для указания того, какой тип авторизации требуется установить для проекта. На последнем шаге процедуры сессия закрывается, если пользователь ответил положительно на вопрос в окне сообщения перед открытием сессии.
| Sub CallOpenADPWindowsOrSQLServer() Dim srvname As String Dim dbname As String Dim prpath As String Dim prname As String Dim suid As String Dim pwd As String "Установка параметров для открытия проекта ADP "Вызов процедуры открытия проекта ADP prname Dim bolLeaveOpen As Boolean "Оставить проект открытым после завершения работы процедуры? "Определение объекта сессии Access (используйте.9 для Access 2000 "Открываем проект с логином и паролем последнего запуска "Устанавливаем новый логин для Windows или SQL Server аутентификации; "Закрываем сессию, или останавливаемся |
Добавление и удаление логинов
Существует три способа подключения к SQL Server. В эти три типа входят один тип, проверяемый SQL Server и два других типа, проверяемых Windows. Два типа, проверяемых Windows это логины для индивидуальных идентификаторов пользователей и логины для групп пользователей Windows. Если у Вас уже есть группа пользователей Windows, всем участникам которой нужно дать одинаковые права на ресурсы SQL сервера, Вам требуется только установить логин на группу Windows. Это избавит Вас от необходимости создавать логин для каждого участника группы.
Пример создания логинов демонстрирует синтаксис создания стандартного логина SQL Server и логина, основанного на группе пользователей Windows. Код по созданию логина на SQL Server для группы Windows почти такой же, как код по созданию логина для отдельного пользователя Windows. Поэтому следующий пример по созданию логина для группы Windows в точности подходит для создания логинов отдельных пользователей Windows.
Пример создания логинов состоит из двух процедур. В первой процедуре определяются параметры для создания логина на SQL Server, с которыми вторая процедура создаст новые логины. Третья процедура - это функция, преобразовывающая значение внутренней константы в строку, описывающую имя константы.
Вторая процедура после соединения с сервером инициализирует объект login. Этот объект - член коллекции Logins, показанной на рисунке. Далее процедура устанавливает три параметра для логина: его имя (login_name), базу данных по умолчанию (defaulf_db_name) и пароль (password). Операции по простому присвоению работают с двумя первыми аргументами. Для установки пароля для логина SQL сервера вызывается метод SetPassword объекта login. При установке пароля для нового логина используйте пустую строку в качестве первого аргумента метода; укажите пароль для нового логина вторым аргументом. В нашем примере пароль для логина login_name определен как "password" (без кавычек). Добавление логина login_name завершается вызовом метода Add коллекции Logins.
Также Вы можете использовать метод SetPassword для переопределения пароля существующего логина. Первый аргумент метода SetPassword в этом случае - текущий пароль. Однако если Вы не знаете текущего пароля, Вы можете указывать первым аргументом пустую строку-так же, как при создании пароля для нового логина. Создание или изменение логина возможно в том случае, если Ваш текущий логин (под которым Вы осуществили соединение с сервером) входит в одну из фиксированных ролей сервера: sysadmin или securityadmin. Неважно, каким способом Вы меняете пароль: программно или другими средствами. В примере используется логин sa, входящий в фиксированную роль сервера sysadmin.
В нескольких следующих строках второй процедуры создается логин для группы Windows group с именем msdn_OS_users. В моем случае эта группа Windows из трех идентификаторов пользователей Windows, в действительности группа Windows могла быть как группой пользователей локального компьютера, так и домена сервера Windows. Процесс создания логина начинается с инициализации нового объекта login (lgn1). При попытке повторного использования lgn1 без переинициализации (обнуления), возникнет ошибка выполнения, напоминающая о том, что требуется новый объект. Процедура использует название группы Windows, состоящее из двух частей. Первая часть - имя сервера Windows, вторая - имя группы сервера Windows. Обратный слэш служит разделителем. Базой данных по умолчанию для второго логина снова указывается база данных your_db_name. Поскольку второй логин создается для аутентификации Windows, нет необходимости устанавливать пароль. Однако необходимо установить свойство Type в SQLDMOLogin_NTGroup. Это значение соответствует внутренней константе, соответствующей логину группы Windows. При добавлении логина SQL Server не нужно устанавливать значение свойства Type, поскольку оно задано по умолчанию для логинов SQL сервера. Заканчивается создание второго логина вызовом метода Add коллекции Logins.
После добавления двух новых логинов процедура печатает имена и типы всех логинов сервера. Свойство Type соответствует значению внутренних констант. Чтобы вывод типов на экран был читаемым, используется функция, преобразовывающая константы в соответствующие текстовые названия.
После печати членов коллекции Logins процедура удаляет два вновь созданных логина поименно: login_name и YOUR_SERVER_NAME\msdn_OS_users. Этот пример демонстрирует использование коллекции Logins ее метода Remove и свойства Name логина, которого Вы хотите удалить. Для подтверждения удаления двух логинов процедура выводит список логинов повторно из коллекции Logins. Откройте окно Immediate и сравните два списка логинов: после добавления и удаления двух логинов.
| Sub CallLoginDemo() Dim srvname As String Dim suid As String Dim pwd As String "устанавливаем параметры для логина при аутентификации SQL сервером "Вызов процедуры создания логинов Sub LoginDemo(srvname As String, _ suid As String, pwd As String) "Инициализация объекта сервера "Вызов метода Connect "Инициализация объекта login "Добавление логина SQL Server srv1.Logins.Add lgn1 "Инициализируем логин для повторного использования "Добавляем логин для группы Windows "Вывод всех логинов после добавления двух новых "Удаление только что добавленных логинов "Повторный вывод всех логинов "Очистка переменных Function DecodeLoginType(lgn_type As Byte) As String Select Case lgn_type |
Создание логина с пользователем - участником роли db_datareader
Программное добавление нового логина и назначение для него базы данных по умолчанию не создает пользователя в базе данных для этого логина. Поэтому проект ADP, основанный на этом логине, не может подсоединиться к базе данных по умолчанию. Для того чтобы проект мог работать с базой данных, Вы должны создать в базе данных пользователя для своего логина.
Пример кода заключительной части статьи демонстрирует процедуру добавления нового логина для аутентификации SQL Server, а также создает пользователя для этого логина в базе данных your_db_name. В этом примере новый пользователь также включается в фиксированную роль базы данных db_datareader. Наконец, открывается проект ADP, основываясь на новом логине и пользователе.
После соединения с сервером и создания нового логина login_name, код создает нового пользователя. Далее назначаются свойства Name и Login для пользователя. Свойство Login означает логин, под которым пользователь соединяется - login_name в нашем примере. После этого пользователь готов к добавлению в коллекцию Users базы данных. Отметим, что синтаксис добавления нового пользователя соответствует синтаксису иерархии объектов. Метод Add для нового пользователя применяется к коллекции Users принадлежащей в свою очередь объекту database. Database - член коллекции Databases принадлежащей объекту сервера (SQLserver). После создания пользователя требуется одна или несколько строчек для внесения пользователя в фиксированную роль базы данных db_datareader. Метод по добавлению пользователя в роль называется AddMember. В нашем случае метод AddMember элемента коллекции DatabaseRoles добавляет пользователя в роль базы данных. Этот метод берет в качестве параметра строковое выражение свойства Name объекта нового пользователя.
Как только настройка безопасности нового логина и пользователя завершены, вызывается процедура OpenADPWindowsOrSQLServer. В предыдущих листингах есть пример использования этой процедуры. В нашем случае открывается файл msdn_test_security.adp, основываясь на логине login_name. Для проверки работоспособности логина откройте таблицы базы данных your_db_name. Дополнительно, работоспособность нового логина можно проверить в диалоговом окне Параметры соединения, указав логин your_login.
Если попытаться повторно запустить процедуру MakeLoginWithDatareaderUser попытка закончится ошибкой. Это потому, что логин your_login уже существует. Ошибка возникнет при попытке повторно его добавить. Попытка запустить процедуру может закончиться неудачей и по другой причине: Вы не закрыли проект, подключенный к БД с этим логином. Вы можете написать стандартную обработку ошибок VBA для обработки ошибок времени выполнения. Подобные ошибки могут сопровождать выполнение предыдущих примеров. Я предпочел не углубляться в обсуждение обнаружения ошибок времени выполнения, акцентируя внимание на вопросах безопасности SQL сервера.
| Sub MakeLoginWithDatareaderUser() Dim srv1 As SQLDMO.SQLServer Dim lgn1 As SQLDMO.Login Dim usr1 As SQLDMO.User Dim srvname As String Dim suid As String Dim pwd As String Dim dbname As String Dim prpath As String Dim prname As String Dim bolWindowsLogin As Boolean "Определяем аргументы "инициализируем сервер "Вызываем метод Connect для логина при аутентификации SQL сервером "Инициализируем и добавляем объект login "Инициализируем и добавляем объект user "Устанавливаем параметры для открытия проекта ADP prpath = "Path_to_project_file" "Этот аргумент контролирует использование логина Windows "Вызов процедуры открытия проекта prname "Оставляем объект открытым для просмотра |
Заключение
Безопасность SQL Server построена на принципах, отличных от тех, что использовали программисты в базах данных Jet. Тем не менее, SQL Server Books Online тщательно документирует правила по безопасности SQL Server. Эта статья переводит основные правила BOL в специфические правила, цель которых использовать проект Access с SQL Server. Примеры кода, приведенные в этой статье, демонстрируют, как управлять безопасностью SQL Server с помощью Access.
Страница 8 из 8
Интеграция с автоматической настройкой SQL
Во время технологических окон Oracle Database 11g автоматически выполняет SQL Tuning Advisor - часть пакета настройки и диагностики (Tuning and Diagnostic pack). Эта задача автоматической настройки SQL нацелена на SQL-операторы, создающие высокую нагрузку на систему. Такие операторы определяются по данным о производительности выполнения, которые собираются в снэпшотах автоматически управляемого репозитория рабочей нагрузки (AWR). Если SQL Tuning Advisor найдет лучший план выполнения для SQL-оператора, он порекомендует SQL-профиль. Для некоторых из этих SQL-операторов с высокой нагрузкой опорные SQL-планы могли быть уже созданы. Если рекомендация для SQL-профиля, сделанная задачей автоматической настройки SQL, будет реализована, то план выполнения, найденный SQL Tuning Advisor, будет добавлен как принятый опорный план SQL.
Можно вызвать SQL Tuning Advisor вручную. Для этого достаточно для данного SQL- оператора создать набор SQL Tuning Set. Если SQL Tuning Advisor рекомендует SQL-профиль для оператора и он реализуется вручную, то этот профиль будет добавлен, как принятый план для опорных планов выполнения операторов SQL, если такой существует.
ЗАКЛЮЧЕНИЕ
Новая опция Oracle Database 11g, которая называется SQL Plan Management (SPM), обеспечивает управляемую эволюцию планов выполнения. В случае использования SPM оптимизатор автоматически управляет планами выполнения и гарантирует, что будут использоваться только известные или проверенные планы. Когда для SQL-оператора находится новый план, он не будет использован, пока для него не будет подтверждено, что его производительность сопоставима с производительностью текущего плана или лучше ее.
В среде SQL Server предусмотрен ряд различных управляющих конструкций, без которых невозможно написание эффективных алгоритмов.
Группировка двух и более команд в единый блок осуществляется с использованием ключевых слов BEGIN и END:
<блок_операторов>::=
Сгруппированные команды воспринимаются интерпретатором SQL как одна команда. Подобная группировка требуется для конструкций поливариантных ветвлений, условных и циклических конструкций. Блоки BEGIN...END могут быть вложенными.
Некоторые команды SQL не должны выполняться вместе с другими командами (речь идет о командах резервного копирования, изменения структуры таблиц, хранимых процедур и им подобных), поэтому их совместное включение в конструкцию BEGIN...END не допускается.
Нередко определенная часть программы должна выполняться только при реализации некоторого логического условия. Синтаксис условного оператора показан ниже:
<условный_оператор>::=
IF лог_выражение
{ sql_оператор | блок_операторов }
{sql_оператор | блок_операторов } ]
Циклы организуются с помощью следующей конструкции:
<оператор_цикла>::=
WHILE лог_выражение
{ sql_оператор | блок_операторов }
{ sql_оператор | блок_операторов }
Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.
Для замены множества одиночных или вложенных условных операторов используется следующая конструкция:
<оператор_поливариантных_ветвлений>::=
CASE входное_значение
WHEN {значение_для_сравнения |
лог_выражение } THEN
вых_выражение [,...n]
[ ELSE иначе_вых_выражение ]
Если входное значение и значение для сравнения совпадают, то конструкция возвращает выходное значение. Если же значение входного параметра не найдено ни в одной из строк WHEN...THEN, то тогда будет возвращено значение, указанное после ключевого слова ELSE.
Основные объекты структуры базы данных SQL-сервера
Рассмотрим логическую структуру базы данных.
Логическая структура определяет структуру таблиц, взаимоотношения между ними, список пользователей, хранимые процедуры, правила, умолчания и другие объекты базы данных.
Логически данные в SQL Server организованы в виде объектов. К основным объектам базы данных SQL Server относятся следующие объекты.
Краткий обзор основных объектов баз данных.
Таблицы
Все данные в SQL содержатся в объектах, называемых таблицами . Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других СУБД и содержат:
· cтроки; каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
· cтолбцы; каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер.
Представления
Представлениями (просмотрами) называют виртуальные таблицы, содержимое которых определяется запросом. Подобно реальным таблицам, представления содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект.
Хранимые процедуры
Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.
Триггеры
Триггерами называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы.
Функции
Функции в языках программирования – это конструкции, содержащие часто исполняемый код. Функция выполняет какие-либо действия надданными и возвращает некоторое значение.
Индексы
Индекс – структура, связанная с таблицей или представлением и предназначенная для ускорения поиска информации в них. Индекс определяется для одного или нескольких столбцов, называемых индексированными столбцами. Он содержит отсортированные значения индексированного столбца или столбцов со ссылками на соответствующую строку исходной таблицы или представления. Повышение производительности достигается за счет сортировки данных. Использование индексов может существенно повысить производительность поиска, однако для хранения индексов необходимо дополнительное пространство в базе данных.
©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-08
Если вы уже когда-либо писали схемы блокировок на других языках баз данных для преодоления недостатка блокировок (как я), то у вас могло остаться чувство, что обязательно нужно самому заниматься блокировками. Позвольте вас заверить, что диспетчеру блокировок можно полностью доверять. Тем не менее SQL Server предлагает несколько методов управления блокировками, о которых мы детально поговорим в этом разделе.
Не применяйте параметры блокировки и не изменяйте уровни изоляции случайным образом - доверьте менеджеру блокировок SQL Server выполнять балансировку конкуренции и целостности транзакций. Только если вы абсолютно уверены, что схема базы данных хорошо настроена, а программный код буквально отшлифован, можете слегка подкорректировать работу диспетчера блокировок, чтобы решить конкретную проблему. В некоторых случаях настройка запросов select на отсутствие блокировок способна решить большинство проблем.
Установка уровня изоляции подключения
Уровень изоляции определяет продолжительность общей или эксклюзивной блокировки подключения. Установка уровня изоляции оказывает влияние на все запросы и все таблицы, используемые на протяжении всего подключения или до явной замены одного уровня изоляции другим. В следующем примере устанавливается более плотная изоляция, чем задана по умолчанию, и предотвращаются неповторяющиеся чтения:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Допустимыми уровнями изоляции являются:
Read uncommited ? serializable
Read commited ? snapshot
Repeatable read
Текущий уровень изоляции можно проверить с помощью команды проверки целостности базы данных (DBCC):
DBCC USEROPTIONS
Результаты будут следующими (сокращенно):
Set Option Value
isolation level repeatable read
Уровни изоляции могут быть также установлены на уровне запроса или таблицы с помощью параметров блокировки.
Использование изоляции уровня снимков базы данных
Существуют два варианта уровня изоляции снимков базы данных: snapshot и read commited snapshot. Изоляция snapshot работает подобно repeatable read, не занимаясь вопросами блокировки. Изоляция read commited snapshot имитирует установленный по умолчанию в SQL Server уровень read commited, так же снимая вопросы блокировки.
В то время как изоляцию транзакций, как правило, устанавливают на уровне подключений, изоляция snapshot должна конфигурироваться на уровне базы данных, поскольку она
эффективно отслеживает версионность строк в базе. Версионностъ строк - это технология, которая создает для обновления копии строк в базе данных TempDB. Кроме основной загрузки базы TempDB, версионность строк также добавляет 14-байтовый идентификатор строки.
Использование изоляции Snapshot
В следующем фрагменте включается уровень изоляции snapshot. Для корректировки базы данных и включения уровня изоляции snapshot к этой базе не должны быть установлены другие подключения.
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
| Для проверки того, включена ли в базе данных изоляция snapshot, выполните SVS следующий запрос: SELECT name, snapshot_isolation_state_desc FROM [ * sysdatabases.
Теперь первая транзакция начинает чтение и остается открытой (т.е. не подтвержденной): USE Aesop
BEGIN TRAN SELECT Title FROM FABLE WHERE FablelD = 2
Будет получен следующий результат:
В это время вторая транзакция начинает обновление той же строки, которая открыта первой транзакцией:
SET TRANSACTION ISOLATION LEVEL Snapshot;
BEGIN TRAN UPDATE Fable
SET Title = ‘Rocking with Snapshots’
WHERE FablelD = 2;
SELECT * FROM FABLE WHERE FablelD = 2
Результат следующий:
Rocking with Snapshots
He правда ли, удивительно? Вторая транзакция смогла обновить строку, даже несмотря на то, что первая транзакция осталась открытой. Вернемся к первой транзакции и увидим исходные данные:
SELECT Title FROM FABLE WHERE FablelD = 2
Результат следующий:
Если открыть третью и четвертую транзакции, то они увидят все то же исходное значение The Bald Knight:
Даже после того как вторая транзакция подтвердит изменения, первая будет по-прежнему видеть исходное значение, а все следующие транзакции - новое, Rocking with Snapshots.
Использование ИЗОЛЯЦИИ Read Commited Snapshot
Изоляция Read Commited Snapshot включается с помощью аналогичного синтаксиса:
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON
Подобно изоляции Snapshot, данный уровень для снятия вопросов блокировок также использует версионность строк. Если взять за основу пример, описанный в предыдущем разделе, то в данном случае первая транзакция увидит изменения, выполненные второй, как только они будут подтверждены.
Так как Read Commited является уровнем изоляции, принятым в SQL Server по умолчанию, требуется только установка параметров базы данных.
Разрешение конфликтов записи
Транзакции, записывающие данные при установленном уровне изоляции Snapshot, могут быть заблокированы предыдущими неподтвержденными транзакциями записи. Такая блокировка не заставит новую транзакцию ожидать - просто будет сгенерирована ошибка. Для обработки подобных ситуаций используйте выражение try. . . catch, выждите пару секунд и попробуйте повторить транзакцию снова.
Использование параметров блокировки
Параметры блокировки позволяют вносить временную коррекцию в статегию блокировки. В то время как уровень изоляции оказывает влияние на подключение в целом, параметры блокировки специфичны для каждой таблицы в конкретном запросе (табл. 51.5). Параметр WITH (параметр_блокировки) помещается после имени таблицы в предложении FROM запроса. Для каждой таблицы можно задать несколько параметров, разделяя их запятыми.
|
Таблица 51.5. Параметры блокировки |
Параметр блокировки |
Описание |
Уровень изоляции. He устанавливает и не удерживает блокировку. Равносилен отсутствию блокировок |
Уровень изоляции, установленный для транзакций по умолчанию |
Уровень изоляции. Удерживает общую и эксклюзивную блокировки до момента подтверждения транзакции |
Уровень изоляции. Удерживает общую блокировку до завершения транзакции |
Пропуск заблокированных строк вместо ожидания |
Включение блокировки на уровне строк вместо уровня страницы, экстента или таблицы |
Включение блокировки на уровне страниц вместо уровня таблицы |
Автоматическая эскалация блокировок уровня строк, страниц и экстента до гранулярности уровня таблицы |
Параметр блокировки |
Описание |
Неприменение и неудержание блокировок. То же, что и ReadUnCommited |
Включение эксклюзивной блокировки таблицы. Запрет другим транзакциям работать с таблицей |
Удержание общей блокировки до подтверждения транзакции (аналогично Serializable) |
Использование блокировки обновления вместо общей и ее удержание. Блокировка других записей в данные между изначальными операциями чтения и записи |
Удержание эксклюзивной блокировки данных до подтверждения транзакции |
В следующем примере в предложении FROM инструкции UPDATE использован параметр блокировки, запрещающий диспетчеру эскалировать гранулярность блокировки:
USE OBXKites UPDATE Product
FROM Product WITH (RowLock)
SET ProductName = ProductName + ‘ Updated 1
Если запрос содержит подзапросы, не забывайте, что обращение к таблице каждого из запросов генерирует блокировку, которой можно управлять с помощью параметров.
Ограничения блокировок уровня индексов
Уровни изоляции и параметры блокировки применяются на уровне подключений и запросов. Единственным способом управления блокировками на уровне таблицы является ограничение гранулярности блокировок на основе конкретных индексов. С помощью системной хранимой процедуры sp_indexoption блокировки строк и/или страниц можно отключить для конкретного индекса, используя следующий синтаксис: sp_indexoption ‘имя_индекса 1 ,
AllowRowlocks или AllowPagelocks,
Это может пригодиться в ряде особых случаев. Если таблица часто вызывает ожидания по причине блокировок страниц, то установка для параметра allowpagelocks значения off установит блокировку на уровне строк. Уменьшенная гранулярность блокировок положительно скажется на конкуренции. К тому же, если таблица редко обновляется, но часто считывается, блокировки на уровне строк и страниц нежелательны; в этом случае оптимальным является уровень блокировки на уровне таблиц. Если обновления выполняются нечасто, то эксклюзивная блокировка таблиц не приведет к большим проблемам.
Хранимая процедура Sp_indexoption предназначена для тонкой настройки схемы данных; именно поэтому в ней используется блокировка на уровне индексов. Для ограничения блокировок по первичному ключу таблицы используйте sp_help имя_ та блицы, чтобы найти имя индекса первичного ключа.
Следующая команда конфигурирует таблицу ProductCategory как редко обновляемый классификатор. Вначале команда sp_help выводит имя индекса первичного ключа таблицы: sp_help ProductCategory
Результат (усеченный) таков:
index index index
name description keys
PK_____________ ProductCategory 79A814 03 nonclustered, ProductCategorylD
unique, primary key located on PRIMARY
Имея в наличии реальное имя первичного ключа, системная хранимая процедура может установить параметры блокировки индекса:
EXEC sp_indexoption
‘ProductCategory.РК__ ProductCategory_______ 7 9А814 03′,
‘AllowRowlocks’, FALSE EXEC sp_indexoption
‘ProductCategory.PK__ ProductCategory_______ 79A81403′,
‘AllowPagelocks’, FALSE
Управление временем ожидания блокировок
Если транзакция ожидает блокировку, то это ожидание будет продолжаться до тех пор, пока блокировка не станет возможной. По умолчанию не существует предела времени ожидания - чисто теоретически оно может длиться вечно.
К счастью, вы можете установить время ожидания блокировки с помощью параметра подключения set lock_timeout. Установите для этого параметра количество миллисекунд или, если хотите не ограничивать время, установите для него значение -1 (оно принято по умолчанию). Если для этого параметра установлено значение 0, то транзакция будет немедленно отклонена при наличии какой-либо блокировки. В этом случае приложение будет исключительно быстродействующим, но малоэффективным.
В следующем запросе время ожидания блокировки устанавливается в две секунды (2000 миллисекунд):
SET Lock_Timeout 2 00 0
Если транзакция выходит за пределы установленного предельного времени ожидания, то генерируется ошибка с номером 1222.
Настоятельно рекомендую устанавливать предельное время ожидания блокировки на уровне подключения. Эта величина выбирается в зависимости от обычной производительности базы данных. Я предпочитаю устанавливать пятисекундное время ожидания.
Оценка производительности конкуренции в базе данных
Очень легко создать базу данных, в которой не решаются вопросы соревнования блокировок и конкуренции при тестировании на группе пользователей. Реальный тест - это когда несколько сотен пользователей одновременно обновляют заказы.
Тестирование конкуренции нужно правильно организовать. На одном уровне он должен содержать одновременное использование множеством пользователей одной и той же конечной формы. Может оказаться полезной и программа.NET, которая постоянно имитирует
просмотр пользователем данных и их обновление. Хороший тест должен запускать 20 экземпляров сценария, который постоянно загружает базу данных, а затем дать возможность команде тестировщиков использовать приложение. Количество блокировок поможет увидеть монитор производительности, о котором говорилось в главе 49.
Многопользовательскую конкуренцию лучше тестировать в процессе разработки несколько раз. Как говорится в экзаменационном руководстве MCSE, “не допускайте, чтобы тест в реальных условиях был первым”.
Блокировки приложения
SQL Server использует очень сложную схему блокировок. Иногда процесс или ресурс, отличный от данных, требует блокировки. Например, может быть необходимо запускать процедуру, которая наносит вред, если другой пользователь запустил еще один экземпляр той же процедуры.
Несколько лет назад я написал программу разводки кабелей в проектах атомных электростанций. Когда геометрия предприятия была спроектирована и протестирована, инженеры вводили состав кабельного оборудования, его местоположение и типы используемых кабелей. После того как вводилось несколько кабелей, программа формировала маршрут их прокладки так, чтобы он оказался как можно более коротким. Процедура также учитывала вопросы безопасности кабельной проводки и разделяла несовместимые кабели. В то же время, если множество процедур прокладки запускалось одновременно, каждый экземпляр пытался маршрутизировать одни и те же кабели, в результате чего результат оказывался некорректным. Блокировка приложений стала отличным решением этой проблемы.
Блокировка приложений открывает целый мир блокировок SQL, предназначенных для использования в приложениях. Вместо использования данных в качестве блокируемого ресурса, блокировки приложений блокируют использование всех пользовательских ресурсов, объявленных в хранимой процедуре sp__GetAppLock.
Блокировка приложений может применяться в транзакциях; при этом может быть объявлен режим блокировки Shared, Update, Exclusive, IntentExclusice или IntentShared. Возвращаемое процедурой значение указывает, успешным ли было применение блокировки.
0. Блокировка установлена успешно.
1. Блокировка была установлена, когда другая процедура сняла свою блокировку.
999. Блокировка не была установлена по другой причине.
Хранимая процедура sp_ReleaseApLock снимает блокировку. В следующем примере продемонстрировано, как блокировка приложения может использоваться в пакете или процедуре: DECLARE @ShareOK INT EXEC @ShareOK = sp_GetAppLock
@Resource = ‘CableWorm’,
@LockMode = ‘Exclusive’
IF @ShareOK < 0
…Код обработки ошибки
… Программный код …
EXEC sp_ReleaseAppLock @Resource = ‘CableWorm’
Когда блокировки приложения просматриваются с помощью Management Studio или процедуры sp_Lock, они отображаются с типом АРР. В следующем листинге приведен сокращенный вывод процедуры sp_Lock, запущенной одновременно с приведенным выше кодом: spid dbid Objld Indld Type Resource Mode Status
57 8 0 0 APP Cabllf 94cl36 X GRANT
Следует обратить внимание на два небольших отличия в том, как блокировки приложения обрабатываются в SQL Server:
Взаимоблокировки не выявляются автоматически;
Если некоторая транзакция устанавливает блокировку несколько раз, она должна снять ее ровно такое же количество раз.
Взаимоблокировки
Взаимоблокировкой называют особую ситуацию, которая возникает только тогда, когда транзакции с множеством задач соревнуются за ресурсы друг друга. Например, первая транзакция установила блокировку ресурса А, и ей необходимо заблокировать ресурс Б, а в это же время вторая транзакция, заблокировавшая ресурс Б, нуждается в блокировке ресурса А.
Каждая из этих транзакций ожидает, пока другая снимет свою блокировку, и ни одна из них не может завершиться, пока этого не произойдет. Если не произойдет внешнего воздействия или одна из транзакций завершится по определенной причине (например, по времени ожидания), то эта ситуация может продолжаться до конца света.
Раньше взаимоблокировки представляли собой серьезную проблему, но теперь SQL Server позволяет успешно разрешить ее.
Создание взаимоблокировки
Проще всего создать ситуацию взаимоблокировки в SQL Server с помощью двух подключений в редакторе запросов утилиты Management Studio (рис. 51.12). Первая и вторая транзакции пытаются обновить одни и те же строки, однако в противоположном порядке. Используя третье окно для запуска процедуры pGetLocks, можно выполнять мониторинг блокировок.
1. Создайте в редакторе запросов второе окно.
2. Поместите код блока Шаг 2 во второе окно.
3. В первое окно поместите код блока Шаг 1 и нажмите клавишу
4. Во втором окне аналогично выполните код Шаг 2.
5. Вернитесь в первое окно и выполните код блока Шаг 3.
6. Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее.
Ниже приведен программный код примера.
– Транзакция 1 — Шаг 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact
SET LastName = ‘Jorgenson’
WHERE ContactCode = 401′
Puc. 51.12. Создание ситуации взаимоблокировки в Management Studio с помощью двух подключений (их окна расположены вверху)
Теперь первая транзакция установила эксклюзивную блокировку на запись со значением 101 в поле ContactCode. Вторая транзакция установит эксклюзивную блокировку строки со значением 1001 в поле ProductCode, а затем попытается эксклюзивно заблокировать запись, уже заблокированную первой транзакцией (ContactCode=101).
– Транзакция 2 — Шаг 2 USE OBXKites BEGIN TRANSACTION UPDATE Product SET ProductName
= ‘DeadLock Repair Kit’
WHERE ProductCode = ‘1001’
SET FirstName = ‘Neals’
WHERE ContactCode = ‘101’
COMMIT TRANSACTION
Пока еще взаимоблокировки не существует, поскольку транзакция 2 ожидает завершения транзакции 1, но транзакция 1 пока еще не ожидает завершения транзакции 2. В этой ситуации, если транзакция 1 завершит свою работу и выполнит инструкцию COMMIT TRANSACTION, ресурс данных будет освобожден, и транзакция 2 благополучно получит возможность необходимой ей блокировки и продолжит свои действия.
Проблема возникает, когда транзакция 1 попытается обновить строку с ProductCode=l. Однако необходимую для этого эксклюзивную блокировку она не получит, поскольку эта запись заблокирована транзакцией 2:
– Транзакция 1 — Шаг 3 UPDATE Product SET ProductName
= ‘DeadLock Identification Tester’
WHERE ProductCode = ‘1001’
COMMIT TRANSACTION
Транзакция 1 вернет следующее текстовое сообщение об ошибке спустя пару секунд. Возникшую взаимоблокировку можно также увидеть в SQL Server Profiler (рис. 51.13):
Server: Msg 1205, Level 13,
State 50, Line 1 Transaction (Process ID 51) was
deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Транзакция 2 завершит свою работу, будто бы проблемы и не существовало:
(1 row(s) affected)
(1 row(s) affected)
Рис. 51.13. SQL Server Profiler позволяет выполнять мониторинг взаимоблокировок с помощью события Locks:Deadlock Graph и выявлять ресурс, вызвавший взаимоблокировку
Автоматическое выявление взаимоблокировок
Как было продемонстрировано в приведенном выше коде, SQL Server автоматически выявляет ситуацию взаимоблокировки, проверяя блокирующие процессы и откатывая транзакции,
выполнившие наименьший объем работы. SQL Server постоянно проверяет существование перекрестных блокировок. Задержка выявления взаимоблокировок может варьироваться от нуля до двух секунд (на практике дольше всего мне приходилось ожидать этого пять секунд).
Обработка взаимоблокировок
Когда происходит взаимоблокировка, подключение, выбранное в качестве “жертвы” взаимоблокировки, должно повторить свою транзакцию. Так как работа должна быть переделана, хорошо, что откатывается именно та транзакция, которая успела выполнить наименьший объем работы, - именно она будет повторена сначала.
Ошибка с кодом 12 05 должна перехватываться клиентским приложением, которое и должно перезапускать транзакцию. Если все происходит как нужно, пользователь даже не заподозрит о том, что произошла взаимоблокировка.
Вместо того чтобы позволять самому серверу решать, какую из транзакций выбирать на роль “жертвы”, самой транзакции можно “сыграть в поддавки”. Следующий код, будучи помещенным в транзакцию, информирует SQL Server о том, что в случае возникновения взаимоблокировки данную транзакцию следует откатить:
SET DEADLOCKJPRIORITY LOW
Минимизация взаимоблокировок
Даже несмотря на то, что взаимоблокировки легко выявить и обработать, лучше все-таки их избегать. Приведенные ниже рекомендации помогут вам избежать взаимоблокировок.
Старайтесь делать транзакции короткими и не содержащими излишнего кода. Если некоторый код не обязательно должен присутствовать в транзакции, он должен быть выведен из нее.
Никогда не ставьте код транзакции в зависимость от ввода пользователя.
Старайтесь создавать пакеты и процедуры, устанавливающие блокировки, в одном и том же порядке. Например, вначале обрабатывается таблица А, затем таблицы Б, В и т.д. Таким образом, одна процедура будет ожидать второй и взаимоблокировки не смогут возникнуть по определению.
Планируйте физическую схему так, чтобы хранить одновременно отбираемые данные как можно ближе на страницах данных. Для этого используйте нормализацию и продуманно выбирайте кластеризованные индексы. Уменьшение разброса блокировок поможет избежать их эскалации. Небольшие блокировки помогут избежать их конкуренции.
Не увеличивайте уровень изоляции, если в этом нет необходимости. Более строгий уровень изоляции увеличивает продолжительность блокировок.
Автор: Майк Вайнер (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 .
Загрузка...
