How to amalgamate client databases into one database?

Question

In my company we have a selected list of companies that are using our in-house built tool (e.g. Northwind).

When we make changes we deploy these to all our client locations.

The structure currently is: the application is installed at the client's location and the databases sit with them.

However, we would like to consolidate all this information into one database and clients will connect via web services for any data requests.

For example....We have deployed Northwind App and Db to company X, Y and Z and would like to create a single database to maintain all these company's data.

We have reviewed one option which is to create a field for the Company to associate it with the various tables and another option is to create a schema for each company and in this way we can allocate permissions to the relevant company. Is there an alternative to this and what are the pros and cons to the ways we could do this.

One con with adding a company field, is that we have to cater for indexes being the same in all the client databases and this makes it more difficult and the performance of the app as a whole due to multiple requests to the same db. Please Help?

Note: Using Sql Server 2008

Answer 1

Research "multi-tenant database architecture". (For your purposes, think of one tenant as one client.) You'll find a spectrum from "one database per tenant" to "every tenant in every table".

Read carefully. Writers in this field can confuse you. Expect technical terms like shared schema to mean different things to different writers.

See this SO answer for tradeoffs.

For your first step, I wouldn't consider anything besides simply moving those client databases in-house. Just doing that is going to give you and your application programmers enough headaches. You don't need an architectural change on top of it.

That will also give you time for research and testing.