Прохождение теста Works with SQL Server 2008. Требование 3.
Требование 3. На сервере должна быть включена трассировка по умолчанию (default trace).
На тему механизма трассирования в SQL Server, я летом написал многословный пост в своем MSDNовском блоге, так что если кто-либо еще не сподобился поработать с трассировками и хочет в доступной форме узнать, что это такое, может его почитать.
Помимо пользовательских трасс, в SQL Server имеется еще служебная, или дефолтная. Полюбопытствуйте ради хохмы, какие трассы сейчас имеются у вас на сервере:
select * from sys.traces
Скрипт 1
Та, у которой в графе is_default будет стоять 1, это будет она. Обычно она самая первая с id = 1, если вы ее до этого не касались. Трассы с NULL в графе path и 1 в поле is_rowset - это, скорее всего, те, что запущены у вас из профайлера. Требование 3 говорит, что служебная трасса должна быть запущена. По умолчанию, она на то и является служебной, что создается и запускается в момент старта сервера, так что если вы с ней специально ничего с ней плохого не делали, требование должно выполняться. Про служебную трассу имеется скупое упоминание в BOL: http://msdn.microsoft.com/ru-ru/library/ms175513.aspx, что совершенно незаслуженно по отношению к такому интересному объекту. Более подробно можно прочитать в статье Kalen Delaney - http://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html.
Проверить, что трасса стартована, можно, взглянув на колонку status в выдаче Скрипта 1. 1 означает, что она бежит, 0 - что стоит. Но это для обычной трассы. Если служебная трасса остановлена, в sys.traces ее просто нет. Кроме того, в отличие от обычной трассы, управление которой осуществляется при помощи процедуры sp_trace_setstatus @traceid = <id трассы>, @status = 1 (старт), 0 (стоп), 2 (убить), служебную трассу нельзя убить, а остановить/запустить можно только при помощи sp_configure. Так осуществляется ее останов:
exec sp_configure @configname = 'show advanced', @configvalue = 1
reconfigure
exec sp_configure @configname = 'default trace enabled', @configvalue = 0
reconfigure with override
Скрипт 2
А так, соответственно, запуск:
exec sp_configure @configname = 'default trace enabled', @configvalue = 1
reconfigure with override
Скрипт 3
Через sp_configure можно также посмотреть текущий статус трассы подобно тому, как мы смотрели его в поле status выдачи Скрипта 1.
exec sp_configure @configname = 'default trace enabled'
Скрипт 4
0 будет означать стояние, 1 - что она запущена.
Резюмируя, проверку и приведение в соответствие Требованию 3 можно выполнить при помощи следующего скрипта:
declare @status tinyint
select top 1 @status = status from sys.traces where is_default = 1
if @status is null begin
exec sp_configure @configname = 'default trace enabled', @configvalue = 1
reconfigure
end
Скрипт 5
Обратите внимание, что проверка выполняется не на @status = 0, а @status is null, потому что, как отмечалось выше, отключение служебной трассы приводит не к изменению поля status, а к исчезанию ее из sys.traces. Это все, что касается Требования 3. Дальнейшее для общего развития.
События, которые собирает SQL Trace, он умеет выплевывать в два типа назначения: в файл и в rowset. Примером приемника первого типа является служебная трасса, второго - профайлер. В случае приемника второго типа возможна потеря событий, если они порождаются слишком быстро и rowset не успевает их разгребать. Создать приемник первого типа можно при помощи хранимой процедуры sp_trace_create. В параметрах указывается шаблон имени файла, максимальный размер, до которого он сможет расти, прежде чем трасса переметнется писать в новый файл, и размер скользящего окна, т.е. при заведении нового файла на сколько файлов нужно отсчитать назад, чтобы оттуда начать удаление старых файлов. Эти и другие атрибуты трассы можно посмотреть в sys.traces или при помощи функции master.sys.fn_trace_getinfo(), параметром которой служит идентификатор трассы (0 - для всех трасс). Чтобы не заглядывать всякий раз в документацию, вспоминая, что какое свойство значит, я вставил их в запрос. Кроме того, распайвотил результат, пустив названия свойств по колонкам. Для служебной трассы имеем:
with cte(TraceID, TraceOption, FileName, MaxSize_MB, StopTime, IsRunning)
as (select * from ::fn_trace_getinfo(1) pivot (min(value) for property in ([1], [2], [3], [4], [5])) pt)
select
TraceID,
case TraceOption when 2 then '2 = TRACE_FILE_ROLLOVER' when 4 then '4 = SHUTDOWN_ON_ERROR' when 6 then '6 = ROLLOVER_AND_SHUTDOWN' when 8 then '8 = PRODUCE_BLACKBOX' else TraceOption end,
FileName, MaxSize_MB, StopTime, IsRunning
from cte
TraceID |
(No column name) |
FileName |
MaxSize_MB |
StopTime |
IsRunning |
1 |
2 = TRACE_FILE_ROLLOVER |
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_27.trc |
20 |
NULL |
1 |
Скрипт 6
Эта информация представляет собой подмножество из sys.traces.
Интерес также представляют события, собираемые служебной трассой и какая информация (колонки) собираются по каждому событию. Ответ дает функция master.sys.fn_trace_geteventinfo(). Чтобы получить события и колонки не в виде своих id, а в виде осмысленных описаний, ее надо сджойнить с DMV sys.trace_events и sys.trace_columns:
select f.eventid, e.name as EventName, e.category_id, cat.name as EventCategoryName, f.columnid, c.name as ColumnName
from ::fn_trace_geteventinfo(1) f
inner join sys.trace_events e on f.eventid = e.trace_event_id
inner join sys.trace_categories cat on e.category_id = cat.category_id
inner join sys.trace_columns c on f.columnid = c.trace_column_id
Скрипт 7
Аналогично, посмотреть, не навешаны ли на события трассы какие-либо фильтры, можно узнать из функции master.sys.fn_trace_getfilterinfo().
Прочитать результаты трассы можно функцией fn_trace_gettable(). В качестве первого параметра выступает шаблон файла, куда пишется трасса. В свойствах трассы (Скрипт 6) мы видели текущий файл, куда в данный момент пишется трасса - log_27.trc. 27 - это монотонно прирастающий порядковый номер файла трассы, значит, шаблон будет log.trc:
select * from master.sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc', default)
Скрипт 8
Обратите внимание, что событие 14 (Audit Login - см. sys.trace_events) не собирается служебной трассой (поставьте в Скрипте 7 where f.eventid = 14). Довключить его туда нельзя. Структуру служебной трассы модифицировать нельзя. Можно только смотреть, какие события/колонки, фильтры в нее входят. Стало быть, для решения задачи мониторинга успешных попыток логона на сервер в предыдущем посте пришлось бы создавать отдельную трассу, не реши мы ее при помощи аудита. А то я было грешным делом подумал, что требование по дефолтной трассе нужно им, чтобы проверить остальные требования.
Алексей Шуленин, Microsoft