Для тех, кто управляет SQL-сервером, но не различает таблицу и индекс

Далеко не всегда на предприятиях средних размеров существуют администраторы баз данных (DBA). В таких организациях работы по управлению и обслуживанию Microsoft SQL Server ложатся на плечи администратора серверов Windows. Администрирование SQL Server может вызвать определенные затруднения у тех, кто недостаточно хорошо разбирается в этом продукте. Тем, кто не знает, с чего начать изучение SQL Server, очень рекомендую обратить внимание на эту статью. В ней я познакомлю читателей с основами этого продукта, а знание основ поможет эффективно управлять SQL Server в подразделении или на малом предприятии. Статья будет состоять из двух частей; в первой содержится важная начальная информация и объясняется назначение основных компонентов SQL Server. Во второй части, которая будет опубликована в следующем выпуске Windows IT Pro, мы рассмотрим важнейшие средства управления SQL Server и выделим опорную точку, с которой можно начать формировать собственную стратегию обеспечения безопасности и создания резервных копий базы данных SQL Server.

Начало начал

Первым шагом в установке системы с SQL Server является выделение необходимого количества памяти. Системы баз данных требовательны к размеру оперативной памяти, и SQL Server не является исключением. Для систем, обслуживающих подразделения внутри организации, абсолютный минимум памяти должен составлять 512 Mбайт. Более крупным системам требуется соответственно больше памяти. Учитывая сегодняшние цены на оперативную память, можно свободно добавить 1 или 2 Гбайт в систему с SQL Server за сравнительно небольшие деньги. Инвестиции в дополнительную память решат проблемы производительности, которая может быть оценена в терминах сокращения времени решения проблем и производительности конечных пользователей.

Разработчики Microsoft сделали SQL Server 2000 легким в установке и запуске. Однако я не рекомендую выбирать самый простой путь — установки по умолчанию. Например, если во время установки отдельного экземпляра (instance) SQL Server выбрать установки по умолчанию, в результате получим систему, производительность которой ниже оптимальной. По умолчанию программа установки SQL Server создает файлы базы данных и файлы журнала на одном и том же диске. Чтобы добиться повышения производительности SQL Server, лучше расположить эти файлы на разных дисках. Таким образом, первое, что необходимо сделать при установке, это убедиться в том, что SQL Server имеет достаточное количество дисков. Как минимум, нужно иметь три диска. Один для операционной системы, другой — для файлов данных и третий — для файла журналов. На рис. 1 показана типичная конфигурация дисков для малых и среднемасштабных установок SQL Server. В этом примере приведена система, состоящая из восьми дисков. Здесь операционная система, файлы данных и файлы журналов располагаются на разных дисках. Для обеспечения отказоустойчивости операционная система и файлы журнала используют зеркалирование. Файлы данных используют чередование данных по RAID 5 для обеспечения эффективности работы хранилища. Для максимальной защиты данных вместо RAID 5 можно использовать RAID 1 для дисков с данными. Однако это решение обходится существенно дороже по сравнению с RAID 5, поскольку зеркалирование требует вдвое большего дискового пространства по сравнению с объемом хранимых данных. Дисковые приводы SCSI превосходят по производительности и скорости приводы IDE, поэтому использование таких дисков положительно сказывается на производительности всей системы.

Рисунок. Типовая конфигурация дисковой системы для малых и среднемасштабных систем SQL Server 2000

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

Другим важным решением при установке SQL Server является тип авторизацию. SQL Server поддерживает два типа авторизации: через Windows (Windows authentication) и авторизацию встроенными средствами SQL Server (mixed-mode). При авторизации через Windows SQL Server проверяет входящие учетную запись и пароль, применяя пользовательскую учетную запись и пароль Windows. При авторизации через SQL Server необходимо создавать и поддерживать отдельный набор учетных записей внутри SQL Server. Хотя каждый тип имеет свои преимущества и недостатки, обычно по возможности используют авторизацию Windows. Авторизация через Windows позволяет содержать только один набор паролей и учетных записей, а приложения, которые подключаются к SQL Server, не нуждаются во время соединения во вводе дополнительных учетных данных и паролей. Windows обеспечивает проверку достоверности учетных записей и паролей и предоставляет соответствующие разрешения. Наконец, следует убедиться, что для учетной записи пользователя SA создан сложный пароль. Многие из вредоносных программ специально разработаны в расчете на то, что пользователь SA не имеет пароля. Нельзя оставлять пароль SA пустым или использовать легко угадываемые значения типа SA или password.

Основы SQL Server

Возможно, процесс управления SQL Server 2000 покажется вам не таким уж сложным, когда вы поймете, как работает продукт. SQL Server после установки содержит четыре системные базы данных (master, model, msdb и tempdb) и две пользовательские базы. База master, наверное, является самой важной из системных баз. Она включает таблицы, которые описывают все другие базы в системе и содержат информацию об учетных записях и параметрах безопасности. База model содержит шаблоны для всех новых баз. Все базы, создающиеся на сервере, наследуют все установки из базы model. База msdb используется SQL Server Agent для хранения информации о расписании заданий. В ней также хранится информация о резервных копированиях и репликациях. База tempdb хранит временные рабочие таблицы. Объекты в базе tempdb присутствуют до тех пор, пока пользователь, который создал их, зарегистрирован в системе.

Две пользовательские базы, Pubs и Northwind, задействованы в качестве примеров. База Pubs - база, структурирующая информацию в виде автор-публикация, содержит примеры информации о списке авторов, их книгах и издателях. База Northwind подобна учебной базе с тем же названием в Microsoft Access. Она содержит примеры информационных форм о продажах фиктивной компании Northwind Traders. Хотя Northwind не так велика по размеру, как обычные базы SQL, она больше, чем игрушечная база Pubs.

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

