DP-300 Microsoft Azure Database – Create scheduled tasks

1. 3, 82. apply patches and updates for hybrid and IaaS deployment

In this video we’re going to have a look at how to apply patches and updates for hybrid and IAS deployment and also automate performance, tuning and patching. So IAS. IAS means VM. So the hosted infrastructure SQL Database and managed instance that’s pass Pas, they have builtin patching and they always use the latest stable database engine version. However, IAS we have got a Windows or Linux background as the operating system and of course operating systems need to be updated. So you’ve got operating system updates as per the standard Microsoft Windows update and it can do updates automatically. However, it could do it at any time and you might particularly want to avoid a certain time period.

And what about if you want other things to be patched? So for instance, SQL Server patches, do you want to have full control of the database engine? Do you want to know when patches are going to happen? So we can do this by looking at our virtual machine in the portal and going down to SQL Server configuration. Now, if I click on that, you’ll see that I can manage the SQL virtual machine. So click on this and we get a few additional options here, including patching. Now you can see that it’s grayed out and what it says is that this virtual machine resource is not in a valid state for management.

So let’s have a look and see what state it is currently in and you can see that I have shut it down, it needs starting up again. So let’s click on Start and when I can connect, that’s when I would be able to do any things like patching. So let’s go back into manage SQL virtual machine. And now you can see I can patch. Now this is automated patching set a patching window during which all Windows and SQL patches will be applied. Now, automated patching can be set up when you set up your original virtual machine. So if I go back to Azure SQL and I create a virtual machine and I go to the management tab, we can see near the bottom things like guest OS updates, so you can enable things here. So if you haven’t enabled it, then you can do so here.

Now what do you need? You need SQL Server 2008, R Two and above and Windows Server 2008, R Two and above. So it can take a few minutes for this to be configured. So you can disable or enable. You can set a maintenance schedule, so you can have it daily, or you can have it on one specific day when you should start local time and also the duration for your updates. So do you want your computer to be offline for 2 hours for instance? Now. You can also do this in PowerShell by using new Hyphenazvm SQL Server autopatching config enable or set Hyphen Azvm SQL Server extension. So the first one sets the schedule and the second installs the extension with the schedule. Now we can also do this for multiple machines. So if we go back into the virtual machines, here we are. You can over here in the maintenance have a look at virtual machines maintenance.

So here we can see this one particular computer. But if we now go to maintenance configurations, we can create a new configuration. So this install platform updates that do not require a reboot. And you can see you can add resources to this maintenance configuration now or after it deploys. You’re limited to five now, but you can add as many as you like after deployment. And so this virtual machine section is a central hub for being able to do your virtual machines maintenance. You can also do it for things like click on here and look at update management for a particular machine. So to apply patches and update it’s for hybrid and IAS deployment, all you’d need to do is go to your particular virtual machine, go to SQL Server configuration, then click on Manage SQL virtual machine and have a look on the left hand side when it comes up at patching.

2. 31. implement Azure Key Vault and disk encryption for Azure VMs

In this video, we’re going to look at how we can implement Azure Key Vault and disk Encryption for Azure Virtual Machines. Why might we need disk encryption? Well, it makes it harder for anyone to just get your files from just having the storage and then being able to use it elsewhere. So to encrypt your disks for your Azure Virtual Machine, first of all, it’s got to be running. So if it stopped, you need to start it. Then we go to the disks on the left hand side and then at the top so we’ve been on this page before, but at the top we go to additional settings.

You’ll see that we can enable or disable ultradisc compatibility, but we also can encrypt disks. So if I select disks, we can encrypt just the OS or the OS and Data Disks. So really, it’s your data that really needs encryption. No one really cares about stealing your Windows disk. They care about stealing your data. So let’s have a look at what it says. Azure Disk encryption is integrated with Azure key vault to help manage encryption keys. So what we need to do is go into Key Vault. So I don’t have an existing key vault, so I’m going to click on Create New.

So what we need is a key vault name. So this has to be something unique. So key vault DP 300. There we go. And you can see that’s actually in use. So I’ll put letter A afterwards. Probably works. Then we can have a look at the pricing tier. So it is offered in two service tiers, standard and Premium. Now, the only difference between them is when you are using hardware security model backed keys. So I’m going to keep it standard.

