This year, I had the opportunity to participate in annual Microsoft LEAP Nordic Program held in Microsoft HQ in Redmond, Washington, USA. LEAP is 5-day intensive program tailor-made for architects and developers fron Nordic countries. My colleagues tells me it has holistic approach and we could get to meet the best and brightest at Redmond.
In this post, I will try to wrap-up my learning on my basic understanding of what has been presented.
Azure SQL Database Multi-tanency
Neeraj Joshi, Program Manager
Neeraj talks about the various design patterns and solutions for building multi-tenant databases in Azure. While my company Ørsted is not an ISV, a multi-tenant setup or an elastic pool might be possible solution in our various availability & availability challenges.
We can set-up three different solutions
- SQL Server on a VM instance
- Single Azure SQL database
- Elastic Pools (Pool of Azure SQL Databases)
An SQL Server on VM is PaaS and is best fit if you want maximum control over resources and management of SQL Server. We have full control on the instance’ RAM, CPU and perform fit-purpose optimization. IMO, this is expensive but quick-step for moving on-prem DB into cloud.
A Single Database approach is SaaS where we created a database on an environment possibly shared with other customers in Azure. While we don’t have full-control, we get the benefit from all Azure-native matrix and management features. It’s managed by Microsoft engineers and we only have to be concern with choosing the right service tier, performance level and storage. This seems to be likely unpopular to internal DBAs.
An Elastic Database Pool is collection of single databases that can automatically expand on peak times and contracts when demand fall. I think the primary driver is operational budget and unpredictable demands in the databases. Pay only for what we use, scale up when demand peaks.
What’s so cool about Elastic Pool?
Let say we have 4 pizza shops and each have a fleet of 2 motorcycles. During peak times, P1 shop may need more riders than others but he can’t because he only have 2 bikes. While the rest of 6 bikes sit idle in other shops, P1 is sad because he can only commit to deliver 2. The other shops are also sad because they pay hourly salary of their riders while they sit idle.
Elastic Fleet. Let’s make a fleet of 8 bikes and dispatch them based on just-in-time demand. In this case, P1 will get 4 riders to deliver all his orders and pay only for each service. And the other shops don’t have to pay anything.
This is a very efficient way of utilizing resources. Resource is CPU, RAM, and compute hours in getting data from RAM into disk. And in Azure, resource is money.
- RFC for Azure SQL Data Warehouse
- POC on Elastic Database Pool