Базы данных. Базы данных содержат информацию, которую используют приложения. База SQL Server включает набор таблиц, представлений, индексов и хранимых процедур. Каждое из приложений обычно разработано так, что оно соединяется со своей базой. Выделенный SQL Server способен поддерживать множество баз данных (32 767 баз на сервер). База данных SQL Server может разрастаться до очень больших размеров. Максимальный размер базы составляет 1,048,516 Tбайт. Каждая база должна состоять, как минимум, из двух файлов — файла данных и файла журнала. Файл данных содержит таблицы, строки и столбцы информации. Файл журнала содержит все транзакционные операции (INSERT, UPDATE, DELETE), которые выполняли пользователи или приложения при работе с базой. Как уже упоминалось, чтобы гарантировать оптимальную производительность, никогда не следует располагать файлы данных и журнала на одном и том же диске работающей системы. Аналогично не рекомендуется располагать файлы данных и журнала на сжатых или шифрованных дисках.

Во время создания базы данных SQL Server использует копию базы model в качестве шаблона для новой базы. Можно задать максимальный объем, до которого может разрастаться база в мегабайтах или процентах от ее размера. Для большинства установок лучше выбрать режим auto-grow (автоматическое расширение), который позволяет базе при необходимости расширяться автоматически. Если администратор хорошо разбирается в особенностях имеющихся приложений, то примерно должен представлять ожидаемый рост и размеры базы. Тем самым можно избежать автоматического расширения базы во время ее активного использования, что может негативно сказаться на производительности всей системы.

Таблицы. Таблица — основная форма хранения информации в реляционных базах данных. Таблицы содержат различные наборы информации. По существу, например, каждая строка в таблице покупателей будет содержать всю соответствующую информацию для выбранного покупателя. Обычно такая информация включает идентификатор покупателя (customer ID), имя, адрес и контактную информацию. Каждый отдельный фрагмент такой информации (например, номер покупателя) содержится в соответствующем столбце. Каждый столбец может хранить данные только заданного типа. Например, первый столбец в таблице покупателей может быть назван CustomerID. Тип содержащихся в нем данных будет определен как int; это будет означать, что в столбце сохраняются только целочисленные данные. Второй столбец с именем CustomerName может быть определен как varchar(40) - значит, в столбце сохраняются текстовые данные длинной до 40 символов.

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

Удалить или добавить индексы можно без вмешательства в общую архитектуру базы данных. Хотя первоочередной целью использования индекса является увеличение скорости доступа к данным, использование чрезмерного количества индексов может дать противоположный результат. Когда индекс создан, SQL Server должен обслуживать его (т. е. поддерживать в актуальном состоянии), поскольку данные добавляются в таблицы базы. Процесс, обеспечивающий работу SQL Server, должен не только добавлять данные в таблицу, но и обновлять существующие индексы одновременно с новыми данными. Естественно, чем больше индексов вы создадите, тем дольше будет проходить процесс их обновления. Чрезмерное количество индексов может отрицательно повлиять на производительность всей системы. Одним из общих методов, которые могут улучшить выполнение пакетных заданий, является следующий. Сначала пишется сценарий, удаляющий перед выполнением задания все индексы. Затем пишется другой сценарий, который будет восстанавливать индексы по окончании процедуры. В SQL Server 2000 имеется мастер настройки индексов (Index Tuning Wizard), который анализирует запросы и предлагает индексы, способные улучшить производительность этих запросов.

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

Хранимые процедуры и триггеры. Хранимые процедуры создаются с использованием кода на языке T-SQL. Хранимые процедуры являются основой большинства приложений баз данных. Триггер - это специальный тип хранимой процедуры, которая может быть подключена к таблице. Поскольку хранимые процедуры скомпилированы, они обеспечивают наилучшую производительность по сравнению с динамически выполняемыми процедурами SQL (код SQL, который программа генерирует перед выполнением команды SQL). Когда динамический код SQL выполняется на сервере, процесс, управляющий работой SQL Server, сначала анализирует и проверяет команду, гарантируя тем самым отсутствие ошибок в синтаксисе. Затем создается план, по которому будет осуществляться доступ к данным. Хранимые процедуры позволяют выполнить эту работу на этапе создания данной хранимой процедуры, а не динамически, в процессе работы приложения. Это дает хранимым процедурам значительное преимущество в производительности перед динамическим выполнением кода SQL при работе приложения. SQL Server имеет функцию кэширования запросов. Он может кэшировать отдельные фрагменты динамического кода SQL на определенное время. Однако кэширование не дает никаких гарантий того, что необходимый динамический код все еще будет присутствовать в кэше при следующем его использовании.

Подобно хранимым процедурам, код T-SQL используется и для создания триггеров. В отличие от хранимых процедур, которые могут выполняться любыми пользователями, имеющими необходимые права на эту хранимую процедуру, триггеры запускаются исключительно самой базой данных. Они связываются с таблицами с помощью процедуры CREATE TRIGGER. Кроме того, они запускаются в тот момент, когда над таблицей производятся определенные действия, такие как INSERT, UPDATE или DELETE. Администраторы баз данных обычно используют триггеры для обеспечения целостности ссылок. У вас имеется возможность выполнить последовательные операции удаления DELETE в таблицах, на которые ссылаются строки ссылающейся таблицы. Код триггера запускается только после проверки SQL Server целостности, включая проверки целостности ссылок.

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

Майкл Оти - Старший технический редактор Windows & .NET Magazine и президент компании TECA. С ним можно связаться по адресу: mikeo@teca.com