SQL Azure’s place in the data world (Part 1)

I recently presented on SQL Azure at Code Mastery in Minneapolis.  The presentation focused on using SQL Azure in application development.  We took the time to create a new server and a new SQL Azure database.  After that part of the demo, we hooked up various management tools (SQL Server 2008 R2 Management Studio and SQL Azure Database Manager) and applications (e.g.  SQL Server 2008 Integration Services, Microsoft Access, Microsoft Excel).

At the end of that presentation, I started discussing how developers and architects could use SQL Azure.  That raised a number of questions on where SQL Azure fits into the data or information architecture in the real world.  Some of the questions that matter to implementers were:

  • Can you back up databases?
  • What is Microsoft’s SLA for SQL Azure?
  • How easily can you keep the data up to date between on premise and cloud databases?
  • How much data can be supported by SQL Azure?
  • Is it really cost effective?
  • What are some use cases that make sense based on the answers to the questions above?

I will tackle these questions in this and some following posts. Hopefully you will get a perspective of where SQL Azure is today and how it may or may not fit in your environment.

Can you backup databases?
What is Microsoft’s SLA for SQL Azure?

The question behind these questions is what are my disaster recovery and high availability options from service and data standpoints.  One of the coolest features of SQL Azure is the fact that it has built in disaster recovery and high availability at the service level.  Microsoft creates three copies of your database within their data centers.  In this way, Microsoft guarantees 99.9% availability of SQL Azure databases.  (See http://www.microsoft.com/windowsazure/sla/ for more details.)

However, this only covers the service availability.  What happens if your data becomes suspect or corrupt?  Well for starters, SQL Azure has no support for the standard backup and restore operations we are used to.  Microsoft is currently pointing us to creating a copy of the database within the SQL Azure platform.  (Details: http://msdn.microsoft.com/en-us/library/ff951631.aspx.)  This will be a new SQL Azure database which is a copy of the existing database.  You can use SQL Azure Data Sync (CTP1) to keep these databases in sync.  This still does not have a way to bring that database backup on premise for storage and historical retention.  Your best option at this point is to use SQL Server Integration Services until SQL Azure Data Sync supports on-premise to cloud synchronization which is expected in CTP2.

How easily can you keep the data up to date between on premise and cloud databases?

So the last questions lead into how to keep the data up to date between on premise and cloud databases.  The best option is most likely yet to come in the SQL Azure Data Sync CTP2.  When that is released, you will be able to exchange data between on premise and cloud databases with built in tools.  Until then, you do have some options.  First, you can look at using SQL Server Integration Services to keep the data moving between the two databases.  It is very straightforward to create an ADO.NET data connection and point it to a SQL Azure database.  This can be a source or destination for the data and it works very well.  Beyond that you can use BCP or T-SQL scripts to move data back and forth, but this involves more development work to complete.

As you can see, until Data Sync support is ready for primetime, keeping data synchronized between on premise and Azure databases will require some development on your end.  You will need to review the current options and stay alert for new options for syncing your data at the moment.

Next post

In the next post, we will discuss database size, cost, and effective use cases for SQL Azure.  Stay tuned.