Edit

Quickstart: Pause and resume compute in dedicated SQL pool in a Synapse Workspace with Azure PowerShell

  • Article

You can use Azure PowerShell to pause and resume dedicated SQL pool in a Synapse Workspace compute resources. If you don't have an Azure subscription, create a free Azure account before you begin.

Note

This article applies to dedicated SQL pools created in Azure Synapse Workspaces and not dedicated SQL pools (formerly SQL DW). There are different PowerShell cmdlets to use for each, for example, use Suspend-AzSqlDatabase for a dedicated SQL pool (formerly SQL DW), but Suspend-AzSynapseSqlPool for a dedicated SQL pool in an Azure Synapse Workspace. For instructions to pause and resume a dedicated SQL pool (formerly SQL DW), see Quickstart: Pause and resume compute in dedicated SQL pool (formerly SQL DW) with Azure PowerShell. For more on the differences between dedicated SQL pool (formerly SQL DW) and dedicated SQL pools in Azure Synapse Workspaces, read What's the difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics Workspace.

Before you begin

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. See Install Azure PowerShell to get started. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

This quickstart assumes you already have a dedicated SQL pool that was created in a Synapse workspace that you can pause and resume. If you need, Create an Azure Synapse workspace and then create a dedicated SQL pool using Synapse Studio.

Sign in to Azure

Sign in to your Azure subscription using the Connect-AzAccount command and follow the on-screen directions.

Connect-AzAccount

To see which subscription you are using, run Get-AzSubscription.

Get-AzSubscription

If you need to use a different subscription than the default, run Set-AzContext.

Set-AzContext -SubscriptionName "MySubscription"

Look up dedicated SQL pool information

Locate the pool name, server name, and resource group for the dedicated SQL pool you plan to pause and resume.

Follow these steps to find location information for your dedicated SQL pool in the Azure Synapse Workspace:

  1. Sign in to the Azure portal.

  2. Select Azure Synapse Analytics in the menu of the Azure portal, or search for Azure Synapse Analytics in the search bar.

  3. Select mySampleDataWarehouse from the Azure Synapse Analytics page. The SQL pool opens.

    Screenshot of the Azure portal indicating that the dedicated SQL pool compute is online.

  4. Remember the resource group name, dedicated SQL pool name, and workspace name.

Pause compute

To save costs, you can pause and resume compute resources on-demand. For example, if you are not using the pool during the night and on weekends, you can pause it during those times, and resume it during the day.

Note

There is no charge for compute resources while the pool is paused. However, you continue to be charged for storage.

To pause a pool, use the Suspend-AzSynapseSqlPool cmdlet. The following example pauses a SQL pool named mySampleDataWarehouse hosted in workspace named synapseworkspacename. The server is in an Azure resource group named myResourceGroup.

Suspend-AzSynapseSqlPool –ResourceGroupName "myResourceGroup" `
-WorkspaceName "synapseworkspacename" –Name "mySampleDataWarehouse"

The following example retrieves the pool into the $pool object. It then pipes the object to Suspend-AzSynapseSqlPool. The results are stored in the object $resultPool. The final command shows the results.

$pool = Get-AzSynapseSqlPool –ResourceGroupName "myResourceGroup" `
-WorkspaceName "synapseworkspacename" –Name "mySampleDataWarehouse"
$resultPool = $pool | Suspend-AzSynapseSqlPool
$resultPool

The Status output of the resulting $resultPool object contains the new status of the pool, Paused.

Resume compute

To start a pool, use the Resume-AzSynapseSqlPool cmdlet. The following example starts a pool named mySampleDataWarehouse hosted on a workspace named sqlpoolservername. The server is in an Azure resource group named myResourceGroup.

Resume-AzSynapseSqlPool –ResourceGroupName "myResourceGroup" `
-WorkspaceName "synapseworkspacename" -Name "mySampleDataWarehouse"

The next example retrieves the pool into the $pool object. It then pipes the object to Resume-AzSynapseSqlPool and stores the results in $resultpool. The final command shows the results.

$pool = Get-AzSynapseSqlPool –ResourceGroupName "myResourceGroup" `
-WorkspaceName "synapseworkspacename" –Name "mySampleDataWarehouse"
$resultPool = $pool | Resume-AzSynapseSqlPool
$resultPool

The Status output of the resulting $resultPool object contains the new status of the pool, Online.

Clean up resources

You are being charged for data warehouse units and data stored your dedicated SQL pool. These compute and storage resources are billed separately.

  • If you want to keep the data in storage, pause compute.
  • If you want to remove future charges, you can delete the dedicated SQL pool.

Follow these steps to clean up resources as you desire.

  1. Sign in to the Azure portal, and select on your SQL pool.

  2. To pause compute, select the Pause button. When the SQL pool is paused, you see a Resume button. To resume compute, select Resume.

  3. To remove the dedicated SQL pool so you are not charged for compute or storage, select Delete.

  4. To remove the resource group, select myResourceGroup, and then select Delete resource group.

Next steps