Azure SQL Database Maintenance

It is increasingly likely that DBAs are now given responsibility for maintaining Azure SQL databases as well as conventional SQL Server databases. What is likely to be required by way of maintenence? What are the differences?

While there are many similarities between Azure SQL Database and  SQL Server, there are also a large number of differences. The main point of contention is that none of the operating system is available to you. This means that all sorts of monitoring and maintenance tasks associated with the operating system and the server are no longer necessary or available. That said, you’re still dealing with a SQL Server database, and there are maintenance tasks that may still be applicable to that Azure SQL Database. Let’s discuss a few of the possibilities and mention those places where you just can’t go.


Initially there was no facility to perform a backup at all. To a degree, the need for backups has been somewhat mitigated by the architecture of the Azure offering which creates three copies of your database on three different machines. However, this hasn’t eliminated the need for backups by any means. Because of this, Microsoft has now relented, and provided a mechanism to backup and restore your database, called the bacpac. Further, this bacpac takes advantage of Windows Azure Storage: As long as you transfer data between datacenters in the same region, you won’t actually incur a data transfer cost. You need to be aware that the bacpac is not really a backup in the traditional sense. You’re not taking a page-by-page copy of the database. Instead, you’re exporting the schema and performing a bulk copy of the data out to the bacpac file. Because of this process, the data may not be transactionally consistent if you take a backup of a database that is being updated in any way. This would lead to you losing data or finding problems with your referential integrity. To avoid this, you take a copy of the live database and create a bacpac from the copy.

To make a bacpac backup happen, you have to first use a function that copies your database:

CREATE DATABASE MovieManagement_Copy
AS COPY OF myserver.MovieManagement;

That’s a T-SQL statement. You can run it from the Portal or from SSMS. You just supply the appropriate database and server names in place of my examples. Oh yeah, and you get charged for the database copy. You also need to be sure you’re connected to the master database on the Azure server in order to run this.

Once it’s done, you now have a copy of your database that you can use to create the bacpac by running the Import/Export wizard either from the Azure Management Portal, or from your SSMS. If you’re going to use Hosted Storage you’ll need to set that up ahead of time. After that, it’s just a matter of using the URLs and Access Key values supplied from the Storage Account. You’ll have a backup. When you’re done, you can then get rid of the database copy.

To bring that database back online, you just run the Import process, more or less reversing the steps you just completed.

Index Fragmentation

While each of the individual databases within Azure SQL Database are likely to be somewhat small, the current max is only 150gb, fragmentation of the indexes within the database could still occur. That can cause performance to suffer which could, in turn, lead to long running queries which may cause throttling on your system. Further, fragmented indexes take up more room which means more storage so you could be using up a precious resource and costing your company money just by having fragmented indexes.

Luckily, because indexes are all within the database, the functionality here is exactly the same as it is within SQL Server running on your local instance. You’ll have the same DMO queries that you can use to ascertain the status of your indexes.

You can run it with the ‘DETAILED’ option as I have if your indexes aren’t very large; but if you are dealing with a larger index, there’s a good chance that running this might end up getting throttled, so I’d strongly recommend you only use LIMITED or SAMPLED, just in case.

Once you identify an index that is fragmented, you can use the ALTER INDEX command to rebuild it:

You can even pass in some commands through the WITH statement:

But, you need to know that some operations require access to the OS, so they won’t be available. These include popular and useful commands like ONLINE or SORT_IN_TEMPDB. You’ll need to think about how you’re planning on doing your index defragmentation because of it.


Because, under the covers, this is SQL Server, you’re going to be very dependent on statistics, exactly the same way you should be when working with your local instance. Statistics determine the decisions made by the optimizer and have every bit as much impact on the performance of your queries. While you can’t run consistency checks on the databases (more on that in The No-Go Zone below), that doesn’t mean you can’t use DBCC commands (remember, DBCC doesn’t mean DataBase Consistency Checker any more, but instead DataBase Command Console):

The output is exactly the same. And when you decide that your statistics are out of date, your options for updating them are exactly the same too. You can run sp_updatestats or you can issue UPDATE STATISTICS commands. This is just another example of how the things you already know are immediately applicable when working with Azure SQL Database.


SQL Agent is gone. Well, it’s not available within Azure SQL Database. So, while we have identified a few places where standard database maintenance is going to be helpful and necessary, your simple method of automating this maintenance is gone. In order to automate these processes, you’re going to have to write some code and use a scheduler to run that code from somewhere on your local instances. It’s not neat. Most importantly, and this applies to any code you write around Azure, you can’t count on connectivity, so you need to be sure have very good logic for attempting retries. Microsoft has a good document on the necessary retry logic.

The No-Go Zone

There are a lot of things you just can’t do. They all go back to the fact that you have no access to the underlying server and operating system. You’re not going to be maintaining your log files and log backups. It’s just not a possibility. You also won’t be able to maintain your data files. In general, if you need more files, you’re going to go to Federated databases. You don’t have to worry about the tempdb, at all. You no longer need to sweat excess data within the MSDB either. One that is both great and worrisome, you no longer have to worry about consistency checks. You can’t run consistency checks.


As long as you’re clear about whether your operations are totally within a database or could possibly include operations within the operating system, it’s easy to know which of your maintenance tasks you can perform within Azure SQL Databases. Most of what you already know will transfer smoothly to the cloud, so you should be able to get your job done. Just be ready to supply some code in order to make it all happen.

To learn even more about creating, managing and maintaining Azure SQL Databases, attend the all day pre-conference seminar, How to Be a Successful DBA in the Changing World of Cloud and On-Premise Data, that I’ll be putting on with Tom LaRock and Dandy Weyn at the TechEd conference in New Orleans and again in Madrid, Spain in June 2013.

For other ways to perform some of the maintenance routines on your Azure SQL Databases, be sure to check out Red Gate Software’s Cloud Services offerings.

Tags: , , , , , , , , , ,


  • Rate
    [Total: 2    Average: 3.5/5]
  • Alexandre Araujo

    limits of azure
    If we have only 150GB and must use Federation, currently won’t possible to work with azure in production. How many years do you think that we’ll see azure overtaking on-Premisse ?

  • Grant Fritchey

    Limits of Azure
    I don’t anticipate on-premise ever going away. I think it’ll shrink, absolutely, but there are just too many places where you’re going to want direct control over your data and your infrastructure. In terms of just being able to support the same sized systems… I’m not sure. Sooner rather than later.

  • Ale Araujo

    Limits of Azure
    Certainly. Now imagine the same size systems and you wish to improve performance but you just can’t. Even if the Hekaton will be great, the SqlDatabase on Azure won’t be ready. So i think that’s the evolution to new versions of azure that will be adding features from on-premise version.

  • Grant Fritchey

    New Features
    But Microsoft says they’re going to be adding new features to Azure first. I’m pretty sure we’re going to see growth in both directions.

  • csm

    maybe fragmentation is not so important in Azure
    It’s hard to believe, but in SQLAzure the fragmentation is not so important as in SQL on premise. Take a look at this post from Cihan Biyikoglu (SQLAzure PM):

  • Grant Fritchey

    Interesting read. Very interesting. And I’m not going to argue with the guys who can see the guts of Azure when all I can see are the external artifacts. I think people in general tend to over-emphasize the impact of fragmentation. But it does have an impact and that impact is possibly increased under some circumstances on Azure.

    Long running queries that use a lot of resources are candidates for throttling within Azure. And if your indexes are severely fragmented AND you’re getting scans, you may experience more throttling. But, this isn’t a call for defragmenting the index nearly as much as it’s a call for tuning the queries and the structures to ensure things run fast.

  • csm

    RE: fragmentation
    After the read of this article, I run a couple of test trying to understand the behaviour of SQLAzure with high fragmentation and ordered scans.
    I create the same tables as the article shows, but with more than 700.000 rows. The table with GUID as CI has 0.6 more times of size and 99.8% fragmented compared with the table with INT as CI, but a "SELECT * FROM <table> ORDER BY <CI>" shows very similar execution times.
    I think this results are very dangerous because anyone can conclude that fragmentation doesn’t matter, something that SHOULD BE be wrong, but the facts say otherwise 🙁

    • Grant Fritchey

      Re: RE: fragmentation
      Again, like I said, the impact of fragmentation is frequently over-emphasized. It does depend on the types of queries being run the degree to which you’ll see negative impact, even on premise.

  • Ale Araujo

    New Features
    Like i thought, growth in both, thus the Azure will need more features than on-premisse certainly because the age of its life. Maybe in a near future we’ll have only azure databases … or no ?!?!?

  • jamesandersson

    Grant would you write a book for us?
    Hey Grant,

    We are an IT publishing company, please share if you have any book idea, so we can collaborate and publish a book. email me at

  • mzz3lh

    On the subject of fragmentation….

    There are times when defragmenting your indexes are necessary in Azure, which I don’t actually cover in my post. For one, your secondaries can become out of step from your primary with regards to fragmentation and overall size, so defragmenting can keep your primary aligned. But as mentioned, performance is not a guaranteed benefit. Unless your indexes are tiny, I would also ALWAYS use the "ONLINE = ON" option when rebuilding, due to the limited transaction log space per connection.

    For similar reasons, I think it’s worth mentioning that the statement "You don’t have to worry about the tempdb, at all" is not 100% true. You get 5GB of tempdb per connection, which can easily fill up if you’re performing large operations. Or you could be throttled. Therefore a good approach is to treat SQL Azure like a VLDB and break up operations into small chunks to stay beneath the limits and thresholds.

    A great introductory article though. The community is crying out for some good documentation on being a DBA in Azure – I’ve considered putting together a book/whitepaper for a while now.

  • Grant Fritchey

    True. Saying to not worry about it is probably a very bad way to say it. You have no control over it, so the options are extremely limited. It goes back to how you’re going to manage your transactions and your queries, but that’s another set of articles.

    Thanks for the valuable input.