How to convert a web app with indvidual DBs to a cloud with a single DB?

Question

Hello, I made a e-commerce app the runs on C#. Each instance of this app right now has it's own:

1. IIS 7 app pool
2. IIS 7 website
3. SQL Server 2008 database

My partner told me that we need to dump this approach and move to a cloud. It seems like SQL Azure charges per database. So it will be very expensive for us to go this approach. Is there a way we can use a single giant database without having to re-write the entire data-access layer? *Or is there a cheap way to have hundreds of tiny SQL Azure databases?*

note: each database is only about 20 MB

Answer 1

Yes, SQL Azure charges you per database, but what you're really paying for is the size of the database. For example a 1GB DB will cost you $9.99 and month, a 10GB DB will cost you $99.99 a month. The databases in SQL Azure also currently have a maximum size of 50GB.

Based on this and what you've said about your app (possibly hundreds of DBs), and making the assumption that each database is going to contain enough data to make paying for 1GB of DB worthwhile, I would continue to use one DB for each instance.

If the data for each instance is actually quite small and even if you had hundreds of instances you wouldn't hit the 50GB limit then you would save money (but not time) by partitioning your data and storing it in just one database.

If cost is your primary concern and you're thinking about a rewrite anyway, I would consider using Azure Table Storage (AZT) where that same 1GB of data will cost you $0.15 a month to store (but you do have to give up on little things like foreign keys and queries with more than 1 table in them)

Answer 2

At 20MB per database, you'd cover about 50 such databases in a single 1GB Azure database.

With the new database sizes, your next size up is 5GB, and your billing is amortized on a daily basis, based on the maximum storage used for a given day. So, for the times when you're under 1GB, you're billed at the 1GB rate ($9.99 per month / days in month). Once you exceed 1GB, you move to the 5GB tier ($49.95 / days in month). This would be considerably less expensive than having 50 1GB databases, which would run approx. $500 monthly.

To combine your databases, you'd need some type of customer id or instance id that you'd have to add to your tables, to provide your partitioning across tenants.

I saw another suggestion by knightpfhor about moving to Table Storage for cost savings. While Table Storage is less expensive than SQL Azure storage, that would likely be a major application change, as Table Storage is non-relational and doesn't have stored procedures or any other support you'd normally get from SQL Server. Adding a partition / customer key is much less invasive than a wholesale storage-layer rewrite.