FAQ по администрированию SQL Server 2005

1. Файлы базы данных и файлы журнала транзакций
1.1. Общие рекомендации для файлов базы данных
1.2. Общие рекомендации для файлов журнала транзакций
2. Режим восстановления базы данных
2.1. Full (режим полного протоколирования)
2.2. Bulk-logged (режим неполного протоколироваия)
2.3. Simple (простая модель восстановления)
3. Режим работы базы данных
4. Специальный режим подключения Dedicated Administrator Connection
5. Работа с DTS пакетами в SQL 2005

Примечание.
О том как заставить 1С работать под SQL 2005 (пока доступ к этой версии SQL Server, к сожелению, невозможен без изменения DLL файла, но будем надеятся что в будущем фирма 1С исправит этот недостаток) можно почитать здесь: FAQ по администрированию 1С и MS SQL Server.

1. Файлы базы данных и файлы журнала транзакций

1.1. Общие рекомендации для файлов базы данных

     Файл базы данных рекомендуется размещать на отдельном внешнем RAID масиве не ниже 5-го уровня
       На жестком диске, где находитятся файлы базы данных должно быть как минимум в 2 раза больше места чем размер самой базы данных. Это связано с административными работами с базой данных (перестроением индексов и т.п.).
Что нежелательно делать с файлами базы данных:
  1. При создании базы данных не ставте автоприращение размера фалов базы, это приведет к фрагментации файлов базы данных.
  2. Не размещайте файлы базы данных на диске, на котором установлена система или испольняемые файлы данных SQL. Особенно не рекомендуется размещать файлы базы данных на тот же диске где устанвлен контролер домена, это связано с тем, что система по умолчанию для диска, на котором находится каталог базы Active Directory (по умолчанию C:\Windows\NTDS) отключает кеширование на запись.
  3. Ни в коем случае не размещайте файлы базы данных не зашифрованом или зжатом диске средствами NTFS (это относится и к самим файлам базы данных), это приведет к значительному снижению производительности.

1.2. Общие рекомендации для файлов журнала транзакций

     Требования по производительности к этим файлам намного меньше чем к файлам баз данных. Ведь в эти файлы пишутся только изменения, которые вносятся в базу, выборки из базы не связаны с этими файлами. Как размещать эти файлы зависит от конкретных требований к базе данных и бюджета выделенного на сервер. Рассмаотрим варианты размежения фалов журнала транзакций от наиболее желательного к наименее желательному:
  • второй RAID-масив;
  • отдельный физический диск (набор дисков) на том же RAID-масиве что и файлы базы данных;
  • два обычных (желательно SATA) дисков в зеркальном отношении друг к другу (точная копия);
  • обычный отдельный диск;
  • тот же диск, на ктором размещены файлы базы данных.
    Фактически, требования к файлам журнала транзакций по производительности, сводятся к быстрой записи. Другое дело - требования по отказоустойчивости. Ведь когда по каким то причинам отказал диск с файлами базы данных, то можно сделать восстановление базы с последнего бекапа включая все последние завершенные транзакции (при соответствующих настройках модели восстановления).
   Самым ненадежным но самым производительным методом - будет размещение файлов журнала транзакций на RAM-диске. К сожалению атовр статьи не обладает информацией насколько это повысит призводительность работы системы при интенсивном изменении или вставке данных в базу. Но теоретически - прирост должен быть.
    Размер файлов журнала транзакций рекомендуется ставить от того для каких целей используется база данных. Так если база работает в режиме OLTP - то рекомендуется ставить размер 10-25% от обьема базы данных. Хотя в практике иногда случается, что размер файлов журналов транзакций может понадобится соимзеримым с размером базы, например при удалении всех записей большинства таблиц.

2. Режим восстановления базы данных

      Всего предусмотрено три режима восстановления базы данных;

2.1. Full (режим полного протоколирования)

   Максимальное количество операций записывается в журнал транзакций. Журнал транзакций автоматически не обрезается. Этот режим обеспечивает максимальные возмозможности восстановление, но рпи этом снижается и производительность. Только в этом режиме можно использовать зеркальное отображение баз данных и автоматическую доставку журналов (log shopping). Этот режим выставляется по умолчанию для новых пользовательских баз, так как такой режим установлен для базы model.

2.2. Bulk-logged (режим неполного протоколироваия)

  Это компромисный вариант между требованиям производительности и возможностями восстановления. При исопльзовании такого режима запись в журнал практически отключается для следующих типов:
  • массовой вставки (команды BULK INSERT, SELECT INTO, загрузка средствами bcp и т.п.);
  • вставки/изменение больших двоичных данных (text, ntext, image);
  • операции по созданию, перестроению и удалению индексов.
Автоматическая перезапись журанла транзакций при таком режиме не производится, работа с транзакциями, не включающими в себя перечисленные операции производится как обычно.

2.3. Simple (простая модель восстановления)

    Максимальный выиграш в производительности и удобвтсе работы за счет возможностей востсановления. Минимально протоколируются те же операции что и в режиме восстановления Bulk-logged, а кроме того, журнал транзакций автоматически очищается (блоками, размер которых изначально равен 256 Кбайт, но при необходимости он может быть автоматически увеличен). В результате можно забыть о проблеме нехватки места под журнал транзакций. Но и воспользоваться журналом транзакций для восстановления базы данных - уже не удастся. К тому же становится нвеозможным выполнение резервного копирования журнала транзакций, команда BACKUP LOG в этом режиме сразу вернет ошибку.

Опыт показывает что обычно выбирается режим SIMPLE.

3. Режим работы базы данных

   Режимы работы базы данных еще называют состояниями базы данных database state. Настраиваются они при помощи вклдаки Options окна своцств базы данных или с помощью команды ALTER DATABASE (за исключением режима ONLINE/OFFLINE, который тзменяется из вонтекстного меню базы данных в окне Object Explorer).

  • ONLINE/OFFLINE/EMERGENCY. ONLINE (оперативный режим) - нормальный рабочий режим. OFFLINE (автономный режим): база данных станет недотсупной для пользувателей, на базу данных больше не будет расходоваться оперативная память, файлы базы данных и журнала транзакций освобождются их их можно копировать средствами ОС. EMERGENCY (аварийный): база данных доступна только на чтение, отключен режим протоколирования (не ведется запись в журнал транзакций), к базе данных могут обращаться только системные администраторы (члены серверной группы sysadmib).
  • READ-ONLY/READ-WRITE. По умолчанию база данных находится в режиме READ-WRITE (чтение и запись). Перевод базы в режим READ-ONLY (только чтение) лишает пользователей возможности вносить изменения в данные, но серьезно ускоряет считывание данных за счет того, чтоникакие блокировки не накладавывается. Чтобы перевести базу в режим READ-ONLY надо вначале отключить всех пользователей. В єтом режиме часто работают архивные хранилища данных Data Warehouse (но на время пактеной загрузки данных база переводится в режим READ-WRITE).
  • MULTI_USER/RESTRICTED_USER/SINGLE_USER. Режим MULTI_USER (многопользовательский) - обычный режим, в нем по умолчанию работают все базы данных. В режиме RESTRICTED_USER (ограничения доступа пользователей) в базу данных допускаются только пользователи, которые принадлежат к роли базы данных db_owner или к одной из серверных ролей sysadmin или dbcreator. Этот режим используется, когда работа пользователей с базой данных нежелетальна (массовая загрузка данных, обновление структуры, перестроение индексов), но нужно иметь возможность открыть несколько соединений с базой данных. В режиме SINGLE_USER (однопользовательский) разрешается только одно подключение к базе данных. Этот режим часто используется в аварийных ситуациях, когда производится восстановление базы данных.
Следует заметить, что изменение режима работы требует оключение пользователей, которые работают в данный момент от базы данных.

4. Специальный режим подключения Dedicated Administrator Connection

    Этот режим подключения к SQL серверу прежназначен для административных работ, и он работает даже тогда, когда сервер занят выполнением дургих запросов или не отвечает. Это достигается за счет выделения ресурсов при старте SQL сервера на одно подключение, которые не осовбождаются в процессе работы.
      Средство для подключения к SQL Server 2005 за счет специально зарезервированых для этого ресурсов называется DAC (Dedicated Administrator Connection - выделенное административное подключение). Для того чтобы подключиться к серверу в этом режиме, используется комманда SQLCmd с параметром -A, однако в окончательную версию SQL Server 2005 была добавлена возможность использовать для этой цели и SQL Server Management Studio. Чтобы подключиттся в режиме DAC из SQL Server Management Studio, нужно выполнить следующие действия:
  1. Нажмите кнопку New Query на панели инструментов и в раскрившемся списке выберите Database Engine Query (Запрос к ядру базы данных).Откроется окно Connect to Database Engine (Подключение к ядру базы данных).
  2. В поле Server Name вместо обычного имени сервера, например, LONDON\SQL2005 введите ADMIN:LONDON\SQL2005.
  3. Выберите режим аутентификации и подключитесь к серверу.
