The Complete Course from ExamCollection industry leading experts to help you prepare and provides the full 360 solution for self prep including DP-300: Administering Microsoft Azure SQL Solutions Certification Video Training Course, Practice Test Questions and Answers, Study Guide & Exam Dumps.

Plan and Implement Data Platform Resources

1. 1. deploy database offerings on selected platforms

Hello. And in this section, we're going to have a look at planning and implementing data platform resources. And we're going to start by using manual methods. So we've already got our first SQL database. So what happened? I went to Azure SQL, and I can go to Create. So here we can create single databases, elasticpools, managed instances, and SQL Virtual Machines. So we could have SQL Server sitting on a Windows server. So if I was to go to any of these, then I would just click Create. So if we have a look at these two, which we've not had a look at yet, So for SQL Managed Instances, you need your subscription and your resource group. You need a managed instance name. A managed instance is like having a full version of SQL Server on the cloud with an SQL Server that you can actually manipulate, though you can't actually manipulate the Windows machine behind it. So you need to put in your managed instancename, a region, and an admin login and password, and you'd also be able to put in the appropriate amount of compute and storage. Much later in this course, we'll look at actually creating an SQL Database-managed instance. Now we can also get SQL Virtual Machines, and you can see lots of different images. So with an SQL Virtual Machine, you will be paying for the operating system as well as for the SQL Server. You no longer have to pay for developer, but you also cannot use it for commercial purposes. You can only use it, for instance, for testing BEDO while learning how to actually use SQL Server. So it's likely you'll be using both Windows Server and a full SQL Server. There is also a "Bring Your License" version as well. Now, this isn't the only place where you can deploy it from.If I return home and look for the marketplace, this is another place where I can find a lot of images. So if I go to the left, you can see all of these different images that I can use. I can just type in SQL Server here and have a big search for what's available. So here you can see SQL Server on Windows or SQL Server on Linux, for instance, and there is this Create button at the bottom. So if I were to go back to Azure SQL and create a new virtual machine, then you'll see there are a lot more things that are needed. A virtual machine name; a region; availability options; availability sets; availability zones; skill sets; that sort of thing. We'll be looking at some of them later on. We'll be looking at what a "spot license" is, a discounted rate that may have infrastructure loss because they can recall it when they actually need it. You'll also need it to have virtual networks in place, so IP addresses So there are a lot more options that are needed here. You can choose the discs that you've got for networking. So don't forget, this is actually a full-blown computer. It's just one that's not on your machine. It's a virtual environment on a really big server that you've got access to. So these are some ways of deploying database offerings using the portal. You could also use SQLDatabase or SQL-managed instances or virtual machines to deploy them from. In the next video, we'll look at ways in which you can deploy them, but not through the portal itself.

2. 2, 80. configure customized deployment templates

