I recently blogged about different multi-tenant data architectures, comparing and constrasting the Separate Databases and Shared Database, Shared Schema architectures, as well as noting what sorts of questions to ask when trying to ascertain which model to use. One of the disadvantages of the Separate Databases approach is that it can be difficult to view data aggregated across the databases:
Viewing data aggregated across the databases is difficult. I've touched upon this topic in an earlier blog post, Running the Same Query Against Multiple Databases. When you find a bug on one database and need to see whether it affects data in other databases there are not many tools at your disposal. One poor man's tool is sp_msForEachDb, but it's less than ideal.
And reader John Chapman added his two cents in the comments regarding this issue:
For lots of applications you actually have situations where there are users who need to be able to see data from multiple customers at the same time. For example you may have situations where you have external customers who see only their data, but yet have internal liaisons who need to oversee the activities of multiple customers. Therefore necessitating that they see data from multiple customers on a single screen.
I have ran into these situations before, which was a key reason why we used a single database shared schema. The application we replaced used separate databases and was unable to provide this sort of functionality.
Over the years I have created a very (very!) rough tool for querying multiple databases in a Separate Databases architecture. In short, you enter a query, select which databases to query against, and then the tool runs that query against each selected database and combines the results into a single <table> on the page. As you can see from the screen shot below (click for a larger version), the tool includes a multi-line textbox for entering the query to execute and a CheckBoxList of the databases to query. The results are included in a single <table>.
While the above screen shot shows a query that just returns a scalar value (one column, one row), it certainly works with queries that return multiple rows and columns. And with a little bit of legwork the tool could be enhanced to include rollup-type functionality, showing subtotals per database and grand totals across all selected databases for numeric columns.
To learn more about how I created this tool, check out my latest 4GuysFromRolla.com article: Querying a Multi-Tenant Data Architecture.