Аудит заданий агента перед миграцией

Автор: Andy Brownsword, Querying msdb: A Pre-Migration Audit for SQL Agent Jobs

В большинстве заданий SQL Server, расписаний и скрытых сложностей гораздо больше, чем вы осознаёте. И только когда вы подходите к миграции и заглядываете под капот, масштаб становится очевидным.

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

Аудит заданий

Сам запрос довольно объёмный, поэтому я не буду встраивать его напрямую — ознакомьтесь с ним тут: Gist. Результат будет выглядеть примерно так (без обфускации):

Обратите внимание, что скрипт использует функцию STRING_AGG. Если вы работаете с версией SQL Server до 2017 года, замените её на FOR XML PATH (или попросите вашего ИИ-помощника сделать это). Также если у вас много заданий или они выполняются часто, у вас могут быть активные расписания, но последнее выполнение отсутствует в журнале истории (как в ежемесячных расписаниях выше) — обратите внимание на этот ложный индикатор того, что они не используются.

В этом запросе мы использовали следующие таблицы:

• sysjobs и sysjobsteps — основная информация о работе и её действиях
• sysschedules и sysjobschedules — параметры времени того, что и когда будет запускаться
• sysproxies — учётные записи, от имени которых будут выполняться задания
• sysjobhistory — история выполнения и показатели производительности (шаг 0 — это всё выполнение целиком)

Расписания работают неинтуитивно, а история выполнения запутана, поэтому мы используем здесь различные обобщённые табличные выражения (CTE), чтобы упростить, отформатировать и организовать выходные данные перед их объединением. Здесь проводится большая работа по форматированию и приведению в порядок. Логика для этого основана на документации, такой как dbo.sysschedules, если вы хотите углубиться в детали.

Выполнить запрос — это простая часть. Что мы можем извлечь из этих результатов?

Выводы и действия

Если вы находитесь в благоприятном положении с небольшим количеством тривиальных заданий или с идентичными возможностями по обе стороны вашей миграции, использование команды Copy-DbaAgentJob из DbaTools может быть простой победой.

Для остальных из нас, простых смертных, эти результаты — лишь начало.

Плохо управляемые задания могут стать троянским конём при миграции; понимание этих результатов имеет ключевое значение. Я видел, как задания получали 10-кратное падение производительности при сопоставимых ядрах и памяти только потому, что на локальной виртуальной машине было флэш-хранилище, а в облаке — операции ввода-вывода на уровне USB. Тщательное планирование, включая тестирование, имеет решающее значение для успеха.

Вот на что следует обратить внимание в результатах и какие действия предпринять:

• Задания, которые не включены — избавьтесь от них
• Задания, у которых нет расписания — также избавьтесь от них
• Проверьте категории, владельцев и учётные записи прокси — если что-то не в порядке, исправьте это заранее, чтобы упростить миграцию
• Проверьте типы шагов — любые необычные или скрытые шаги, такие как CmdExec или PowerShell, могут потребовать особых стратегий миграции
• Проверьте наличие общих расписаний — они представляют риск, если изменения вносятся без понимания последствий. Если они не должны быть общими, разделите расписания
• Большая продолжительность выполнения — проведите тесты в новой среде, чтобы убедиться, что производительность приемлема или находится в допустимых пределах

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

Предстоит ещё много работы, чтобы получить окончательный список того, что необходимо перенести. Это относительно небольшая часть работы, особенно если вы имеете дело с миграцией полноценной установки — движка SQL, Reporting Services, Analysis Services и Integration Services — из локальной среды в другой стек в Azure или от другого поставщика.

Но это уже ваша задача, дружище. Я здесь сделал простую часть.

Заключение

Задания агента SQL — это не «остатки», которые нужно подчистить после миграции. Некоторые задания могут управлять основными бизнес-процессами, поэтому их понимание и учёт при миграции имеют решающее значение.

