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:
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:
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:
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:
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
- Take the information listed above and create the necessary conditions and policy.
- Read more about Policy Based Management
- Read more about SQL Azure
- Read more about Clustered Indexes
