We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours. One of our goals from a DevOps perspective to be able to ship software (including database changes) during business hours without customer impact (i.e., zero-down deployments). We've been able to do this successfully in nearly all areas of our database using compatibility techniques however we run into issues when it comes to including new columns into our Indexed (Materialized) Views in SQL Azure.

I'm looking for general guidance, experience, or unique techniques that would allow for the inclusion of new columns into a fairly large Indexed View without customer impact.

If needed I can provide more specifics but given that we may deal with more indexed views in the future, hoping someone has a couple patterns in mind to consider.

TL;DR; - What is the best way to deal with indexed view changes while maintaining high service availability?

  • 1
    Have you researched using synonyms? This would let you build a new view with the needed changes and once it’s ready you would just update the object that the synonym references. – Aaron Jun 6 at 5:25
  • Yes we looked at synonyms and do use them for certain cases outside of this. They work for backwards compatibility but doesn't necessarily help us with zero-down / no-impact. When we create the indexed view, it causes locking on the underlying tables and leads to performance degradation. – Scott Jun 6 at 21:42
  • You probably won’t be able to get away from any locks or performance issues while you are building the index. You can try to minimize them by sorting in the tempdb and ensuring there is plenty of free space in the targeted file group. A google search turned up a recommendation from MS about reducing the fill factor, second to last bullet – Aaron Jun 7 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.