Azure has two main services for persistent data storage for application: Azure Table Storage and SQL Azure. The really boil down to a normal SQL relational database, and an document based database. Initially Azure had only Table Storage, but it appears Microsoft was ahead of the NoSQL movement that has become popular of late and they were met with a strong cry for SQL. The good news is both options are robust and well supported, though in many cases its likely Table Storage will be cheaper than SQL.
GameMarx.com and XboxIndies.com share a single database. The database is running on MS SQL Express 2008. The sites are a good candidate for Table Storage. Under the hood, all of the content is cached to disk in document format. A game page on either site gets data from a single file that will contain all the data needed to render that page. This simplifies the site down to pretty much applying a template to a data file for more requests and is quite speedy. It was probably a premature optimization, as neither site does enough traffic to stress a modern database, but we built it for web scale.
Even though Table Storage is an option, time is a factor. Since I can migrate the sites to Table Storage any time, I will start off using SQL Azure. Thanks to reading Azure in Action I learned of the SQL Azure Migration Wizard. This tool makes it easy to export from an MS SQL database and import the structure and data to SQL Azure. SQL Azure is similar to MS SQL, but not a 100% match and requires several small tweaks to DDL statements. The Wizard will make all of these tweaks, and alert you to any structures that are not SQL Azure ready (SQL Azure does not like tables without a primary key for example).
In our sites we are using the ASP.NET Membership Provider and I was concerned that database structures needed to support this would not import cleanly into SQL Azure. I was glad to be wrong. Our entire database imported cleanly and after changing my connections strings a local dev instance of the sites were running smoothly against SQL Azure.
There was one bump in the road, and that was I did not have SQL 2008 R2 SP1 installed locally, only SQL 2008 R2. The Wizard requires SP1 to be installed, and if it’s not you’ll get some odd errors when you attempt to connect to SQL Azure. The good news is the latest Management Studio includes support for SQL Azure, so many things you can do via GUI instead of command line now. The support isn’t prefect though, the add user option for example just launches a query window with an template for adding a user via SQL.
Can I be so lucky the rest of the move goes this smoothly?
10-02-2011 5:57 PM
Michael C. Neel