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
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.