Then we need to say whether we can have soft delete, but you can see that it’s deprecated, so you don’t need to worry about that. Number of days to retain deleted vaults? 90 days? Why not? So if you delete it, you can resurrect it and purge protection. So are deleted vaults and vault objects protected once they’ve been deleted? And so can be resurrected again. So that’s fine. So we can access Virtual Machines for deployment, resource Manager for template deployment. And then this is the one we want, azure Disk Encryption for volume Encryption.

So we don’t need Virtual machines for deployment, it’s deployed. Instead, what we need is Disk Encryption so we can get the secrets and unwrap the keys. So we’re going to leave all the rest as is, go into the review and create it. So click create. So it will just take a little while. So it took about a minute to create that key vault. So leave the key blank, leave the key version blank and just click Save. And you can see updating disencryption settings for the virtual machine. So just let it do that. And after about a minute or two, it is now set.

So we have now implemented Azure Key Vault and disk encryption for our Azure Virtual Machine. And the way we did that was we went to our Virtual Machine, we went to Disks, we went to additional settings and then we selected which disks to encrypt. We select our Key Vault and that’s it. So that is how we implement Azure Key Vault and Disencryption for Azure Virtual Machines.

3. 74. configure multi-server automation

In previous videos we’ve created jobs and we can see when we get to targets. It says Target Local Server or Target Multiple servers and we haven’t seen how to configure multiserver automation. So for Mi and virtual machines you need a master server and one or more target servers. So in other words the master server. This is the one that you define the jobs on and then they are executed on the target surface. So let’s see how we can do that. So we need to create a master server and we have a target server.

So at the top we have a master server bottom with a target surface. All I did was connect to them. Now it’s the connection problem which is probably the biggest problem and that requires knowledge of firewalls and networks and ports being go open things which are not really part of the DP 300 course. So you just need to know actually how to create the master and the target servers once you’re able to connect to everything.

So let’s go to SQL Server Agent. Remember it is always running for Mi but it’s not always running for VMs. So first of all I’m going to start it. So do I want to allow this app to make changes on the device? Do you want to start the service agent? So yes so attempting to start the service. So now it started going to now right and click on it and go to Multiserver Administration and make this the master. So we start with a splash screen. Click next you can specify any email addresses or pager addresses for notifications. Again pager addresses are being deprecated so I would just stick to email addresses. If you don’t specify an operator then there will be no notifications sent.

Next we need to add a connection to our target server so I’ll click Add Connection. So here’s our connection and it gets added if you’ve done all the credentials correctly. So next it’s going to check that the versions of the master and target server are compatible and finally it says the target server uses Windows Security to connect to the master server and download jobs for the SQL Server Agent. Create a new login if necessary and assign it rights to the MSX. The MSX is the master server and then you can actually perform the operations. Now to create a target server you do almost exactly the same thing you right and click on SQL Server Agent in the target, go to Multi Server Administration, make this a target. So there’s a splash screen and then you need to pick a server.

So I’ll pick my server there, click next it’s going to check the compatibility and we have exactly the same thing. So create a new login if necessary and assign it writes to the MSX. So once it is completed you can then create jobs and go to the Targets place and select Target Local Server or Target Multiple servers. So for Mi and VM to configure multiserver automation, you need a master server and one or more target servers. A target server cannot be linked to only one master server.

However, a master server can have multiple target servers and all you need to do is right and click on SQL Server Agent multi Server administration and either say make this a master, make this a target and then you need to have all of the credential details to connect and any firewall and other connection problems sorted out. That’s how you configure multi server automation.

4. 83. implement policies by using automated evaluation modes

In this video we’re going to look at how we can implement policies by using automated evaluation mods. What are policies? Policies are things that we want to be true. For example, I have got a database here and this database has got a compatibility mode of the latest SQL Server. At the time of recording, 2019, it’s probably now 2021, 20, maybe even 2023, the latest one, but it happens to be 2019. I have created for this video two additional databases and they have different compatibility mods. So what I need to do is check that I have a compatibility mode for each of my databases, which is what I want, 2019. And I can do this using policies.

