Querying Multiple Databases 1

[bboffin]

I have a need to write some common queries that will work across multiple databases (all on the same server). The databases all have the same schema but are 3rd party so I am not allowed to create Stored Procedures or User functions in these databases. For this reason I have a separate database to hold the stored procedures and use synonyms to access the tables in the databases.

The number of databases may increase as the company involved is buying up other companies many of which use the same software/database combination. At present there are at least 4 such databases in use.

The queries will be executed from a .NET application so unions can be achieved by not clearing the data from a datatable before filling it.

I can see a number of ways of doing this but I'd like to know if there's something I'm missing.

My intention is to provide a means of creating new Synonyms for each 3rd party database in the private database and then to create stored procedures that use these synonyms from a master set of stored procedures. All this seems to be pretty straightforward even though to my mind it's a bit clunky. If only SQL allowed textual substitution!

So have you any suggestions as to a better approach to this problem?

Regards

 

[gmmastros]

Here's an idea....

Create a new database on the server, call it something like "Combined". Then, create a bunch of views within the database that combines the data from the 4 databases.

For example, suppose you had a "People" table within the 4 databases. Also suppose that your 4 databases are called "Apple", "Banana", "Pear" and "Grape". You could create a view like this:

Create View dbo.People
As
Select 'Apple' As DatabaseName,
       PersonId, 
       Name
From   Apple.dbo.People

Union All

Select 'Banana',
       PersonId,
       Name
From   Banana.dbo.people

Union All

Select 'Pear',
       PersonId,
       Name
From   Pear.dbo.people

Union All

Select 'Grape',
       PersonId,
       Name
From   Grape.dbo.people

Now you can query the People table on the Combined database and you will have access to all the data, and you will even know which database it is coming from.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bboffin]

Many thanks. That's an approach that hadn't occurred to me. I'll think it through and see if it will provide what I'm looking for.

I already have a separate DB on the server that acts as the 'controller' for the queries.

The main purpose is to provide a means of producing company reports across multiple databases and user reports on a single database. The main problem is that it has to be dynamic as the company is forever buying up other companies using the same software/database. For example the sort of Company query they need is 'How many new items arrived yesterday (or last week)' while the User reports are more specific.

The actual production of the reports is carried out by a Service and the reports are automatically emailed to the appropriate recipients.

At the moment in the Maintenance application I have the ability to generate the table synonyms when a new database is added to the system and I can create SPs automatically from SQL generated by a built in SQL Query designer. I can generate an SP for each of the different databases and test execute the query and its report as well.

It did occur to me that possibly I should be looking an an OLAP database being fed from each of the individual databases but at the moment it's not clear what data would need to be aggregated so I've left that one aside.

Regards



Bob Boffin

 

[gmmastros]

If you decide to follow this advice, the only thing you'll need to do is to modify the views in the controller database whenever you acquire a new company. It probably wouldn't even be all that difficult to automate the creation of the views.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom