(Возврат на основную страницу)
Чери Уоррен
Минимизация блокирования в SQL Server
Мафесами Ананта Кумар
Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Часть 7
Винс Якобони
Более интеллектуальное резервное копирование и восстановление
Рик Бихэм
SQL Server 2008 безопасность
Кален Делани
VARBINARY(MAX) приручает BLOB
Минимизация блокирования в SQL Server
Блокирование необходимо для поддержки текущих действий чтения и записи базы данных, но оно может отрицательно влиять не производительность системы, подчас трудно обнаруживаемыми способами. В данной статье рассматривается оптимизация базы данных SQL Server 2005 и SQL Server 2008 для минимизации блокирования, а также отслеживание состояния системы для лучшего понимания воздействия блокирования на производительность.
Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Часть 7*
В части 1 и 2 серии обсуждалось, как установить PowerShell и как использовать простые командлеты SMO и WMI. В части 3 рассказывалось о том, как писать скрипты PowerShell и подключаться к SQL Server. В части 4 было продемонстрировано, как использовать скрипт PowerShell для прохождения по содержимому файла в цикле и как подключаться к разным серверам. В части 5 обсуждалось, как использовать PowerShell и SMO для создания базы данных. В части 6 говорилось про осуществление резервного копирования базы данных SQL Server при помощи PowerShell и SMO. В данном фрагменте будет продемонстрировано использование PowerShell в сочетании со SMO для отображения объектов SQL Server.
*См. Мафесами Ананта Кумар. Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Части 1–6 // SQL Server для администраторов. 2008. № 2–6.
Более интеллектуальное резервное копирование и восстановление
Резервное копирование и восстановление — еще интеллектуальнее, чем раньше
Одной из основных обязанностей администратора баз данных является резервное копирование и восстановление, и это не развлечение. Особенно, если вам нужно соорудить корректное выражение MOVE для восстановления с одного сервера на другой, или найти для восстановления наиболее поздний по времени файл с именем YYYYMMDDHHMM, или оценить обстановку с помощью утилит сторонних производителей, таких как Imceda’s SQL Litespeed или Redgate’s SQL Backup. А сколько раз вы приступали к восстановлению, забыв отключить существующие соединения? Или после восстановления с другого сервера, забывали выполнить sp_change_user_login для сопоставления имен входов с недавно восстановленными пользовательскими id? Чтобы облегчить ваши страдания, я написал процедуры sp_ABBackupDb и sp_ABRestoreDb. Моей целью было максимально облегчить для себя процесс резервного копирования /восстановления. Загрузить zipфайлы можно на нашем сайте.
Замечание по поводу наименований: sp_ — это волшебный префикс, который ищет в базе данных master хранимую процедуру, используя при этом системные таблицы текущей базы данных. При использовании в названии AB подразумевается тот же смысл, который вкладывает Microsoft при использовании имен sp_MS* для дополнительных хранимых процедур, которые существуют, но не документированы. До того как компания, на которую я работаю, объединилась с другой компанией, ее инициалами были AB.
Основная характеристика
• Определяет присутствие SQL Litespeed или SQL Backup, в случае обнаружения использует их по умолчанию.
• Может восстанавливать список баз данных, включая {UserDbs}, {SystemDbs}, или {AllDbs}.
• Понятная спецификация файлов, в которую могут быть включены:
• Имя базы данных;
• Текущее время (только для резервного копирования);
• Использование масок для отбора недавних файлов;
• Дописывание начала пути для резервной копии при наличии соответствующей спецификации файла;
• Удобные установки по умолчанию при отсутствии спецификации файла;
• Для параллельного резервного копирования/восстановления можно задавать несколько спецификаций файлов.
• Динамическое создание выражения MOVE при восстановлениях:
• Если восстанавливаемая база данных существует, используются имена файлов физически существующей базы данных;
• Если восстанавливаемой базы данных не существует, используются данные по умолчанию Default Data и путь по умолчанию Default Log, заданные в Enterprise Manager и сохраненные в реестре;
• Если имена файлов существующей базы данных и файлы для восстановления различаются, они будут логично приведены в соответствие.
• Не будет производиться дальнейших попыток, если количество типов логических файлов (данные, журнал) в резервной копии отличаются от количества в базе данных.
• Перед восстановлением существующие пользователи будут уничтожены.
• После восстановления с использованием sp_change_user_login будет установлена адресная привязка пользовательских имен входа.
• Старые резервные копии удаляются спустя определенное количество часов, дней или недель.
• Можно задать дополнительные условия WITH для проверки при резервном копировании или восстановлении.
• Выборочное сравнение резервных копий.
Для использования этих процедур, вызовите их из любой базы данных (конечно, кроме той базы данных которую вы хотите восстановить) с помощью оператора EXEC. Процедура в результате вернет ноль, если все прошло без ошибок, в противном случае будет возвращен наибольший номер из имеющихся номеров ошибок (пояснения смотрите в разделе «Ограничения»).
SQL Server 2008 безопасность
В SQL Server 2008 появилось немало новых функций и возможностей, повышающих общий уровень безопасности баз данных. Среди них ключевые возможности шифрования и проверки подлинности, а также новая система аудита, позволяющая составлять отчеты по действиям пользователей и обеспечивать соблюдение всех норм и требований. В данной статье мы вкратце рассмотрим самые важные изменения, произошедшие в системе безопасности SQL Server с выходом версии 2008. Первое, на что обращаешь внимание, — это отсутствие средства «Настройка контактной зоны», которое присутствовало в SQL Server 2005. Параметры протокола, которые раньше были доступны в этом средстве, теперь перенесены в диспетчер конфигураций. Включение и выключение функций теперь выполняется через новую среду управления на основе политик, реализованную в SQL Server 2008.
Усовершенствования в области шифрования
В системе шифрования были сделаны два серьезных изменения. Вопервых, ключи теперь могут храниться на внешних аппаратных модулях сторонних разработчиков. Вовторых, данные, хранящиеся в SQL Server, можно шифровать с использованием метода, понятного для приложений, подключающихся к базе данных. Это означает, что администратор может зашифровать все содержимое базы данных, не меняя при этом код приложения.
Первое нововведение стало возможным благодаря функции расширенного управления ключами (EKM) — она имеется в SQL Server 2008 Enterprise, Developer и Evaluation. EKM зарегистрировать в SQL Server системы управления корпоративными ключами и аппаратные модули безопасности (HSM), разработанные сторонними компаниями. После регистрации устройства пользователь может хранить ключи на этом модуле.
Кроме того, производитель модуля может добавить дополнительные возможности шифрования (например, задать правила устаревания и замены ключей). В некоторых конфигурациях это позволяет закрыть доступ к данным тем администраторам, которые не являются членами группы администраторов системы. Криптографические инструкции TSQL выполняют шифрование и расшифровку данных с применением ключей, хранящихся на внешнем EKMустройстве.
Еще одна новая функция, прозрачное шифрование, позволяет шифровать файлы баз данных, не меняя код приложений. Шифрование и расшифровка входных и выходных данных и журналов происходит в режиме реального времени. При этом используется ключ шифрования баз данных (DEK), хранящийся в загрузочной записи базы данных: так его можно использовать при восстановлении. Ключ DEK защищен сертификатом, которых находится в основной базе данных сервера. Схема на рис. 1 демонстрирует архитектуру, обеспечивающую прозрачное шифрование данных.
Все описанное помогает защитить статические данные. Существуют различные способы повышения безопасности баз данных: шифрование, настройка брандмауэра на серверах баз данных. Но дело в том, что физический носитель, на котором хранятся базы данных (в том числе и ленты с резервными копиями) подвергаются опасностям еще и другого характера. Злоумышленник может украсть сам носитель, а это, возможно, позволит ему получить доступ к информации, имеющейся на нем.
Используя прозрачное шифрование, можно зашифровать конфиденциальные данные и защитить ключи при помощи сертификата. Тем самым будет обеспечено соблюдение многих законодательных требований и отраслевых рекомендаций по обеспечению безопасности данных.
Функции прозрачного шифрования данных позволяют применять алгоритмы AES и 3DES. Шифрование файла базы данных выполняется на уровне страницы, причем страница шифруется перед записью на диск, а потом расшифровывается во время считывания в память. Для шифрования резервных файлов тоже используется ключ шифрования базы данных.
Для расшифровки базы данных необходимо иметь доступ к сертификату или асимметричному ключу, использованному при шифровании. В противном случае базу данных восстановить не удастся. Ключи следует хранить, пока резервная копия не перестанет использоваться.
Усовершенствования в области проверки подлинности
Kerberos — это сетевой протокол, предоставляющий высоконадежное средство взаимной проверки подлинности между клиентом и сервером (или двумя участниками системы безопасности). Kerberos помогает снизить число уязвимостей в случае атак с приманкой и попыток перехвата «посредником». Этот протокол имеет отношение к проверке подлинности Windows NTLM, но он надежнее и производительнее.
Чтобы использовать Kerberos для взаимной проверки подлинности, необходимо, чтобы экземпляр SPN, относящийся к SQL Server, был зарегистрирован в Active Directory, а драйвер клиента при подключении должен предоставить зарегистрированный SPN. В SQL Server 2008 проверка подлинности Kerberos была распространена на все сетевые протоколы, включая TCP, именованные каналы, общую память и адаптеры VIA. По умолчанию драйвер клиента выводит нужный SPN из экземпляра SQL Server, к которому он подключается. SPN можно также указать явным образом в параметрах строки подключения: это повышает уровень безопасности и контроля, упрощает поиск и устранение неполадок.
Службы IIS перестали использоваться для доступа к вебслужбам ASP.NET, диспетчеру отчетов и серверу отчетов. В SQL Server 2008 службы отчетов обрабатывают все запросы на проверку подлинности, проводя их через новую специальную подсистему, поддерживающую как проверку подлинности Windows, так и нестандартные варианты проверки.
Службы отчетов теперь размещают Microsoft .NET Framework и технологии ASP.NET, встроенные в среду CLR SQL Server, кроме того, они используют возможности HTTP.SYS, предлагаемые в операционной системе. Сервер отчетов включает в себя прослушиватель HTTP, который принимает запросы, направленные на URLадрес и порт, указанные во время настройки сервера. Резервированием и регистрацией URL теперь управляет непосредственно сервер отчетов (через HTTP.SYS).
Аудит системы безопасности
Подсистема аудита — это новая функция SQL Server, позволяющая проверять события ядра базы данных и настраивать параметры проверки. Для записи данных аудита используются расширенные события. В подсистеме имеются средства и процессы, которые необходимы для того, чтобы проводить, сохранять и просматривать конфигурации аудита для различных серверов и объектов баз данных.
Подсистема аудита SQL Server работает быстрее, чем функция трассировки, а среда SQL Server Management Studio упрощает создание и контроль журналов аудита. Теперь можно проводить более подробные проверки: отслеживать инструкции SELECT, INSERT, UPDATE, DELETE, REFERENCES и EXECUTE для отдельных пользователей. Более того, подсистема аудита полностью поддерживает инструкции TSQL CREATE SERVER AUDIT и CREATE SERVER AUDIT SPECIFICATION, а также связанные с ними инструкции ALTER и DROP.
Для настройки аудита необходимо его создать и указать место записи событий. Аудит может храниться в журнале безопасности Windows, в журнале приложений Windows или в любом файле. Вы присваиваете аудиту имя и настраиваете его характеристики, в частности, путь к файлу аудита и его максимальный размер. Также можно настроить аудит так, чтобы в случае сбоя проверки работа SQL Server завершалась. Если события аудита нужно записывать в несколько журналов, создается несколько аудитов.
Следующий этап — создание спецификаций аудита. В спецификации
аудита сервера собирается информация об экземпляре SQL Server;
в нее включаются объекты, относящиеся к серверу: данные учетных записей,
членство в серверных ролях. Там же имеется информация о базе данных,
контролируемая в основной базе данных, например сведения о правах доступа к
базе. При создании спецификации аудита вы указываете, в какой аудит будут
поступать наблюдаемые события. Вы можете создать несколько аудитов сервера и
несколько спецификаций аудита, но аудит может иметь только одну активную
спецификацию в каждый конкретный
момент времени.
Также можно создать спецификации аудита базы данных: они будут использоваться для отслеживания событий по отдельной базе данных. В аудит можно добавить несколько спецификаций аудита баз данных, но в каждом аудите сервера активной может быть только одна спецификация аудита баз данных.
События подсистемы аудита SQL Server, используемые в спецификациях аудита сервера, объединяются в коллекции связанных событий. Они предоставляются в виде групп действий аудита. Если такую группу добавить в спецификацию аудита, можно будет отслеживать события, включенные в группу. К примеру, существует группа действий аудита DBCC_GROUP, предоставляющая доступ к командам DBCC. Отдельно же команды DBCC включаться в аудит не могут.
Всего существует 35 групп действий аудита сервера, причем некоторые из них тесно связаны друг с другом. Например, группы SUCCESSFUL_LOGIN_GROUP, FAILED_LOGIN_GROUP и LOGOUT_GROUP. Также есть тип действий аудита AUDIT_ CHANGE_GROUP, который можно использовать для проверки самого процесса аудита.
В спецификациях аудита баз данных можно также указывать группы событий аудита, собранные в группы действий аудита на уровне баз данных. Кроме групп действий аудита, спецификации аудита баз данных могут включать в себя отдельные события аудита, позволяющие проверять инструкции, отвечающие за работу с данными. Эти события можно настроить таким образом, чтобы отслеживать всю базу данных или только определенные объекты. Действие SELECT, в частности, может использоваться для проверки запросов SELECT, обращенных как к отдельной таблице, так и ко всей схеме в целом. Кроме того, события такого рода можно настроить так, чтобы отслеживать действия по отдельным пользователям или ролям, например db_writers.
Скажем, действие SELECT можно использовать для проверки запросов SELECT, обращенных к отдельной таблице, источником которых является пользователь Mary, или роль FINANCE_DEPT, или роль базы данных Public. Нельзя не отметить, что все это предоставляет широчайшие возможности контроля и дает большой запас гибкости при настройке аудита.
Отчеты о зависимостях
В модуле составления отчетов о зависимостях появилось новое представление каталогов и новые системные функции. Если использовать sys.sql_expression_dependencies, sys.dm_sql_referencing_entities и sys.dm_sql_referenced_entities, можно создавать отчеты по зависимостям между серверами, зависимостям между базами данных и SQLзависимостями в пределах базы данных как для привязанных к схеме объектов, так и для непривязанных.
Новые роли баз данных
В роли баз данных, включенные в базу данных msdb, был внесен ряд изменений. Роль db_dtsadmin была переименована в db_ssisadmin, db_dtsltduser — в db_ssisltduser, db_dtsoperator — в db_ssisoperator. Для поддержки обратной совместимости старые роли при обновлении серверов добавляются как члены новых ролей.
Кроме этого, были добавлены новые роли для поддержки новых функций SQL Server 2008. В частности, появились новые роли для групп серверов (ServerGroupAdministratorRole и ServerGroupReaderRole), управления на основе политик (PolicyAdministratorRole), сборщика данных (dc_admin, dc_operator, and dc_proxy). В базе данных хранилища данных управления также появились новые роли для сборщика данных (mdw_admin, mdw_writer, and mdw_reader).
Безопасность файловых потоков
В SQL Server появилась поддержка хранилища файловых потоков, которое позволяет приложениям SQL Server хранить неструктурированные данные, к примеру файлы и изображения, в файловой системе. Это означает, что клиентские приложения могут воспользоваться потоковыми API, причем согласованность транзакций между структурированными и соответствующими неструктурированными данными будет поддерживаться попрежнему. Данные файловых потоков должны храниться в файловых группах типа FILESTREAM — это специальные файловые группы, содержащие не сами файлы, а системные каталоги. Эти каталоги, так называемые контейнеры данных, формируют интерфейс между хранилищем ядра базы данных и хранилищем файловой системы.
Что касается безопасности, то данные файловых потоков защищаются точно так же, как и любые другие данные: на уровне таблиц или столбцов предоставляются разрешения. Единственная учетная запись, получающая разрешения NTFS для контейнера файловых потоков, — это учетная запись, под которой работает учетная запись службы SQL Server. При открытии базы данных SQL Server ограничивает доступ к контейнерам данных файловых потоков. Исключение делается только в том случае, если доступ осуществляется при помощи транзакций TSQL и OpenSqlFilestream API.
Управление на основе политик
Управление на основе политик — это новая система управления SQL Server, появившаяся в SQL Server 2008. Она позволяет создавать политики для тестирования и создания отчетов по различным аспектам SQL Server. Политики можно применять к отдельным базам данных, отдельным экземплярам SQL Server или ко всем управляемым экземплярам SQL Servers.
Управление на основе политик позволяет тестировать параметры конфигурации SQL Server, а также многие параметры системы безопасности. Для некоторых параметров системы безопасности можно создать политики, которые будут обнаруживать серверы баз данных, не соответствующие заданным требованиям, и пытаться исправить их.
В SQL Server 2008 некоторые не самые важные функции отключены по умолчанию для сокращения возможностей атаки. Управление на основе политик позволяет выборочно включать и отключать функции. После этого можно по расписанию проверять конфигурацию и получать уведомления в случае несоответствия параметров конфигурации установленной политике.
Управление на базе политик объединяет связанные свойства и предоставляет их в пользование в виде компонентов, называемых аспектами. Например, аспект под названием Surface Area Configuration включает такие свойства как Ad Hoc Remote Queries, CLR Integration, Database Mail, OLE Automation, Remote DAC, SQL Mail, Web Assistant и xp_cmdshell. Можно создать политику, которая будет включать свойство CLR Integration, а все остальные — отключать. В политике могут задаваться сложные условия, например можно отключить свойство Database Mail на всех экземплярах SQL Server, кроме Customer_Response.
После создания политики можно ее проверить на свех серверах и создать отчет о том, какие серверы не соответствуют требованиям. Чтобы согласовать параметры серверов с требованиями политики, нужно кликнуть кнопку «Настроить». Политику следует проверять регулярно, чтобы контролировать состояние серверов. Аспекты Surface Area Configuration доступны для ядра базы данных, служб аналитики и служб отчетов.
Обратите внимание, что управление на основании политик не предназначено для внедрения средств повышения безопасности. В большинстве случаев пользователь, имея достаточные привилегии, может использовать инструкции, противоречащие политике или обходить ее и применять параметры действий, идущие вразрез с установленной политикой безопасности. Функцию управления на основе политик, реализованную в SQL Server 2008, следует рассматривать всего лишь как вспомогательное средство для наблюдения за параметрами системы безопасности SQL Server.
Разные аспекты различаются способностью принудительно изменять параметры. Это зависит от того, могут ли связанные инструкции DDL работать в режиме, отличном от режима автофиксации. В некоторых случаях аспект настраивает параметры экземпляра ядра базы данных, но администратор после этого может вернуть прежние значения. Некоторые аспекты могут вызываться серверным триггером. В таком случае пользователи с низкими привилегиями не смогут изменить параметры, да и вероятность случайного изменения параметров администратором снижается. Здесь администратору приходится временно отключать политику, чтобы изменить значения. Другая группа аспектов только создает отчеты о состоянии свойства, но менять его не может. Так работает политика, проверяющая длину симметричного и асимметичного ключа (рис. 2).
Аспекты существуют для большинства типов объектов баз данных, и многие из них участвуют в обеспечении безопасности. Например, аспект для входа в систему может определить, применяется ли политика использования паролей при входе. Аспект для хранимых процедур может проверять, все ли процедуры шифруются. Есть аспекты, которые проверяют свойства пользователей, схем, поставщиков криптографии, соответствие общим критериям, выполняют аудит C2.
Windows Server 2008
SQL Server 2008 полностью протестирован на стабильность работы в Windows Server 2008, который поставляется с включенным брандмауэром. Следующим шагом было бы неплохо научиться настраивать параметры брандмауэра. Кроме того, Windows Server 2008 обеспечивает управление доступом пользователей, так же, как и Windows Vista. Это приводит к ограничению привилегий, получаемых администратором автоматически. Эти функции влияют на работу всех версий SQL Server.
Заключение
Система безопасности в SQL Server постоянно совершенствуется. Появляются новые возможности шифрования и проверки подлинности. Новая подсистема аудита и управление на основании политик, реализованное в SQL Server 2008, дают средства, позволяющие контролировать соблюдение установленных норм безопасности.
VARBINARY(MAX) приручает BLOB
Подобно желеобразному монстру по имени Blob из старых ужастиков Стива МакКуина (Steve McQueen), смешение больших двоичных объектов (BLOB) с реляционными данными традиционно было неестественным и трудно управляемым. BLOB и данные реляционных БД это совершенно разные сущности. Реляционные данные обычно состоят из текста или чисел и, как правило, небольшого размера. Большие двоичные объекты, напротив, зачастую хранят данные изображений в форматах .jpg, .tiff или .bmp, например фотографии продукции на вебсайте, которые могут быть довольно объемными.
В качестве ремарки к дискуссии «Хранить BLOB в базе данных или в файловой системе?»: совмещение BLOBданных и данных реляционных БД имеет свои «за» и «против». И до последнего времени работать с ограниченными типами данных SQLсервера для хранения BLOB было труднее, чем с обычными реляционными данными. Но появление трех новых типов данных в SQL Server 2005 позволило обращаться с любыми данными одинаково — хранит ли ваш BLOB изображения, большие объемы информации, аудиофайлы, такие как MP3 или даже исполняемые файлы.
В этой статье я покажу, как легко использовать один из этих новых типов данных — VARBINARY(MAX) — для импорта BLOBданных и получения из базы данных SQL Server 2005. Вы можете скачать проект простого примера, демонстрирующего эту функциональность на практике. Но перед тем, как вчитываться в код, давайте изучим ключевые элементы техники хранения BLOB в SQL Server.
(Возврат на основную страницу)