Автоматизация задач управления с помощью заданий Агента SQL в Управляемом экземпляре SQL Azure

Область применения: Управляемый экземпляр SQL Azure

С помощью Агента SQL Server в SQL Server и Управляемом экземпляре SQL можно создавать и планировать задания, которые могут периодически выполняться в одной или нескольких базах данных для выполнения запросов Transact-SQL (T-SQL) и задач обслуживания. В этой статье рассматривается использование Агента SQL для Управляемого экземпляра SQL.

Когда следует использовать задания Агента SQL

Существует несколько сценариев, в которых можно использовать задание Агента SQL:

• Автоматизация задач управления, а также их добавление в расписание для запуска каждый рабочий день, в нерабочие часы и т. д.
  • Развертывание изменений схемы, учетных данных, сбора данных о производительности или телеметрии клиента.
  • Обновление эталонных данных (например, сведений о продукте, которые являются общими для всех баз данных), загрузка данных из хранилища BLOB-объектов Azure. Корпорация Майкрософт рекомендует использовать проверку подлинности подписанного URL-адреса для аутентификации в хранилище BLOB-объектов Azure.
  • Общие задачи обслуживания, в том числе DBCC CHECKDB для обеспечения целостности данных или обслуживание индекса для повышения производительности запросов. Настройка заданий для выполнения в коллекции баз данных на постоянной основе, например в часы наименьшей нагрузки.
  • Собирайте результаты запросов из набора баз данных в центральную таблицу на постоянной основе. Запросы производительности могут выполняться непрерывно и вызывать дополнительные задачи.
• Сбор данных для создания отчетов
  • Сбор данных из коллекции баз данных в одну целевую таблицу.
  • Выполняйте запросы обработки данных с повышенным временем выполнения для большого набора баз данных, например коллекции телеметрии клиентов. Результаты собираются в одну целевую таблицу для дальнейшего анализа.
• Перемещения данных
  • Создание заданий, которые реплицируют изменения, внесенные в базах данных, в другие базы данных, или собирают обновления, выполняемые в удаленных базах данных, и применяют изменения в базе данных.
  • Создание заданий, загружающих данные в базы данных и обратно, с помощью SQL Server Integration Services (SSIS).

Задания агента SQL в Управляемом экземпляре SQL

Задания Агента SQL выполняются службой Агента SQL, которая по-прежнему используется для автоматизации задач в SQL Server и Управляемом экземпляре SQL Azure.

Задания агента SQL — указанный ряд сценариев T-SQL в базе данных. Используйте задания, чтобы определить задачу администрирования, которую можно запустить один или несколько раз и отслеживать ее успешное выполнение или сбой.

Задание может выполняться на одном локальном или на нескольких удаленных серверах. Задания Агента SQL являются внутренним компонентом ядра СУБД, выполняемым в службе Управляемого экземпляра SQL.

В заданиях агента SQL существует несколько ключевых концепций:

• Набор шагов задания, состоящий из одного или нескольких шагов, которые должны выполняться в рамках задания. Для каждого шага задания вы можете определить стратегию повторов и действие, которое должно произойти, если шаг задания завершился успешно или с ошибкой.
• Расписания определяют, когда должно выполняться задание.
• Уведомления позволяют определять правила, которые будут использоваться для уведомления операторов через электронную почту по завершении задания.

Шаги задания

Шаги задания агента SQL — это последовательности действий, которые должны выполняться агентом SQL. После каждого шага предусмотрен следующий шаг, который должен выполняться, если предыдущий шаг выполнен успешно или неуспешно, а также если было предпринято определенное количество попыток повторного выполнения в случае сбоя.

Агент SQL позволяет создавать различные типы шагов задания, например, шаги задания Transact-SQL, выполняющие один пакет Transact-SQL для базы данных, или шаги команды ОС или PowerShell, которые могут выполнять пользовательский сценарий ОС, шаги задания SSIS, позволяющие загружать данные с помощью среды выполнения SSIS, или шаги репликации, которые могут публиковать изменения вашей базы данных в других базах данных.

Репликация транзакций может реплицировать изменения из таблиц в другие базы данных в Управляемом экземпляре SQL Azure, Базе данных SQL Azure или SQL Server. Дополнительные сведения см. в разделе Настройка репликации в Управляемом экземпляре SQL Azure.

Другие типы шагов задания в настоящее время не поддерживаются в Управляемый экземпляр SQL, таких как репликация слиянием и средство чтения очередей.

Расписания заданий

Расписание определяет время выполнения задания. Несколько заданий могут выполняться по тому же расписанию, а несколько расписаний могут применяться для одного задания.

Расписание может определить следующие условия для времени выполнения задания:

• при каждом запуске агента SQL Server; Задание активируется после каждой отработки отказа.
• Один раз в определенную дату и время, что удобно для некоторых заданий, выполнение которых необходимо отложить.
• Для повторяющегося расписания.

Дополнительные сведения о планировании задания Агента SQL см. в статье Schedule a Job (Планирование задания).

Уведомления заданий

Задания агента SQL позволяют получать уведомления об успешном завершении задания или в случае возникновения ошибки. Уведомления можно получать по электронной почте.

Сначала необходимо настроить функцию Database Mail в Управляемом экземпляре SQL, если она еще не включена:

GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE

В качестве примера упражнения настройте учетную запись электронной почты, которая будет использоваться для отправки уведомлений по электронной почте. Назначьте учетную запись профилю электронной почты с именем AzureManagedInstance_dbmail_profile. Чтобы отправить сообщение электронной почты с помощью заданий Агента SQL в Управляемый экземпляр SQL, необходим профиль, который должен быть вызван AzureManagedInstance_dbmail_profile. В противном случае Управляемый экземпляр SQL не сможет отправить сообщения электронной почты через Агент SQL.

