{"id":105189,"date":"2025-01-22T21:46:03","date_gmt":"2025-01-22T21:46:03","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105189"},"modified":"2025-01-22T22:12:01","modified_gmt":"2025-01-22T22:12:01","slug":"azure-sql-serverless-discover-whats-new-and-increase-your-savings","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-serverless-discover-whats-new-and-increase-your-savings\/","title":{"rendered":"Azure SQL Serverless: Discover What&#8217;s new and Increase Your Savings"},"content":{"rendered":"<p>Before jumping into the news and code, let&#8217;s start from the beginning: Why does someone use Azure SQL Serverless?<\/p>\n<p>The answer is simple: Save money.<\/p>\n<p>Considering my personal scenario, I can think about at least two different scenarios related to saving money.<\/p>\n<ul>\n<li>In my personal scenario, I have lots of sample databases. Using the lowest DTU level possible costs US$ 5.00, but the sum of a lot can be too much. Changing them to serverless ensures they will only get online when I really need them.<\/li>\n<li>In many enterprise scenarios, the reason is low usage. The database should only get online when in use<\/li>\n<\/ul>\n<h2>The News to Increase Your Savings with SQL Serverless<\/h2>\n<p>It was announced in the most recent PASS Summit: The minimal Auto Pause delay was reduced from 1 hour to 15 minutes.<\/p>\n<p>This configuration defines the minimal time the database will be online when a user makes a query. If a user connects for one simple query and goes away, the database would be online for at least 1 hour.<\/p>\n<p>The new feature allows us to reduce this value to 15 minutes.<\/p>\n<p><strong>When you would like to change the value:<\/strong> When you have lots of situations of users making single queries and going away, with considerable idle time between the queries.<\/p>\n<p><strong>When you wouldn&#8217;t like to change the value: <\/strong>When the idle time is not too small, but the users are active, and you wouldn&#8217;t like to always have the delay of the server getting online again.<\/p>\n<h2>How to Change the Value<\/h2>\n<p>In my situation, when I heard about the news, I immediately decided to change the default value in all my Azure SQL Serverless instances.<\/p>\n<p>It&#8217;s simple, we can use a powershell script to get all the Azure SQL Serverless Databases and change this property.<\/p>\n<p>I like to use the <strong>Cloud Shell<\/strong> in the portal. It doesn&#8217;t require much control over the login process, it uses your portal login.<\/p>\n<h2>Changing all Serverless instances in a Single Script<\/h2>\n<p>The script to change the properties of all serverless SQL Databases at once is the following:<\/p>\n<p><!-- HTML generated using hilite.me --><\/p>\n<div style=\"background: #ffffff;overflow: auto;width: auto;border: solid gray;border-width: .1em .1em .1em .8em;padding: .2em .6em\">\n<pre style=\"margin: 0;line-height: 125%\" class=\"crayon:false\"><span style=\"color: #888888\"># Set the desired minimum pause time in minutes<\/span>\n\n<span style=\"color: #996633\">$desiredPauseTimeInMinutes<\/span> = 15\n\n<span style=\"color: #007020\">Get-AzSqlServer<\/span> `\n   | <span style=\"color: #008800;font-weight: bold\">ForEach<\/span>-Object {\n         <span style=\"color: #888888\"># Retrieve databases for <\/span>\n         <span style=\"color: #888888\"># each server<\/span>\n\n         <span style=\"color: #007020\">Get-AzSqlDatabase<\/span> \n             -ServerName <span style=\"color: #996633\">$_<\/span>.ServerName \n             -ResourceGroupName <span style=\"color: #996633\">$_<\/span>.ResourceGroupName `\n         | <span style=\"color: #007020\">Where-Object<\/span> { <span style=\"color: #996633\">$_<\/span>.Edition <span style=\"color: #333333\">-eq<\/span> \n           <span style=\"background-color: #fff0f0\">\"GeneralPurpose\"<\/span> <span style=\"color: #333333\">-and<\/span> \n            <span style=\"color: #996633\">$_<\/span>.SkuName <span style=\"color: #333333\">-match<\/span> <span style=\"background-color: #fff0f0\">\"_S_\"<\/span> } `\n         | <span style=\"color: #008800;font-weight: bold\">ForEach<\/span>-Object {\n           <span style=\"color: #007020\">Write-Host<\/span> <span style=\"background-color: #fff0f0\">\"Updating minimum<\/span>\n<span style=\"background-color: #fff0f0\">               pause time for database: <\/span>\n               <span style=\"background-color: #eeeeee\">$(<\/span><span style=\"color: #ff0000;background-color: #ffaaaa\">$<\/span><span style=\"background-color: #eeeeee\">_.DatabaseName)<\/span><span style=\"background-color: #fff0f0\"> in server: <\/span>\n               <span style=\"background-color: #eeeeee\">$(<\/span><span style=\"color: #ff0000;background-color: #ffaaaa\">$<\/span><span style=\"background-color: #eeeeee\">_.ServerName)<\/span><span style=\"background-color: #fff0f0\">\"<\/span> -ForegroundColor Green\n\n\n               <span style=\"color: #008800;font-weight: bold\">try<\/span> {\n                      <span style=\"color: #888888\"># Update the minimum <\/span>\n                      <span style=\"color: #888888\"># pause time for the <\/span>\n                      <span style=\"color: #888888\"># database<\/span>\n\n                      <span style=\"color: #007020\">Set-AzSqlDatabase<\/span> -ResourceGroupName <span style=\"color: #996633\">$_<\/span>.ResourceGroupName `\n                      -ServerName <span style=\"color: #996633\">$_<\/span>.ServerName `\n                      -DatabaseName <span style=\"color: #996633\">$_<\/span>.DatabaseName `\n                      -AutoPauseDelayInMinutes <span style=\"color: #996633\">$desiredPauseTimeInMinutes<\/span> `\n                     | <span style=\"color: #007020\">Out-Null<\/span>\n\n                     <span style=\"color: #007020\">Write-Host<\/span> <span style=\"background-color: #fff0f0\">\"Successfully updated: <\/span><span style=\"background-color: #eeeeee\">$(<\/span><span style=\"color: #ff0000;background-color: #ffaaaa\">$<\/span><span style=\"background-color: #eeeeee\">_.DatabaseName)<\/span><span style=\"background-color: #fff0f0\">\"<\/span> \n                     -ForegroundColor Cyan\n\n                  } \n            <span style=\"color: #008800;font-weight: bold\">catch<\/span> {\n\n                     <span style=\"color: #007020\">Write-Host<\/span> <span style=\"background-color: #fff0f0\">\"Error updating <\/span><span style=\"background-color: #eeeeee\">$(<\/span><span style=\"color: #ff0000;background-color: #ffaaaa\">$<\/span><span style=\"background-color: #eeeeee\">_.DatabaseName)<\/span><span style=\"background-color: #fff0f0\">: $_\"<\/span> \n                     -ForegroundColor Red\n\n                  }\n\n            }\n\n    }\n<\/pre>\n<\/div>\n<p>Some notes about this script:<\/p>\n<ul>\n<li>There is the need to loop the servers and the databases because the statement for the databases requires the server name and the resource group as parameters.<\/li>\n<li>&#8220;_S_&#8221; is only part of the SKU name when the serverless is configured.<\/li>\n<li>The statement to change the property is using &#8220;| Out Null&#8221; to avoid a bit output of all the properties<\/li>\n<li>This is prepared to run in cloud shell. In other environments you need to control your login first<\/li>\n<li>This will make the changes of all serverless SQL in a single subscription. For multiple subscriptions, you need to make a loop on the subscriptions as well.<\/li>\n<\/ul>\n<h2>Summary<\/h2>\n<p>This change was long requested by Microsoft. The minimal Auto Pause of 1 hour was too high and cost money for the ones with very low usage.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Before jumping into the news and code, let&#8217;s start from the beginning: Why does someone use Azure SQL Serverless? The answer is simple: Save money. Considering my personal scenario, I can think about at least two different scenarios related to saving money. In my personal scenario, I have lots of sample databases. Using the lowest&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":105190,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137091,2,143524],"tags":[5364,143636,159245,4168,4170,4150,4151],"coauthors":[6810],"class_list":["post-105189","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-blogs","category-sql-server","tag-azure","tag-azure-sql-database","tag-azure-sql-serverless","tag-database","tag-database-administration","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105189","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=105189"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105189\/revisions"}],"predecessor-version":[{"id":105216,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105189\/revisions\/105216"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105190"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=105189"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=105189"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=105189"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=105189"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}