In this video, we're going to look at some alternative ways of deploying resources. So let's go back into our SQL database. So here it is. Now, on the left side, we have a plethora of different options. And going nearly to the bottom, we've got a default template. Now, this is what's called an ARM template. Azure Resource Manager arm It's also written in code, as you can see. This code is called JSON-JSON, and it's fairly readable if you're used to something like XML. Well, it's a relative, let's say a cousin, but you can see it uses these pair values. So we've got the name and then we've got the value over here; it uses parameters as well. And if I go to the top, you can see that we've got a parameter called the server, and the initial value of the parameter is null. And here, we can see I've given it a name. Now, how was this generated? What is generated when you actually create an Azure database? In fact, any resource. So if I go and create a resource, here we have my SQL database section, and I will click Create, and here's my database. So I'm just putting in default values and going to review and create. You can see that we've got at the bottom a section where you can download a template for automation. So when I do that, you'll see this Arm template again. and I've got the same things that I can do. I can download; I can also deploy. What does deploy mean? Well, if I make any changes in here and click Deploy, then hopefully, by the end of this particular instance, my SQL database will reflect the changes that I've made here. So all I'm doing when I'm creating all of these SQL databases here is basically building up an Arm template. It all gets encoded. And then when I click Create, then it will take a look at the template to make sure it actually works and then create a document based on that. So if I have an existing GetDatabase or anything, I can download it. Now, I can download this one database. Now you can see there are some items that are not exportable yet. And so if I was to do what I'm about to do and actually try and deploy it, it wouldn't work because not everything is there. But I can download it, make an adjustment, and then deploy it separately to this. But I'm not going to do that. Instead, what I'm going to do is go to the resource groups here, and I will go to this resource group, SQL Database. And again, I'll go down to the bottom export template, and you can see we've got 41 resources. I think we had 16 in the previous one. So I can download this. So what is included in this SQL database resource? Well, we've got the server as well. So I can download all of this or click Download. You can see it here. So I will open up Internet File Explorer and go into my download. Here it is. So I'll unzip it so I can send it in a compressed zip folder. If I were zipping it the other way, I'd use something like Wind zip and unzip to a specific folder. There we go. And then I can edit these. So I can edit these on notepads. You probably don't have this option—you probably have to choose another app. So what you could do is just open up Notepad and open it here. And then I can change anything I want—any of the parameters. for instance, and I've got this other file here. So I have downloaded it, and I've made the changes. And what I can do now is deploy it. So deploy a custom template. So I will build my own template in the editor. I will upload files, so I'll go to where those files are located. So there you have it, I'll click Save, and the parameters will be created as well. "Okay, give me a resource group to review and create," I'd say if everything was working properly. But this wouldn't work because, as you saw, not everything is downloadable at the moment for the SQL database. It would work with other resources. Now. I could also use Azure PowerShell. So, Azure PowerShell is this thing here. So if I click on it, you'll see we get a new window, and I can choose with this cloud shell to use PowerShell Bash, also known as the CLI command-line interface. So if I were to click on one, and I'm going to create a little bit of storage that's needed, which will incur a small monthly cost, then I could type commands in this new window. Now to show you the sort of commands that I'll be using. It would be like writing new Hyphenaz" as your resource group to create a new resource group, "new Hyphena SQL Server" to create a new server, and then firewall rules and lots more things. Now, we do not have to get intoall of the my nutrition, thankfully, of howto learn to use as your cloud shell. That would take an entire course by itself. Here's the commandline interface as well. And you can see it uses a different syntax, the AZ space group, space-creating everything in lowercase. So I could put all of these commands down here and have them run. So this is another way of doing it. Now, if I was using something called an Azure Pipeline, then I could also use something called a Dacpak DACPAC. That's a Data Tier Application portable artifact. So this would be added to something called the Azure Hyphen Pipelines. Dot YML YML is an abbreviation for yet another Markup language. Believe it or not, I can also deploy databases, perhaps using SQL scripts together with PowerShell. So there are lots of different ways. Now, why would I be using any of these ways? Well, if I use Azure Portal, then I've got to go click, click, click—lots of clicks. Suppose I had to create a hundred databases—plenty of room to make errors. So, if I choose an automated method, such as deploying a custom template or using PowerShell or the CLI command line interface, I will be able to automate this and reduce the number of errors that I will make. You can always start with QuickStart templates and then modify them. So here are various automated deployment methods for resources. You can use PowerShell, you can use the CLI, you can use DACPAC if you are using Azure Pipeline, which we're not going to be using in this, or if you have an existing template. So let's go to our resource group. We can always export the template, make whatever adjustments we want to do, and then deploy it later to create a new database.

3. 4, 5. evaluate requirements and functional benefits/impact for the deployment

So we saw in a previous video that we were able to have an Azure SQL Database, but we're also able to have SQL managed instances and SQL Virtual Machines. So what's the difference between them? Now is probably a good time to have a look at these and compare and contrast them while looking at the similarities between Azure SQL Database and Azure SQL Manager instances; they are both PAS platform as a service), as opposed to SQL Server, which is AaHA. So the infrastructure is a service, the infrastructure is the Azure Virtual Machine, and SQL Server sits on top of that just like a normal program. Azure manages the database in SQL Server for these two, SQL Database and SQL managed instance. On a virtual machine, you need to manage your virtual machine, your Windows machine, and your Linux machine, and that gives you control of the database. Now, resources are always running for the SQL database unless they are dropped. Now, this is not quite true for serverless databases, so ones where you say, "Actually, I want to pause my database when it's not in use now for managed instances," they're always running on the dropoff for the virtual machine. You can shut down your virtual machine, and that means you won't be using any compute power, but you'll still be using other stuff like storage. And there will be a cost to your virtual machine even when it is shut down. Pass is best when you don't want to use Windows and don't want to deal with the overhead of doing so. Managed instances are probably best for most migrations to the cloud because, while your SQL requirements may require some changes because of the managed instances, they require fewer changes than SQL databases. However, if you just want to lift and shift your SQL Server database, then it's much easier to do it on a virtual machine because it's just like moving from an on-premises server to another on-premises server. It's just that this is a server that is in the cloud. Unfortunately, you do pay for this higher cost. You have to pay for the Windows license, you have to pay for the SQL Server license, and you also have to manage it as well. So when would you use these? Well, SQL Database is best for modern cloud applications, and when you need a quick solution, you need a quick way to market the managed instance that's best for new applications and existing on-premises applications for use in the cloud because you can use more features in the traditional SQL Server, the managed instance, that you can do in the database. We're going to have a look later, for instance, at how you can use agents on managed instances. SQL agents can't be used in databases. There are some other things you can do when you use an SQL Server virtual machine. Well, it's best when you don't want any database changes at all. when the list of compatible items has to be 100% complete or when you acquire operating service level access. Apart from SQL Database Serverless, you can now use all of these as hybrid benefits. So this reduces the cost if you currently have an existing SQL Server licence with software assurance and for VMs only. If you have a Windows Server license, you can also have reserved capacity. So you can say, for instance, I want to have an SQL database for a year, for three years, and that can reduce the cost as well. So in terms of compatibility, I've already had a brief look at this. You have all on-premises compatibilities for virtual machines; you have a high degree of compatibility with managed instances; you have morphed compatibles but not highly compatibles. You can use a lot of SQL Server features, but not some of the more advanced ones. So here you can see some of the syntax that isn't available in SQL Server. Whether you need it or not in your particular application, only you know. So trace flags are not supported in SQL databases. If you've ever used a trace flag, why is that? Well, trace flags are things when you startup in Server, there's no server to startup, you don't have any control of itmanaging instance, there are some trace flags supported. All trace flags are supported in virtual machines. As you can see, the more we talk to the right side, the more options we have. However, you still have to manage on the right side. So you have to manage your backups. You have to manage your patches. There are ways to assist with this in Azure, which means you have less to manage for SQL Database. For a SQL managed instance, then there are built-in backups, built-in patching, and built-in recovery. You can now have up to four terabytes of database space with SQL Database. More importantly, if you use something called the Hyperscale, you can go up to around 100 terabytes, or maybe even above that. With managed instances, you can get up to eight terabytes. And on a virtual machine, well, it just depends on SQL Server at the moment. SQL Server allows up to 256 terabytes, so the databases can be up to the maximum size. So you move your SQL Server from an on-premises environment to a virtual machine. With SQL databases, you have the choice of serverless compute. So why do you not actually have access to a specific server? Not that you have much access to it, but also because it can just be automatically decommissioned when it's not being used. And that, of course, can save on cost. One significant difference between SQL Database and managed instance is that you cannot use CLR (Net Framework common language run time) in a SQL Database, but you can in a managed instance. Now, which version of SQL Server are you using? Well, if using a virtual machine You can use any version starting with 2008 R2, which means you can use any additional developer, Express, Webs, Standard Enterprise, or any operating system. So suppose you needed SQL Server 2016 service pack 2 on a Windows 2016 server. Well, that is a virtual machine. Anything else? The SQL Database Ssql managed instance, youhave whatever the latest Stable Enterprise Editionis, or rather a version of that. As I said, not everything is compatible with those, but it's based on the latest Stable Enterprise Edition. So you don't have a choice as to which particular version you've got. You've got the latest. And as I said before, for managed instances, you can use SQL Agent Jobs. You can do that on the virtual machines. This is the standard way of automating various jobs. There is a different way. an SQL database. the Elastic Job Agent Service. So these are some of the requirements for your deployment, and these are some of the functional benefits and impact of the possible database offerings.

4. 6, 8. evaluate scalability and security aspects