Здесь мы рассмотрели скрипт, который извлекает ключевые детали и показатели из msdb для включения в план миграции. Вы узнаете, какие подсистемы используются (SSIS, PowerShell), какие процессы выполняются дольше всего (для проверки паритета производительности), а какие не используются и могут быть удалены (отличное время для наведения порядка).

Перечислить задания просто, но реальность такова, что для миграции заданий может потребоваться много усилий. Миграция в идентичную среду или работа с тривиальными TSQL-заданиями может быть простой. Если вы также рефакторите свой технологический стек — переходите с SSRS на Power BI или с SSIS на Data Factory — это лишь начало пути.

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

WITH Steps AS (

	SELECT job_id,
		StepCount = COUNT(1),
		SubSystem = CONCAT(subsystem, ' (', COUNT(1), ')'),
		Proxy = CONCAT(ISNULL(p.[name], 'Not Set'), ' (', COUNT(1), ')')
	FROM msdb.dbo.sysjobsteps js
		LEFT JOIN msdb.dbo.sysproxies p ON js.proxy_id = p.proxy_id
	GROUP BY job_id, subsystem, p.[name]

), SharedSchedules AS (

	SELECT js.job_id, SharedWith = STRING_AGG(j.[name], ', ')
	FROM msdb.dbo.sysjobschedules js
		INNER JOIN msdb.dbo.sysjobschedules x
			ON js.schedule_id = x.schedule_id
			AND js.job_id <> x.job_id
		INNER JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
	GROUP BY js.job_id

), LastResult AS (

	SELECT TOP (1) WITH TIES
		job_id,
		LastRanAt = DATETIMEFROMPARTS(
			run_date / 10000, (run_date % 10000) / 100, run_date % 100,
			run_time / 10000, (run_time % 10000) / 100, run_time % 100, 0),
		LastStatus = CASE run_status
			WHEN 0 THEN 'Failed'
			WHEN 1 THEN 'Succeeded'
			WHEN 2 THEN 'Retry'
			WHEN 3 THEN 'Cancelled'
			WHEN 4 THEN 'In Progress'
			ELSE 'Unknown' END,
		LastDuration = STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	FROM msdb.dbo.sysjobhistory
	WHERE step_id = 0
	ORDER BY ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC)

), Schedules AS (

    SELECT
        js.schedule_id,
		js.freq_type,
		js.freq_interval,
		js.freq_subday_type,
		js.freq_recurrence_factor,
        DaysOfWeek = STUFF(
            CASE WHEN (js.freq_interval & 1) = 1 THEN ', Sunday' ELSE '' END +
            CASE WHEN (js.freq_interval & 2) = 2 THEN ', Monday' ELSE '' END +
            CASE WHEN (js.freq_interval & 4) = 4 THEN ', Tuesday' ELSE '' END +
            CASE WHEN (js.freq_interval & 8) = 8 THEN ', Wednesday' ELSE '' END +
            CASE WHEN (js.freq_interval & 16) = 16 THEN ', Thursday' ELSE '' END +
            CASE WHEN (js.freq_interval & 32) = 32 THEN ', Friday' ELSE '' END +
            CASE WHEN (js.freq_interval & 64) = 64 THEN ', Saturday' ELSE '' END,
            1, 2, ''),
        MonthlyRelativeDay = CASE js.freq_interval
            WHEN 1 THEN 'Sunday'
            WHEN 2 THEN 'Monday'
            WHEN 3 THEN 'Tuesday'
            WHEN 4 THEN 'Wednesday'
            WHEN 5 THEN 'Thursday'
            WHEN 6 THEN 'Friday'
            WHEN 7 THEN 'Saturday'
            WHEN 8 THEN 'Day'
            WHEN 9 THEN 'Weekday'
            WHEN 10 THEN 'Weekend day'
            ELSE 'Unknown' END,
        MonthlyRelativeOrdinal = CASE js.freq_relative_interval
			WHEN 1 THEN 'First'
			WHEN 2 THEN 'Second'
			WHEN 4 THEN 'Third'
			WHEN 8 THEN 'Fourth'
			WHEN 16 THEN 'Last'
			ELSE 'Unknown' END,
        SubdayText = CASE js.freq_subday_type
            WHEN 1 THEN ''
            WHEN 2 THEN ' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' second(s)'
            WHEN 4 THEN ' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
            WHEN 8 THEN ' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' hour'
            ELSE '' END,
        StartTimeText = STUFF(STUFF(RIGHT('000000' + CAST(js.active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':'),
		TimeText = CASE js.freq_subday_type
            WHEN 1 THEN ' at ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 2 THEN ' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' second(s)' +
				' between ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') +
				' and ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_end_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 4 THEN	' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' minute(s)' +
				' between ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') +
				' and ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_end_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 8 THEN ' every ' + CAST(js.freq_subday_interval AS VARCHAR(10)) + ' hour(s)' +
				' between ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') +
				' and ' + STUFF(STUFF(RIGHT('000000' + CAST(js.active_end_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            ELSE ''
        END
    FROM
		msdb.dbo.sysschedules js
)
SELECT
	JobId = j.job_id,
	JobCategory = c.[name],
	JobName = j.[name],
	JobOwner = SUSER_SNAME(j.owner_sid),
	IsEnabled = j.[enabled],
	JobSteps = SUM(s.StepCount),
	StepTypes = STRING_AGG(s.subsystem, ', '),
	Proxies = STRING_AGG(s.Proxy, ', '),
	Schedule = CASE sch.freq_type
        WHEN 1 THEN 'One time at ' + sch.StartTimeText
        WHEN 4 THEN 'Every ' + CAST(sch.freq_interval AS VARCHAR(10)) + ' day(s)' + sch.TimeText
        WHEN 8 THEN 'Weekly every ' + CAST(sch.freq_recurrence_factor AS VARCHAR(10)) + ' week(s)' + ' on ' + ISNULL(sch.DaysOfWeek, '(none)') + sch.TimeText
        WHEN 16 THEN 'Monthly on day ' + CAST(sch.freq_interval AS VARCHAR(10)) +
            ' of every ' + CAST(sch.freq_recurrence_factor AS VARCHAR(10)) + ' month(s)' + sch.TimeText
        WHEN 32 THEN 'Monthly on the ' + sch.MonthlyRelativeOrdinal + ' ' + sch.MonthlyRelativeDay +
            ' of every ' + CAST(sch.freq_recurrence_factor AS VARCHAR(10)) + ' month(s)' + sch.TimeText
        WHEN 64 THEN 'When SQL Server Agent starts'
        WHEN 128 THEN 'When CPU is idle'
        ELSE 'Unknown' END,
	ScheduleSharedWith = ss.SharedWith,
	NextRunAt = DATETIMEFROMPARTS(
		js.next_run_date / 10000, (js.next_run_date % 10000) / 100, js.next_run_date % 100,
		js.next_run_time / 10000, (js.next_run_time % 10000) / 100, js.next_run_time % 100, 0),
	lr.LastRanAt,
	lr.LastStatus,
	lr.LastDuration
FROM
	msdb.dbo.sysjobs j
	LEFT JOIN Steps s ON j.job_id = s.job_id
	LEFT JOIN SharedSchedules ss ON j.job_id = ss.job_id
	LEFT JOIN LastResult lr ON j.job_id = lr.job_id
	LEFT JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
	LEFT JOIN Schedules sch ON js.schedule_id = sch.schedule_id
	LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
GROUP BY
	j.job_id,
	c.[name],
	j.[name],
	j.owner_sid,
	j.[enabled],
	sch.freq_type,
	sch.freq_interval,
	sch.freq_recurrence_factor,
	sch.StartTimeText,
	sch.DaysOfWeek,
	sch.MonthlyRelativeDay,
	sch.MonthlyRelativeOrdinal,
	sch.TimeText,
	ss.SharedWith,
	js.next_run_date,
	js.next_run_time,
	lr.LastRanAt,
	lr.LastStatus,
	lr.LastDuration
ORDER BY
	j.[name]