Introduction
Maintenance
SQL Agent Jobs
Server & Policy Management
Recovery & Backup
Azure Automation Service
Elastic Jobs
Automated Tuning
Automated Patching
Summary
The course is part of this learning path
Whether you’re running SQL Server on-premise, in a VM, on a managed instance, or using Azure SQL, there is maintenance to be performed and other tasks that need to be executed on a regular basis. In this course, we will look at the options available to you in these various environments for automating maintenance and regularly repeated tasks.
SQL Server has a long history of task automation through SQL Server agent, not only will we see how to use it but also how it’s been adapted to carry out maintenance tasks, and how similar functionality has made it into Azure services. From mundane single-database scheduled backups through to task and performance automation across multiple servers, this course looks at on-premise and in-cloud options.
If you have any feedback relating to this course, feel free to contact us at support@cloudacademy.com.
Learning Objectives
- Maintain your databases and data through the use of maintenance plans and the SQL Server Maintenance Wizard
- Use SQL Agent Jobs to automate maintenance tasks
- Automate maintenance across multiple SQL servers
- Set up rules to enforce policies
- Back up your SQL Server instances using Azure Recovery Services Vault
- Learn about the Azure Automation Service and how it can be used
- Use Elastic Jobs to schedule tasks within the context of databases
- Manage database performance with Automated Tuning
- Manage database updates with Automated Patching
Intended Audience
- Data engineers
- Database engineers
- IT professionals or anyone looking to learn about automating tasks in Azure SQL and SQL server
Prerequisites
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
https://github.com/cloudacademy/azure-sql-task-automation-dp-300
To create an Azure automation service click create a resource on the portal home page and then select IT management tools followed by automation. Give your automation account a name and create it. So the first thing I'll do is create a runbook called Transaction Load Transform, and that will be of a PowerShell workflow type.
As I'm connecting to an Azure SQL database I'll need to provide credentials. So I'll just create new credentials for my Azure user with a name of SQLServerCred. Now I'll go back to my runbook and click edit and add the PowerShell commands.
Now this is all pretty straightforward. I have variables to hold parameters from my connection string, as well as the name of the stored procedure I want to execute. Next I'm going to get the credential that I created earlier using its name, and I'll just get the username and password from the credential, and then also put them in variables.
As you can see, I'm just using write-output statements to display the content of some of those variables. Next, I am creating the database connection and opening it, followed by initializing the SQL command with the connection and stored procedure name. Finally executing the stored procedure and closing the database connection.
Within the runbook editor we can test our runbook, which I will do now. Okay, that looks like it's all in order so next, I will create a schedule. I'll go back to the main automation page and click on schedules and then add a schedule. I'll just call a schedule daily import and get it to run at 8:30 every morning and click create.
Before I can assign my schedule to a runbook I need to publish the runbook. So that's what I'll do now. I'll go back into the runbook, click edit, and then publish. Now that it is published, I can link to a schedule. And as we can see here I could create the schedule directly from the runbook.
Now going back to SQL Server management studio where I've connected to my Azure your SQL Customer Load database, I'll just execute some solid statements so we can see where we are with the transaction input data. Our staging table has the data and the DBO transaction import table is empty and so is the input archive table. I won't wait until 8:30 tomorrow morning, I will just manually kick this off by going back into the runbook and hitting start.
After that's finished, I'll go back to Management Studio and execute those select statements again. And we can see that that has worked. Going back to the portal you can click on jobs to look at an execution instance of the runbook. On the output tab we can see all those write-output statements that are embedded in the PowerShell script. No errors, no warnings, no exceptions. And we can see that same output in the All Logs tab.
While this has been a very trivial demonstration. It gives you a flavor of what is possible with a combination of PowerShell and SQL commands. So in no way are you limited as to what is possible within Azure SQL in terms of task automation and scheduling.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.