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
You don't have to use the wizard; you can create a new maintenance plan with the benefit of the graphical interface. Once again, I will right-click on Maintenance Plans, but this time select New Maintenance Plan. A maintenance plan can have multiple sub plans, and if I click on the schedule of subplan_1 I get the same job schedule window as we saw in the wizard.
Because I'm in the context of a maintenance plan when I go to the toolbox, all the tasks that we saw before plus some extra ones like Execute SQL Statements and the Notify Operator Task is available. To set up a maintenance plan, it is merely a case of dragging and dropping the desired tasks onto the design area.
Once a task has been dropped you can select the databases to apply the tasks too and even view the SQL statement that will be executed for that task. As you can see the options windows are being displayed correctly at 4K DPI. This makes me think that this is the preferred method of setting up a maintenance plan and that's why it got the attention that the wizard hasn't.
Once the tasks have been configured, they can be joined together in the order you want them to execute by dragging the connector line between them. Finally, I will finish my plan with a Notify Operator Task. Currently, I have no operators set up on the server. I will just go into operators under SQLServerAgent and create a new operator and select that operator and enter a message, subject, and body text before clicking okay. This is a new installation of SQL Server and I haven't yet set up a default database mail profile.
For my operator notification to work, I'll need to set up database mail. The database mail configuration wizard walks you through setting up a profile that is in turn linked to an email account configuration. I'm just going to call my profile default and I will add an email SMTP account. One thing I forgot to do was to set my profile to be the default profile. Even though there is only one profile it does not automatically become the default one. With the operator and database mail configured I'll now run the maintenance plan. With that successfully completed, I've managed to find the notification email in my spam folder.
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.