Validating Stored Procedures using SQL Server 2008 policies and C#

Microsoft SQL Server 2008 incorporates a new feature named Policy Based Management, allowing the administration of SQL 2008-based policies. DBAs can control many different aspects of SQL Server and automate several time-consuming tasks.

Among other uses, the policies can help to ensure that Stored Procedures follow a desired standard (set of policies).

Then you could also write your own application to validate a set of stored procedures against the different policies defined in the target server, using .NET code and a set of classes available to interact with SQL Server objects. 

Additionally, you could validate stored procedures from previous SQL versions such as 2005 and 2000.

In order to write some .NET code to achieve this, first we have to refer to a few assemblies:

   1: using Microsoft.SqlServer.Management.Dmf;

   2:  

   3: using System.Data.SqlClient;

   4:  

   5: using Microsoft.SqlServer.Management.Sdk.Sfc;

   6:  

   7: using Microsoft.SqlServer.Management.Smo;

Then we should get the policies collection from our target server. We will use the GetPolicyCollection method which receives a parameter with the connection string information from our SQL Server 2008 hosting the policies.

   1: private PolicyCollection GetPolicyCollection(string ConnectionString)

   2:  

   3: {

   4:  

   5: PolicyStore policyStore = new PolicyStore();

   6:  

   7: SqlConnection oSqlConn = new SqlConnection(ConnectionString);

   8:  

   9: SqlStoreConnection oStoreConn = new SqlStoreConnection(oSqlConn);

  10:  

  11: policyStore.SqlStoreConnection = oStoreConn;

  12:  

  13: return policyStore.Policies;

  14:  

  15: }

Then we can write some code to ensure that our StoredProcedure complies with established policies.

This method receives a StoredProcedure  object, which represents the stored procedure we will evaluate.

Policy class exposes some methods to validate an StoredProcedure objects agains it.

We will iterate through the collection of policies and invoke the Evaluate method passing the sp object as parameters, and telling the framework to check whether the validation fails or not, using the AdHocPolicyEvaluationMode enum (in this case will be set to Check), and an array with the objects to validate.

   1: private bool Validate(StoredProcedure sp)

   2:  

   3: {

   4:  

   5: Object[] parameters = new Object[1];

   6:  

   7: parameters[0] = sp;

   8:  

   9: PolicyCollection policyCollection = GetPolicyCollection(strConnectionString);

  10:  

  11: foreach (Policy policyItem in policyCollection)

  12:  

  13: {

  14:  

  15: if (policyItem.Evaluate(AdHocPolicyEvaluationMode.Check, parameters))

  16:  

  17: {

  18:  

  19: return true;

  20:  

  21: }

  22:  

  23: return false;

  24:  

  25: }

  26:  

  27: }

And that’s it! You can have a friendly UI to display how a set of stored procedures will comply or not with the defined policies at the target server, before you actually decide to move them.

(by the way, it’s quite easy to move stored procedures from one server to another using the classes included in these libraries and a few lines of .NET code).

Post written by Santiago Gonnet - .NET Developer @ UruIT