System Design Options(lengthy)
Scenario - Company has major primary system(third party) which uses Oracle (on VAX). This primary system does not meet all their needs so they have decided to create mini-applications (about 50) using VB and SQL Server 7.0 - the Oracle data would be strictly read only and is used as Lookup tables. Each mini-app would have less than 20 users, some only contain one or two screens and the data is quite small. One of the larger of these apps contains less than 50,000 records over 12 tables (and its been running for a year).
The few apps that were created before I arrived where strictly client server. Pull data from Oracle, SQL Server. Data retrieved from Oracle was then saved in SQL Server. Unfortunately if the Oracle data changes it is not reflected in SQL Server.
Just trying to figure out all my options. We want to get away from having to install the Oracle Client on every machine (licensing issues). We also use Crystal Reports, as many of these apps are created for decision making analysis.
My first thought was to use replication but a new app that we are developing requires the users to enter a record into the Oracle system and then go straight into their VB app. This means replication must be immediate. Is this possible between SQL Server and Oracle? How would it be done, Pull Subsciption or execute a DTS package every few seconds?
I then thought of creating MTS components that would create a recordset from a combination of SQL data and Oracle data. How would I say get a Customer number from SQL server and the Customer Name from Oracle returning one recordset to the Client application?
Does anyone have a lot of experience with Linked Servers and SQL Server(7.0)? I was told that linked servers were not very reliable?
What methods have you used in a similar scenario? If you have any documents or any web links I can read I would appreciate it.
Thanks for your time.
