DP-300 Microsoft Azure Database – Configure Azure SQL Database resource for scale and performance Part 3

5. 9, 53, 56. Other Azure SQL Database configuration settings

In this video, we’re going to have a look at the other tabs in the Create and Azure SQL Database. So, first of all, we have got networking. So we’ve got two different parts of this Firewall rules. First of all, do you want to allow Azure services and resources to access this server? In other words, maybe you’ve got something within Azure which wants to communicate with the database. So if so, you have to say yes. If not, then you can leave it as no. Do you want to add the current client IP address? So this is useful if you personally wish to have a look at the database.

So I’m going to say yes here. So that would be a public endpoint. You can also add a private endpoint. So notwithstanding get these services and resources, you could have a private connection from a private IP address within a virtual network. So if there’s something very specific that you want accessing, you can do that if it’s got an IP network. So you can have communication between these different devices. Now, you note that I can’t actually change this at the moment, but I can modify it from the Firewalls and Virtual Networks blade that I can select after the database creation.

Now going down we’ve got a connection policy. So how are things going to be connected? So, there are two different possibilities. Either it goes directly to the node hosting the database, or it goes via an Azure SQL Database gateway. So you can see that the default uses both policies. It uses the Redirect policy if it’s inside Azure and the proxy if the connection is from outside Azure. So if I was connecting, then it would go through a gateway. And if another Azure device or service is connecting, then it would go through the Redirect policy.

In other words, direct. And that’s just the default. So you can change it down here. You’ve got the encrypted settings or connection. You have got TLS, that’s the transport layer security version, and the one two is the latest version. So that’s for more modern computers, if you go into security, you can have a free trial of Azure defender for SQL for 30 days. It’s already enabled on the selected server. And if you’re wondering why you couldn’t change these, that’s because we are putting this onto an existing server. If we’re creating a new server, then we can set this up for the server.

You can have sample data or you can go from a backup. So your most recent georeplicated backups. And you can also specify the database collection. So, database collection, how do you put letters in alphabetical order, a to Z? Now, sometimes it’s not as easy as you might think. And so if you’re used to Western European or even English, then you might think there’s no issue. But think about some languages that have got accents, think about languages which are not Western European and we have got two sets of choices that we can make in addition to the standard collation that we used. So the standard here is that Latin one, we can have case insensitive.

That’s CI or case sensitive. That’s CS. In other words, does a capital A be collated? Is it in the same position as a lower case? And then we have accent sensitive or accent insensitive AI. So is an E the same sorting position as an E with an accent? So these are some things to consider when you’re configuring azure SQL database.

6. 12. calculate resource requirements, including Elastic pool requirements

Now, this video is probably a bit of a recap of what we’ve previously seen. We need to work out how to calculate the resource requirements. Now, there are two purchasing models as we have seen the Vcore based model and the Dtub based purchasing model. So this is a package of compute, storage and IO resources. With simple preconfigured resources, it’s more complicated. For a VCode based model, we have independent scaling of compute storage and I O resources. It’s flexible, but you can see you’ve got control and it’s fairly transparent in terms of what you’re getting.

And you can use with Azure hybrid benefit for cost savings. The business critical service tier includes three replicas, but it’s about 2. 7 times the price of the general purpose version. Now, with the DTU based purchasing model, it can be difficult to work out how many DTU’s you need. You should factor in the following factors the maximum storage byte. So you can see for the higher service tier, you get 500GB for the standard, 250GB, and for the basic you just get 2GB. So the more storage you need, the higher the actual tier that you should go for.

You should also have a look at the average DTU utilization multiplied by the number of databases. That can give you a good idea. Additionally, have a look at the number of concurrently peaking databases multiplied by the peak DTU utilization per database. So, in other words, let’s say you have got ten databases with an average utilization DTU of ten. Well, you need 100, but maybe that’s just the average. You have four databases which peak at 30 DTUs at the same time. Well, you would need 120 DTU, so it would be whichever is the higher. Now, should you use a pool or not a pool? Well, it’s down to you. You need to work out. Do your databases peak at the same time? If they do, then a pool is probably not for you if they peak at different times.

