Moving Microsoft Azure SQL Server Databases to SQL Elastic Pools without Changing Connection Strings

Written by: Luke Chung, President

Introduction to SQL Server on Microsoft Azure

Microsoft Azure lets you easily create and deploy enterprise quality SQL Server on the cloud at a very cost effective price.

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:

Monitoring and Setting SQL Server Usage and DTU Limits

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.

Microsoft Azure SQL Elastic Pools

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:

Basic for less demanding workloads and lots of small databases (similar to the Basic database that has a 5 DTU limit)
Standard for most production workloads
Premium for IO intensive workloads

Here are some examples of the configurations and pricing from data collected on May 9, 2018:

Plan Type	Total DTU	Max DTU per Database	Max Total Data	$/Month
Basic	50	5	4.88 GB	$75
	100	5	9.77 GB	$150
Standard	50	50	50 GB	$112
	50	50	250 GB	$129
	100	50	100 GB	$225
	200	50	200 GB	$450
	1,200	800	1 TB	$2,700
Premium	125	75	250 GB	$700
	250	75	500 GB	$1,400
	1,000	75	1 TB	$5,580

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.

Microsoft Azure References for Elastic Pools

Main article: SQL Azure Elastic Pools and its sections:

Purchasing Considerations

Step by Step Conversion of SQL Server Databases to Elastic Pools without Changing Connection Strings

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.

Requirement

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.

Create a New SQL Elastic Pool

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:

Configure the Elastic Pool

Click on the Configure Pool >

From the Resource Configuration & Pricing panel

Select the tier across the top: Basic, Standard, Premium, or vCore for even more options
Under Pool Settings, you can set the Maximum DTUs and Data Size
The pricing for your selections appears in the Cost Summary box on the right

Add Your Databases to the Elastic Pool

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.

Set the Per Database Settings

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).

Finish

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.

Conclusion

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!