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.