Сведения об управлении, безопасности и резервировании

SQL Server: основы управления

Если сотрудник, отвечающий за сопровождение серверов Windows, на которые установлен Microsoft SQL Server 2000, не имеет специальных знаний по администрированию баз данных, ему бывает нелегко разобраться во всех этих таблицах, индексах, хранимых процедурах, запросах и тонкостях резервирования. В статье «SQL Server: основные концепции для администраторов Windows», опубликованной в предыдущем номере журнала, я попытался изложить основные понятия и сведения, касающиеся установки и настройки системы. В этой статье я продолжу введение в SQL Server описанием инструментов, необходимых для управления SQL Server, базовых понятий настройки безопасности, а также процедур резервирования и восстановления базы данных.

Средства управления SQL Server

В состав SQL Server 2000 входят три основные программы, которые используются для управления сервером: Server Manager, SQL Server Enterprise Manager и SQL Query Analyzer. Server Manager предназначена для запуска, остановки и приостановки службы SQL Server, ее значок находится на системной панели. Если на значке зеленая стрелка, это означает, что служба SQL Server работает; красный квадратик указывает, что сервер остановлен. Помимо запуска и остановки службы SQL Server, программа Server Manager позволяет запускать и останавливать агент SQL Server Agent, диспетчер распределенных транзакций DTC (Distributed Transaction Coordinator), а также службы поиска Microsoft Search и анализа Analysis Services.

Программа Enterprise Manager (см. экран 1) позволяет управлять одним или несколькими серверами SQL Server. Запуск Enterprise Manager осуществляется из Start, Programs, Microsoft SQL Server, Enterprise Manager. Enterprise Manager можно запускать непосредственно с сервера, на котором исполняется SQL Server, но чаще Enterprise Manager устанавливается в составе клиентских программ на рабочую станцию администратора и запускается с нее.

Экран 1. Просмотр в SQL Server Enterprise Manager объектов базы данных

Чтобы использовать Enterprise Manager, необходимо сначала зарегистрироваться на сервере, которым предстоит управлять. Для этого требуется щелкнуть правой кнопкой мыши на узле SQL Server Group и выбрать из локального меню пункт New SQL Server Registration. При этом будет запущен мастер SQL Server Registration Wizard, который запрашивает имя сервера и регистрационные данные, а затем устанавливает соединение с сервером. Входящий в состав SQL Server 2000 Enterprise Manager позволяет управлять серверами SQL Server 2000 и SQL Server 7.0, но не подходит для управления SQL Server 6.5 и более ранними версиями.

После регистрации на сервере SQL Server становятся доступны для просмотра все объекты базы данных на сервере, как показано на экране 1. Щелчок правой кнопкой мыши на объекте, как правило, вызывает контекстное меню со списком действий, которые можно применить к данному объекту. Так, щелчок правой кнопкой мыши на объекте Tables (таблицы) позволяет создать новую таблицу, запустить мастер создания таблиц или просмотреть данные в таблице.

Анализатор запросов Query Analyzer является одновременно и средством управления, и средством разработки. Для запуска Query Analyzer следует нажать Start, Programs, Microsoft SQL Server, Query Analyzer. На экране 2 показано, как Query Analyzer может использоваться для написания и выполнения команд T-SQL. При запуске Query Analyzer на экране показывается пустое окно, позволяющее вводить команды T-SQL. Query Analyzer позволяет создавать объекты баз данных с использованием языка описания данных DDL (Data Definition Language) и извлекать и обновлять данные с помощью предложений языка манипулирования данными DML (Data Manipulation Language). Введенные в окне предложения T-SQL можно выполнить, нажав клавишу F5 или щелкнув по кнопке с изображением зеленой стрелки, расположенной на панели инструментов. Если введенное предложение представляет собой запрос, результат исполнения запроса будет отображен в нижней панели окна Query Analyzer. Следует иметь в виду, что все выполняемые в Query Analyzer операции производятся непосредственно на сервере SQL Server, а не на рабочей станции, на которой запущен Query Analyzer.

Экран 2. Создание кода T-SQL в SQL Query Analyzer

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

Средства безопасности SQL Server

