Windows Azure migration smell: SQL Server over-utilization by Mark Seemann
Recently I partook in a Windows Azure migration workshop, helping developers from existing development organizations port their applications to Windows Azure. Once more an old design smell popped up: SQL Server over-utilization. This ought to be old news to anyone with experience designing software on the Wintel stack, but apparently it bears repetition:
Don't put logic in your database. SQL Server should be used only for persistent storage of data.
(Yes: this post is written in 2011…)
Many years ago I heard that role described as a ‘bit bucket' - you put in data and pull it out again, and that's all you do. No fancy stored procedures or functions or triggers.
Why wouldn't we want to use the database if we have one? Scalability is the answer. SQL Server doesn't scale horizontally. You can't add more servers to take the load off a database server (well, some of my old colleagues will argue that this is possible with Oracle, and that may be true, but with SQL Server it's impossible).
Yes, we can jump through hoops like partitioning and splitting the database up into several smaller databases, but it still doesn't give us horizontal scalability. SQL Server is a bottleneck in any system in which it takes part.
How is this relevant to Windows Azure? It's relevant for two important reasons:
- There's an upper size limit on SQL Azure. Currently that size limit is 50 GB, and while it's likely to grow in the future, there's going to be a ceiling for a long time.
- You can't fine tune the hardware for performance. The server runs on virtual hardware.
Development organizations that rely heavily on the database for execution of logic often need expensive hardware and experienced DBAs to squeeze extra performance out of the database servers. Such people know that write-intensive/append-only tables work best with one type of RAID, while read-intensive tables are better hosted on other file groups on different disks with different RAID configurations.
With SQL Azure you can just forget about all that.
The bottom line is that there are fundamental rules for software development that you must follow if you want to be able to successfully migrate to Windows Azure. I previously described an even simpler sanity check you should perform, but after that you should take a good look at your database.
The best solution is if you can completely replace SQL Server with Azure's very scalable storage services, but those come with their own set of challenges.
