DP-300 Microsoft Azure Database – 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 with 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 database devices, elastic pools, 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 will just click Create. So if we have a look at these two, which we’ve not had a look at yet.

So SQL managed instances, you need your subscription and your resource group. You need a managed instance name. So a managed instance is like having a full up version of SQL Server on a 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 instance name, region and an admin login and password. And you’d also be able to put in the appropriate amount of compute and storage.

We’ll be looking at actually creating an SQL Database managed instance much later on in this course. 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. Now you don’t have to pay for developer, but then you also can’t use developer for commercial reasons. You can only use it for instance for testing bed or for 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 own license version as well.

Now this isn’t the only place where you can deploy it from. If I go back to home and I look for the marketplace, then here is another place that I can go and find lots of images. So if I go on the left hand side, 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, 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, scale 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 which may have infrastructure loss because they can recall it when they actually need it. You’ll also need to have virtual networks in place, so IP addresses, so there’s a lot more options that are needed here. You can choose the disks that you’ve got the 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 then got access to. So these are some of the ways of deploying database offerings using the portal. You could also go for things like SQL Database or SQL managed instances or virtual machines and deploy them from there. In the next video we’ll look at ways where 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 hand side, we’ve got lots of different options that we’ve got. And going nearly to the bottom, we’ve got Export template. Now this is what’s called an Arm template as you’re Resource Manager all, and you can see it’s written in chord. 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 maybe a cousin, but you can see it uses these pair values. So we’ve got the name and then we 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 the name.

Now how was this generated? Well, it’s generated when you actually create an Azure database or in fact any resource. So if I go and create a resource, so 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 go to review and Create. You can see that we’ve got at the bottom a section download a template for Automation. So when I do that, you can see again this Arm template. 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 my SQL database in this particular instance 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 I’m basically building up an Arm template, it all gets encoded and then when I click Create, then it will have a look at that template to make sure it actually works and then create it based on that.

So if I’ve got an existing database or anything, I can download it. Now, I can download this one database. Now you can see there are some things which are not exportable yet. And so if I was to do what I’m about to do and actually try and deploy it, it won’t work because not everything is there. But I can download it, I can make any adjustments and then I can 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 will go down to the bottom export template and you can see we 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 an Internet file Explorer and going to my download series. So I will unzip it so I could send to a compressed zip folder. If I was zipping it up the other way, I’ll do something like Wind zip and I will unzip to a particular folder. There you go. And then I can edit these. So I can edit these in notepad. You probably don’t have this option, you probably have chosen of an app.

So what you could do is just open up Notepad and open it in 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, 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 we go, I’ll click Save and that’s created parameters as well. And then if everything was working correctly, then I would say, okay, give me a resource group and review and create. 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 or Bash, also known as the CLI command line interface. So if I was 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 write Hyphen, host new Hyphenaz as your resource group to create a new resource group, new Hyphen AZ SQL Server to create a new server and then firewall rules and lots more things. Now, we do not have to get into all of the Minutiae, thankfully, of how to learn to use as your cloud shell. That would take an entire course by itself. Here’s the command line interface as well. And you can see it uses a different syntax.

AZ space group. Space created everything in lower case so I could put all of this command 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 DAC Pack, DAC Pac, that’s a data tier application portable artifact. So this would be added to something called the Azure Hyphen Pipelines. Dot YML YML stands 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, lots of clicks. Suppose I had to create 100 databases, plenty of room to make errors. So if I were to use some sort of automated way so for instance, if I was deploying a custom template, if I was using PowerShell or CLI command line interface, then this would allow me to automate this and reduce the number of errors that I would be creating. And you can always start from Quickstart templates and then modify it. So here are various automated deployment methods for resources. You can use PowerShell, you can use CLI, you can use Dacpak if you are using an Azure Pipeline, which we’re not going to be using in this, or if you have an existing, 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 were also able to have SQL managed instances and SQL Virtual Machines. So what’s the difference between them? Now, it’s probably a good stage to have a look at these and compare and contrast them while looking at the similarities between Azure SQL Database and Azure SQL managed instances. They are both Pass platform as a service as opposed to SQL Server which is IAS. So the infrastructure of the service, the infrastructure is the Azure Virtual Machine and SQL Server sits on top of that just like a normal program. Whereas for these two, SQL Database and SQL managed instance, azure manages the database in SQL Server.

On a virtual machine, you need to manage your virtual machine, your Windows machine, your Linux machine, and that gives you control of the database. Now, results are always running for 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 or less dropped 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.

Now, Pass is best when you don’t actually want to use Windows, when you don’t want to have the overhead of doing Windows. Managed instance is probably best for most migrations to the cloud because while your SQL requirements may require some changes because of what the managed instances, they require fewer changes than SQL Database. 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 Prem Server to another on Prem Server. It’s just that this is a server which 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 want a quick solution, you need a fast time to market the managed instance.

That’s best for new applications and existing on Prem applications for use in the cloud because you can use more of the features in the traditional SQL Server in the managed instance than you can do in the database. We’re going to have a look later, for instance, about how you can use agents on managed instance. SQL agents can’t be used in database. There are some other things you can use when to 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%, or when you acquire operating service level access. Now, with all of these you can use as your hybrid benefits apart from SQL Database Serverless.

So this reduces the cost if you have currently got an existing SQL Server license 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 Machine. You have a high degree of compatibility with managed instance and you have most compatible, but not highly compatible. You have a lot of things that you can use in SQL Server features, but not some of the more advanced stuff. 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 can know. So trace flags are not supported in SQL Database. If you’ve ever used trace flags, why is that? Well, trace flags are things when you start up in Server, there’s no server to start up or you don’t have any control of it. Managed instance, there are some trace flags supported. All trace flags are supported in Virtual Machines. So you can see the more we go off to the right hand side, the more things are available. However, you still have to manage on the right hand side. So you have to manage your backups, you have to manage your patches. Though there are ways to help do this in Azure, which means you have less managing to do for SQL Database. For SQL managed instance, then there are built in backups, built in patching and built in recovery. Now, with SQL Database you can have up to around four terabytes of database space. More if you use something called the Hyper scale, 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 instant size. So you lift and shift your SQL Server from your on premises onto a virtual machine. With SQL Database you have the choice of Serverless Compute so where you don’t actually have access to a specific server, not that you have much access to it, but also that it can just be automatically decommissioned when it’s not being used. And that of course can save on cost. Now, one big difference between SQL Database and managed instance is that you can’t use Clr, that’s the Dotnet framework, common language runtime, but you can use it in managed instance.

Now, which version of SQL Server are you using? Well, if you’re using a virtual machine, you can use any version from 2008 R, two upwards. So you can use any edition, developer, express, Web, standard enterprise, any operating system you want. So suppose you needed SQL Server 216 service Pack Two on a Windows Server 2016. Well, that is a virtual machine. Anything else? The SQL database. SQL managed instance, you have whatever the latest Stable Enterprise Edition is, or rather a version of that. As I said, not everything is compatible on those, but it’s based on that 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, managed instance, you can use SQL Agent Jobs. You can do that on virtual machines. That’s the standard way of automating various jobs. There is a different way in 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.

• Category: Uncategorized