SQL Admin: Июнь 2008

Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Часть 6

Структура базы данных и повышение производительности. Часть 2

Йоэль Мартинез (Yoel Martinez)

В среде SQL Server обработка больших двоичных объектов (BLOB) и больших символьных объектов (CLOB) всегда вызывала трудности. К счастью, SQL Server 2005 обеспечивает три новых типа данных для хранения больших объектов (LOB). С помощью этих новых типов данных легко можно манипулировать BLOB- и CLOB-объектами, особенно если вы используете процедуры и функции среды Common Language Runtime (CLR). Используя алгоритмы сжатия, реализованные в библиотеке Microsoft .NET Framework 2.0, можно создать CLR-функции, которые «не оставляя швов» сжимают и распаковывают сжатые LOB-объекты с минимальным падением производительности. Прежде чем я покажу, как это делается, позвольте познакомить вас с тремя новыми типами данных, которые обеспечивают возможность такого сжатия и распаковки.

Версия SQL Server 2005 обеспечивает три новых типа данных для хранения и обработки LOBобъектов:

• nvarchar(max) используется для CLOBобъектов с Unicodeзначениями.

Эти новые типы данных varbinary(max), nvarchar(max) и varchar(max) функционируют почти так же, как традиционные типы varbinary(n), nvarchar(n) и varchar(n) соответственно. Однако максимальный объем хранения для этих трех новых типов данных составляет 2³¹1 байт или около 2 Гб.

Новые типы данных заменяют типы данных text и image предыдущих версий сервера SQL Server. В документации SQL Server 2005 Books Online (BOL) утверждается, что вы не должны использовать типы данных text и image в новых приложениях, создаваемых для версии SQL Server 2005, и следует заменить на новые все существующие типы данных text и image в унаследованных приложениях.

В отличие от значений типов text и image, значения типов varbinary(max), nvarchar(max) и varchar(max) могут использоваться для переменных и параметров функций. Это также могут быть скалярные значения, возвращаемые CLR (или TSQL) функциями. Благодаря таким характерным особенностям, эти типы являются успешными претендентами на выполнение работ по обработке данных.

Процедуры и функции среды CLR используют .NETтипы данных SqlBytes и SqlChars для получения и обработки BLOB и CLOBобъектов соответственно. Тип SqlBytes используется для передачи параметров типа varbinary(max), тогда как тип SqlChars используется для передачи параметров типов nvarchar(max) и varchar(max). CLRсреда опирается на стандарт Unicode, поэтому использование в качестве параметра типа varchar(max) подразумевает предварительное преобразование параметров перед передачей в Unicodeформат. Все это версия SQL Server 2005 делает без вашего вмешательства, но важно, чтобы вы осознавали отличия, существующие между строками, представленными в формате Unicode, и строками, не являющимися Unicodeстроками.

Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Часть 6*

Мафесами Ананта Кумар (Muthusamy Anantha Kumar aka The MAK)

*См. Мафесами Ананта Кумар. Командная оболочка Microsoft Windows PowerShell и объектная модель SMO сервера SQL Server 2005. Части 1–5 // SQL Server для администраторов. 2008. № 2–5.

В части 1 и 2 серии обсуждалось, как установить PowerShell и как использовать простые командлеты SMO и WMI. В части 3 рассказывалось о том, как писать скрипты PowerShell и подключаться к SQL Server. В части 4 было продемонстрировано, как использовать скрипт PowerShell для прохождения по содержимому файла в цикле и для подключения к разным серверам, а в части 5 обсуждалось, как использовать PowerShell и SMO для создания базы данных. В данном фрагменте будет продемонстрировано, как использовать PowerShell и скрипты PowerShell для резервного копирования баз данных.

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

Структура базы данных и повышение производительности. Часть 2*

Кимберли Л. Трипп (Kimberly L. Tripp)

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

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

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

• Ваша таблица слишком денормализована или излишне нормализована?

• Вы обнаружили, что многие атрибуты имеют те же самые значения в нескольких столбцах и в нескольких строках?

Рави С. Маньям (Ravi S. Maniam)

С удовольствием наблюдаю энтузиазм, проявляемый в отношении SQL Server 2008. Мне ежедневно задают массу вопросов об этой версии, что свидетельствует о наличии к ней интереса. Я ничуть не сомневаюсь в том, что люди регулярно читают о SQL Server 2008 и непрестанно изучают этот продукт с тем, чтобы больше узнать о нем самом и о том, как можно с его помощью совершенствовать их производственную среду. В редакции CTP5 появилась масса новых возможностей и я полагаю, она не позволит: мне — перестать публиковать свои записи, а вам — прекратить чтение. Так что давайте приступим к делу и займемся чем-нибудь полезным.

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

