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
I have two instances of SQL Server, the default instance which will be the master, and MSSQLTarget that will be the target. The first registry setting I will change is MsxEncryptChannel option for the target server. This resides under "HKEY_local_machine\software\Microsoft\Microsoft SQL Server" then the instance name followed by the SQLServerAgent key. That needs to change from two to zero, which means turning off encryption.
The next registry key I need to change, also under SQLServeragent is AllowDownloadedJobsToMatchProxyName. This needs to change from zero to one for both the master and the target servers. Now that we've taken care of that pre-configuration, let's go to our master server. Right-click on SQL Server agent and choose "Make this a master" from the multi-server administration context menu.
Going through this wizard is reasonably straightforward. I'll enter an email address for the operator and then I will choose my target server. Once the wizard has finished, we can see that the SQL Server agent on both the master and the target servers have little annotations appended to them. On the master server it's MSX, and on the target server, it's TSX, followed by the name of the master.
Now I'll set up a job that will run on both instances by going into jobs, right-clicking on multi-server jobs, and clicking new job. The only difference between a multi-server job and a normal job, is that you need to select the server targets for the job. So, that is under the targets page, and I can see the target server sitting in the list. The job itself is not important so I will just create a one-step job that will rebuild an index on a table that is present in the database that is on both server instances.
As maintenance plans can also be targeted to multiple servers, let's set up a basic multi-server maintenance plan as well. So, the same as last time, right-click on maintenance plans and select maintenance wizard. Just name that multi-server plan, and not worry about a schedule and click Next. I'll select both the local and the target server and click next. And just check "backup database full." I'll just select the Skyport database, remembering that the target database has to be present on both servers.
I'll click next and finish. And we can see the plan is set up and saved as well as the plan jobs appearing under local jobs of the master SQL Server agent. The multi-server plan is also now visible under the multi-server job node. If I right-click on SQL Server agent on my master instance, then choose managed target servers from the multi-server administration menu, that will bring up a target server status window. And in the download instructions, we can see the multi-server job that I set up earlier plus the multi-server plan, and in each case, these are being downloaded to the target server.
Going to the target server and expanding jobs under the server agent, we can see that both the multi-server job and the multi-server plan are now visible there.
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.