Подключение в режиме DAC обладает некоторыми специфическими особенностями:
  • по умолчанию соединение в режиме DAC можно выполнить только с локального компьютера (т.е. с того компьютера, на котором работает SQL Server 2005). Чтобы разрешить такое соединение с удаленного компьютера, необходимо настроить для  параметра сервера remote admin connection значение 1:
    sp_configure 'remote admin connections' 1
  • в этом режиме к серверу одновременно может быть установлено только одно соединение;
  • подулючение в режиме DAC может производится только от имени учетной записи, обладающей правом CONTROL SERVER для экземпляра SQL Server. По умолчанию этим правом обладают только системные администраторы;
  • подключение в этом режиме может быть установлено только с использованием сетевой библиотеки TCP/IP;
  • подключение в режиме DAC является "неубиваемым": его нельзя закрыть командой KILL;
  • при подключении в режиме DAC вы получаете возможность напрямую производить запросы и вносить изменения в системные таблицы сервера в базе данных master(и то и другое для обычных подключений в SQL Server 2005 запрещено);
  • только в режиме DAC (и только тогда, когда сервер запущен в однопользовательском режиме) вы можете получить доступ к секретной базе данных resourse (обратится к ней можно по комаенде USE mssqlsystemresource). Эта база данных содержит копии всех системных объектов (например, системных таблиц в базах данных), которые поставляются с SQL Server 2005. Изменения в нее вносятся только при установке пакетов обновления и патчей.
Подключение в режиме DAC имеет приоритет по отношению к другим подключениям. Но это подключение имеет строго ограниченное количество ресурсов, поэтому использовать такой режим покдючения для выполнения "тяжелых" запросов не рекомендуется, и использовать этот режим следует для административных функций.

5. Работа с DTS пакетами в SQL 2005

      Для работы с пакетами DTS в SQL2005 предназначено приложение SQL Server Integration Services (сокращенно SSIS). Главное средство для работы с SSIS - это SSIS Designer, который является сотавной частью Busines Intelligence Development Studio. Чтобы открыть окно SSIS Designer, достаточно запустить Busines Intelligence Development Studio из меню Пуск/Программы/Microsoft SQL Server 2005/SQL Server Dusines Intelligence Studio и создать новый проект на основе шаблона Integration Services Project.
      Для перенесения информации с одного источника в другой с минимумом затрат по времени разработки - рекомендуется использовать SQL Server Import and Export Wizard. Его можно запустить разными способами:
  • из командой строки опреационной системы при помощи команды DTSWizard;
  • из SSIS Designer при помощи меню Project/SSIS Import and Export Wizard;
  • из SQL Server Management Studio, Если в дереве Object Explorer щелкнуть правой кнопкой мыши по объекту базы данных и в контексткном меню выбрать Tasks/Import Data (Задачи /Импортировать данные) или Tasks/Export Data (Задачи/Экспортировать данные).
Если надо работать с пакетами DTS созданные в SQL Server 2000, то их можно или просто запускать на выполнение или же в случае, елси надо редактировать пакеты - то их надо импортировать в формат SSIS. Проще всего это сделать с помощью мастера Package Migration Wizard. Его также можно запустить по разному:
  • из коммандной строки при помощи команды DTSMigrationWizard;
  • из SSIS Designer при помощи меню Project/Migrate DTS 2000 Package (Проект/Перенести проект DTS2000);
  • из SQL Server Management Studio. Для этого нужно раскрыть контейнер Management/Legacy/Data Transformation Services (Управление/Унаследование/Data Transformation Services) и в контекстном меню для этого контейнера выбрать Migration Wizard.
   Запуск пакета из командной строки операционной системы призводится при помощи утилиты dtexec. Сгенерировать для нее командную строку (или точно так же запустить пакет) можно при помощи графической утилиты dtexecui.
     Для проведение административных работ с пакетами можно воспользоваться утиитой, работающей с командной строки dtutil.
© pvase 2007