Разработчики изобретают собственные механизмы хранения неструктурированных данных; поделюсь некоторыми своими открытиями, сделанными несколько лет тому назад, когда я был разработчиком и имел дело исключительно со старыми базами данных и унаследованными системами вроде NT 4.0. Проект приложения предусматривал надежное хранение таких неструктурированных данных, как котировки цен, данные поставщиков и т. д. Мне надо было хранить в базе данных файл и полный UNCпуть доступа к этому файлу. Отсюда возникала следующая проблема: доступ к UNCпути должен был иметь каждый пользователь, следовательно, соответствующие этому пути файлы лишались защиты, и их мог видеть кто угодно. Поэтому, столкнувшись с такой проблемой, я создал несколько UNCпутей доступа, и только один пользователь имел доступ к такому пути. На этот раз возникла новая проблема, а именно: если группе из пяти пользователей необходимо было одновременно использовать один и тот же файл, тогда я должен был создать либо новую доступную для этих пяти пользователей папку, либо в отдельных папках создать пять копий одного и того же файла с тем, чтобы его могли видеть пять членов группы. Управляемость такой системы характеризовалась совсем не гибкостью (AGILE), но напротив, хрупкостью (FRAGILE), при условии, что за день подобная ситуация возникала раз 2030.

Задача типа данных FILESTREAM — решить все проблемы такого рода. Приложения могут задействовать насыщенные возможностями потоковые (streaming) APIинтерфейсы и производительность файловой системы (FileSystem) и при этом поддерживать согласованность транзакций между неструктурированными данными и структурированными данными с защитой. Сегодня способы работы с данными обеспечивают создание по большей части неструктурированных документов, например текстовых документов, видеоизображений, изображений, электронных таблиц и т. д. Эти неструктурированные данные нередко хранятся вне базы данных, отдельно от ее структурированных данных, и для их хранения чаще всего используется такой хитроумный способ, как тот, что описан в предыдущем параграфе. Подобное разделение могло бы усложнить управление данными. Или если данные ассоциированы со структурированным хранилищем, могут оказаться урезанными возможности потоковой передачи данных (file streaming). Тип данных FILESTREAM обеспечивает интеграцию механизма SQL Server Database Engine с файловой NTFSсистемой, поскольку хранит данные больших двоичных объектов типа varbinary(max) (BLOB) в виде файлов в файловой системе. С помощью предложений языка запросов TransactSQL можно выполнять вставку, обновление, обращение с запросами, осуществлять поиск и создавать резервные копии FILESTREAMданных. Win32интерфейсы файловой системы обеспечивают потоковый доступ к таким данным. Тип FILESTREAM использует системный NTкеш для кеширования файловых данных. Это помогает уменьшить какоелибо влияние, которое могли бы оказать FILESTREAMданные на производительность механизма Database Engine. Буферный пул сервера SQL Server не используется; следовательно, эта оперативная память доступна для обработки запросов.

Внутренняя организация типа данных FileStream

Чаще всего задают вопрос о том, что именно следует хранить как тип данных FileStream; ниже приводятся несколько возможных сценариев. Перед тем как приступить к их рассмотрению, мне хотелось бы подчеркнуть, что SQL Server 2008 попрежнему поддерживает типы данных varbinary(max), varchar(max), XML, так что к использованию типа данных FileStream следует подходить разумно. На сервере SQL Server BLOBданные могут быть обычными данными типа varbinary(max), тогда данные хранятся в таблицах, или FILESTREAMобъектами типа varbinary(max), тогда данные хранятся в файловой системе. Размер и предназначение этих данных определяют, должны ли вы прибегнуть к хранению в базе данных или в файловой системе. Если выполняются следующие условия, следует подумать об использовании типа данных FILESTREAM:

• Объем объектов, подлежащих хранению, в среднем превышает 1 Мб;

• Вы разрабатываете приложения, которые используют средний уровень для логики приложения;

• Для объектов меньшего размера хранение BLOBданных типа varbinary(max) в базе данных часто обеспечивает более высокую производительность при потоковой передаче данных.

Хранение данных типа FILESTREAM реализуется как столбец типа varbinary(max), в котором данные хранятся как BLOBобъекты в файловой системе. Размеры этих BLOBобъектов ограничены только размером тома файловой системы. Стандартное ограничение в 2 Гб, накладываемое на размер файла для типа varbinary(max), неприменимо к BLOBобъектам, которые хранятся в файловой системе. Чтобы указать на то, что данные столбца должны храниться в файловой системе, задайте для столбца типа varbinary(max) атрибут FILESTREAM. Этот атрибут заставит механизм Database Engine хранить все данные этого столбца в файловой системе, но не в файле базы данных. FILESTREAMданные должны храниться в файловых группах (FILESTREAM filegroups). Группа FILESTREAMфайлов — это особая группа файлов, содержащая каталоги файловой системы вместо самих файлов. Такие каталоги файловой системы называются контейнерами данных. Контейнеры данных — это интерфейс между механизмом хранения данных Database Engine и механизмом хранения данных файловой системы.

