A Basic Plan for a database with a maximum of 2 GB of data, 5 DTU (Database Transaction Unit), 3 disk mirroring, and 7 days of data recovery is only $5 a month. It eliminates the need to buy your own machine, software licenses and hosting costs in a data center.
Standard Plans with 250 GB of data, 10 DTU and 28 days of data recovery start at only $15 a month. Overall, it's very cost effective for a few databases.
As you need more performance, larger databases, etc., you can fine-tune and purchase higher service levels for each database. For information on monitoring the performance and demand on your SQL Server databases, read our paper:
However, if your needs increase, purchasing individual databases becomes expensive. Especially if the databases are only used sporadically, are small, and/or have usage spikes rather than continuous demand.
Fortunately, Microsoft Azure offers an alternative to share server resources across multiple databases with its SQL Elastic Pools feature.
Once you need 50 DTU, you should consider the SQL Server Database Elastic Pools. Elastic Pools let you purchase a block of DTU and database size to share among an unlimited number of databases. Rather than paying for individual databases, you pay for server resources and it allocates the resources across your databases.
Assuming the load on your individual databases fluctuate throughout the day, this should provide more DTU for each database when it needs it. The server takes advantage of the peaks and valleys of demand to share its resources across your databases.
Azure offers three types of pools:
Here are some examples of the configurations and pricing from data collected on May 9, 2018:
|Max DTU per Database
|Max Total Data
This is just a sample of combinations of total data size, total DTU, and maximum DTU per database that you can specify. You need to determine what works best for your applications, monitor it, and adjust as needed.
For comparison, a single Standard database with 50 DTU is $75 a month, so for $112 ($37/month extra), one can share those 50 DTU with other databases, and eliminate the per database price of small, limited use databases.
Main article: SQL Azure Elastic Pools and its sections:
The Microsoft pages provide information on Elastic Pools and how to set them, but no information on converting existing databases to an Elastic Pool.
It turns out you can convert existing SQL Azure databases to a pool without changing their connection strings. This lets you take advantage of Elastic Pools without changing the applications that use them.
The databases need to be on the same SQL Server. If you have multiple databases on one server, you can convert them to a single elastic pool.
If not, you'll need to create a SQL Server with Elastic Pool and transfer databases into it. That will require changes to the connection strings in the applications that use those databases.
The following instructions are for databases on one server and transferring them to the shared elastic pool without disrupting the applications that use them.
Search for the SQL Elastic Pools Service. From All Services, enter "elastic pool" and click on "SQL elastic pools"
Provide a name and select your subscription, resource group, and existing SQL Server:
Click on the Configure Pool >
From the Resource Configuration & Pricing panel
Click on the "Databases" tab to add databases to your Elastic Pool:
Click on the [+ Add databases] link to retrieve the list of databases on your server. You can pick one or all of them to move to the Elastic Pool.
To start, you should just choose one database to see how this works, then confirm your existing applications are still connected to it and functioning properly. You can come back and add the other databases later.
On the third tab, "Per Database Settings", you can specify the minimum and maximum DTU to allow for each database:
Note that if the maximum DTU is 50, you can still set the maximum to 50 DTU per database without depriving other databases from functioning. That is, if only one database is active, it can use the maximum DTUs of the pool, but when another database becomes active, the server adjusts to provide DTU to that rather than having it not work at all.
If a database consistently uses the maximum when it's active, you may need to raise the DTU pool or reserve a minimum level for the other database. The latter can be done by reserving DTU for each database. In this case, one can set the minimum to 10 DTU. With 4 databases, a reserve of 10 DTU would limit the maximum to 20 DTU (50 DTU minus 3 X 10 DTU).
When done, press [Apply] to save the configuration, and [Create] to create the Elastic Pool. It may take a few minutes to make the change, but once it's done, your databases will be sharing the resource pool.
You can see the resource usage from the Overview.
To adjust the settings, click on Configure Pool.
We hope this is helpful for maximizing your investment in Azure and SQL Server.
If you need help with your application development efforts using Azure, SQL Server, Visual Studio .NET, and Office, please contact our Professional Solutions team for more information on how you can apply our experience to your mission.
Have any questions, feedback or suggestions? Visit our Blog and leave us a comment!