Используйте следующий пример скрипта, чтобы создать учетную запись Database Mail и профиль, а затем связать их вместе:

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL Agent Account',
    @description = 'Mail account for Azure SQL Managed Instance SQL Agent system.',
    @email_address = '$(loginEmail)',
    @display_name = 'SQL Agent Account',
    @mailserver_name = '$(mailserver)' ,
    @username = '$(loginEmail)' ,
    @password = '$(password)';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @account_name = 'SQL Agent Account',
    @sequence_number = 1;

Протестируйте конфигурацию Database Mail с помощью T-SQL, используя системную хранимую процедуру sp_send_db_mail:

DECLARE @body VARCHAR(4000) = 'The email is sent from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @recipients = 'ADD YOUR EMAIL HERE',  
    @body = 'Add some text',  
    @subject = 'Azure SQL Instance - test email';  

Вы можете уведомить оператора, что с вашими заданиями агента SQL что-то произошло. Оператор определяет контактные сведения о лице, ответственном за обслуживание одного или нескольких экземпляров в Управляемом экземпляре SQL. Иногда обязанности оператора возлагаются на одно лицо.

В системах с несколькими экземплярами в Управляемом экземпляре SQL или сервере SQL Server обязанности оператора могут разделяться между несколькими лицами. Оператор не содержит сведений о безопасности и не определяет субъект безопасности. В идеале оператор является не сотрудником, обязанности которого могут измениться, а группой рассылки электронной почты.

Создать операторы можно с помощью SQL Server Management Studio (SSMS) или сценария Transact-SQL, показанного в следующем примере:

EXEC msdb.dbo.sp_add_operator
    @name=N'AzureSQLTeam',
    @enabled=1,
    @email_address=N'[email protected]';

Подтвердите успешность или сбой отправки сообщения электронной почты с помощью журнала Database Mail в SSMS.

Вы можете изменить любые задания Агента SQL и назначить операторов, которые будут получать сообщения по электронной почте после завершения, сбоев или успешного выполнения задания с помощью SSMS или следующего сценария Transact-SQL:

EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS',
    @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete
    @notify_email_operator_name=N'AzureSQLTeam';

Журнал заданий

В настоящее время Управляемый экземпляр SQL Azure не позволяет изменять какие-либо свойства Агента SQL, так как они хранятся в базовых значениях реестра. Это означает, что параметры настройки политики хранения Агента для записей журнала заданий фиксированы по умолчанию — 1000 записей всего и максимум 100 записей по каждому заданию.

Дополнительные сведения см. в разделе Просмотр журнала заданий Агента SQL.

Исправлено членство в роли базы данных

Если пользователи, связанные с именами входа без системного администратора, добавляются в любую из трех предопределенных ролей агента SQL в msdb системной базе данных, существует проблема, в которой явные разрешения EXECUTE необходимо предоставить трем системным хранимым процедурам в master базе данных. Если эта проблема возникла, отобразится сообщение The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229) об ошибке.

После добавления пользователей в предопределенную роль базы данных Агента SQL (SQLAgentUserRole, SQLAgentReaderRole или SQLAgentOperatorRole) в msdb для каждого пользовательского имени входа, добавленного в эти роли, выполните приведенный ниже скрипт T-SQL для явного предоставления разрешений EXECUTE хранимым в системе процедурам, перечисленным в списке. В этом примере предполагается, что имя пользователя и имя входа совпадают:

USE [master]
GO
CREATE USER [login_name] FOR LOGIN [login_name];
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [login_name];

Ограничения заданий Агента SQL в управляемом экземпляре SQL

Стоит отметить различия между Агентом SQL, доступным в SQL Server, и тем, который находится в составе Управляемого экземпляра SQL. Дополнительные сведения о различиях в поддерживаемых функциях между SQL Server и Управляемым экземпляром SQL см. в статье Отличия T-SQL Управляемого экземпляра SQL Azure от SQL Server.

Некоторые функции Агента SQL, доступные в SQL Server, не поддерживаются в Управляемом экземпляре SQL:

• Параметры агента SQL Server доступны только для чтения.
  • Системная хранимая процедура sp_set_agent_properties не поддерживается.
• В настоящее время включение или отключение агента SQL не поддерживается. Агент SQL работает всегда.
• Хотя уведомления частично поддерживаются, следующие элементы не поддерживаются:
  • Пейджер не поддерживается.
  • NetSend не поддерживается.
  • Оповещения не поддерживаются.
• Прокси-серверы не поддерживаются.
• Eventlog не поддерживается.
• Триггер задания по расписанию на основе бездействующего ЦП не поддерживается.
• Действия задания репликации слиянием не поддерживаются.
• Читатель очереди пока не поддерживается.
• Службы Analysis Services не поддерживаются.
• Выполнение скрипта, хранящегося в качестве файла на диске, не поддерживается.
• Импорт внешних модулей, таких как dbatools и dbachecks, не поддерживается.
• PowerShell Core не поддерживается.

Подробнее

• Что такое Управляемый экземпляр SQL Azure?
• Что нового в Управляемом экземпляре SQL Azure?
• Различия T-SQL между Управляемым экземпляром Базы данных SQL Azure и SQL Server
• Сравнение функций: База данных SQL Azure и Управляемый экземпляр SQL Azure

Следующие шаги

• Настройка Database Mail
• Устранение проблем с исходящими подключениями SMTP в Azure