Database performance and scalability with Azure SQL Database Hyperscale elastic pools | Data Exposed

Key Takeaways

hyperscale elastic pools recently went generally available learn all about it and why you might use it and some other things you might not have known this week on data [Music] exposed hi I'm Anna Hoffman and welcome to this episode of data Expos today I'm joined by Arvin and Arvin you've been on the show before but it's been a really long time so can you remind folks a little bit about what you do great thank you Anna for having me here and hello everyone I'm Arin shamsunder I'm a principal product manager at Microsoft I work on a product called hyperscale which is part of the Azure SQL database family I wear hyperscale on my sleeve literally that's what I do for a living and I'm really excited to be here to talk about uh something that we made generally available today uh very recently which is called hyperscale elastic pools awesome cool over excited to have you on the show you're the exact right person to break this down for us so let's get started so I wanted to start by asking you a little bit about hypers scare hyperscale more generally like why should people think about using hyperscale and is it only for really really big databases so hyperscale is SQL server at the core um but it's also built ground up for the cloud um it's been out there for a while and it's it's been really popular with a lot of workloads like whether it's a new work Cloud that you're bringing uh a fresh to the cloud or you're modernizing existing applications um it it presents a cloud native architecture that you can use to scale your application from whether it's 10 gigabytes of database to 100 terabytes of storage uh it has some of the best performance that we can offer in uh aure for relational workloads and it also allows you to have some very unique capabilities like like read scale out uh to offload read only workloads um so much so that we believe that Azure SQL hypers scale should be the only uh database that you consider for your Cloud native database development awesome cool this really helps kind of demystify the name and what it's for now you mentioned we announced the ga of hyperscale elastic pool so what's a hyperscale elastic pool sure um let's take this in the form of a real life example reduced to somewhat of a fictional walkth through um so I'm going to use a character kind of a journey that she has and this is Tina our fictional character uh she is an architect at startup called wingtip and wingtip is actually developing an application for ticketing um purposes so this is uh things like concerts uh sports events uh think of you know any kind of uh event that you want to issue tickets for so wing tip tickets is what the name of the application is and Tina is using um Azure SQL hyperscale because she knows how scalable it is and um uh what kinds of features it offers to uh for uh easy development uh she has used a very popular pattern called a single database per tenant pattern so essentially what she has is a database for each of the events that she's essentially going to be providing these services for so tenant 01 02 and so on each of these could be a different event that we uh that uh wi tip tickets is providing the ticketing uh uh kind of application services for um by putting each tenants data in its own database she ensures isolation because you don't want to have um you know ticket sales for uh one event being mixed up with another one um but then each of them um also can be operated independently so the application uh doesn't have to worry about things like multi- tenant uh you know code and stuff like that so um simple but um you know easy to scale as he adds more tenants he just adds a new database and she's ready to go so it's very easy to get started with hyperscale elastic pools it's completely supported in the Azure portal as well as all other programmatic mechanisms whether you use Azure Powershell CLI DC qu U so here we're going to walk through how Anna how Tina um creates a new hyperscale elastic pool she click she clicks on the new elastic pool gives it a name changes the addition to hyperscale and then picks a size so she's going to start with a 16 Vore size the good news is she can right size this elastic pool Vore allocation after the fact so she does that clicks on uh create it's typically a 2 minute or less um operation to create a new hyperscale elastic pool now fast forward the pool is created one of the important things that you would want to do and which Tina also does in this case is to set a policy and uh if you think about it whenever you're resource sharing whenever you're pulling things together it's important to set limits it's important to set a quota of sort saying Hey I want to ensure that no single database in the pool um goes and uses all the res ources in the pool so Tina does this by using the elastic database settings on the hyperscale elastic pool when she clicks on that she can then slide this essentially up or down in this case she picks a policy saying no database in the pool is allowed to use more than four V course uh this allows her to efficiently manage runaway database kind of scenarios where there's a particularly busy database uh you don't want to have that bring down the rest of the database in the pool so highly recommended whenever you're working with hyperscale elastic pools set the per database maximum uh to something that you think is reasonably um adequate but at the same time um kind of like a finite limit as well on how much resources are being allocated to uh that particular database creating databases in the pool is also very easy you go to the pool and then you click create database all that you have to do is to give it a name uh and pick things like storage redundancy you don't have to manage the Vore allocations to individual databases because remember it's part of the pool so it inherits essentially the configuration of the pool and those per database maximum settings that we earlier uh talked about I'm going to fast forward again uh Tina and her team are very busy they have actually launched this application uh they have multiple tenants in this particular pool let's imagine that they have eight databases and some ticket sales are going on it's preparing for the holiday season maybe and uh they find that the uh resource usage on the hyperscale elastic pool has gone up uh in this case it's gone up to like 90% of CPU usage the great news with hyperscale elastic pool is that such scenarios are easy to handle like operationally you can scale up the pool in response to high resource usage events like this scale it up and let the event kind of do what it needs to do um give it the resources that it needs to uh get and then uh you can right size the allocation back uh down again after the fact so to scale the pool you go to configure so you kind of click the configure uh option there uh and you go down and in this case t Team kind of scale that pool to 24 V course which they think should be adequate uh to get over this peak uh usage scenario that's being observed for this pool um so as you can see kind of again uh pool scaling operations are more or less constant time uh typically takes on the order of a minute to two minutes uh regardless of the size of the data in the pool this is huge because if you had tried to scale a database previously with something other than hyperscale sometimes you have to copy data around because the operation has to find a new place in the cloud to have that database hosted and then uh you you have to wait for the time that it takes to copy that data around and that's not the case with hyperscale because storage and the compute are decoupled uh the the scaling operation is almost always a a matter of minutes um fast forward again um after they finish scaling the pool uh they find that the CPU usage on the pool is a lot more uh uh you know tolerable it's at 65% uh CPU usage uh what I'm showing to you here is also a a view called the metrics view uh each pool can be monitored in isolation um you can have a lot of details uh like the percentage of data iio the CPU usage how much storage um U how much data IO is happening on the pool etc etc so lots of details um you know while we make it very convenient for you to work with the hyper scale elastic pool we are also giving you all the power behind the scenes to be able to administer it a lot more in detail last but not the least as Tina's application gets more popular uh the size of data stored across all these databases and the pool keeps growing one of the best things about hyperscale is you don't have to allocate storage as your needs grow hypers scale grows as you uh as your applications uh grow as well in this case you can see that hyperscale elastic pool that Tina started with has actually grown to 17.8 ter in size um so this is super cool because you don't have to then manage or micromanage the allocation of storage to each database in the pool you just kind of put the databases in there allow them to grow uh up to 100 terabytes at this point in time is supported in a single elastic pool and as the saying goes that should be plenty for everybody but we'll see how far that gets us awesome cool thanks so much Arvin this was super great to kind of walk through the scenario and see what it looks like in real code and real uh deployment so thanks for doing that uh as we close out uh any call to actions or advice for folks of things they should do as they get started sure I want to talk about one of the really useful features which is unique to hyperscale elastic pools and then I'll kind of leave you with a few trailing thoughts let's take again the case of Tina and her application wi to tickets WIP tickets has been U growing widely popular they are adding new functionality every uh few months uh with writing the AI wave one of the things that uh wi tickets wants to add is uh analytics uh and AI kind of module um Tina is Tina knows what she wants to do because with Azure SQL she gets a lot of uh bleeding edge AI support features um but she's a little worried about U using the databases primary replica uh for those new functionality the cool thing is with hyperscale we offer virtually unlimited read scale out at the pool level you can add up to four high availability secondary replicas so this is the primary pool plus four secondary replicas of the pool itself uh this is a unique capability which we only make available with hyperscale elastic pools so this way all readon workloads can then be redirected to those High availability replica is um leading Tina and her team to offload all of that processing to those replicas uh this is done without duplicating storage so all that she needs to do is literally go to the pool click on configure and um if you caught this at the beginning of there's a little slider there for high availability secondary pool replicas um in this hypothetical example Tina uh decides to add one replica to start with she can go all the way up to four and then when her application needs to read data from those replicas all that it takes is a connection string attribute which can then allow the readon queries to go to that replica again no duplication of storage you still share the same um decoupled storage architecture that hyperscale has and uh all the compute then uh is used from the secondary pool replica so this was just a kind of a quick synopsis of all the nice things that you can do with hyperscale um it's great Cloud native capabilities Leading Edge performance at one of the best price points that you can get in the cloud for relational databases um I would just ask you to try hyperscale out and with hyperscale elastic pools as you grow as you add more databases to your Fleet um you remain cost efficient and you don't have to worry about performance at an individual database level by pulling things together you kind of work with the economy of scale as said many times already um do try out hyperscale elastic pools um Tina is extremely happy and we believe you will be even more happy once you try out hyperscale elastic pools uh product documentation very easy to find aka.ms slhs and uh for more information if you want to have more um um you know dialogue with us we would love to hear from you uh you can reach us through the SQL database feedback page or if you would like to email us we would love to hear from you at SQL HS feedback microsoft.com thank you again and we hope that you can uh try out hyperscale elastic pool sometime soon awesome thanks so much Arvin and we should also probably thank Tina uh for her hard work here and success uh to our viewers if you like this episode go ahead give it a like leave us a comment let us know what you think we'll put links in the description for you to learn more or to reach out directly to the hyperscale team and we hope to see you next time day exposed [Music]