В основе системы безопасности SQL Server лежат знакомые концепции: учетные записи и регистрация пользователей, добавление пользователей и предоставление разрешений доступа к объектам базы данных. Каждый из этих компонентов играет свою роль в обеспечении безопасности SQL Server. При использовании встроенной системы безопасности (режим integrated security) для доступа к объектам базы данных применяются учетные данные и пароли Windows. В противном случае администратор должен лично завести учетные записи пользователей в SQL Server. Учетная запись предоставляет пользователю разрешения на доступ к серверу, но не к базам данных, поддерживаемым сервером. Чтобы пользователь смог обращаться к базе данных, необходимо создать для него учетную запись и добавить эту учетную запись в базу данных. Администратор должен создать набор пользовательских учетных записей для доступа к каждой базе данных. Аналогично, то, что пользователь имеет доступ к базе данных, еще не означает, что пользователь имеет разрешения на доступ к объектам базы данных. Чтобы пользователь смог обращаться к объектам базы данных, необходимо, чтобы администратор предоставил учетной записи пользователя соответствующие разрешения доступа. Таким образом, регистрация (login) позволяет подключиться к серверу, учетная запись доступа к базе данных предоставляет возможность использовать базу данных, но для обращения к объектам базы данных необходимо предоставить соответствующие разрешения доступа. В SQL Server 2000 и SQL Server 7.0 для упрощения управления доступом пользователей к базам данных используются роли, которые выполняют ту же функцию, что и группы Windows: объединяют пользователей, обладающих одинаковыми разрешениями доступа к объектам баз данных.

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

Если выбран вариант аутентификации SQL Server, необходимо создать регистрационное имя с помощью хранимой процедуры sp_addlogin или с помощью Enterprise Manager. Для создания регистрационного имени в Enterprise Manager нужно запустить Server, Security, Logins, щелкнуть правой кнопкой мыши на Logins и в контекстном меню выбрать New Login. В диалоговом окне New Login следует указать имя регистрационной записи, имя учетной записи, пароль, используемый по умолчанию язык и имя базы данных.

Настройка подключения к серверу позволяет пользователю обращаться только к серверу, но не к базе данных. Чтобы разрешить обращение к выбранной базе данных, необходимо создать учетную запись пользователя базы данных, это делается с помощью T-SQL или через Enterprise Manager. Чтобы добавить нового пользователя с помощью T-SQL, используется хранимая процедура sp_adduser. Сначала необходимо установить соответствующий контекст базы данных с помощью команды use database. Затем следует вызвать хранимую процедуру sp_adduser, указав в качестве первого параметра имя учетной записи пользователя. Для разрешения доступа к базе данных в Enterprise Manager нужно выделить в панели слева узел необходимой базы данных, раскрыть его и щелкнуть правой кнопкой мыши на узле Users, после чего в контекстном меню выбрать New User.

После добавления пользователя базы данных можно предоставить ему доступ к различным объектам базы данных (например, таблицам и представлениям). SQL Server содержит три команды управления разрешениями: Grant, Deny, Revoke. Как следует из названий команд, Grant предоставляет пользователю разрешение на использование объекта, Deny запрещает использование. Deny имеет более высокий приоритет. Revoke отменяет уже назначенные (т. е. ранее предоставленные или запрещенные) разрешения на использование объекта. Можно управлять разрешениями на использование отдельных операций T-SQL над объектами, т. е. индивидуально управлять разрешением использования операций SELECT, INSERT, UPDATE, DELETE, EXEC и DRI.

Управление разрешениями осуществляется с помощью предложений T-SQL GRANT, DENY и REVOKE или с помощью Enterprise Manager. Для этого нужно запустить Enterprise Manager и перейти к узлу Users соответствующей базы данных. В панели деталей следует щелкнуть правой кнопкой мыши на имени пользователя и выбрать в контекстном меню пункт Manage Permissions. На экран будет выведена таблица с перечислением всех объектов базы данных в левой части и всех предложений, которые могут быть применены. Для предоставления разрешения на использование необходимо щелкнуть в соответствующей ячейке таблицы мышью (появится зеленая «галочка»), а для запрета разрешения щелкнуть еще раз (обозначение — красный крестик).

Стратегия резервного копирования

Обеспечение защиты данных является первоочередной задачей каждого администратора, а важнейший шаг по обеспечению защиты — резервное копирование. Чтобы обеспечить защиту и должный уровень резервного копирования, необходимо обладать знаниями об имеющихся в SQL Server трех моделях восстановления и режимах резервирования баз данных. Упрощенно говоря, модель восстановления устанавливает баланс между дополнительной нагрузкой из-за ведения журналов и возможностью полного восстановления данных. SQL Server 2000 имеет три основных режима восстановления: Simple (простой), Full (полный) и Bulk-Logged (пакетное журналирование).

• Режим восстановления Simple предлагает минимальную дополнительную нагрузку из-за журналирования транзакций, но не позволяет восстановить изменения данных, сделанные после последней операции резервного копирования. Все модификации данных, выполненные после последнего резервного копирования, считаются некритичными и в случае возникновения необходимости восстановления должны быть выполнены повторно.
• Режим Full расценивает как критически важные и, следовательно, подлежащие резервированию все данные вплоть до момента возникновения отказа. Все модификации данных сохраняются. По умолчанию SQL Server работает в режиме Full.
• Режим Bulk-logged представляет собой промежуточный вариант. В этом режиме большинство типичных транзакций журналируются и могут быть полностью восстановлены, а массовые (bulk) операции, такие как массовое копирование (bulk copy) и операции SELECT INTO не сохраняются в журнале и должны быть выполнены повторно. Таким образом, все остальные транзакции сохраняются и могут быть восстановлены на момент последнего выполненного резервного копирования базы данных или журнала.

