Click here to monitor SSC
  • Av rating:
  • Total votes: 14
  • Total comments: 12
Grant Fritchey

Azure SQL Database Maintenance

22 March 2013

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.

Backups

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.

SELECT o.name AS ObjectName,
   i.name AS IndexName,
   ddips.avg_fragmentation_in_percent,
   ddips.page_count,
   ddips.compressed_page_count,
   ddips.index_depth,
   ddips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID('MovieManagement'),

OBJECT_ID('dbo.MovieStage'), DEFAULT,
   DEFAULT, 
'DETAILED') AS ddips
   JOIN sys.objects AS o   
   ON ddips.object_id = o.object_id
   JOIN sys.indexes AS i
   ON ddips.index_id = i.index_id
      AND ddips.object_id = i.object_id;

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:

ALTER INDEX AgentPK
ON dbo.Agent
REBUILD;

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

ALTER INDEX AgentPK
ON dbo.Agent
REBUILD
WITH (STATISTICS_NORECOMPUTE = ON);

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.

Statistics

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):

DBCC SHOW_STATISTICS(Agent, AgentPK);

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.

Automation

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.

Conclusion

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.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 14 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: limits of azure
Posted by: Alexandre Araujo (not signed in)
Posted on: Thursday, March 28, 2013 at 9:15 PM
Message: 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 ?

Subject: Limits of Azure
Posted by: Grant Fritchey (view profile)
Posted on: Friday, March 29, 2013 at 7:25 AM
Message: 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.

Subject: Limits of Azure
Posted by: Ale Araujo (view profile)
Posted on: Friday, March 29, 2013 at 8:27 PM
Message: 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.

Subject: New Features
Posted by: Grant Fritchey (view profile)
Posted on: Saturday, March 30, 2013 at 8:22 AM
Message: 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.

Subject: maybe fragmentation is not so important in Azure
Posted by: csm (not signed in)
Posted on: Monday, April 01, 2013 at 9:28 AM
Message: 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): http://blogs.msdn.com/b/cbiyikoglu/archive/2011/06/20/id-generation-in-federations-identity-sequences-and-guids-uniqueidentifier.aspx

Subject: Fragmentation
Posted by: Grant Fritchey (view profile)
Posted on: Monday, April 01, 2013 at 10:15 AM
Message: 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.

Subject: RE: fragmentation
Posted by: csm (not signed in)
Posted on: Tuesday, April 02, 2013 at 4:12 AM
Message: 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 :(

Subject: Re: RE: fragmentation
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, April 02, 2013 at 4:20 AM
Message: 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.

Subject: New Features
Posted by: Ale Araujo (view profile)
Posted on: Tuesday, April 02, 2013 at 10:18 AM
Message: 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 ?!?!?

Subject: Grant would you write a book for us?
Posted by: jamesandersson (view profile)
Posted on: Friday, April 12, 2013 at 2:52 AM
Message: 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 james.anderson@inkstall.com

www.inkstall.com

Subject: On the subject of fragmentation....
Posted by: mzz3lh (view profile)
Posted on: Monday, May 20, 2013 at 2:13 AM
Message: See: http://beyondrelational.com/modules/2/blogs/76/posts/15290/index-fragmentation-in-sql-azure.aspx

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.

Subject: TemdBB
Posted by: Grant Fritchey (view profile)
Posted on: Monday, May 20, 2013 at 4:55 AM
Message: 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.

 

Top Rated

Data Science Laboratory System – Object-Oriented Databases
 Object-Oriented Databases (OOD) avoid the object-relational impedence mismatch altogether by tightly... Read more...

Tales from a Cloud Software Firm
 Following on from a discussion about how people are using the cloud, the Simple-Talk Editorial Team sat... Read more...

Data Science Laboratory System – Document Store Databases
 A Document Store Database (DSD) is similar to a Relational Database Management system with the... Read more...

Data Science Laboratory System - Instrumentation
 It is sensible to check the performance of different solutions to data analysis in 'lab' conditions.... Read more...

Testing the StreamInsight Service for Windows Azure
 Getting 'up to speed' with StreamInsight is easier if you take the time to run it and test it out.... Read more...

Most Viewed

Windows Azure Virtual Machine: A look at Windows Azure IaaS Offerings (Part 2)
 We continue our introduction of the Azure IaaS by discussing how images and disks are used in the Azure... Read more...

PHPFog and Pagoda Box: A Look at PHP Platforms
 Cloud platforms such as Heroku, AppEngine, PHPFog and Pagoda Box are ideal for companies who just want... Read more...

An Introduction to Windows Azure BLOB Storage
 Azure BLOB storage is persistent Cloud data storage that serves a variety of purposes. Mike Wood shows... Read more...

Managing session state in Windows Azure: What are the options?
 Because you can't maintain session state for ASP.NET applications in Azure using the default in-process... Read more...

Creating a custom Login page for federated authentication with Windows Azure ACS
 Windows Azure Acess Control Service (ACS) provides a way of authenticating users who need to access web... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.