Azure Database Design, partition out data tables vs federations?

Question

I am looking at the viability of moving our .NET web application to Azure. The multi-role setup to handle the web server aspect seems more than capable.

I have reservations with regards to the Azure Database performance and adopting the right scale-out strategy for our needs. We use a single database comprising of 120 tables, 80% of the transactions run on 10 tables. The remainder are used for various account level settings and global references. The largest table consists of 5 million rows and is manipulated using a set of triggers on the remaining 9 of the big tables, each of which in-turn holds anywhere between 500k and 250k rows.

My initial thoughts were as follows:

1. Move the largest table into it's own database instance and reference it using a SYNONYM. I now realise Azure DB doesn't appear to support SYNONYMS across database instances.

2. Use Federations to dissipate the workload across more Azure DB instances. Our DB is only 5GB so perhaps this is a premature option?

3. Use a higher spec Virtual Machine with SQL to serve the database.

I appreciate there are many unknowns here and I don't expect a definitive answer, I'm just looking to see what experience the Stackoverflow community can offer.

Additional information

• Current setup: Single SQL Server 2008 R2 instance with a single database of 120 tables running on a decent spec multi-core server with 12 GB RAM.
• Current performance is very good, to the extent that we could trade some in for scalability.
• Database is growing by 10% per month and relies heavily on relational data, triggers and complex stored procs, making it difficult to use Azure tables as an alternative.

Answer 1

You are trying to solve two problems at once, which may not be the best approach. First, you are migrating an existing application. Second, you are trying to develop a scale out architecture. If you try and do both of these at once, you are going to run into architectural problems. I suggest that you migrate the application first, without too much concern about the scale out. Once you have the application running, you can then develop a more scalable architecture. You will find very few solutions that have scalable, SQL, and low cost together — so any 'scalable' solutions that you come up with, with such a high dependance on SQL, are going to have a few painful compromises.

In order to build something that is more scalable, you are going to have to take a close look at, and plan extensive rework, of your existing architecture. Break the application into separate workloads, ditch your high dependency on T-SQL, triggers, and complex stored procs... and a few other things. The need/business case for whether or not this is worthwhile depends on your application roadmap.

Assuming that you have longer term good reasons to move to Windows Azure (cheap for existing app is not one of them), then you are currently embarking on the first step of your migration strategy. I suggest that for this first step that you change as little as possible and simply just 'get it working'. In that case, it may make the most sense to put SQL on a VM — if nothing else but to give to more control and leeway. After everything is running (step 1 of your migration), then you can look at further phases of migration. Steps 2 to n mean that your application architecture will change dramatically, making more use of table storage, and less of SQL. So by step n - m the performance and/or scalability of SQL will not be a problem.

The data model of a cloud application is more complex, and requires careful consideration. This is something that I have written about in detail in data model of CALM.

DON'T PANIC.

If your application has a mundane future, then a co-lo hosted solution may be the best — where you can specifically configure a database server that meets your needs. If you have high ambitions for your application, then over time you can migrate it to a much more scalable and cloud-friendly architecture that runs well on Azure.

Answer 2

I'd go with option #3. SQL Azure can be somewhat... unpredictable in its performance, especially when dealing with performance sensitive queries over large amounts of data

Answer 3

The VM option will be your easiest path especially if your not up to making some changes in your app.

SQL Federations will require changes to your app (the USE FEDERATION thing).

On top of that, for SQL Azure, you will have to account for transient fault handling/throttling. In addition, you'll have to verify that all the existing features you're using in SQL Server are fully supported (e.g., partially supported TSQL, CLR support, etc...)