Large scale business application - Azure cloud database & storage issues

3
votes

We are creating large scale, database oriented business application. This business application is multitenant and we want to provide it as SOA (Software as a service) in Azure cloud. Azure SQL Database has limit of 150 GB only which is not sufficient for us. Our clients have huge volume of data that is likely to increase every year. Our application database is relational and complex and uses complex queries (Groups, Joins, Aggregate functions), stored procedures to fetch/save data. Therefore we are reluctant to use Big Table directly. There is a suggestion to break database into partitions and file groups, but we are not going for it. There is another possibility to keep only current year data into app and rest into other partition/backup database, but this does not suit our application as past data is accessed frequently. If we use Azure Table our complex operations will be done in memory, which is not possible because of limited memory resources. We are thinking however to make a mix of Azure SQL Database and Azure Table. But we are unable to find any suitable example that uses a mix of both.

We are looking for answers of following questions as well?

  1. Is azure suitable to keep very large scale of relational data (TB's) or should we have our own database servers to support this?
  2. Should we use only Azure Table and NOT Azure SQL?
  3. Should we use mix of Azure SQL and Azure Table storage? Is there any sample architecture/example that guides us about it?
1
azureazure-sql-databaseazure-table-storageazure-storage
"There is a suggestion to break database into partitions and file groups, but we are not going for it." Why? Sounds like the best solution. – Craig

1 Answers

2
votes

The new Standard and Premium Azure SQL Database SKUs now support 250GB and 500GB respectively. Regardless, Azure SQL Database is very much a scale-out model for large amounts of data. A preview of an elastic-scale feature for Azure SQL Database has just been announced.

There are good reasons to store data in the most appropriate location - Azure Tables provides much more cost-effective, high-scale storage than does Azure SQL Database. There are definitely reasons to use each for the data that is most appropriate - high-scale, semi-structured data in Azure Tables and relational data in Azure SQL Database.

1) If you are creating a new application you should consider Azure SQL Database for your relational data and only move to SQL Server in Azure Virtual Machines if that won't work. While SQL Server in a VM seems easy, things start getting trickier once you start adding traditional DBA activities such as making the deployment HA. If you have high-scale relational needs you should consider sharding.

2) Your choice should not be one or the other, but about using them both where appropriate.

3) Microsoft Patterns & Practices has a number of eBooks you should look at to help you with your architecture.