По материалам статьи Brian Knight на sqlmag.com «Configuring SQL Сервер 2000 Options»

Брайен пишет, что для рядового DBA настройка параметров SQL Сервера и базы данных является наименее понятой процедурой и наиболее часто становится причиной неправильной работы. Документации об этих параметрах недостает или она чересчур запутана. В SQL Server 2000 Микрософт устранил часть этого беспорядка, удалив несколько параметров конфигурации и сделав их динамическими. В SQL Server 2000 добавлено только два новых параметра конфигурации сервера и откорректированы некоторые параметры базы данных, что должно сделать жизнь DBA немного проще. Эти два новых параметра не доступны для изменения из Enterprise Manager, что должно гарантировать Вас от неосторожного нанесения вреда серверу. Вместо этого, Вы должны использовать T-SQL и системную хранимую процедуру sp_configure, которая позволяет изменять эти дополнительные параметры. Чтобы увидеть текущую конфигурацию Вашего сервера, запустите sp_configure без параметров. В результате, столбец config_value, который SQL сервер формирует из таблицы базы master syscurconfigs, покажет текущую конфигурацию вашего сервера. Столбец Run_value показывает, какие значения SQL сервер получил посредством sp_configure, необходимые данные для этого берутся из таблицы sysconfigures. После того, как Вы изменяете параметры, необходимо выполнять команду RECONFIGURE и перезапустить SQL сервер. После этого активизируется новое значение run_value.
По умолчанию, sp_configure показывает Вам только 10 из 36 доступных параметров сервера, скрывая дополнительные параметры. Если включить опцию 'show advanced options', SQL сервер покажет Вам все настройки. Чтобы включить эту опцию, используйте следующий синтаксис:

EXEC sp_configure 'show advanced options', '1'
RECONFIGURE

Активизирует эту опцию команда RECONFIGURE. Представленный выше запрос выводит следующий результат:

Configuration option 'show advanced options' changed
from 0 to 1.
Run the RECONFIGURE command to install.

После включения расширенного списка параметров, Вам станут доступны два новых параметра настройки. Наиболее важная опция «awe enabled». Эта опция позволяет SQL Server Enterprise Edition использовать большую память. По умолчанию, SQL сервер может использовать максимум 3GB RAM. Под Windows 2000, прикладные программы могут использовать Address Windowing Extensions (AWE) API, которое позволяет адресовать большее количество памяти. В Win2K Advanced Сервер, например, Вы можете использовать до 8GB RAM, а в Win2K Datacenter до 64GB. Когда SQL сервер имеет большое количество доступной памяти, это позволяет кэшировать больше данных и улучшает время исполнения запроса. Однако, после установки опции awe enabled, SQL сервер не сможет больше динамически распределять память. Потеря такой функциональной возможности увеличит стоимость администрации, т.к. необходимо будет контролировать использование RAM. После того, как Вы установите эту опцию, Вам придётся установить опцию «max server memory». Если Вы не установите max server memory, и ваш сервер имеет 3GB RAM, SQL сервер заберёт себе всю доступную память, оставив только 128МБ для Windows и других прикладных программ. Установив опцию max сервер memory, Вы можете ограничить объем памяти, потребляемый  SQL сервером. Опция awe enabled применима только для SQL Server 2000 Enterprise Edition, работающем под управлением Win2K AS или Datacenter. Для других версий опция игнорируется.
Обеспечение поддержки AWE у SQL Server Enterprise Edition 2000 включает три шага. Сначала, Вы должны гарантировать, что учетная запись, от имени которой стартует SQL сервер, имеет право блокировать страницы памяти в Windows. При инсталляции SQL сервера, это право автоматически предоставляется учетной записи, которая запускает сервер баз данных, Но эти права могут быть изъяты администратором сервера. Вы можете проверить наличие этих разрешений, используя утилиту Win2K Group Policy. Следующим шагом Вы должен выполнить sp_configure и установить опцию awe enabled в 1. После чего выполняется команда RECONFIGURE и вручную перезапускается SQL сервер. Синтаксис следующий:

EXEC sp_configure 'awe enabled', '1'
RECONFIGURE

Обратите внимание, что кроме уже описанных шагов, Вам потребуется внести изменения и в конфигурацию Win2K или NT, чтобы обеспечить адресацию физической памяти выше 4GB. Вы должны изменить файл boot.ini, добавив ключ /pae. Для получения дополнительной информации об этом, см. Windows 2000 Books Online (BOL).