And have a think whether you can level it out better with using an elastic pool. But do bear in mind the unit price for Edtu pools is a 50% premium if you go for the DTU based purchasing model. If you go for the vehicle based purchasing model, it’s the same. However, you can’t have elastic pools with the hyperscale model. Now, you should note that single databases can be moved in and out of elastic pools. They are isolated from other databases and are portable, and they can be dynamically scaled up and down. That’s not same as auto scaling. You can scale it. Now, for elastic pools, you can assign resources which are shared by all pool databases, and you can dynamically scale or auto scale resources up and down. Now, how much does it cost?

Well, you need to work out what it is you want. You have seen previously that there is an estimate over here, but this gives you a few more options. So you can have a look at Gen Five or different hardware types. You can have a look at the service tier, the region, the currency display pricing by the month or the hour. And you can also show Azure hybrid benefit pricing as well for single databases or elastic pools. So these are some of the things to think about when you’re calculating resource requirements. Which purchase model and tier are you going to be using? Elastic pools. What storage do you need? Can you get a wave of DTUs or should you go for the Vcar purchasing model?

7. 13, 71. evaluate database partitioning techniques, such as database sharding

In this video we’re going to look at how we can improve performance for very big databases. Now, we’ve got, for instance, a table here. So a table with all of these rows and all of these columns. It’s not a big table, 540 rows. I’ve only got a basic database and that’s absolutely fine. But what if it wasn’t 500 rows, but 500 billion rows, some huge figure? How could I best allow the end user to be able to get access to their information?

Well, here we have a bird’s eye view of this table, so we can actually just see it with the columns. So what does this table have? Well, it has storage space represented by here. Now, maximum storage capacity can be exceeded. In a survey, we’ve seen that with the SQL database you can go up to four terabytes, depending on what capacity you have, whether it be core or DTUs. Yes, you can go up to 100 terabytes if you want with the hyperscale.

But there’s also other problems. You have computing result limits, so exceeding this might well result in a timeout. And then you have network bandwidth limits. So exceeding this can result in a failed request. Now, you can add more of this, of course. So this is called vertical scaling, or scaling vertically. So you can add disk capacity, processing power, memory and network connections. And disk can be good in the short term. However, you may reach the same problem later. So perhaps there should be a different solution, a more permanent solution. All we can do is we can take this table and we can divide it up. Now, there are lots of ways we can divide it up.

For example, we could divide it up if this was, as it says, sales order detail by month or by year. So we have 2015 in one particular table, one database separate, and from a completely different database with the 2018 data, from the 2021 data from 2024. Each of these could be in separate tables or in a separate database. Now, you’d need some way so that when requests come in, you would have a way of going, ah, you want this particular data. Well, it’s in here. So this is called scaling horizontally. Now, there are reasons why you might want to consider this.

Yes, it adds to the complexity, but if you’ve got hardware limits, you no longer have to worry about them because you can scale it out. You can increase performance, small amounts of data in a single partition or a single database. And multiple data stores can be accessed at the same time. If you’ve got, for instance, these are set up as separate databases. While it will have its own separate hard drives, there could be security considerations. So you might have sensitive and nonsensitive partitions, as we will see later for administration. You could have different strategies of monitoring, management and backup per partitions.

Now, I should tell you that backups for particular partition can be a lot quicker than backing up for the entire data. So suppose we had from 2015 all the way to 2024. Well, that could take a long time to back up. But if we had instead a backup strategy of just backing up the latest month daily and then maybe the latest year monthly, and then these archived ones, we only need a long term backup. You can have different hardware or services or premium or standard where required, and you could increase availability. So, for instance, suppose for some reason the 2015 data fails. Well, while it’s offline, yes, we wouldn’t be able to access it, but you’d be able to access all the rest of it. Now, if you’re doing this sort of strategy where we’re taking the same data and dividing it up into different partitions, which is also known as database sharding, sharp D sharding, then we could, for instance have a Lookup strategy.

So we could have a table which has a shard key and a map which shows where they are stored. So this offers as much control as you need. So you can say for any particular role you go in this particular database, this particular shard. It does require additional overhead, of course, for that. Or you can use a range strategy, as I was suggesting earlier, so we could have it by month or by year.

