CODEWITHSUNDEEP
azureazure-sql-database
mgalpy
mgalpy

Reputation: 399

resume serverless azure database

I am trying to automate a serverless azure database to be online during set hours and then paused "off hours".

Is there a way to do this using PowerShell commands or another automated way to put it online starting at a specific time during the work week?

Right now using the autopause option, but the main issue is that it requires an initial database hit to put the database online, which causes too long of a down time between the first database hit to the database being online and usable by the application. Thanks

Upvotes: 0

Views: 2774

Answers (3)

Reza NA
Reza NA

Reputation: 75

As pause and resume commands have not implemented yet on Serverless edition of Azure SQL Database, for resume you can just open a connection to it using a simple powershell

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$sqlConnection.Open()

By setting ConnectTimeout and also doing retry you can ensure the Database is online before making your actual connection from the application/service.

Upvotes: 0

mgalpy
mgalpy

Reputation: 399

The PowerShell command Set-AzSqlDatabase which has an option to set the AutoPauseDelayInMinutes for a serverless azure database might to be an option to automate this process.

Set the AutoPauseDelayInMinutes to -1 to opt out during normal business hours and then to the desired value prior to off hours.

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12969

You have got REST API call to pause Azure SQL database.

https://learn.microsoft.com/en-us/rest/api/sql/Databases/Pause

POST https://management.azure.com/subscriptions/00000000-1111-2222-3333- 
444444444444/resourceGroups/Default-SQL- 
SouthEastAsia/providers/Microsoft.Sql/servers/
testsvr/databases/testdwdb/pause?api-version=2019-06-01-preview

Similarly, you can resume the Azure SQL Database, using REST API call

https://learn.microsoft.com/en-us/rest/api/sql/databases/resume

POST https://management.azure.com/subscriptions/{subscriptionId} 
/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/ 
databases/{databaseName}/resume?api-version=2019-06-01-preview

You can set up automated calls as specific times to pause and resume. You can leverage Invoke-RestMethodReference in Powershell to call the rest method.

Upvotes: 3

Related Questions