If you’ve got a database that doesn’t need to be on all the time, Azure SQL DB Serverless is in public preview now. It makes a lot of sense for those development databases you have to keep around, but don’t really want to pay for.
If your database doesn’t get any queries for 6 hours, it’ll pause billing, and automatically resume again when your queries kick in. Here’s how to set it up in the portal:
The gotchas list looks fair, but a few things stand out:
- This tier only has 0.5-4 cores (~2-12GB RAM)
- The minimum auto-pause duration is 6 hours (that’s some pretty doggone slow response time)
- After auto-pause, the first login attempt will fail
- Any queries whatsoever reset the clock (including login attempts, which means this won’t really work for databases exposed to the Internet, which you probably shouldn’t be doing without IP restrictions anyway)
- Not currently available in Australia Central, China East, China North, France South, Germany Central, Germany Northeast, India West, Korea South, South Africa West, UK North, UK South, UK West, and West Central US
This is a total no-brainer for non-public-facing, development-only or archive databases. For hobby apps, the 6-hour time with zero queries doesn’t really make sense: it’s just too long, especially when search engines are going to hit your site at least that often, let alone things like monitoring tools and automated builds.
Update 2019/07/08 – Microsoft just updated the minimum auto-pause duration, dropping it down to 1 hour (from 6). That makes it a much more viable option for cost savings.
20 Comments. Leave new
I really have to wonder who is in a situation where:
1) They have random archive databases laying around.
2) They use those random archive databases rarely enough to justify DB Serverless (as opposed to DB)
3) They use those random archive databases often enough to justify DB serverless (as opposed to sticking a DB backup somewhere).
4) Leaving a cheap DB machine turned would represent a non-trivial segment significant portion of their cloud budget.
5) Sticking the DB on some random developer machine is against policy, and that policy is enforced.
I’m not saying that nobody wants this, nor am I even saying this feature is worthless. However, I find myself surprised that this feature was higher priority than something else.
Brian – sometimes, your competitors dictate the features you build. Amazon brought out Aurora Serverless back in 2017, and I think this is one of those features that’s really important to developers.
It’s certainly important to us, and not just for our dev servers – also for our EU expansion, where I wouldn’t want to dedicate full time servers for a relatively low workload that’s very bursty. Once you build applications with serverless stuff like AWS Lambda or Azure Functions, the most expensive piece of your infrastructure is likely the database server. If your code only runs a few hours a day, why pay for a 24/7 database?
I recently left a contract position with a public entity that needed this exact kind of solution. As a public entity, they are required by law to keep certain older data available in an easily-accessible format. This data will only be accessed once every 2-3 months for historical purposes and yearly by an auditor. Keeping a backup file was not considered a good solution due to several bad experiences where both long-term local storage and cloud storage files were lost. They were thinking of putting the data into an Azure SQL DB instance, but this would serve their purposes even better as they would not be charged when not needed. Public entities are big on cutting as many costs as possible.
Nice. Is there a delay between the billing pause and when first query hits? i.e. like Express Idle Timeout ‘feature’
M$ is such a tease with that dropdown box that looks like you can set auto-pause to something less than 6 hours. I’m currently tinkering with a pet project and paying about $20/mo for dog-slow db.t2.micro on AWS. I’d love to have something with a little more horsepower like Azure SQL DB Serverless where I could spin it up, do some work for a couple hours, and then go back to playing Fortnite and have the DB spin down after say, 2 hours of inactivity. Hopefully the Microsoft people fix the glitch and not just update the png cited in your comment.
Thanks for fighting the good fight, Brent!
@Brandon: Also, the screenshot at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless shows a pause at 1 hour.
Thanks for sharing this. I’m not sure if will be useful for us right now, but it’s good to know it’s available if we need it. If we do use it, I think it would be for cases when a dev wants their own copy of our database to play with so they don’t affect everyone else. Hopefully the 6 hour minimum will change.
Oh man, this is a godsend. Can’t wait for MS to extend this to managed instances.
Gaby – my guess, and this is only a guess, is that they won’t do that quickly. The documentation on this excludes Azure SQL DBs that are involved in geo-replication.
I do bet that they’ll do it, just not quickly. Aurora Serverless does do it, for example – but it’s a matter of replicating the storage rather than replicating through SQL Server methods, which require the SQL Server to be up and running. There’s no reason they couldn’t have the MDF/LDF files on disk replicated via the storage layer like how Azure SQL DB Hyperscale works, but right now Hyperscale and MI are different product lines.
Yeah, unfortunately that’s my worry. However, we have a very slow moving developer team (not their fault, i work for a mutual fund company, so by definition, everything is slow).
As an aside, regarding the storage layer, has it improved, even for Azure? I’ll have to read up on it, but when we tested it in a previous company (think 1000+ DB’s on a SQL enterprise box with traditional failover clustering), bringing up the server for DR testing on the DR site inevitably had 2 to 3 suspect databases. Mind you, this was not in Azure, but with a traditional data center that would replicate from the main site to their DR site. I forget the exact technology but the backup was done at the block level, and me and my fellow DBA put the caveat that as it wasn’t a backup or HA solution from within SQL, we could not guarantee integrity on the DR.
We did have a fix, which involved bringing up the DR disks to a point in time X minutes back, not last timestamp, and offload those specific DB’s to merge with the full set we’d restore from the last timestamp.
Gaby – I haven’t tested it since, so your best bet would be doing your own testing. Microsoft raised the published IO limit from 48 MB/sec to 96 MB/sec last time I looked, but, uh…that’s still slower than a $50 USB thumb drive, so I gotta take a pass there.
I love the idea of this but as usual MS seem to have fallen short of what is practical.
Why can’t we pause manually? Any developer would be happy to stop and start at beginning and end of a dev session (most are building/rebuilding already)
I process data over the weekends and don’t need a SQL Server that is always on, so the new Serverless tier of Azure SQL Database is attractive. However, the auto-resume feature doesn’t seem to resume without having to write custom retry logic. Are there any combination of Connection String parameters which will make the initial connection wait until the Serverless database has resumed? When the database is in the PAUSED state, initial connections always fail in just under 40 seconds – even when ConnectionTimeout=10000 is provided in the Connection String.
Damon – the documentation explicitly states that the first connection attempt always fails. You have to have retry logic in your app. (They say the same thing about cluster failovers too, for example.)
Is there a feature to start up automatically ? For example our developers work between 8:00AM and 6:00PM
I don’t want them to come to me every morning with ” my database is down ” 🙂
So if I create a PowerShell script that is running at 8:00 and try to connect to database will automatically start up ?
Virgil – We ended up writing an Azure Logic App which triggers on a schedule. The Logic App has a SQL Server action which runs a simple query against the database (SELECT CAST(GETDATE() AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Central Standard Time’ AS datetime) as TheDate, ‘Database is running!’ as Message, @@SERVERNAME as ServerName, DB_NAME() as DatabaseName). The SQL Server action is also configured with a retry policy which will retry up to three times with 60 seconds in between. This was enough to resume the database when paused. We even passed the results of the SQL Query action to a Sendgrid Send_email_(V3) action which emails us so we know the Logic App fired, resumed the database, and actually got data back from it.
How do I migrate data into serverless? SMSS isn’t allowing it, nor does the RESTORE operation ‘work in this version of SQL’. Anyone successfully restore a bak file onto a serverless instance?
For answers to general questions, head to a Q&A site like https://Dba.stackexchange.com.
I try to test the autopause feature but it didn’t seem to work in my testing environment.
Looking into the doc https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless#autopausing-and-autoresuming, it stated
Autopausing is triggered if all of the following conditions are true for the duration of the autopause delay:
Number sessions = 0
CPU = 0 for user workload running in the user pool
So, even if there is a sleeping or suspended user session in the database, it won’t auto pause, right??
Please shed some light on this.
thanks & regards,
Irene
Irene – for support questions, you’ll want to check with Microsoft.