In this video, we're going to start by looking at the scalability of these various database offerings. So first of all, as for 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 generation five underscore two; we've got half a terabyte here for this earlier version. And then once you progress through all of the various models, you get up to around four terabytes. If you want to go higher, you can use the Hyperscale model, which we'll go over briefly in later videos. For an Azure SQL managed instance, if you're looking at the general purpose, you can have up to eight terabytes, and for 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 these different databases. SQL Server on Azure Virtual Machines Well, it entirely depends how much you can 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. With the size that you've got within a managed instance, you don't actually have elastic pools because there's no need. Everything, basically, is in the pool. It is a single instance containing lots of databases. You can use resource governance to affect what resources are being used. Now you can also add more compute power. That's called vertically scaling. You can also charge your data into multiple database nodes using SQL Database. This is called horizontal scaling. You can do it if you have managed instances. It's not as easy. Now, with SQL databases, you can change the service tiers from Standard and General Purpose, which use premium disks, to Premium and Business Critical, which use SSDs (solid state disks). For the managed instance, you've only got Premium and Business Critical. SQL Database Basic and Hyper Scale now have two additional service tiers. 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. You can do if you wished. However, you can't change into or out of Hyperscale. So if you start at Hyperscale, that's where 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 Managed Instant and Azure Virtual Machines. But the database level for an 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. Azure Defender is now available for all of these. 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. And it costs about two cents per instance, per hour. So that's about fifty cents per day, per instance. You've got data encryption built into 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. Of course, there's also the issue of firewalls. 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 an Azure 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 has an availability guarantee. Now, it doesn't have any guarantee that your databases will be that accessible. However, if they're not available at that level, then you'll get a partial credit. So you can see SQL Server and virtual machines. The minimum SLA that's a service level agreementis 95% and that means it will beup 19 times out of 20, basically. Now, depending on what you do, you can increase your chances and increase them quite a lot, all the way up to 99.99% of the time. With an Azure SQL managed instance, it has 99.99% availability. With Azure SQL Database, it's 99.95% availability, which is twice as good. In other words, half the amount of downtime. However, it does depend on how you are configuring it. So somewhere between 99.99%, which is called "four nines," and 95%, 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 three to three and a half and nine s. Now, with SQL Server on an Azure virtual machine, you can configure the availability of 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. As a result, this is a component of SQL Database high availability and disaster recovery. Then you have locally redundant availability at the basic, standard, and general purpose levels. And you'll notice that those three are frequently grouped together, whereas in the premium business-critical or elastic pools, a three- to four-node cluster with either local or zone redundant availability is automatically included. And you can also add read-only replicas, which are 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 likefor instance West US or South United Kingdom. Now, each of these is 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, and 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 willhave at least three buildings, each of whichwill have their own generation, each of whichwill 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 them, then all three versions might be off. But if you had zones replicated, then you would have them in different geographical 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 is that you would have up to five updated 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's 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 shutdown at the same time for updates. So in addition to locally redundant and zone redundant, you can also have georedundant data, which means data from more than one region. So you might have it in the west or the east us.So, when it comes to database deployment, we have locally redundant, zone redundant, and geo redundant options. So you've got your pick of your redundancies. So you can see when we use local or zone-wide redundant availability. If one of the nodes gets knocked out, then the other nodes can say we're still here. Zone redundant configuration is also possible at the general-purpose level for Azure SQL Database. And no doubt Azure will be adding bit by bitwhat 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. We can use a plugin to help automate them, but essentially, if you're in charge of a SQL Database and managed instance, you can have automated backups; in fact, you have to have them because they're included in the price and include full differential and transaction log backups for seven to 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 longterm backup retention, also called LTR. And this is configurable in the Azure SQL Database and can be done on demand, essentially copying the 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 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, 5, and 7 seconds. You will need full differential and transaction logs, which are of course automatically included in the Azure SQL Database and Azure SQL managed instance. You have to configure it in the virtual machine version. So, if you have those, you can do point in Time Restores. On the virtual machine, you can configure geo-replication storage. In other words, I have my files on one disk, and they are on another disc in a different region, country, or continent; even this occurs asynchronously, that is, it does not occur at the same time. It might be that I finish doing something on one computer, write a file, and then a few seconds later, a few minutes later, it's on another file, and it takes time to get there. Active geo-replication can be configured with SQL Database. 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, and it will failover to one of the backups. So the primary fails and fails over to a secondary. The secondary calls, "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 the features and requirements for high availability and disaster recovery.