Edit

PowerShell for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

  • Article

Many dedicated SQL pool administrative tasks can be managed using either Azure PowerShell cmdlets or REST APIs. Below are some examples of how to use PowerShell commands to automate common tasks in your dedicated SQL pool (formerly SQL DW). For some good REST examples, see the article Manage scalability with REST.

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.

Note

This article applies for standalone dedicated SQL pools (formerly SQL DW) and are not applicable to a dedicated SQL pool created in an Azure Synapse Analytics workspace. 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 created in an Azure Synapse Analytics workspace, see Quickstart: Pause and resume compute in dedicated SQL pool in a 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.

Get started with Azure PowerShell cmdlets

  1. Open Windows PowerShell.

  2. At the PowerShell prompt, run these commands to sign in to the Azure Resource Manager and select your subscription.

    Connect-AzAccount
Get-AzSubscription
Select-AzSubscription -SubscriptionName "MySubscription"

Pause data warehouse example

Pause a database named "Database02" hosted on a server named "Server01." The server is in an Azure resource group named "ResourceGroup1."

Suspend-AzSqlDatabase –ResourceGroupName "ResourceGroup1" –ServerName "Server01" –DatabaseName "Database02"

A variation, this example pipes the retrieved object to Suspend-AzSqlDatabase. As a result, the database is paused. The final command shows the results.

$database = Get-AzSqlDatabase –ResourceGroupName "ResourceGroup1" –ServerName "Server01" –DatabaseName "Database02"
$resultDatabase = $database | Suspend-AzSqlDatabase
$resultDatabase

Start data warehouse example

Resume operation of a database named "Database02" hosted on a server named "Server01." The server is contained in a resource group named "ResourceGroup1."

Resume-AzSqlDatabase –ResourceGroupName "ResourceGroup1" –ServerName "Server01" -DatabaseName "Database02"

A variation, this example retrieves a database named "Database02" from a server named "Server01" that is contained in a resource group named "ResourceGroup1." It pipes the retrieved object to Resume-AzSqlDatabase.

$database = Get-AzSqlDatabase –ResourceGroupName "ResourceGroup1" –ServerName "Server01" –DatabaseName "Database02"
$resultDatabase = $database | Resume-AzSqlDatabase

Note

Note that if your server is foo.database.windows.net, use "foo" as the -ServerName in the PowerShell cmdlets.

Other supported PowerShell cmdlets

These PowerShell cmdlets are supported with Azure Synapse Analytics data warehouse.

Next steps

For more PowerShell examples, see:

For other tasks that can be automated with PowerShell, see Azure SQL Database cmdlets. Not all Azure SQL Database cmdlets are supported for Azure Synapse Analytics data warehouse. For a list of tasks that can be automated with REST, see Operations for Azure SQL Database.