Best option for analytical data currently in Access and process data in SQL Server

[Michael Fuller]

We are currently searching for a solution to manage data. We have a bunch of laboratory and field analytical data in an Access 2003 database. We also have some plant process data that is recorded in a Wonderware Historian data server, which I think is based upon MS SQL Server.

We often have need to look at some of the data from each database together, but querying and managing the 2 seperate databases is somewhat costly and prone to poor management (lack of attention).

I can think of 3 possible options to move forward:
1. Keep the existing databases, but find a software tool that can query both databases seamlessly to provide the data that we need for our evaluations/decision making. Is there such a tool/software?
2. Migrate the Access Data into the Wonderware Historian database. Then use the available Wonderware products to query the historian database.
3. Have someone develop an entirely new SQL database that incorporates all of the data in the current Access Database and is updated by or linked to the Wonderware Historian database.

Does anyone have any experience that they can offer or ideas on why one option should be preferable to another?

Thanks,
Mike

[ck9663]

One word: COST:) (aka...Funds....Manpower...Time)

Assess your current and immediate need, including cost analysis. Your option might be the more practical option, since you already have the infrastructure.

Option 1: The most cost effective, but not efficient. You're going to have to put in a lot of automation and procedure to make sure you enforce data consistency.

Option 2: The most cost efficient. SQL Server can also read Access which makes the transition easier. But it'll cost more then option 1.

Option 3: The most flexible, not to mention the opportunity to fix any nagging errors, change some business rules, create new reports, etc. But this is the most costly, in terms of budget, time and manpower...

Do some cost-benefit analysis first and decide from there.

Good Luck!!!

~~ CK