Когда используете FILESTREAMхранение, учтите следующее:

• Если таблица включает FILESTREAMстолбец, каждая запись должна иметь уникальный идентификатор записи (ID).

• Контейнеры FILESTREAMданных не могут быть вложенными.

• Если вы используете отказоустойчивую кластеризацию (failover clustering), группы FILESTREAMфайлов должны размещаться на разделяемых (shared) дисковых ресурсах.

• Группы FILESTREAMфайлов могут размещаться на сжатых томах.

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

Если база данных открыта, сервер SQL Server ограничивает доступ к контейнеру(ам) FILESTREAMданных за исключением тех случаев, когда к ним обращаются из транзакций TransactSQL и из APIинтерфейсов OpenSqlFilestream. Однако если база данных закрыта, физический контейнер данных полностью доступен и является объектом управления для системы безопасности ОС Windows. Мы рекомендуем обеспечить защиту каталогов, содержащих FILESTREAMданные, с тем, чтобы эти данные нельзя было случайно изменить или удалить.

Тип данных FILESTREAM реализован как столбец типа varbinary(max) и встроен непосредственно в механизм Database Engine, с FILESTREAMданными без какойлибо модернизации работают большинство инструментальных средств и функций управления сервером SQL Server. Например, применительно к FILESTREAMданным можно использовать все модели резервного копирования и восстановления, и эти данные попадают в резервную копию вместе со структурированными данными базы данных. Если вы не хотите копировать FILESTREAMданные вместе с реляционными данными, можете использовать частичное резервное копирование, чтобы исключить группы FILESTREAMфайлов.

Прежде чем обращаться к FileStreamданным и использовать их, нам необходимо разрешить (Enable) применение типа данных FileStream. Для того чтобы разрешить поддержку типа данных FileStream, должна быть выполнена системная хранимая процедура sp_filestream_configure.

Хранимая процедура sp_filestream_configure имеет следующий синтаксис:

sp_filestream_configure [ [ @enable_level = ] level ]
[ [ , @share_name = ] ‘share_name' ] ;

Параметр @enable_level должен иметь значение между 0 и 3 (табл. 1). @share_name — это параметр, который получает строковое (string) значение, указывающий имя, по которому должен осуществляться удаленный доступ к FileStreamданным. Чтобы использовать этот параметр, необходимо принадлежать к стандартной серверной роли sysadmin и к группе Windows Administrators на локальных компьютерах.

EXEC sp_filestream_configure
@enable_level = 3,
@share_name = "MyFileStreamShare";

Выполнив этот пример программного кода, мы можем перейти в командное окно и посмотреть созданное имя совместно используемого ресурса, набрав в ответ на приглашение команду NET SHARE (рис. 1).

Сценарий: мы создадим новую базу данных с именем FileStreamDB и таблицу, где можно хранить каталоги товаров поставщиков (Vendor Product Catalogs); мы могли бы вставлять записи, удалять и обновлять их. Поэтому давайте засучим рукава и поиграем с типом данных FileStream.

Первый шаг к использованию типа данных FILESTREAM — наличие поддерживающей этот тип базы данных. Поскольку механизм FILESTREAM использует группу файлов (filegroup) особого типа, вы должны хотя бы для одной группы файлов указать опцию CONTAINS FILEGROUP. Следующий пример программного кода на языке TransactSQL позволяет создать базу данных с именем FileStreamDB. Эта база данных включает три группы файлов: PRIMARY, RowGroup1 и FileStreamGroup1. PRIMARY и RowGroup1 — стандартные группы файлов, которые не могут содержать FILESTREAMданные. FileStreamGroup1 — группа FILESTREAMфайлов. В переменной FILENAME хранится путь доступа к группе FILESTREAMфайлов. Должны существовать пути доступа ко всем папкам кроме последней, последняя папка существовать не должна. В данном примере, в момент выполнения предложения CREATE DATABASE должна существовать папка C:\FSDemo, но не может существовать вложенная папка VendorCatalog.

После выполнения приведенного в примере программного кода вы должны обнаружить на своем жестком диске папку C:\FSDemo\VendorCatalog. Эта папка содержит файл filestream.hdr и папку $FSLOG folder.

Замечание Файл filestream.hdr — это важный системный файл. Не открывайте его и не вносите в него изменений.

CREATE DATABASE FileStreamDB ON PRIMARY
( NAME = FileStreamDB_data,
    FILENAME = N'C:\FSDemo\FileStreamDB_data.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%),
FILEGROUP RowGroup1
( NAME = FileStreamDB_group1,
    FILENAME = N'C:\FSDemo\FileStreamDB_group1.ndf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = FileStreamDBResumes,
    FILENAME = N'C:\FSDemo\VendorCatalog')
LOG ON
( NAME = ‘FileStreamDB_log',

    FILENAME = N'C:\FSDemo\FileStreamDB_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB);

Мы создадим две таблицы для управления подробной информацией о поставщиках (Vendor Details) и подробной информацией о присланных поставщиками каталогах. После этого мы должны будем вставить в эти таблицы какието записи. В данном примере я просто выполняю преобразование типа (casting) применительно к текстовым данным и помещаю их в хранилище FileStream.

USE FileStreamDB;

Go

--Создание главной таблицы поставщиков (Vendor Master Table)
Create Table dbo.Vendor
(
VendorID int Primary Key,
VendorName varchar(200),
VendorAddress Varchar(250),
VendorCity varchar(50),
VendorCountry varchar(50),
)

GO

--Создание таблицы, которая будет содержать каталоги
--хранилища FileStream
CREATE TABLE dbo.VendorCatalog
(
VendorID int References dbo.Vendor(VendorID),
CatalogName varchar(100),
Catalog varbinary(max) FILESTREAM,
CatalogID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
);
GO

--Вставка записи в таблицу поставщиков Vendor
Insert into dbo.Vendor
Values(
1,
‘Adventure Works',
‘4th Cross',
‘Seattle',
‘USA'
)

GO

--Вставка первой записи в таблицу каталогов Catalog
Insert into dbo.VendorCatalog
Values(
1,
‘Baseball Stuff',
Cast (‘Baseball Catalog' As varbinary(max)),
newid()
)
Go

--Вставка второй записи в таблицу каталогов
Insert into dbo.VendorCatalog
Values(
1,
‘Diving Gear',
Cast (‘Deep Sea Diving Gear Catalog' As varbinary(max)),
newid()
)

Томас ЛаРок (Thomas LaRock)

В журнале ошибок SQL Server содержится много информации. Иногда даже слишком много, а иногда недостаточно много. В некоторых ситуациях вы не знаете, достаточно ли данных в журнале, до тех пор, пока гораздо позже не найдете решения проблемы. По моему личному мнению, в версии 2005 все еще больше запутано изза огромного количества сообщений, заканчивающихся словами «Это информационное сообщение; от пользователя никаких действий не требуется». В таком случае, я могу захотеть или не захотеть получать эти сообщения, так? Действие пользователя может не «требоваться», но что если оно «желаемо» пользователем?

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

Код для необходимых таблиц и хранимых процедур для проекта вы можете загрузить в zipфайлах. Файл readme поможет вам с установкой. Я пытался сделать проект максимально универсальным, но в зависимости от вашей ситуации, возможно, придется внести некоторые изменения.

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

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

Это было очень важно для нашей группы, потому что количество обслуживаемых нами серверов менее чем за пятнадцать месяцев увеличилось с десяти до семидесяти. Наличие производственных серверов, серверов для тестирования и серверов для разработки означало, что возникающие ошибки будут очень разнообразными. Например, наши производственные серверы настроены на фиксирование неудавшихся входов в систему, а тестовые и серверы разработки нет. Любой проект, который мы разворачиваем на новом сервере, должен работать независимо от конфигурации. Но хотим ли мы, чтобы нас предупреждали каждый раз по электронной почте о том, что ктото не смог войти в систему? Нет, наверное, не хотим. Но что, если ктото пытался войти под именем «sa» или «admin» или «root»? Возможно, об этом факте мы захотим узнать как можно быстрее, потому что это может оказаться атакой или вирусом.

Советы по установке процедур-утилит и представлений

Дэвид Пул (David Poole)

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

0	Блокировать. Это значение используется по умолчанию
1	Разрешить доступ только TransactSQL
2	Разрешить доступа только TransactSQL и локальной файловой системе
3	Разрешить доступ TransactSQL, локальной файловой системе и удаленной файловой системе

Содержание номера за Июнь 2008 год

SQL Server
для администраторов

Июнь 2008
№ 6 (24)

Содержание номера за Июнь 2008 год

SQL Server для администраторов

Июнь 2008 № 6 (24)

SQL Server
для администраторов

Июнь 2008
№ 6 (24)