So you can see Policies and conditions and facets in policy management, which is part of management. Now, this is just for virtual machines only. If you have a look at our managed instance, you can see that policy management is not there. So in my virtual machine, I go to Policies, right, and click and go to New Policy. So the first thing I need to do is give it a name. So I’m going to say compatibility mod 150. Next I want to check a condition. So I click on a new condition. So you can create your condition separately if you wanted to by right and clicking on conditions. So I’m going to say compatibility mode again, 150. So the facet. So this is basically the category.

So I’m looking at databases, I’m looking at tables, I’m looking at views, that sort of thing. So in my particular case I want to check these databases. So I’m going to scroll up to database. So now I need what I want to be true. So I’m going to have a look for at compatibility level. So you can see lots of other things that you can check. So at compatibility level and is equal to or is greater than and equal to, greater than, less than or equal to less than or not equals two. So if you’re not used to exclamation equals, that can mean not equals two in some languages, so it’s equal to. And then I’ll put a value. So my value is version 150. So that’s what I want to be true. So I could have additional expressions if I wish to do so. I’m not going to do that.

So I just have the one expression. So click OK. Now I have to assign it against targets. So in this case every database. Or I can have new conditions about the databases it’s going to be assigned against. Then in evaluation mode, there will be up to four different evaluation modes on demand on schedule. So you can select or pick an existing schedule or create a new schedule. We’ve had a look at schedules in the past, or we can say what happens if things change on change prevent. So in other words, if I try to change it to in this instance, in other compatibility mode it would stop it or on change log, it would just log it to an event notification. So I’m going to say on demand.

Now, if I was doing anything else, then I’ve got this enabled button or checkbox at the top so I can say I want a particular policy to be enabled and I’m going to pick a particular schedule to be run against, but I’m going to choose on demand. You’ve also got a server restriction. Again, you can choose a new condition to say which servers you would want it to be run against. So let’s click OK, so now we have a new condition and a not running policy because it’s on demand. So let’s run it. So I right and click on it and I evaluate and we can see that there are two databases where it is not true.

So I’ll just expand this a fair bit further. There we go. So database one, it’s not true. Click on the details and you can see the expected value, version 150, the actual value 140. And the same for this one, expected value 150, the actual value 130. So these are wrong. So what I can do is I can check it, I can check multiple databases in this case and I go to apply so this modifies the selected targets that do not comply. So at the moment all we have done is evaluated it. We’ve come to see whether it’s true or false. This actually changes it. So yes, go to yes. And now you can see they all apply. So let’s check that we’re right and click on database one which was 140, it’s now 150.

And database two, again it was 130, it’s now 150. So what I can do at a later stage, say maybe in a month or maybe when I’ve imported new databases, is just to run this and make sure that I’ve got everything on the compatibility level in this case that I want. So I can put in multiple conditions, I can put in multiple policies and I can say I want each of these facets. So these are the things that we had to look at. I want each of these to be exactly where I want. So there is a compatibility level. So if you need to lock up what each of these condition facets are, then you can do so here in the facet and you can see it in plain English. So in this video we’ve had a look at how we can implement policies by using automated evaluation mods on our virtual machine.

So we have got facets. So Facets are things that it is checking against, we have got conditions. Conditions are what we are wanting something to be against. So in this case, maybe create date is greater than or equal to particular value. And then we have got the policies which joins it all together and allows us to enable the policies, evaluate the policies, we can view the history of the policies. So in this way we have a bit of an audit trail as to what we have done as well. So that’s a really good advantage to doing policies rather than doing it manually. Of course, another problem with doing it manually is that mistakes are made. So here we have policies and we can export the policy to our hard drive and then we can import the policy later on. Maybe I’m exporting it to a different virtual machine. Maybe. I’ve got a set of policies I want to export, and I can even script these policies so I can put them into TSQL code.

And then I could have a huge amount of code, run it on a different machine, set up all the policies and make sure that all of the databases, tables, views and other facets that you want to have a look at are exactly as you want them. So this is how you can create policies. And if you want to check what policies have been put against a certain facet, you can have a look at those, go to properties and go to dependent policies and dependent conditions.

And if you want to know what policies are enacted on a particular database, then right and click on that database, go to policies and you can view categories and evaluate if you wish. So in this database, when you’re there is this policy with this evaluation mod and you can have a look at history and you can evaluate. All of these are hyperlinked. So these are other ways of checking the policies against the database. You can go to a database and see what policies are enacted against it.