DP-300 Microsoft Azure Database – Automate tasks and Perform backup and restore a database by using database tools

1. 46. What is Elastic Job Agent?

Hello. And in this section we’re going to be talking about automating tasks, including backup and restore. So first of all, automating database maintenance tasks. So you could have a lot of databases all over the place. So we’ve got two servers here, some of which are Azure SQL Database single databases and some which are in a pool. Now it might might be that we need to do exactly the same thing to all of them. So it could be backups, though that is actually automated separately in SQL Database.

It could be you manage credentials, collect performance data, telemetry data, update reference data. So you might be updating a table within various databases. You could be loading or summarizing data from databases or Azure Blob storage. So in other words, you would have the same thing that you might want to be doing lots of different times. So the targets could be in different servers, they could be in different subscriptions, they could be in different regions.

However, for our purposes, they must all be in the same Azure Cloud. So what that means is you couldn’t have one in the standard Cloud and one in the government cloud, the US. Government or one perhaps in the China Cloud, which is separate from the rest of the world. Now this is the equivalent of SQL Agent jobs, which are available in SQL Server managed instance, but which are not available in Azure SQL Database. So we could be looking at a single database, multiple databases, all the databases in a server, or an Elastic Pool or a Shard Map.

So what do we need? Well, we need an elastic job, agent. So this is the Azure Resource, which runs the jobs, and this is free. We also need an elastic job database. So this could be an existing Azure SQL Database, or it could be a completely blank one. It stores job related data such as metadata logs, results and job descriptions. It also contains stalled procedures and other objects for jobs. Now, it’s charged for as per an Azure SQL Database, and you can’t use the Basic Service tier. You have to use at least a standard S Zero or above or a Premium Service Tier.

S One or above is recommended. So we don’t recommend using S Zero. However, if you run frequent jobs or against a big target group, you may well need a higher grade. So if this is the Elastic Job Agent and the Elastic Job Database, then the terminology for this lot over here is the target group. So these are the servers, elastic Pool databases and databases of Shard Maps, which are affected. So if a server or Elastic Group we’re talking about, then all databases in the server at the time of running a job will be affected. So I could just say, give me server one.

So if I just show this, then I could say, give me all of the databases in Server One. So it’s not all of the databases which were in existence at the time of setting up the job. But it’s at the time of running the job you would also need something called a master database credential, so that all the databases could be enumerated. In other words, countered. Now, you can also choose to exclude individual databases or all databases in an elastic pool. So you could say I just want this database in this database. Well, you don’t actually say that, you say all databases, excluding that. Or you could say individual databases. So I want to just score for this one database. Or I could say I want all databases in a particular pool. Now, a job, which is what we’re executing, that is a unit of work which contains job stats, each of which specifies the TSQL script and other details. Now, scripts must be called item portant. That’s the name for it, item portant. But that just means they’re capable of running twice with the same result. We will then have a job output.

This could be saved in a table and a job history. So this will be stored for 45 days in jobs, job underscore executions. So this is how we can, in theory, create a job agent. We need a job agent, a job database, and then we can execute the jobs against target group. The individual steps we need to do is we need to go to the Elastic Job database and add some credentials. We then need to define our target group. We then need to add credentials to the individual databases and the server. So we need to add these credentials, these login and users, into the master database and into the individual databases. Then we need to create the job.

So we create a job and then we add as many job steps as we want. And then we run or schedule the job. And we’re going to see a practical example of how to do that in the next video. However, for the exam at DP 300, you might not be required to know the details of how we do each and every one of these items, but you will need to know what we do. So we have credentials for the Elastic Job database. We define the target group, we add credentials, we define the job, and then we run the job. And we’ll do that in the next video.

2. 46. automate database maintenance tasks

Right, so let’s create an Elastic job. First of all, we need a job database. So I’m going to create a new Azure SQL database and it’s exactly the same as what we have previously done. So the most important thing about this is the compute and storage. So it’s got to be at least standard, standard S zero or above. But it’s recommended to be S one or above. So S zero would cost around eighteen US dollars. S Two would cost around $35 per month. So I’m going to pause the video, I’m going to create it, I’m going to create a different server because it might as well be on a standalone server. And then I’m going to restart the video. But I think we know by now how to configure all the rest of this. So I’m going to pause the video and do that right now. It has now been created.

So here is my resulted database code at DB 300 agent. Next. I need to create an elastic job. Agent. So I can do this by just going to elastic Job Agents. Most of these steps, by the way, can also be done in PowerShell as well. So I’m going to create an elastic job. Agent So I need to specify a name. So elastic job agent and a select a job database. So you’ve created the database, but it’s just an ordinary database. So at the moment I need to say it’s going to be this particular one. So you can see it must have a service level objective of S zero or above. So there we are. And click create. So now you can see deployment in progress. So again, I’m going to pause the video until that’s done. So here you can see it’s now succeeded. Now if I log into my new database, you can see there are all sorts of additional tables, views and other objects which have been created.

So you don’t need to worry about those. Just remember, they start with jobs or jobs underscore internal. So the next step is to create a database master key. So here’s how we can do it. Create master key encryption by password and a very strong password you need. Next, you need to create two database sculpt credentials. So you need one to execute drops and you need one to refresh the database metadata in the server. So we use create database sculpt credential. Name a Credential with identity equals and then you have your identity and you have your secret, which is your password. And I just need to insert a comma there. There we go.

So now you can see these commands, even though there’s lots of squiggly on the lines, are being completed successfully. So the next thing we need to do is create a target group. So these are the servers that you are going to be looking at. They could also be pools, there could be lots of things. So I’m going to add a group to start with. So Jobs SP, add Target Group and the name of my group and then I’m going to add a group member. So this uses Jobs SPAD target group member. So we have a target group name that we just used, we have our target type which is SQL Database so it could be SQL Server or it could be Pool Group.

You could be using this to exclude a particular computer so you might have an entire server and then say I want to exclude a certain thing, a database or a pool group. So if that’s the case then we would use membership type equals exclude and if you’re targeting a server or pool as opposed to just a database, then you would also be using a refresh Credential name. But the important thing is that you’ve got your group name that you’ve just done, your target type and your server name.

So my server name is DP 300 database Windows net and I’ve also got a database name of DP 300 so you won’t need the database name if you are targeting just an individual server with lots of databases. So that creates a target group member. And so we can reflect on that when we look at jobs target groups and jobs target group members, we can see what we have included now in each database in a target. You will need a job, Agent Credential, so you could use PowerShell for this, but again, I’m going to be using TSQL. So I’ll get a new query here.

So in the master database so going to master, we need to create a master user with a login and a user and then another login and we’ll create the target user in the target user database. So we’ll run each of these in turn. So first of all the login with a password, the user created from the login, another login, this is for the job and then we’ll change to the target user database and we’ll create that user, right? So now we can go back to our elastic job database and what we need to do now is create a job and the job steps. So this is going to be a very elemental basic job so I’m going to create a job called my first job and the description is I’m just going to look at objects and then I’m going to add a job step into my first job and it’s just going to be very basic, select star from sys objects.

In other words, I just want something that runs so I’m going to add it in to the job that I previously got. So there we go. Next I’m going to run or schedule the job. So to run now I would run using Jobs Spstartjob and if I wanted to schedule it so in other words maybe I want to say I want to do it every minute, hour, day, week, month or just once. Then I can update this job and say have it enabled and the schedule interval type is every 1 minute. And there we go, commands completed successfully. So I’m just going to pause the video now and let it run a few times so I’m not going to actually do anything more. And then at the end I’m going to have a look at the job execution, right, well it’s run a few times so now I can go to jobs, job underscore executions and see how many times it has run. So you can see we’ve got quite a lot of roles at the moment, some of which probably need filtering out. But you can see various things where the lifecycle has been completed and various create time, start time, end time. And if we go to the right, you’ll see that we have got this last message.

With job execution succeeded. So we could order this by adding at the end order, by create time or by start time and executing that. And now it’s in a better order. And you can see these three are the same job execution ID. And you can see the message job execution succeeded, step one succeeded, step one succeeded. Execution on target. Additionally, if we go back to the portal and we have a look at our elastic job agents and have a look at this specific agent, you will also see here the last 100 job executions and whether they have been successful or not.

You will also see in the settings we have our jobs, our target groups credentials. But you’ll also notice that there is no way to actually add them. So you’ve got to do that using something else like for instance Visual Studio or as we’ve done here, SQL Server Management Studio or PowerShell. So let’s just have a review of the steps that we took to get to this position. First of all, we created an Elastic job database so that’s a normal Azure SQL database with a minimum standard of S zero S one recommended, and the more targets you’re going to be using, then the higher specification that database should be. Then we created an Elastic job agent in the portal.

Then we defined our target group after creating some credentials in our job database. So we added a target group and then added target members. And we can see that in jobs, target groups and target group members. Then in each affected database we needed to create two logins and users, one in the master database and one where the login is in the master database and the user is in the target user database. Then we created a job using Jobs spadjob and then as many job steps as we wanted. I’ve only created the one, but you could create lots of them.

Then we started the job we run. Now we updated the job to say I wanted to run every minute. And then we can monitor the job execution using jobs, job executions. So most of that work was done over here in the Elastic Job Agent and Elastic Job database specifically. And the agent is that which is executing those jobs against our target groups. So it could be all the databases in a server, and you can exclude individual databases if you wish, all the databases in a pool or a single database, so those can be added to your target group.