How to isolate SQL Data from different customers?

Question

I'm currently developing a service for an App with WCF. I want to host this data on windows-azure and it should host data from differed users. I'm searching for the right design of my database. In my opinion there are only two differed possibilities:

• Create a new database for every customer
• Store a customer-id to every table (or the main table when every table is connected via entities)

The first approach has very good speed and isolating, but it's very expansive on windows azure (or am I understanding something of the azure pricing wrong?). Also I don't know how to configure a WCF- Service that way, that it always use another database.

The second approach is low on speed and the isolating is poor. But it's easy to implement and cheaper.

Now to my question: Is there any other way to get high isolation of data and also easy integration in a WCF- service using azure? What design should I use and why?

Answer 1

You have two additional options: build multiple schema containers within a database (see my blog post about this technique), or even better use SQL Database Federations (you can use my open-source project called Enzo SQL Shard to access federations). The links I am providing give you access to other options as well.

In the end it's a rather complex decision that involves a tradeoff of performance, security and manageability. I usually recommend Federations, even if it has its own set of limitations, because it is a flexible multitenant option for the cloud with the option to filter data automatically. Check out the open source project - you will see how to implement good separation of customer of data independently of the physical storage.