如何实现Azure Synapse Analytics数据库周六、日及周一上午自动暂停恢复？

Got it, let's tackle your requirement to auto-pause and resume your Azure Synapse Analytics database on weekends and Monday mornings. Here are two reliable, straightforward solutions you can use:

方案1：Azure Automation Runbook + Scheduled Tasks #

This is a script-based approach that gives you more control over the workflow, great if you're comfortable with PowerShell.

Step 1: Create an Azure Automation Account #

• Go to the Azure Portal, search for Automation Accounts and create a new one.
• Make sure to enable System-assigned managed identity during creation (this lets the account authenticate with Synapse without hardcoding credentials).

Step 2: Assign Permissions to the Managed Identity #

• Navigate to your Synapse Workspace → Access control (IAM) → Add role assignment.
• Assign the Synapse Contributor role (or a custom role with just Microsoft.Synapse/workspaces/sqlPools/pause/action and Microsoft.Synapse/workspaces/sqlPools/resume/action permissions) to the automation account's managed identity.

Step 3: Create Pause and Resume Runbooks #

1. Pause Runbook

   • In your Automation Account, go to Runbooks → Create a runbook.
   • Choose PowerShell as the type, name it something like Pause-SynapseSqlPool, then paste this script (update the variables to match your resources):

   # Configure your resource details
   $resourceGroupName = "your-resource-group-name"
   $synapseWorkspaceName = "your-synapse-workspace-name"
   $sqlPoolName = "your-sql-pool-name"
   
   try {
       # Authenticate using the managed identity
       Connect-AzAccount -Identity | Out-Null
       # Pause the SQL pool
       Stop-AzSynapseSqlPool -ResourceGroupName $resourceGroupName -WorkspaceName $synapseWorkspaceName -Name $sqlPoolName -Force
       Write-Output "Successfully paused SQL pool: $sqlPoolName"
   }
   catch {
       Write-Error "Failed to pause SQL pool: $_"
   }
   

2. Resume Runbook

   • Create another PowerShell runbook named Resume-SynapseSqlPool with this script:

   # Configure your resource details
   $resourceGroupName = "your-resource-group-name"
   $synapseWorkspaceName = "your-synapse-workspace-name"
   $sqlPoolName = "your-sql-pool-name"
   
   try {
       # Authenticate using the managed identity
       Connect-AzAccount -Identity | Out-Null
       # Resume the SQL pool
       Start-AzSynapseSqlPool -ResourceGroupName $resourceGroupName -WorkspaceName $synapseWorkspaceName -Name $sqlPoolName
       Write-Output "Successfully resumed SQL pool: $sqlPoolName"
   }
   catch {
       Write-Error "Failed to resume SQL pool: $_"
   }
   

Step 4: Schedule the Runbooks #

• For the Pause Runbook:
  • Go to the runbook → Schedules → Add a schedule.
  • Create a recurring schedule that runs at 00:00 every Saturday, Sunday, and Monday (adjust the time to your needs).
• For the Resume Runbook:
  • Create a schedule that runs at 10:00 every Monday (or whatever time you want the database to come back online). You can also add an extra schedule for weekdays (e.g., 08:00 Tuesday-Friday) as a fallback in case the database gets paused accidentally.

方案2：Azure Logic Apps (No-Code/Low-Code) #

If you prefer a visual, no-code approach, Logic Apps is perfect for this.

Step 1: Create a Pause Logic App #

• Go to the Azure Portal, search for Logic Apps → Create → Consumption plan (cost-effective for scheduled tasks).
• Start with a Blank Logic App.
• Add a Recurrence trigger:
  • Set the frequency to Weekly, select Saturday, Sunday, Monday as the days, and set the start time to when you want the pause to happen (e.g., 00:00).
• Add an action: Search for Azure Synapse Analytics → Pause SQL pool.
  • Connect your Synapse account, then select your workspace and SQL pool from the dropdowns.

Step 2: Create a Resume Logic App #

• Repeat the above steps to create another Logic App for resuming:
  • Use a Recurrence trigger set to run every Monday at 10:00 (adjust the time as needed).
  • Add the Resume SQL pool action, selecting your Synapse resources.

Step 3: Verify Permissions #

• Logic Apps will automatically create a managed identity when you connect to Synapse. Make sure this identity has the same permissions mentioned in Step 2 of the Automation Account approach.

Key Tips for Reliability #

• Test First: Manually trigger the runbooks or Logic Apps before relying on the schedules to confirm they work as expected.
• Add Notifications: For both solutions, you can add steps to send email alerts (e.g., using Outlook/Office 365 action in Logic Apps, or Send-MailMessage in PowerShell) if the pause/resume operation fails.
• Adjust Schedules: Tweak the times based on your team's actual working hours—there's no one-size-fits-all here.

内容的提问来源于stack exchange，提问作者pooja dhonde