Quickstart: Pause and resume compute in dedicated SQL pool (formerly SQL DW) with Azure PowerShell

You can use Azure PowerShell to pause and resume dedicated SQL pool (formerly SQL DW) 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 (formerly SQL DW) and not dedicated SQL pools created in Azure Synapse Workspaces. 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 in an Azure Synapse Workspace, see Quickstart: Pause and resume compute in dedicated SQL pool in an Azure Synapse Workspace 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. To get started, see Install Azure PowerShell. 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 (formerly SQL DW) that you can pause and resume. If you need to create one, you can use Create and Connect - portal to create a dedicated SQL pool (formerly SQL DW) called mySampleDataWarehouse.

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 (formerly SQL DW) information

Locate the database name, server name, and resource group for the dedicated SQL pool (formerly SQL DW) you plan to pause and resume.

Follow these steps to find location information for your dedicated SQL pool (formerly SQL DW):

1. Sign in to the Azure portal.

2. Select Dedicated SQL pool (formerly SQL DW) in the menu of the Azure portal, or search for Dedicated SQL pool (formerly SQL DW) in the search bar.

3. Select mySampleDataWarehouse. The SQL pool opens.

   

4. Remember the dedicated SQL pool (formerly SQL DW) name, which is the database name. Also write down the server name, and the resource group.

5. Use only the first part of the server name in the PowerShell cmdlets. In the preceding image, the full server name is sqlpoolservername.database.windows.net. We use sqlpoolservername as the server name in the PowerShell cmdlet.

Pause compute

To save costs, you can pause and resume compute resources on-demand. For example, if you are not using the database 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 database is paused. However, you continue to be charged for storage.

To pause a database, use the Suspend-AzSqlDatabase cmdlet. The following example pauses a SQL pool named mySampleDataWarehouse hosted on a server named sqlpoolservername. The server is in an Azure resource group named myResourceGroup.

Suspend-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "sqlpoolservername" –DatabaseName "mySampleDataWarehouse"

The following example retrieves the database into the $database object. It then pipes the object to Suspend-AzSqlDatabase. The results are stored in the object $resultDatabase. The final command shows the results.

$database = Get-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "sqlpoolservername" –DatabaseName "mySampleDataWarehouse"
$resultDatabase = $database | Suspend-AzSqlDatabase
$resultDatabase

Resume compute

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

Resume-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "sqlpoolservername" -DatabaseName "mySampleDataWarehouse"

The next example retrieves the database into the $database object. It then pipes the object to Resume-AzSqlDatabase and stores the results in $resultDatabase. The final command shows the results.

$database = Get-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "sqlpoolservername" –DatabaseName "mySampleDataWarehouse"
$resultDatabase = $database | Resume-AzSqlDatabase
$resultDatabase

Check status of your SQL pool operation

To check the status of your dedicated SQL pool (formerly SQL DW), use the Get-AzSqlDatabaseActivity cmdlet.

Get-AzSqlDatabaseActivity -ResourceGroupName "myResourceGroup" -ServerName "sqlpoolservername" -DatabaseName "mySampleDataWarehouse"

Clean up resources

You are being charged for data warehouse units and data stored your dedicated SQL pool (formerly SQL DW). 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 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 Start button. To resume compute, select Resume.

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

4. To remove the SQL server you created, select sqlpoolservername.database.windows.net, and then select Delete. Be careful with this deletion, since deleting the server also deletes all databases assigned to the server.

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

Next steps

• To learn more about SQL pool, continue to the Load data into dedicated SQL pool (formerly SQL DW) article. For additional information about managing compute capabilities, see the Manage compute overview article.

• 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.