sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

SQL Azure Revisited

Issue 3 Number 2 February

Is SQL Azure Ready for Primetime?

It’s been a couple of years since I reviewed SQL Azure at the end of its beta period. My original assessment was “Not ready for prime time” but a lot has changed since then and I felt it was time to get a little closer to the current version of SQL Azure.

So, I signed up for an Azure subscription and created a database to play with. When you buy a SQL Azure subscription you also get a Windows Azure instance in which your database runs and which you can use to host the applications that access the database if you like.

This is not going to be a how-to, step-by-step article. Azure is too big a subject to cover in a short article like this. We are going to talk about why you might find SQL Azure useful and the basic things you should know before you decide whether or not SQL Azure fits your needs.

For Azure limitations that we do not deal with explicitly in the text , there is a list of common SQL features that are not supported in SQL Azure at the end of this article.

My cloud play uncovered a number of important limitations in SQL Azure, but it also showed me that Azure is now worth a serious look as a development option.

Certain kinds of databases in certain kinds of situations can be deployed in SQL Azure and take advantage of the undeniable benefits of a cloud database. However, the existing limitations to SQL Azure will make it difficult or impossible for many types of databases and database applications to be adapted to the Azure platform with its current restrictions.

Azure is changing rapidly and I am confident many of the limitations I discuss here will be removed in future versions of Azure. However this is now, and this is what I found.

Getting Your Head Around Azure

It is important to understand that you will not have a SQL Server instance when you move to SQL Azure. You will have only a database or databases. The implications of having no control over most server-level and physical implementation details need to be well understood before jumping into a cloud project.

To be sure, you will have a master database as well as your own user-defined database(s). But this master database is not really a database. It is an interface to SQL Azure internals that allows you to manage, in a limited way, instance level objects like logins. Other instance-level and physical implementation issues like server configuration, file placement, server security, etc are invisible to you.

This is both the strength and weakness of cloud computing.

The Advantages

The advantages to SQL Azure and cloud databases in general are well-known so I won’t spend much time on them. In the order of my favorites are:

• The ability to scale up and down automatically, as needed. You do not have to maintain a huge database infrastucture all year long just because your ecommerce site needs that kind of power to handle the Christmas rush.

• You get 99.9% percent uptime without the cost and hassle that is involved when you have a local database server.

• You do not need to buy, maintain or replace hardware that will be obsolete in three years.

It is not hard to sell cloud databases to anyone who is responsible for IT budgets. It is harder to sell it to a DBA or developer who has to live within Azure’s limitations.

Size Matters

In my opinion, the important limitation for most enterprise-scale users is the maximum database size. Currently, a business edition database in SQL Azure cannot exceed 50 gigabytes. If the database you have or the one you want to create will never grow past 50 gigabytes, then this restriction will not affect you.

It is true that you can have more than one database in Azure, each with a maximum size of 50 gigabytes. Breaking up your database design into several separate databases is often mentioned as a workaround for the size limitation. The term used is ‘sharding’ the database. However, there is a huge gotcha to sharding.

Azure databases can’t talk to each other. SQL Azure does not support remote procedure calls, linked servers, functions like OPENQUERY, or 3 and 4 part identifiers to allow databases to communicate at the TSQL level.

It is possible to write an application that can work with several totally independent databases but it is a very different architecture than you are probably used to. Sharding an existing database to accommodate Azure’s size limit often means a major rewrite of the applications that access the database.

Better Options on the Horizon

As mentioned above SQL Azure changes almost daily. It is certain that Microsoft will be increasing the maximum size of an Azure database as fast as it can. If 50 gigabytes won’t do it for you now, wait for a while. The limit might be raised significantly relatively soon.

Tools to make it easier

Microsoft has announced that a partitioning tool will be available soon to help you shard your large databases. That will be a great help, but sharding the database is only a part of the problem. The other part is writing or re-writing an application so that it can use the database shards.

Some very useful wizards and programs are available or are in development. They will help you move existing databases into SQL Azure, sync your Azure database with another database, etc. However, there is no room in this article to describe all of them. It should not be difficult for you to find what you need on the web.

Security

The data in your databases are the crown jewels of your organization. It is a difficult decision to put those jewels in someone else’s care. if your database contains data so sensitive or critical that you are reluctant to put it in the cloud, follow your intuition. Keep it where you feel most comfortable.The cloud is not the answer for every database.