So you can use sequential shards keys in ranges, so similar data is kept on the same storage nod, so you can retrieve multiple items in a single operation. So if I wanted to have everything from the fourth to the 7 January 2024, well, that would be in the same shard. Whereas if I was using the Lookup strategy, then we might have a data from the fourth here, the fifth here, the 6th here. We’d have to actually get it all up into the same query. Now, this doesn’t necessarily provide optimal balancing. For example, you might be in a seasonal industry and so your November table might be much bigger than your June table.

And rebalancing these shares is difficult in how do you rebalance June? Do you have to split it up or something? And then there is a hash strategy where there is some random element for distribution, so it reduces hotspots because there is no cluster that is going to be more used. Or this particular shard isn’t going to be more used by any of our data is distributed evenly among the shards, so you have more even data, low distribution. Computing hash, which is what you need for this hash strategy, might increase overall requirements. And rebalancing the shards can be difficult. And of course, if you’re trying to get sequential data like from the fourth to the 7 January, well, again, just like the Lookup strategy, you’d be all over the place.

So the Lookup strategy is using a shard key where you can control it. The hash strategy is one, which is done more random. And you’ve got of course, the range strategy. Now you can use file groups to help you. File groups can’t be used. In the Azure SQL database you’ve only got what’s called a primary file group, but in the virtual machine and in the managed instance, you can add additional files, data files, put them into file groups and then say, okay, I’m going to create this partition function. So if you have a value, let’s say this is your Sales or the Detail ID. If this value is one or less, then it goes into the first file. If it’s 100 or less it goes into second. If it’s 1000 or less, then it goes into the third. Anything else, it goes into the fourth. And these are the individual files.

So you create the partition function to say, okay, these are my limits, and then the partition scheme to say, okay, we’re using this particular partition function and putting them into these files. So partition function is about what goes and partition scheme is where it goes. Now this is just one way of partitioning. So this is a table partition. Another table partition is if we split the table up. So for instance, this is probably not the best example, but maybe we have some of the data in one particular table or partition and some in another partition.

So each partition holds a subset of the columns, so you might be using some much more frequently than the others, and the others can be separated away and only retrieved when needed. Additionally, some columns may be more sensitive and they can be separated away and then you can have different security considerations. Now of course we would need some way of being able to join all these partitions. So you would need, for instance, the same primary key in each. So that’s how you can partition tables, how you can partition databases. Well, you don’t need to have all of your tables in the same database if you don’t want to. And this is called functional partitioning.

So we can have different tables in each partition. So here’s one partition and here’s another relating to function. So store data could be in one partition, employee data or address data maybe could be in another. So some tables could be more sensitive. For instance, address details could be sensitive under things like the GDPR, the General Data Protection Regulation of the European Union. So they could be separated away into another partition with additional security requirements. So these are some of the techniques that you can use horizontal partitioning or Shading vertical partitioning. You can also scale vertically as well to add more compute power and other things, network and functional partitioning, where you can partition the tables according to a function.

And these techniques can be combined. Now, where possible you should keep the data geographically close to the users so that it can be accessed with less latency. Now, for each of these, you should consider, obviously, the ease of which you can do it, but also the backup. So when we’re talking about having, say, the 2015 data, since that’s several years ago, how often does that really need to be backed up? It’s not going to change archiving, including deleting.

So do you need all of the data? Is there now a time when you can just archive the data offline and only bring it back? Or what’s called rehydrate it when needed? Also, think about high availability and disaster recovery requirements for your partition. Again, the 2015 data. Do really need such high availability? Maybe you do, maybe don’t. And what about disaster recovery? You probably need a quicker response time for newer data, more active data than you do perhaps for the 2015.

And of course, if you need a quicker response time, you might well have to pay extra to achieve it. So this is Data and Table database Partitioning Strategies horizontal partitioning, also known as database sharding, vertical partitioning, functional partitioning, and just adding more resources to an existing database. Also called scaling vertically or vertical scaling.

• Category: Uncategorized