I am always hesitant to use the word “Best Practice”, and even “Best Known Practice” is a little wrong. But this is the best practice I know of, given the situation that I’m in.
While I’m aware that data-warehousing techniques might be better, the following is a fairly easy way to integrate many databases together, at least for the purposes of extracting and analyzing data. They key is to make a “Meta-Database” that, for the most part, only contains Views.
Most of the views are what I’m calling “Mirror Views” (I hope if there is a term that has already been coined, someone tells me, so I can use that word instead.) Mirror Views are simply a View that Selects * from an equivalent table in another database. By using these views, I can write additional views to work with them without having to write in the original database, thus I do not void any support options from the original database vendor.
In SQL Server, I use either sp_tables (for databases hosted on the server) or sp_tables_ex (for linked servers), to retrieve the name of all the tables that I want to make Mirror Views from. I am pretty sure I could do some sort of T-SQL set of commands to convert these into views, but my T-SQL isn’t that high, so I copied the output into a CSV, then into a spreadsheet and actually wrote formulas to make the Views in the form DBName_TableName in my Meta-Database. (I would greatly appreciate if any readers who can share some code to do this in T-SQL, as I know the spreadsheet is probably not a best practice, but it gets the job done!)
This technique works easy for databases that are hosted in the SQL server, but for other databases hosted either on other servers, or that are on different platforms (such as MS Access, MySQL, etc.), then using a linked server is the way to go. BUT, be forewarned, linked servers are not always as easy to setup as one would hope. I will be writing more about this in a future posting, giving what I have learned through the school of hard knocks.
I am trying to move towards using Free and Open Source software more often, and so if anyone knows how to do something like linked servers in MySQL or Postgre, please let me know, as I’d like to use that solution in the future.