However, based on a thousand or so SQL installations I have looked at, I think Microsoft is going to take better care of your data than most organizations or co-location facilities take care of theirs.

Your Azure database and at least two mirrored copies will be running on separate servers somewhere. Microsoft seems unlikely to lose your data.

All connections to the database must be SSL encrypted and only connections from IP addresses listed in the firewall are permitted. Complex passwords are strictly enforced.

Backup

Backup however is a concern for me. There are no backup or restore commands in SQL Azure.

Cloud advocates minimize the importance of backups by pointing to always having redundant copies of your database but this availability, not data safety. Sure, the cloud might hold three copies of your database, but what happens if there is an accidental or malicious deletion of data? In a matter of seconds the mirrored copies are going to be missing that data too.

Nothing really replaces the ability to restore your database to a point in time. However, Redgate and other vendors are pushing out Azure backup utilities. I haven’t examined any of them, but hopefully that problem is solved.

One other backup concern is that you are charged for bandwidth out of your Azure instance. If I understand things correctly, you will pay bandwidth charges for bringing your backed up data down to your local environment and that could be expensive if you backup often.

Cost

Another important thing to know about SQL Azure is what it will cost you. This is not as simple as it sounds. The pricing model is unbelievably complex. Just after I signed up for my subscription, I got a call from Azure support to explain the pricing I had subscribed into.

This courtesy call to new subscribers has to cost a small fortune. That tells me that Microsoft might be having a lot of problems with subscribers not clearly understanding what the true cost is.

I strongly advise that you study the pricing carefully before you buy. That is not simple because pricing for the Windows Azure components and SQL Azure components are scattered around the Azure site.

The different Azure features are listed on this page and you can drill down from there to the details that include pricing for each option.

To show you the range of costs, here are examples of the least and most expensive set of options for a single database subscription.

I believe the figures are accurate at the time of this writing, but you should not depend on this pricing. Check before you buy.

The Long and the Short of It

Least Costly

Database Edition: Web, 1 gb

• Compute Instance: Very Small

• Monthly Fee: $9.99

• Compute Time: $0.04 (about $30/month if you deploy your application in Windows Azure)

• Bandwidth: 5 mbps

• Memory: 768 mb

Most Costly

Database Edition: Business, 50 gb

• Compute Instance: Very Large

• Monthly Fee: $499.95

• Compute Time: $0.96/hr (about $700/month if you deploy your application in Windows Azure.)

• Bandwidth: 800 mbps

• Memory: 14 gb

Compute charges apply only if you deploy your database application in Windows Azure. You are not required to put the database application in Windows Azure but if you do, be aware that hourly compute time charges start the moment you deploy your application in Windows Azure and continue to apply until you remove the application. It does not matter how much the application is used or even whether the application is used at all. Don’t create a test application in Windows Azure and forget about it. That can be costly.

The prices above reflect typical single database subscriptions. They do not cover charges for things like storage, caching, queues, etc. If you are planning to use any of those features, make sure you know the cost.

Other important limitations:

What follows is not an exhaustive list. I have kept it to what I feel are significant problems with no practical workaround. If your applications require any of these features, SQL Azure is probably not for you, at least in its current incarnation.

• Idle connections and queries that exhibit excessive resource usage will be killed by SQL Azure. To my knowledge no one has quantified what ‘excessive resource usage’ means.

• Read committed snapshot is apparently the only isolation level permitted. The documentation is ambiguous about whether there is a way to change this, but I don't think there is.

• OLE DB is not supported for connecting to SQL Azure

• The USE command is unsupported. You cannot switch from one database to another using TSQL. Your application has to open another connection.

• User-created CLR is not supported. System level CLR datatypes and some functions (like geospatial functions within the datatype) are supported.

• Your Azure database uses the default collation (SQL_Latin1_General_CP1_CI_AS) and cannot be changed. You can only change collation at the column level or expression level.

• Fulltext search is not supported

• Filestream not supported

• No xml indexes are permitted

• SQL Profiler can’t be used on a SQL Azure database.

• SQL Agent is not supported

• No trace flags can be set

• Only a limited set of data management views are available. In general, dmv’s that reveal instance level or physical implementation details are excluded.

In Summary

Don’t spend your energy trying to push a square peg into a round hole. Use the guidelines above to determine if the SQL Azure platform is appropriate for your project before you get too far along.

Azure can be very cost-effective, but the cost is not trivial. Make sure you understand the true cost of your cloud databases before you commit.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Azure Revisited

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator