Hotspot Question You have a new Azure SQL managed instance. You plan to create a SQL Server Agent job named SalesTotal. You need to ensure that SalesTotal can send out notifications. How should you co

Question

Exhibit

Answer Area

• @profile_name'AzureManagedInstance_dbmail_profile'
  'AzureManagedInstance_dbmail_profile''AzureManagedInstanceDefault_profile''AzureManagedInstanceSystemEmail_Profile'
• Job notification parameter@notify_level_email=2,
  @notify_level_netsend=2,@notify_level_email=2,@notify_level_pager=2,

Explanation

This question tests knowledge of configuring SQL Server Agent jobs in Azure SQL Managed Instance to send notifications via Database Mail using T-SQL system stored procedures.

Approach. To enable SQL Server Agent job notifications in Azure SQL Managed Instance, you must first configure a Database Mail profile using 'msdb.dbo.sysmail_add_profile_sp', then add an account using 'msdb.dbo.sysmail_add_account_sp', associate the account with the profile using 'msdb.dbo.sysmail_add_profileaccount_sp', and finally create an operator using 'msdb.dbo.sp_add_operator' with the email address. The job itself is created with 'msdb.dbo.sp_add_job', and notifications are configured using 'msdb.dbo.sp_update_job' or by specifying the notify_email_operator_name parameter. The key stored procedures involved are sp_add_job, sp_add_jobstep, sp_add_schedule, sp_attach_schedule, and sp_add_operator - all within the msdb database context.

Concept tested. Configuring SQL Server Agent job notifications in Azure SQL Managed Instance using T-SQL stored procedures in the msdb database, including Database Mail configuration, operator creation, and job notification settings.

Reference. https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

No community discussion yet for this question.