SQL Server with Mr. Denny

Jul 12 2012   2:00PM GMT

PBM, Replication and Stored Procedure Names

Denny Cherry Denny Cherry Profile: Denny Cherry

One of the things that Policy Based management is really good at is making sure that people don’t create stored procedures that start with “sp_”.  However when the server that you are deploying the policies to is also being used for SQL Server replication this can get a little more complex seeing how all of the replication stored procedures start with “sp_” by default and they aren’t marked as system stored procedures.

I came across this problem when setting this up on a clients system.  The way that I was able to get around this was to setup Policy to evaluate against a condition which I had setup which filtered out the stored procedures that were used for SQL Server Replication.  I did this by setting up a condition, which I called “User Defined Stored Procedures” which had two values in the Expression.  The first was looking at the Schema field and excluding anything in the “sys” schema (which takes care of all the system objects), then looking at the Name field and excluding everything that matched “sp_MS%”.  You can see this condition below (click any of the images to enlarge).

Now to ensure that this only ran against the user databases I created another condition against the Database facet which looked at the IsSystemObject field and make sure that it was False, shown below.  That way I could put procedures like sp_whoisactive and sp_who3 into the master database and not have a problem with them.

The actual Check condition of the policy was setup easily enough, simply checking that the stored procedure name wasn’t like “sp%” as shown below.

Bringing this all together is the actual Policy which is configured with the check condition, and is configured to filter the objects being checked against the two other check conditions which helps to limit the amount of time that the policy takes to execute as shown below.  As this is a SQL Server 2008 R2 instance in this example I had to use a schedule to verify everything nightly, but that’ll do.

Hopefully if you run across this situation this will help you get everything setup faster that I was able to.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: