We have a large database hosted in sql azure and we need to occasionally run maintenance tasks such as index rebuild. Pretty common of course, and we also try to minimize downtime.

We use the premium sql azure database, which comes with replicas. It got me thinking whether it would be possible to run expensive maintenance tasks on a replica and then somehow switch this replica to become the primary. Things like index rebuild, but also potentially adding indexes and altering tables.

I have a feeling this is not going to work because the replicas must be identical to the primary. But, if I understand correctly, whenever I change my azure plan, behind the scene azure spins up a database, copies the database over and then switches the 2 database once the new server has completely caught up on all transactions from the original server. Is it possible to use a similar technique to run tasks on the new server before it switches with the original server?

I should say that we are fine with the db being down for writes for a while, but we want minimal downtime for reads.

  • Why do you need to rebuild indexes? May 9 '18 at 1:04
  • which comes with replicas --do you mean the 3 copies you get by default or you have set up with Read scale-out? May 9 '18 at 1:11
  • @sp_BlitzErik to improve performance.
    – Clement
    May 9 '18 at 5:08
  • 1
    Why not rebuild the indexes online? Have you tried this and seen a measurable performance hit? Why not pause the operation during peak hours, and finish the rebuilds in off-peak hours? May 9 '18 at 7:10
  • 1
    Rebuilding indexes to improve performance is like moving into a new apartment to avoid cleaning your room. It's a lot of work, it's expensive, and your room is just gonna get dirty again. Watch this first. May 9 '18 at 10:10

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.