Pause and resume compute in Azure SQL Data Warehouse with PowerShell

Log in to Azure

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

PowerShell

Connect-AzAccount

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

PowerShell

Get-AzSubscription

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

PowerShell

Set-AzContext -SubscriptionName "MySubscription"

Look up data warehouse information

Locate the database name, server name, and resource group for the data warehouse you plan to pause and resume.

Follow these steps to find location information for your data warehouse.

1. Sign in to the Azure portal.
2. Click SQL databases in the left page of the Azure portal.
3. Select mySampleDataWarehouse from the SQL databases page. The data warehouse opens.
   
4. Write down the data warehouse name, which is the database name. Also write down the server name, and the resource group.
5. If your server is foo.database.windows.net, use only the first part as the server name in the PowerShell cmdlets. In the preceding image, the full server name is newserver-20171113.database.windows.net. Drop the suffix and use newserver-20171113 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. 

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 data warehouse named mySampleDataWarehouse hosted on a server named newserver-20171113. The server is in an Azure resource group named myResourceGroup.

Powershell

Suspend-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "newserver-20171113" –DatabaseName "mySampleDataWarehouse"

A variation, this next 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.

Powershell

$database = Get-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "newserver-20171113" –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 newserver-20171113. The server is in an Azure resource group named myResourceGroup.

Powershell

Resume-AzSqlDatabase –ResourceGroupName "myResourceGroup" `
–ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"

A variation, this 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.

Powershell

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

Check status of your data warehouse operation

To check the status of your data warehouse, use the Get-AzSqlDatabaseActivity cmdlet.

Get-AzSqlDatabaseActivity -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database02"

Clean up resources

You are being charged for data warehouse units and data stored your data warehouse. 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 data warehouse.

Follow these steps to clean up resources as you desire.

1. Sign in to the Azure portal, and click on your data warehouse.
   
2. To pause compute, click the Pause button. When the data warehouse is paused, you see a Start button. To resume compute, click Start.
3. To remove the data warehouse so you are not charged for compute or storage, click Delete.
4. To remove the SQL server you created, click mynewserver-20171113.database.windows.net, and then click Delete. Be careful with this deletion, since deleting the server also deletes all databases assigned to the server.
5. To remove the resource group, click myResourceGroup, and then click Delete resource group.