Following on from yesterday’s post, I wanted to develop this idea of “intent” in management. What we mean by that is that you should be able to declare your intent about a situation on your server, such as “I want to make sure that all stored procedures start with the letters ‘usp’. I want to report on the ones I have, and then make sure all new ones are created properly.”
There are a few ways to accomplish the first part of this statement. I could simply run some SELECT queries in Transact-SQL to find the names of the stored procedures and compare them to a known value. Once I run that query, I can do whatever I want with the data. I could also create a DDL Trigger to capture the fact that someone tried to create a stored procedure with the wrong name. But the report runs on a single server, and the EVENTDATA() function in a DDL Trigger Is not all that easy to understand. Add to that the fact that a DDL Trigger doesn’t support INSTEAD OF, and you can see the need for something else.
So that is why the SQL Server team created Policy Based Management (PBM) which can, for certain events anyway, capture the fact that something is “violating” your intent and even prevent some of the actions. I’ll explain that statement a little further later this week.
Tomorrow I’ll cover some of the terms we use in PBM and how they map back to this intent-based management idea.