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.
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
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.