In enterprises, especially Product based ones, “SaaS” (Software as a Service) is a frequently used term where product owners sell their product to multiple customers to enhance their business. In theory, it sounds very simple, but SaaSification is full of challenges. Besides scalability and performance, cost and resource consumption also are key aspects. As far as Databases are concerned, services like “Elastic Pool” comes in handy to calculate and minimize the challenges that may arise.
This article is published from the DNC Magazine for Developers and Architects. Download this magazine from here [PDF] or Subscribe to this magazine for FREE and download all previous and current editions.
In this article, we will explore Elastic Pool in Azure with a case study, and see how it helps to scale and achieve economical resource consumption especially in SaaS scenarios.
Current Databases scenario and options available in Azure
Before we start discussing about Azure Elastic Pool, let’s discuss the various Database options available in Azure. For enterprise applications there are a lot of open and NoSQL database offerings available in Azure like MongoDB, DocumentDB etc. There are two main flavors of SQL in Azure - Azure SQL (PaaS) and SQL Server (IaaS). Both have good set of features.
IaaS based solutions are generally used in enterprises if there is a requirement of SSIS, SSRS and SSAS, since PaaS based SQL Azure does not provide or support these services. SQL database is used in both scenarios of Predictable and Unpredictable workloads. When we talk about SaaS, we almost always have unpredictable workloads. In this case, it becomes complex to architect the storage/database distribution and pattern, and maintenance as well. Following are the challenges we normally see in SaaS applications, as far as the Database is concerned:
1. Customers who need their unique database ins tance can grow in an unpredictable manner
2. Customers are scattered geographically
3. Customers may have unique demands of performance (CPU, Memory and IO)
Once enterprises start tackling these issues, most of the times they end up in either over provisioning of resources, or under provisioning them. With this, they neither meet performance expectations, nor they save anything on the costs. Hence Elastic Pool becomes a natural choice to overcome these barriers.
What is Elastic Pool?
Elastic Pool is a service offered by Azure which helps you to run multiple independent and isolated databases, and can be auto scaled across a dedicated tier of private resources pool. It helps you to set a policy over a group of elastic databases, and helps to manage performance individually, instead of enterprises managing them exclusively. While this is a great feature and service, not all databases are eligible/candidate for an elastic pool. Databases with variable activities over time, and where all databases are not active at the same time are eligible for Elastic pool. In that case, they can share resources i.e. eDTUs (elastic Database Transaction Units).
There are different tiers like Basic, Standard and Premium, and each of the pools are given some predefined eDTUs. Within the pool, databases can scale on demand based on the scaling parameters, and as per the load. So the entire price or cost model is based on the consumption in eDTU (which is a relative measuring unit for SQL databases to handle resource demands). Hence instead of provisioning the resources for a particular database, you can do it over the pool, and thus management of all those databases also becomes easier.
Elastic Database Pools in Azure - Case Study
A large enterprise say A2ZDNC (fictitious name) has a SaaS solution with multiple SQL Azure database instances on a single SQL Azure Server. A2ZDNC has multiple customers, and each one of these customers have different demands for performance. A2ZDNC also has its own instance of the database. Master Database keeps metadata of all customer databases, as well as its distribution and configuration. A2ZDNC recently found that to meet customer demands of scaling and performance, they always end up over provisioning, which is in turn impacting performance and cost.
Let’s see how Elastic Pool can solve this problem that A2ZDNC is facing.
Here's a diagrammatic representation of the current scenario representing instances based on customer demands of scaling and performance. The Databases in Red shows over provisioning.
With this current architecture, Cost and Performance are not balanced. Now with Elastic Pool, this is how the Architecture will look like:
We will understand this architecture and its advantages in the forthcoming sections.
Azure SQL Server Databases
We have multiple databases within the server blade which are created for individual customers as per need, and which is one of the fundamental requirements of SaaS. Select SQL Server which is created and hosting multiple databases.
You can see that each customer has their own instance of database as per their unique requirement. There is a Master database which is acting here as a transactional database and has all the relevant metadata of all the databases created for individual SaaS customers.
Creating Elastic Pool and adding databases to the Pool
To create a new Elastic Pool, you need to first start SQL server blade where you have all the databases. On the top menu, you will see a “New Pool” option.
You need to click on that and then you will get a new blade where you need to fill mandatory parameters and information.
Give a meaningful name to your pool. Then choose the Pricing Tier. There are three pricing tiers available as Basic, Standard and Premium which enterprises can choose based on their requirements. Currently we are choosing the Standard Pool. If you compare the tiers, the primary classifications are based on Size, Number of Databases and eDTUs per database supported.
Now configure the pool by setting eDTU Max and Min per database instance, and overall Pool eDTU and size in GB as shown here.
Which Databases go in the Elastic Pool?
By nature, elastic pools work well for a lot of database which are not active at the same time. Here Azure uses Machine Learning (ML) and gives you recommendations as well. For example, if there is a CRM service which is talking to one of the database instances, usually not all customers would be running queries at same time on the database. Say on an average, these instances are active up to only 5% time during the day. So such databases are ideal candidates to go in the pool. This way Azure will provide us recommendations based on the historic usage. Alternatively we can always add these databases manually.
Note that in Azure you can have multiple databases in one pool, and you can have multiple such elastic pools as well. Everything depends on the nature of your SaaS application, and Database demand and usage.
Now you can configure eDTU Max and Min along with Pool eDTU. Note that you can add additional databases to the pool, as well as remove databases from the existing pool as well.
So the choice of databases to be added to the elastic pool is driven by various other important factors like shared eDTUs between multiple databases.
Elastic Pool configured, what will be my cost after pool creation?
In the existing Pool blade, on the Summary tab, you can see all the configuration details along with the probable price/consumption for the month (31 days). This now gives you a clear vision on the cost and savings you are going to get, post the pool goes active. Based on the eDTU, you can always change configurations. Note that SQL also helps to evaluate the performance and usage of the databases based on their historic usage.
Monitoring Pool
Once your pool is active, you can access it from the Elastic Pool blade. The Overview tab gives you a wide range of monitoring tools, and you can customize the monitoring charts as well. It also allows you to add Alerts on a pool, and you can get notifications related to it as well.
You can create multiple pools depending upon your SaaS design and architecture.
Elastic Pool Jobs
Managing the Pool is one of the key activities in Elastic Pool, and it is done using Jobs. You can quickly create any job from the Elastic Pool blade by choosing “Create Job” and “Manage Jobs” options. Note that the jobs have to be created using your Administrative credentials.
The Portal will ask you to key in the required Administrator credentials to create and run jobs. Jobs also deliver all the failure reports, and they can be run in an auto-retry mode as well. Jobs bring in a lot of improvements and automation, as far as managing the pool and databases in it is concerned.
The advantage of Jobs is you can easily manage large number of SQL instances easily from script/T-SQL to all the databases in the Pool. You can also apply DACPAC with the help of Jobs. DACPAC enables data-tier developers and database administrators to package SQL Server objects into a portable artifact. This can also be done using PowerShell as well. Jobs can consist of performance queries, schema changes, collect data from multiple databases into a single master database for processing and reporting.
Case Study Conclusion:
With the old architecture, A2ZDNC was spending more due to over provisioning, and the architecture also had a negative impact on performance. With Elastic Pool solution in Azure, A2ZDNC now can easily scale and make optimal usage of database resources, and thus can see a good amount of cost savings in their Azure billing. Moreover, they also get the flexibility to apply changes and do administrative operations to multiple databases at the same time. With built-in automatic retries in case of transient failures of elastic jobs, A2ZDNC now has full control on the databases. This helps to bring down the cost due to overprovisioning, and also boosts performance.
Conclusion:
Elastic Pool or Pool of SaaS elastic databases share a set of resources (eDTUs) for an industry best price/performance ratio. Individual DBs in the pool auto scale within set resource parameters. Single database per tenant model provides full tenant isolation and security. Intelligently auto-managed performance and predictable budget for the pool. Tools for simplified management tasks across 1000s databases & pools. All DBs use the same pool when they are active. With this benefits from Elastic Pool, your SaaS solutions thus becomes cost effective and highly effective.
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Vikram Pendse is currently working as a Cloud Solution Architect from India. He has 13+ years of IT experience spanning a diverse mix of clients and geographies in the Microsoft Technologies Domain. He is an active Microsoft MVP since year 2008. Vikram enables Customers & Communities globally to design, build and deploy Microsoft Azure Cloud and Microsoft AI Solutions. He is a member of many Microsoft Communities and participates as a Speaker in many key Microsoft events like Microsoft Ignite, TechEd, etc. You can follow him on Twitter @VikramPendse and
connect at LinkedIn.