Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Policy Base Management Has Clustered Index Condition


By:   |   Last Updated: 2010-11-05   |   Comments   |   Related Tips: More > Policy Based Management

Problem

As SQL Azure gains in popularity, more and more people find themselves thinking about having their databases migrated to SQL Azure. Microsoft has published a list of general guidelines at http://msdn.microsoft.com/en-us/library/ee336245.aspx. One of those guidelines details how every table in a SQL Azure database must have a clustered index. Therefore prior to any migration to SQL Azure you must verify that all tables in your database have clustered indexes.

Solution

You can build a policy inside of Policy Based management in order to quickly verify that all tables inside a database have a clustered index defined.


The first step is to create a new condition. The new condition should have a target of 'Table', and the facet we want to make use of is named '@HasClusteredIndex'. Since we are looking to verify that every table has a clustered index we set the operator and value to be '= True' as displayed below:

prior to any migration to SQL Azure you can build a policy inside of Policy Based management in order to quickly verify that all tables inside a database have a clustered index defined.

This next step is optional. I am going to create another new condition in order to limit the final policy to be run against just one database. You may want to run your policy against more than one database which means you will not want to bother creating this new condition. If you do decide to limit the policy to just one particular database then you are going to want to create a condition with the target of 'Database' and filtering on the @Name facet as shown here:

 you are going to want to create a condition with the target of 'Database' and filtering on the @Name facet as shown here

Now we can create the policy. We will use the condition we created above first (named 'Clustered Index Check Conditon') and run our policy against every table inside of the AdventureworksDW database. We do this by clicking on the down arrow next to the word 'Database' and selection the condition we created above named 'AdventureworksDW_DB'. We will leave the evaluation mode to be 'On Demand' and we will also not configure any server restriction as shown below:

we will leave the evaluation mode to be 'On Demand' and we will also not configure any server restriction as shown below

Click OK to save the policy. After saving you can right click and evaluate. If you use the same AdventureworksDW database that I used, then you should see a screen similar to the following:

after saving you can right click and evaluate

And now you can go back to the tables that are missing a clustered index and take action before thinking about trying to push the database into the cloud.


Next Steps


Last Updated: 2010-11-05


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools