Auditing SQL Server With Policy-Based Management

Auditing SQL Server is one of the main tasks every DBA should take care of, and Policy-Based Management is here to help make that process easy.
Course info
Rating
(46)
Level
Intermediate
Updated
Jul 14, 2014
Duration
5h 4m
Table of contents
Introduction to Policy-Based Management
Fundamentals of Policy-Based Management
Working With Policies
Troubleshooting and Monitoring Policies
Advanced Policy-Based Management
Policy-Based Management Internals
Reporting on Policies With EPM Framework
Summary
Description
Course info
Rating
(46)
Level
Intermediate
Updated
Jul 14, 2014
Duration
5h 4m
Description

Auditing SQL Server is one of the most critical tasks a database administrator is assigned to. The understanding and knowledge of who does what and when is just as important as making sure that your database servers are up and running. What's more, DBAs do not manage just one instance of SQL Server nowadays, but hundreds or even thousands. In addition to that, sometimes there is a need to check for or even enforce a specific business rule or configuration and thus prevent someone violating some specific requirements, for example. All of those tasks can be quite time-consuming or even impossible to accomplish except if Policy-Based Management is used. With this course, the learner will be prepared to face the challenge of auditing and enforcing rules across many database servers, without spending a lot of hours working on these tasks.

About the author
About the author

Boris is the Founder and CEO of Presentation Design and Training Agency 356labs. He is also a top rated international speaker, trainer, and blogger. You can find him on Twitter under @BorisHristov and follow his work on his blog – borishristov.com

More from the author
Introduction to Presentation Design
Beginner
1h 6m
Dec 29, 2014
Section Introduction Transcripts
Section Introduction Transcripts

Introduction to Policy-Based Management
Hello, and welcome to the Pluralsight course for Auditing SQL Server With Policy-Based Management. My name is Boris Hristov, and I am a SQL Server MVP speaker, trainer, blogger, and DBA. In this course, you will learn how to audit, create, and define the rules for your whole SQL Server environment. So if you are the DBA or if you are the person responsible for those tasks, this course will definitely be of great help to you. Before we actually talk about the modules of this course, though, I want to share with you why are we going to use policy-based management, and what makes it the best choice for accomplishing those goals.

Fundamentals of Policy-Based Management
Welcome to the next module from the course for Auditing SQL Server with Policy-Based Management. In this module, we are going to take a look at the fundamentals of what actually makes our policies work. In general, what we are going to do in the upcoming videos is that we are going to search and you are going to learn the answers of three questions. We are going to first talk about what we can use Policy-Based Management for and then when we can use it actually, because as this functionality, as this feature is quite a new feature, it turns out that you cannot use it in each and every release of SQL Server, and we are going to spend time talking about this. The second answer is of the question what are the components of a policy, which means that we will learn what makes up a policy? What are the components that make a policy work and why is it crucial to understand what is Facet, what are Conditions, what are Targets, and what are evaluation modes and how they can affect the execution of our policies, because they definitely can. Once we know those two, we are also going to talk about what are the limitations of this feature. I will show you some neat scripts, and I will also show you some tips and tricks that you can use to guarantee that your policies are actually going to work as you expect them. So I suggest we start, because we have quite some work to do.

Policy-Based Management Internals
Welcome to the next module from the course for Auditing SQL Server With Policy-Based Management. In this module, we are going to take a look at the internals of the technology. Now, what do I mean by saying the internals? In this module, we are going to spend our time by first looking at why is msdb, a system database inside our SQL Server database instances, why is msdb as a database such an important database, especially when we talk about Policy-Based Management. Once we know that, we are going to take a closer look to the system objects which make Policy-Based Management actually work. And by that, I mean, the system tables, the system views, and some system store procedures that actually do the work for us behind the scenes. The final topic from this module is related to security, and security is always an important part and is always an important topic, so we are going to spend some time learning about the security in Policy-Based Management, and I will show you that there are some gotchas that you need to be careful about. So we have quite some work in this module, so let's start.

Reporting on Policies With EPM Framework
Welcome to the final module from the course Auditing SQL Server With Policy-Based Management. In this module, we are going to do just one thing, but probably the coolest thing. We are going to generate beautiful reports based on how our policies actually execute it. Now, what we are going to do in general is first we are going to discuss shortly why do we need to report and generate those reports for our policies and their executions. Do we need that at all, and why would we spend our time managing and creating a solution for that. Then we are going to talk about the solutions that we have for these problems. So we took the decision that we need to report and generate reports based on our policies. How can we do that? We are going to discuss the approaches here. And, as a third thing in this module, we are going to take a closer look to what's called EPM Framework, which stands for Enterprise Policy-Based Management Framework and you'll see how you can set it up for yourself and for your environment. So we have quite a lot of work to do in this final module, so I suggest we start.