3

We're looking to migrate some existing Excel/Access processes into a new system which will provide the users with a Silverlight frontend to run and view the reports instead of using MS Access. The initial idea was to have SQL Server 2008 as RDBMS.

The problem is that we've got some static data such as country codes, counterparties, etc which live in an existing Oracle DB. Since we do not want to duplicate that data (if possible), we were thinking of having a DB link between SQL Server and Oracle, but our firm does not allow that.

So the options are either duplicate the data or use Oracle as RDBMS - surprise, the firm does allow DB links between Oracle databases.

The initial idea was also to use WCF RIA Services, Entity Framework, etc which we're not sure they play well with Oracle, that's why it was decided to go with SQL Server in the first place.

Would you advise to go for Oracle so that we can just link the static data? Or use SQL Server 2008 and replicate it because it's "safer" to stay within the Microsoft land? To use or not to use Entity Framework and WCF RIA Services at all?

Regards.

UPDATE: Thanks everyone for your answers. Nothing is set in stone yet. We'll try to import the data instead of linking, as if the other DB goes down, our system can still carry on. We're likely to use SQL Server just because most developers are more experienced with it. Even if we used RIA Services, we can swap out the Data Access Layer and use other frameworks such those mentioned below.

Daventry
  • 39
  • 2
  • 2
    Its always a shame when people choose technology just because 'its microsoft'. There's a ton of really good stuff out there waiting to be used, if only you'd go look, you'd see some of it is way better than the MS offering. – gbjbaanb Jun 11 '12 at 13:05

4 Answers4

2

The DailyWTF is littered with companies that put in new systems for the sake of it rather than go with what works, learn from them :)

EF does (mostly) work with Oracle, but frankly, I'd avoid EF in the first place and go with a different ORM. My experiences with EF were not the best when we did a very large system using it. Fortunately, there are alternatives that are better - DevArt did some work to make an ORM that works with Oracle's features and it superior to EF, Castleprojects' ActiveRecord might be suitable for you, or SubSonic has a very good ORM, you could even use NHibernate.

So, I'd go with the existing Oracle infrastructure, it'll fit better with your company and that is always a big win, if only for the DBAs who can apply all their backup and maintenance expertise to your data.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
1

The key question to ask here to make a reasonable decision is

  • who maintains that static data, how (and how often) are updates/changes/bugfixes are provided?

If the answer is "The software developers of the System", then maintain those data outside of any database and provide some SQL scripts for initializing or updating the data. This can be done in a way you have to maintain the data in one place, either by using DB independent SQL scripts or by building a SQL generator for each system.

If the answer is "Some users connected to the Oracle system, and the data must not be instantanously available at the MS SQL DB", then better provide some extract-transform-load process (to be run in reasonable intervals) to get the data out of the Oracle DB into your MS SQL DB. Advantage: if the Oracle DB is going offline, your system still works, you have a much higher availability and the whole developing and testing will be easier since you decouple yourself from a third system.

Only if the answer is "the data must be instantanously available", think about using the same database (system) for the static data and your system.

I would normally not make a decision between MS SQL DB and Oracle because of "some static data". Both systems are mature and provide a rich development infrastructure. Ask your company/customer what database they prefer, this is mainly a question of which database know-how is already there, do they want to live with a mixed DB infrastructure or not etc. If the company is using primarily Oracle, then use Oracle. If they have both kinds of DB, ask what they prefer for the future.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
1

I don't know about "WCF RIA Services, Entity Framework" working with Oracle or not, but your company seems to rely heavily on Oracle; I would stick with it. In an enterprise situation, everyone needs to plan things out and get on the same page. Having multiple copies of data IMHO is a bad idea without some sort of link or replication. Obviously someone needs to think twice about altering any of these tables without considering your app as well.

JeffO
  • 36,816
  • 2
  • 57
  • 124
1

Does your software going to be read-only of that external database ? Does your software going update, insert, delete, that external database ?

If your answers are "yes", and "no":

(1) You mention "external [SQL] database". Wheter is Oracle, Ms.SQL Server, or other.

Several proyects, I have worked on, had several technical problems, among different "sql vendors".

I suggest to make a copy of that external Oracle Database to an internal [Put your local SQL Brand] Database. Using a schema, if not equal, as similar as possible, to the original one. Make a process to import the data.

Advantages

This way, you leave the source data untouched, problems of access, compatibility, security issues.

Disadvantages

Adding an extra layer of data and logic. Extra work.

(2) For the library framework, such as M.S. "Entity",WPF, whatever, you may want to check, your company's policy, and Technology Development choices at long term.

I understand this point seems not related, to the question, but, in real world, this affects, specially with M$ software.

I already met a few cases, where companies migrated from other database to Oracle, due to its stability (Im not related to Oracle or M$).

You mention "Silverlight", which has been officially deprecated, by M$.

Cheers.

umlcat
  • 2,146
  • 11
  • 16