1.Problem

Azure Managed SQL 에서는 설치형 MSSQL DB와 달리 스케줄 잡을 실행할 수 없습니다.

참고: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance?view=azuresql

2.Solution

Azure Automation Account에 PowerShell 로 DB Procedure 실행하는 Job을 등록해서 매일 특정시간에 동작하게 하면 됩니다. 이 외에도 Azure Function을 만들어 주기적으로 실행하는 방법도 있지만 Microsoft 제품인 만큼 생산성을 위해 파워셸을 사용해 간단한 코드로 해결하였습니다.

Write-Output "Run started"

# Instantiate the connection to the SQL Database

$sqlConnection = new-object System.Data.SqlClient.SqlConnection


$sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"


$sqlConnection.Open()


Write-Output "Azure SQL database connection opened"


# Define the SQL command to run

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

$sqlCommand.CommandTimeout = 120

$sqlCommand.Connection = $sqlConnection


Write-Output "Issuing command to run stored procedure"


# Execute the SQL command

$sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'

$result = $sqlCommand.ExecuteNonQuery()


Write-Output "Stored procedure execution completed"


# Close the SQL connection

$sqlConnection.Close()


Write-Output "Run completed"

참고: https://global.hitachi-solutions.com/blog/azure-sql-databases-stored-procedure/