A multi tenant data architecture

There is no one "correct" way to do anything in programming, even with a cloud-computing specific architecture such as Windows Azure.

A cursory search of the MSDN documentation reveals that Azure does in fact have some useful functionality to help you separate your client's data. It appears as if you could decide to give each client their own SQL server instance, or you could instead use a common instance that you either partition directly or leverage some built-in functionality to partition for you.

Each of those solutions have their own pros and cons, which you will need to evaluation on your own. Some conceptual guidance as to the selection:

• If you use a separate instance for each client you will have built-in separation for your clients' data, but some reduced ability to examine data from multiple clients. Microsoft may also charge more for the additional overhead.
• If you use the same instance but partition the data manually in a single database, you'll have to manage and adjust the various keys and indexes yourself. You obviously don't want to have a large client's 50,000,000 records slow down the query a smaller client's 500 records.
• If you give each client a distinct schema or database in your single server instance, query management becomes easier, but you may need to worry about server-locks. (On the plus side, a seperate-db-per-client may let you make the transition easier.)
• If you use Microsoft's bulit-in partition tools, they may very well wind up doing exactly what you want them to do. Or you may get 70% of the way throuh implementation and discover a need their tools do not cover, requiring you to abandon much of your work. (Research and proper models are important when using anyone else's toolchain.)

As for the question of "how do you move data from single-instance to multi-instance", I imagine it would largely be a matter of constructing your new architecture and then importing the old data. You'll want to know how to do that for future clients, anyway.

In the multi tennent systems I have worked on as a DBA and Developer we used 1 database per client. That database was completely self contained and did not rely on any shared database, not even for things like the states in the US.

To make standing up a new client easy a model database was created that had all the things like States prepopulated.

The reason we did not store addresses or other common data in a shared database is that it allowed us to move the databases around several different servers to normalize the load accross the servers we had. If a database needed a higher SLA due to a client upgrade we could just take a backup and restore the client database on the appropriate server and not need to worry if "the address for Stark Industries" existed on the new server.

The obvious question is what happens when client A updates the phone number or address for a customer? Should client B see that change? Wouldn't that lead to "duplicated" data if every thing is stored in seperate databases?

What if "Stark Industries" wants your Client A using a different phone number than your Client B? Then you will have to have a way to show that and store the different numbers in the Client database or move Client A and Client B to different instances. I am sure as you look through your shared database you will find additional instances of this problem of different clients wanting to use data differently.

For updates to the common tables a script that loops through each database is rather easy to write, a cursor on sys.databases and some dynamic SQL takes care of that part.

Overall while having a shared database may look good from a normalization standpoint once you start to look at different ways that clients use the different data, a shared database may not make sense any more. If you are going to go the route of seperate databases per client, just go full bore and go completely seperate with no shared data.