DP-300 Microsoft Azure Database – Plan and Implement Data Platform Resources Part 2

4. 6, 8. evaluate scalability and security aspects

In this video, we’re going to start by looking at the scalability of the various database offerings. So first of all, as your SQL Database, it really depends on what version you go for. So it starts off at around the 1 TB level. So we’ve got 1 TB here for this gen five underscore two, you’ve got half a terabyte here for this earlier version. And then once you put rest through all of the various models, you get up to around four terabytes. If you want higher than that, then you can do that using the Hyperscale model, which again we’ll have a quick look at later on in later videos. For Azure SQL managed instance, if you’re looking at the general purpose, you can have up to eight terabytes and the business critical, you can have between one and four terabytes. Again, it depends on what you’re provisioning.

Now, you can have up to 100 databases. So the managed instance itself supports all of these different databases. SQL Server on Azure Virtual Machines well, it entirely depends how much can you get on a particular machine. So you can go up to 256 terabytes and have up to 50 instances per server. Now, the size of your virtual machine can be changed whenever you need, as can the Compute Power. Now, with Azure SQL Database, the size of a single database or the elastic pool can be changed as needed, as is the case with the managed instance. The size that you’ve got with the managed instance, you don’t actually have elastic pools because there’s no need.

Everything basically is in a pool. It is a single instance containing lots of databases. You can use resource governing to affect what resources are being used. Now, you can also add more Compute Power. That’s called a vertically scaling. With SQL Database, you can also shard your data into multiple database nodes. This is called horizontal scaling. You can do it if you’ve managed instance. It’s not as easy.

Now, with SQL database, you can change the service tiers from Standard and General Purpose, which use Premium disks to Premium and Business Critical, which uses SSDs Solid State disks. For the managed instance, you’ve only got Premium and Business Critical. Now, there are two additional service tiers for SQL Database basic and Hyperscale.

You can actually change from Basic to any of these. It’s probably not usual to change all the way from Basic all the way to Business Critical, as you can do if you wished. However, you can’t change into or out of Hyperscale. So if you start a type of scale, that’s why you stay. If you want to change it, you have to decommission it.

So these are some of the considerations to be made when looking at the scalability of possible database offerings. I also want to talk about the security aspects of these database offerings. So you’ve got auditing available. It works at the server level for the managed instance and Azure Virtual machines, but the database level for Azure SQL Database. Now, these log files for the auditing are stored in Azure Blob storage, apart from SQL Server for Azure Virtual Machines, where they are generally stored in the file system or in Windows event logs.

Now, for all of these you can get Azure Defender. In fact, I’ve got a free trial of Azure Defender on my existing Azure SQL database, so you can see my free trial expires in 26 days. So it includes vulnerability, assessment and threat detection costs about two cent per instance per hour. So that’s about fifty cents a day per instance. You’ve got data encryption built in a standard using transport layer security, so that is securing data on the move, transparent data encryption TDs, so that’s encrypting data that’s there on the actual database.

And additionally you can have something called Always Encrypted. And of course you’ve also got an element of firewalls as well. You can use SQL authentication such as username and password on any of these, but obviously you can’t use Windows authentication on anything other than a sure virtual machine because that has Windows if you so choose. But you can use Azure Active Directory on any of them, though I think it’s more common on the SQL database and the SQL managed instance. So these are some of the security aspects of these database offerings.

5. 7. HA/DR of the possible database offerings

In this video we’re going to have a look at the HADR high availability and disaster recovery of the possible database offerings. So first of all, Microsoft have an availability guarantee. Now, it doesn’t have to guarantee that your databases will be available that much. However, if they’re not available to that level, then you’ll get a partial credit. So you can see SQL Server and Virtual Machines minimum SLA that’s a service level agreement is 95% and that means it will be up 19 times out of 20, basically. Now, depending on what you do, you can increase the chances and increase it quite a lot all the way up to 99. 99% of the time. With Azure SQL managed instance it is that 99. 99% availability. With Azure SQL Database it’s 99. 99 5% availability which is twice as good. In other words, half the amount of downtime. However, it does depend how you are configuring it.

So it’s somewhere between 99. 99%, which is called four nines and 99. 99 5%, which is called four and a half nines. Now, this is apart from the hyperscale version which is 99. 9 to 99. 95%. So from between three and three and a half nine s. Now, with SQL Server on Azure Virtual Machine, you can configure availability replicas using a domain controller virtual machine. So what all of this means is that if one particular instance goes down, then you can switch over to another replica. So this is part of the high availability and disaster recovery for the SQL Database and managed instance. Then you have locally redundant availability at the basic, standard and general purpose levels and you will see that those three are often grouped together, whereas at the Premium, business critical or Elastic pools then you have automatically included in that a three to four nold cluster with either local or zone redundant availability. And you can also add read only Replicas.

So none of those versions that you can read. Now you might be going, okay, what’s this local and Zone and all the rest? Well, let’s just have a look at a particular region. So obviously these computers have to be stored somewhere. So they are stored in geographic regions like for instance West US or South United Kingdom. Now, each of these are housed in, let’s call it a building, it might be several buildings. And they have their own servers, maybe lots of servers, and they have their own electricity, their own networking, their own cooling.

Now, if it’s locally replicated, then you’ll find that you may have one version here and another version here. So if this one fails, this one can take over. However. A zone version. So this could be an availability zone will have at least three buildings, each of which will have their own generation, each of which will have their own networking and their cooling.

So you may have a version here, a version here and a version here. So let’s say there was an earthquake in this particular area, which knocked the entire building offline. Well, if you had locally replicated then the all three versions might be off. But if you had zone replicated then you would have them in different areas geographically. So the failure of one building would not necessarily affect the other two because they have their own infrastructure. Now you could have availability sets by the way, that’s also within one particular building. The advantage of this, you would have up to five update domains.

So in other words, five sets of computers. And what happens? Let’s say this computer needs to be updated, it will update this computer, it will finish updating that computer, say operating system, but it won’t go onto the next computer or the next set of computers until this first set is finished. So that allows you some certainty that not all of your virtual machines will be shut down at the same time for updates. So in addition to locally redundant and zone redundant you can have also georedundant which means more than one region. So you might have in the west US or the east us. So when we look at deploying a database you can see we’ve got locally redundant, zone redundant, Geore redundant. So you’ve got your choice of your redundancies.

So you can see when we use locally or zone redundant availability. If one of the nodes gets knocked out then the other nodes can say we’re still here. And also at the general purpose level for Azure SQL Database you can have zone redundant configuration as well. And no doubt Azure will be adding bit by bit what you can do with all of the various things. Now with SQL Server and Azure Virtual machines you can configure backups but you have to do that yourself.

Now there is a sort of plugin that we can use to help automate them but essentially you’re in charge with SQL database and managed instance you can have automated backup in fact not you can have you have to have it is included in the price including full differential and transaction log backups for between seven and 35 days. I think you could go all the way down to one, but seven is the default and generally you would expand it rather than collapse it. You can also have full database backups for long term backup retention, also called Ltr. And this is configurable in Azure SQL Database and can be done on demand, essentially copy only backups for longer term backup retention. Now with the appropriate backups you can do point in time restores. What does that mean? Well, let’s say it is now quarter past of two and I want to restore the database as it was at five minutes past two because something has gone badly wrong, somebody’s deleted a table.

You can do that. You can do it at 20, five and 7 seconds. You will need full differential and transaction logs, which of course is automatically included in Azure SQL Database. And Azure SQL managed instance, you have to configure it in the virtual machine version. So if you’ve got those, then you can do point in time Restores. On the virtual machine you can configure Georeplication storage. In other words, I have the my files on a disk and they are on another disk in a different region, a different country, different continent even. It happens asynchronously in other words, it doesn’t immediately happen at the same time. It might be I finish doing something on one computer or write a file and then a few seconds later, a few minutes later it’s on the other file. After all, it takes time to get there. With SQL database you can configure active Georeupication.

So this means that your database just gets duplicated onto up to four readable secondary databases and then finally you can use failover. So what does failover mean? Well, let’s say I’ve got several databases replicated. Something goes wrong with one particular database, the infrastructure fails. Well, it will fail over to one of the backups. So the primary fails, it fails over to a secondary. The secondary goes okay, I am now the primary. You can do something similar with SQL Server and Azure Virtual Machines using something called shared storage. So these are your Hairr high availability and disaster recovery functionality and requirements.

• Category: Uncategorized