SQLBits 2015

Prevention is better than cure!

Come to learn how to implement solutions to prevent undesirable settings creeping in your SQL Server environment. To accomplish this, we will look Policy Based Management with Central Management Servers, SQL Server Audits and DDL Triggers.
As DBAs, we have to manage a huge number of servers and deal with a high volumes of changes/releases and implement vendor products which need elevated levels of permission to run. As a result, all the control and scrutiny that we wish to exercise becomes hard and almost impossible.

Very often, this results in undesirable settings, sub-optimal setups or bad implementations creeping in our environment – all against best practices or our mandated guidelines. e.g autoshrink, badly named objects.

Once they are in, it becomes extremely hard to fix or get rid of many of these issues and the reasons are wide ranging.

With this in mind, this session will explore ways for an administrator to implement solutions to prevent these issues from being introduced in the first place. Or if we cannot stop them, we should be notified about them when they happen.

To accomplish this, we will look at Policy Based Management with Central Management Servers.

Policy-Based Management gives us the ability to:
  • Define Standard Policies
  • Selectively Enforce Policies
  • Automate Policy Checking and Enforcement
  • Fix Out of Policy Conditions with the Click of a Button
Used in conjunction with Central Management Servers, we can easily control and manage groups of servers centrally.

Finally, for settings that Policy Based Management does not cover, we will look at some alternatives (like DDL Triggers) which can be used to prevent unwanted settings or notify us on the spot, if the changes cannot be stopped.