Dynamic Database / Schema in Entity framework on Windows Azure / SQL Azure

Question

We are currently in process of developing SAAS application codename FinAcuity which will be hosted on Windows Azure platform and primary database will be SQL Azure.

Here are some Technical Specifications for our product:

• Development Environment - Asp.Net 4.0 with MVC 3 (Razor), Entity Framework
• Database - SQL Azure

Here is our Business Case:

• Our product is a SAAS product, And as it will contains Financial Data of Client, we are going to provide separate database to each client to achieve higher level of multi-tenancy, Data Isolation & Security.
• Now Client can create multiple companies under their account and these companies will be separated by Schemas under particular Client DB.

Note: Table structure will be same for each Schema.

Here are some scenarios to will give you a deeper view of our application processes.

Scenario 1:

• To provision new database upon client registration, we are going to run Store Procedure that will create database with basic structure.
• Our Doubt: Is this correct way of doing it on SQL Azure or there is some other way for it?

Scenario 2:

• For accessing multiple schemas under client database, we have dynamically generated SSDL file for individual schema and used that file for connection.
• Our Doubt: Is there any other way of doing it, like using same SSDL file instance for multiple connections and passing Metadata for connection?

Scenario 3:

• As our application supports ad-hoc querying and dynamic table creation from Excel file, we are going to provide wizard that will run Store Procedure in back-end and create that table dynamically from Excel file upon header selection from wizard under particular schema for client database.
• Our Doubt: Suggest us a better way of doing it, if any?

Scenario 4:

• Now as the new table is added to schema, we have to update EDMX file to get data from that new created table. To do this we are going to run Store Procedure that will fetch data from newly created table.
• Our Doubt: Is there any way of updating EDMX file runtime and getting data?

Need advice for best possible solution for each scenario that is listed above.

Thank you in advance.

Best Regards - Sahil

Answer 1

I think this is a little too much for 1 single question.

And I personally think you look at it from a wrong perspective. Why did you choose Entity Framework and SQL Azure? Do you really think these are the best technologies to address your problems?

I suggest you take a step back and investigate what other technologies could be used. Because what you're asking here looks like a schema-less solution, and SQL Azure / SQL Server wasn't built for that IMHO.

You can start by looking at a NoSQL (schema-less, key value store) solution, possibly in Windows Azure. There's a whitepaper that will get you started on that: NoSQL and the Windows Azure platform -- Investigation of an Unlikely Combination

Windows Azure Table Storage is a key-value store that could solve some of your issues:

• Customer isolation / Multiple schemas: WAZ Table Storage supports partitions, you could partition your data per customer instead of putting all the data together.
• Provisioning: No need to provision anything. Get a storage account and you can get started. Then you can simply have some code that writes data in a specific partition for that customer.
• Cost (not mentioned in the question): Table Storage is much cheaper than SQL Azure
• ...