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
At the beginning of this course, I talked about three categories of database administration tasks that lend themselves to being automated. They are generic server maintenance types of tasks, like backups, integrity checking, and index rebuilding. The type of administrative duties that will keep your server running at its optimum. Then we have the type of tasks that are focused on maintaining data stored within the databases on the server.
I think of the third category is all those tasks that are neither administrative nor solely focused on the maintenance of data. This would include jobs like regular importing or exporting of data to meet business requirements. Obviously, there can be a lot of overlap between these categories depending on your perspective. But I think as we move through the different functions and features available first in SQL server and Nina and Azure SQL, we have seen how they address those needs.
We started with SQL service maintenance functionality with predefined tasks to perform the basic range of server maintenance jobs. These pre-configured tasks of index maintenance, integrity checking, and database backups all need to be performed regularly in a production environment. So it's a no brainer that SQL server has this built in. Because it's a given that these tasks need to be performed Azure SQL does them automatically behind the scenes without even involving the user.
It is also obvious that the maintenance plan functionality is essentially predefined SQL Server agent jobs. After looking at maintenance, we then looked at how you can use SQL Server agent jobs to do pretty much anything you want to within the server space. SQL Server agent is essentially a scheduling service to perform pretty much any action regularly and repeatedly.
If you wanna perform actions across multiple servers, you could just use server agent jobs with linked servers. But SQL Server has the ability to set up servers in a master target hierarchy that will allow you to perform the same task across all servers. If I were to be completely honest, I find the SQL multi-server administration to be restrictive and I suspect it's aimed at quite a niche segment of users, environments with a lot of mirrored servers.
Sticking with SQL Server, we then looked at how you can automate the enforcement of rules or standards across databases with SQL Server policy management. For some objects known as facets in policy management parlance like stored procedures, you can set up rules that will prevent unwanted behavior within on change perfect mode. For other facets, you can regularly run reports that'll tell you if any of your policies have been contravened.
As I said earlier, in the cloud environment excluding SQL Server running on virtual machines, much of the day-to-day routine maintenance stuff takes place automatically. Backups are automatically taken and you can configure Azure SQL to manage and maintain database index with automatic performance churning.
In terms of SQL Server agent jobs equivalent in the cloud for running customized tasks, you have the choice of using Azure automation service or the new elastic job agent. Azure automation services is not confined to databases but allows you to automate all types of functionality and resources within the Azure environment. On the other hand, elastic job agent follows very much in the footsteps of SQL server agent. And as we saw its configuration is database driven.
When it comes to automating maintenance tasks for SQL Server running on a VM, you can utilize recovery services vault which will enable you to schedule full differential and log backups for databases on multiple servers. In essence, a cloud combination of maintenance plans and multi-server administration. You can even utilize recovery services vault with on-premise servers using Microsoft Azure backup service agent.
SQL Server VMs also allow you to configure automated windows and server patching. Whether you're running SQL Server on-premise in a VM or managed instance, or Azure SQL, there are multiple functions and services that will allow you to automate almost any task. In fact, would be fair to say that there is overlap in redundancy in terms of automation functionality.
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.