Moving local SQL Server 2008 databases over to a single Internet based server

Question

Has anyone any experience moving a number of identical SQL Server databases (with unique data) from individual local servers to a single internet based server?

We currently have 10 companies using our Windows app which uses SQL Server 2008 for data storage, but we are finding that maintaining the databases and ensuring they are backed up etc is a major headache as they are all on individual local LANS.

We are now looking into the prospect of increasing the client base to 40-50 organisations and also needed to report on the data across all the organisations.

If we simply created 50 identical databases on our own internet based server, we could achieve this without too much of a redesign, but is this the best approach?

Or should we merge all the databases into one and create an extra column to identify the organisation so they only see their own data?

Each organisation has between 2-10 staff members and input about 5000-10000 records per year consisting of 20 fields of mainly chars and ints so it's not too enormous.

Any guidance would be appreciated.

Answer 1

If all databases contains some common tables with same data, the best solution is to merge all the databases into one with partitioning of tables with different data for each organization by new organization column, though.

Answer 2

You'll probably find that re-architecting the whole app to only allow access to data identified by the organization column a lot more development work with the possibility of leaking data from one organization to another if it all isn't thoroughly tested.

Also you'll probably find that having lots of databases on a single server might help with scaling things up at a later date, as you can move some databases off to another server. For backups, a single maintenance plan can backup all databases on the SQL server.

In the longer term I think this is probably the best solution without knowing much more about how the database and app execution details and planned growth over time.

Answer 3

You are most likely better served to keep the databases separate. We had a similar issue and the need to assure customers that their data would not be mixed with others (in case of a security problem) was the primary concern, although the ability to move some customers to another server is a good one as well. Also, with separate databases you can have minor modifications to one customer without affecting them all (either for customized solutions or different versions if one doesn't want to upgrade, etc).

We created a single "global" database to track information about each customer (including the database name and server their data was located on). Then you can do most high level queries against that and rarely have to go through each individual customer database.

We also created some scripts/batch programs that would run SQL queries against all (or given) databases so that updates or reporting queries could be run against 50+ databases very quickly without any extra work. Those scripts are definitely worth the effort to develop, and there might be third party applications that do the same thing.