0

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

mgalpy
  • 369
  • 4
  • 13
  • what's the desired behavior if the database is hit during off hours? – Derek Gusoff Oct 14 '20 at 01:57
  • Did you open **Auto-pause delay**: The database automatically pauses if it is inactive for the time period specified here, and automatically resumes when database activity recurs. Alternatively, auto-pausing can be disabled.? – Leon Yue Oct 14 '20 at 02:06
  • @DerekGusoff Off hours I would prefer to have it behave like a normal autopause based on a set time and in and idle state unless hit... off hours that's acceptable... but if it needs to be offline off hours to be automated that's fine also. – mgalpy Oct 14 '20 at 02:52
  • @LeonYue Can that be done in an automated or PowerShell method? I can setup the Auto-pause delay manually but would need an automated way to disable it during normal business hours. – mgalpy Oct 14 '20 at 02:54
  • I did just find the PowerShell command Set-AzSqlDatabase which has an option to set the AutoPauseDelayInMinutes... I'm now wondering if that would be the best way (or at least a way) to enable and disable that feature in an automated way for the server db instance to be instantly available during normal hours and then having autopause enabled during off hours? (sorry I'm not in a location to be able to test this right now) – mgalpy Oct 14 '20 at 02:56

3 Answers3

2

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.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

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.

mgalpy
  • 369
  • 4
  • 13
0

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.

Reza NA
  • 75
  • 8