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
Automatic tuning comes in two basic flavors, query plan correction and automatic index management. Query plan correction became available in SQL Server 2017 and as a feature of Azure SQL. Automated index management is only available in Azure SQL and Azure SQL managed instance.
Whichever type of auto performance tuning is enabled, it will only be enacted while a database is not under heavy load. It will be kind of ironic that automated performance tuning would degrade database performance while being implemented. Automatic query plan correction is a roundabout way of saying that the database keeps track of query plans. It monitors a query's performance based on metrics like CPU load, IO operations, and execution time. And when it sees a degradation in a query's performance, perhaps when parameters will change the dynamics of a query, it will revert back to the last known good plan.
In this context, performance degradation is known as regression, and the process of reverting back to a previous query plan, is known as forced last good plan. To turn on automatic tuning via SQL, use the alter database command with CIT automatic tuning and CIT forced last good plan to on. Alternately, you can right click on a database inside SQL Server Management Studio, select properties and under query store, set operation mode to read, right.
As I said earlier, this feature became available in SQL Server 2017. So if you're running on premise or in a VM and have restored an older database, be sure to update the compatibility level. Obviously, data does change over time, as well as the database schema. So constantly reverting to the previous query plan when there is a regression, will not permanently fix most issues.
So query plans are re-evaluated after a statistics update or a schema change. The automatic tuning feature within Azure SQL, also features query plan correction, with the force plan option. And introduces create index and drop index options. Apart from the addition of index management, the key difference between SQL Server and Azure SQL, is the use of artificial intelligence and the analysis of hundreds of thousands of databases and their performance, running in the Cloud.
Instead of just using an algorithm based on a set of parameters, artificial intelligence leverages off the experience or learnings from many databases. So based on query performance and the structure of your tables and existing indices, Azure may create an index to improve performance. On the flip side, if it sees that an index is being underutilized or is of no use and may be impairing performance, then it will drop that index if both of these options are set to on.
When an index is automatically created, Azure SQL will verify performance gains, in terms of DTQ savings, the number of queries with improved performance, the number of queries with regressed performance, and the index size, not only is this information available to the user, but it's also used to measure against the database performance baseline. If the created index results in degraded or regressed performance, it will be rolled back.
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.