We have a pretty large sql azure database, over 300GB.

Many tables have been altered over time and we would like to create fresh indexes, including clustered ones. Of course we would like to minimize downtime as much as possible, even if it costs us.

The database is a DTU based, premium instance. We have upgraded/downgraded tiers multiple times and the experience is pretty seamless. As I understand it, Azure spins up a new instance in the background, copies the database, and then switches new connections to the new instance. No transactions are lost in the process and the connection break is very short, less than a minute.

This got me thinking of whether it would be possible to use a similar technique to rebuild indexes (and perform other potential maintenance tasks). Any idea on how we could achieve this? We are OK with paying for 2 instances during the transition.

  • 1
    The two are different concepts are they not? What Azure does is copy the data, in your scenario you're asking if you can write data to the temporary replica during provisioning? Why not just build the indexes in ONLINE mode? – George.Palacios Sep 27 '18 at 7:52
  • But Azure also copies the transactions that happen while the data is being copied. – Clement Sep 27 '18 at 22:59
  • We will experiment more with ONLINE mode but in the past I recall it taking a very large amount of time and resources. – Clement Sep 27 '18 at 22:59

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.