Следующим новым параметром SQL Server 2000 является режим безопасности C2. C2 – это показатель оценки защиты системы (принятый в США), который показывает, что система достаточно хорошо защищена и имеет развитые функции аудита. Этот режим позволяет контролировать все попытки обращения к объектам базы данных. Чтобы включить режим C2, используйте следующий синтаксис:

EXEC sp_configure 'c2 audit mode', '1'
RECONFIGURE

Чтобы полностью обеспечить требования c2, Windows также должен соответствовать этому классу защищённости. После того, как Вы установили опцию «c2 audit mode» и перезапустили SQL сервер, автоматически создаётся файл трассировки в каталоге \MSSQL\Data. Используя Profiler, Вы можете просматривать этот файл трассировки.
SQL сервер сохраняет данные в файл трассировки блоками во 128Кб. Т.о. если SQL сервер будет отключен не надлежащим образом, самое большее, что Вы можете потерять, это 128Кб данных аудита. Не трудно представить, что файлы трассировки аудита будут быстро расти. Когда журнал аудита достигнет 200Мб, он будет завершен и создастся новый. При каждом своём запуске SQL сервер начинает новый файл аудита. Если пространство на жестком диске будет исчерпано, SQL сервер завершит свою работу и не будет запускаться до тех пор, пока Вы не освободите место на диске для файла аудита и не перезапустите сервер. У Вас есть возможность отключить аудит при запуске сервера, используя ключ -f.

В SQL Server 2000 были удалены несколько параметров, которые теперь конфигурируются автоматически. Наиболее известный из удаленных параметров – «max async IO», которую указывал DBA сколько асинхронных, дисковых операций чтения и записи может быть разрешено для каждого файла базы данных. Это был один из наиболее непонятых параметров SQL Server 7.0, который по умолчанию устанавливался в 32, и редко кем – либо изменялся. В SQL Server 2000 опция max async IO динамически увеличивается или уменьшается, поскольку теперь сервер обладает адаптивной обратной связью. SQL сервер использует алгоритм обратной связи, чтобы определить загрузку сервера и сколько он сможет обработать операций.

Запустив SQL Server 2000 Enterprise Manager и открыв вкладку Properties/Options для базы данных, Вы увидите, что там отсутствуют некоторые привычные уже параметры. Например, отсутствуют параметры «trunc. log on chkpt.» и «Select Into/Bulk Copy». Для ясности и обратной совместимости, эти параметры теперь объединены в опции «recovery model». Если Вы используете SQL Server 2000 Enterprise Manager для подключения к базам данных SQL Server 7.0, эти параметры будут показаны. Традиционно, для включения опции trunc. log on chkpt. использовался следующий синтаксис:

SP_DBOPTION Northwind ,'trunc. log on chkpt.', true

После этого, Вы могли проверить состояние этой опции во вкладке Options или выполнив следующий запрос:

SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

Если запрос возвращает «1», значит опция установлена в истину. Возврат «0» говорил о том, что опция не была активирована. Значение NULL означало, что Вы выбрали недопустимую опцию, или база данных не существует.
В целях упрощения применения параметров «trunc. log on chkpt.» и «Select Into/Bulk Copy», Микрософт включил их в состав опции recovery model. Целью этого изменения является то, что применение опции recovery model гарантирует полное понимание DBA стратегии восстановления данных. SQL Server 2000 теперь имеет три модели восстановления баз данных: простое, полное, и bulk_logged.
Простая модель восстановления самая лёгкая в администрировании, но и наименее гибкая из стратегий восстановления. Выбор простой модели - эквивалентен установке trunc. log on chkpt. в истину. При такой модели, Вы можете исполнять только полные и промежуточные резервные копии, поскольку журнал транзакций будет постоянно усекаться. Резервирование записей журнала транзакций будет не доступно.
Полная модель восстановления устанавливает trunc. log on chkpt. и опцию Select Into/Bulk Copy в ложь. Такая конфигурация предоставляет максимальные возможности резервирования, включая сбои при массовой загрузке.
Последней моделью восстановления является bulk_logged, которая устанавливает trunc. log on chkpt. в ложь, а Select Into/Bulk Copy в истину. При этом SQL сервер не регистрирует в журнале транзакций оптовые операции. Поэтому, если сбой произошёл во время массовой загрузки данных, полное восстановление не гарантируется.
Установка модели восстановления теперь возможна через команду ALTER DATABASE, синтаксис которой в новой версии был расширен:

