DEMO: Maintenance Wizard

Contents

Introduction
1
SQL Agent Jobs
Azure Automation Service
Elastic Jobs
18
Automated Tuning
Automated Patching
Summary
Start course
Difficulty
Intermediate
Duration
1h 1m
Students
753
Ratings
5/5
starstarstarstarstar
Description

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

Transcript

Within the object explorer in SQL Server management studio, open the management node and right click on the management plans and select maintenance plan wizard. Give your plan a name and then you can select what schedule or how often you want your plan to run. I'm just going to have my basic plan run every day at midnight starting immediately and with no end date.

Next, I will select the maintenance tasks I want to be performed within the plan. So once a day, I want to check the database integrity, shrink the database, rebuild indexes, update statistics, clean up backup, and restore historical data and do a full backup. This backup plan covers many of the tasks required to keep a database in an optimal operating state.

We are checking the database for any issues around the file structure and integrity. By rebuilding indexes and updating statistics, query performance will be maintained relative to the amount of data. Shrinking the database will free up system resources and possibly contribute to better performance. If possible, a daily full backup is also good from a disaster recovery point of view.

Next, you have the opportunity to specify what order you want the tasks to be performed by moving a task up the list. The wizard will take us through pages that let us set options for our selected tasks. Unfortunately, as you can see the maintenance plan wizard hasn't benefited from the 4K DPI support that other features of management studio have. Here, as with the other pages you can select which databases you want your plan tasks to be applied to. I'm just going through these pages with default settings and just select one database.

With the backup task, you can select the media to backup to and the destination. Because I've already selected full backup, the backup type is disabled. I'll get the wizard to create a sub-directory for each backup so that multiple databases go to separate folders. I'll just accept the defaults for the cleanup task, click Next, Next again and then Finish.

Now, if I go and double click on the basic plan I've just created, a nice graphic comes up showing the schedule and then the different steps with the various parameters. By right clicking on the plan in object explorer, I can execute it now. I'll just let that run and come back to it when it's finished. A maintenance plan is a type of job, so we can go to jobs underneath the SQL Server Agent node, right click and view job history. And we can see here, the job has been successfully executed although we already knew that.

About the Author
Students
17729
Courses
62
Learning Paths
12

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.