1. Home
  2. Training Library
  3. Microsoft Azure
  4. Courses
  5. Azure SQL and SQL Server Database Task Automation

DEMO: Policy Management

Contents

keyboard_tab
Introduction
1
SQL Agent Jobs
Azure Automation Service
Elastic Jobs
18
Automated Tuning
Automated Patching
Summary

The course is part of this learning path

play-arrow
Start course
Overview
DifficultyIntermediate
Duration1h 1m
Students61

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

In my CustomerLoad database, I don't want table names to have spaces in them so I'm going to set up a policy using the table facet to let me know if any tables are created with spaces in their names. So first of all I'll right-click on the table facet and go New Condition and give the condition a name. 

In the expression, I want to evaluate the name field or attribute of the table, so I'll select name from the drop-down, and then for the operator, I will select not like, and for the value I'll just specify the space between two % symbols. It's important that your value is enclosed in quotes.

Now I'll go up to policies under Policy Management and right-click and select New Policy. I'll give that policy a name and then from the check condition drop-down, I'll select the condition No space in table name that I just defined. Because it's a table facet, it's already checked against every table, but I also get to specify which database I want this policy to be run against.

So essentially, I am defining another condition here. So I'll set the database name equal to CustomerLoad. Having done that, I can now manually evaluate the policy by right-clicking and selecting evaluate from the context menu. And I can see that the Customer Item table with a space has been highlighted in the results.

So if I go and drop that table and then re-evaluate, I can see that it's all successful. The table facet doesn't allow for On change evaluations and we can see that by going into the properties and in the evaluation mode there is only on demand and on schedule.

So now I'm going to set up a policy to make sure that stored procedures when they are created with the prefix USP. Because the stored procedure facet does allow on change evaluation, I will be able to prevent, in real-time, users from creating stored procedures that do not conform to my naming policy. It's the same process as with the table name policy.

I'll create a new condition under the stored procedure facet, specify the name field, and use the like operator to compare with my value of USP_%. So go back to Policy Management and create a new policy, giving it a name and selecting the stored procedure prefix check condition.

Next, I'll specify on change prevent as my evaluation mode and again set the target database as CustomerLoad, not forgetting to enable the policy.

Now, let's try that out in real-time by creating a stored procedure that doesn't conform to my naming policy. Sure enough, we have an error there saying that the stored procedure doesn't meet the policy condition of name like USP_ and if I change that to usp_Customer_Item_Get then that will be successfully created.

About the Author
Students8561
Courses13
Learning paths1

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.