ALTER DATABASE Northwind
             SET RECOVERY FULL

Наиболее сильным изменениям подвергся параметр, который переводит базу данных в однопользовательский режим. DBA часто переводят базу данных в однопользовательский режим, чтобы восстановить целостность или разрешить другие проблемы данных. В этом режиме только один пользователь может обращаться к данным, и он позволяет вносить необходимые исправления прежде, чем пользователи смогут использовать не корректные данные. В SQL Server 7.0 Вы должны были предварительно отсоединить всех пользователей от базы, а потом переводить её в однопользовательский режим. Для мощных OLTP баз данных, с большим числом клиентов, это сделать достаточно трудно. В SQL Server 2000 можно предоставить пользователям определенное количество времени для завершения сеансов, после чего сервер  автоматически их отсоединит. Также существует возможность отключать всех пользователей немедленно. Команда ALTER DATABASE была дополнена возможностью переводить базу данных в однопользовательский режим. Простым методом перевода базы данных в однопользовательском режим является предварительная установка переключателя Restrict Access, затем выбор Single user для базы данных во вкладке Options. Альтернативным вариантом является использование команды ALTER DATABASE:

ALTER DATABASE Northwind
             SET SINGLE_USER

После того, как команда будет выполнена, SQL сервер дожидается пока завершатся все транзакции текущих подключений к базе данных. Пользователи, которые попытаются соединиться с базой данных в однопользовательском режиме, получат сообщение об ошибке, которое говорит о том, что база данных блокирована, и все пользователи отключены. Если для клиента, отправившего команду, не задано значение параметра «query timeout», устанавливающего временной интервал ожидания ответа на запрос, клиент будет ждать ответ на запрос до тех пор, пока все клиенты не будут отключены от базы. С помощью Query Analyzer, Вы можете определить величину значения query timeout в секундах (вкладка Connections окна Параметры из пункта меню Tools). Только пользователь, который выполнил команду ALTER DATABASE, останется подключённым к базе данных. Существует возможность отключить от базы всех пользователей без ожидания. Для этого используют команду ROLLBACK IMMEDIATE, которая не задаётся из Enterprise Manager. Допустимо использовать запрос в Query Analyzer, который имеет  следующий вид:

ALTER DATABASE Northwind
             SET SINGLE_USER
             WITH ROLLBACK IMMEDIATE

После того, как будет исполнена эта команда, SQL сервер разорвёт все подключения и откатит не завершённые транзакции назад. Все подключённые пользователи получат сообщение об ошибке соединения и не смогут подключиться к  базе данных. Можно определить некоторый временной интервал, который SQL сервер даст пользователям для завершения транзакций прежде, чем они будут отключены. Для этого используется  ключевое слово ROLLBACK AFTER:

ALTER DATABASE Northwind
             SET SINGLE_USER
             WITH ROLLBACK AFTER 20 SECONDS

В этом примере, SQL сервер будет ждать 20 секунд после запуска команды, а затем отключит пользователей и откатит транзакции. В течение этого процесса новым пользователям не будет отказано в подключении, и для них всё будет выглядеть так же, как будто сервер находится в состоянии паузы. Если к базе данных не подключен ни один пользователь, после исполнения запроса, база данных немедленно будет переведена в однопользовательский режим.

В заключение, рассмотрим параметр, который Вы можете увидеть в Enterprise Manager во вкладке Options, это уровень совместимости. Чтобы устанавливать эту опцию, необходимо выбрать в раскрывающемся списке соответствующий уровень. Уровень 80 соответствует  SQL Server 2000, уровень 70 соответствует SQL Server 7.0, а уровень 65 соответствует SQL Server 2000 6.5. Уровень совместимости определяет реакцию сервера на некоторые запросы к базе данных и обеспечивает обратную совместимость. Уровень совместимости существовал и  SQL Server 7.0, но доступен он был только через системную хранимую процедуру sp_dbcmptlevel. Подробную информацию о том, какой уровень совместимости Вам необходимо выбрать при переходе на новую версию, особенно когда не подлежит переделке клиентское приложение, Вы можете найти в BOL.