Резервное копирование базы данных SQL Server может выполняться на диск, ленту или другие носители. Резервное копирование на диск является самым быстрым механизмом резервирования и восстановления данных. При этом следует учитывать, что при резервировании на диск для обеспечения защиты данных от отказа диска резервирование следует выполнять на отдельный диск, который, в идеале, должен быть подключен не к тому же контроллеру, что и диски, на которых размещена база данных. SQL Server поддерживает три основных способа резервирования базы данных: Full (полное), Differential (дифференциальное) и Log (журнальное). При полном резервировании выполняется полное копирование базы данных. При дифференциальном копировании выполняется резервное копирование только тех страниц базы данных, которые претерпели изменения с момента последнего полного резервного копирования базы. При резервировании журнала выполняется копирование только файла журнала транзакций. Можно выполнить и частичное резервное копирование базы данных, когда выполняется копирование только файловых групп, но этот вариант копирования мы здесь рассматривать не будем.

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

• Каковы требования к доступности данных?
• Каков максимальный допустимый интервал недоступности данных?
• Какова стоимость простоя (отсутствия доступа к данным)?
• Являются ли одни базы данных более критичными, чем другие?
• Какова частота изменения данных в базе?
• Могут ли данные быть воссозданы?

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

Сведения, приведенные в данной статье, помогут начать администрирование вновь установленных систем SQL Server или улучшить качество сопровождения уже имеющихся. Управление и администрирование SQL Server — тема практически неисчерпаемая. После освоения основ можно обратиться к дополнительным ресурсам, например сайту Microsoft SQL Server (http://www.microsoft.com/sql). Во врезках «Дополнительные средства для работы с SQL Server» и «Импорт и экспорт данных» дается дополнительная информация по инструментам управления.

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

Дополнительные средства для работы с SQL Server

Microsoft предлагает ряд полезных утилит, позволяющих упростить управление системами SQL Server. В первую очередь следует отметить анализатор Microsoft SQL Server Best Practices Analyzer, доступный для свободной загрузки с сайта Microsoft Download Center (http://www.microsoft.com/downloads/search.aspx). Анализатор позволяет проверить соответствие установленной системы более 70 лучшим примерам использования SQL Server 2000, в том числе в вопросах резервного копирования и восстановления, настройках сервера, эффективности работы базы данных и даже готовности к переходу на SQL Server 2005. Анализатор позволяет также строить собственные определения лучших практик использования.

Администраторам SQL Server следует регулярно использовать Microsoft Baseline Security Analyzer (MBSA), который также доступен для загрузки с сайта Microsoft Download Center. MBSA позволяет проверять уязвимые места системы безопасности для множества продуктов Microsoft, в том числе Windows Server 2003, Windows 2000 Server, Windows NT, Microsoft Internet Information Services 5.0 и Microsoft Internet Information Server 4.0, Microsoft Office, SQL Server 2000, и SQL Server 7.0.

Библиотека оперативной документации SQL Server Books Online (BOL), строго говоря, не является средством управления, но, несомненно, занимает достойное место в арсенале каждого администратора SQL Server. BOL предоставляет более развернутую документацию, чем справочные системы большинства других продуктов Microsoft. Для запуска BOL следует использовать Start, Programs, Microsoft SQL Server, Books On-line. BOL содержит превосходный источник сведений о T-SQL. Очень удобно, что в BOL даются параллельные способы решения большинства задач, как через SQL Enterprise Manager, так и с использованием T-SQL , по администрированию SQL Server. В библиотеке также описано множество примеров управления сервером. При возникновении проблем или вопросов в первую очередь имеет смысл поискать ответ в BOL.

Импорт и экспорт данных

Хотя импорт/экспорт данных Microsoft SQL Server и не является задачей администраторов, с этим приходится периодически сталкиваться практически каждому из них. Для этой цели в SQL Server 2000 и SQL Server 7.0 используются два основных инструмента DTS (Data Transformation Services, службы преобразования данных) и bcp (bulk copy program, программа массового копирования данных). Утилита DTS обладает значительно более удобным пользовательским интерфейсом, она позволяет задействовать как мастера исполнения различных операций, так и оболочку конструктора для задания сложных преобразований и передач данных между различными источниками. Интерфейс с использованием мастеров больше подходит для выполнения простой передачи данных. Для запуска DTS нужно щелкнуть Start, Programs, Microsoft SQL Server, Import and Export Data.

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

bcp

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

Таблица. Примерный график резервирования базы данных