Re: Migrating from 6.5 SQL Server to Oracle 8.1

ramsfanatic9999 wrote:

> Newbie here trying to understand the general considerations when
> migrating from SQL Server 6.5 with a PowerBuilder from end to Oracle
> 8.1. If our in-house IT staff try to do it, versus the current
> consulting firm, what do we need and need to consider knowing that we
> would like to minimize co-operation from the consulting firm.
>
> I understand much of the application is written with stored
> procedures. So starting there what are the options when making our
> move over to Oracle? Do we have full access to these procedures? Any
> other considerations?
>
> Thanks in advance!

The architectural differences between Oracle and SQL Server are considerable. They start with the most basic concepts and architecture and extend to best practices as to how things are implemented for stability, security, and scalability. Here are some of the big ones to consider.

1. Oracle uses a multiversion read consistent model for transactions. Thus reads don't block writes, writes don't block reads, and transactions designed in SQL Server may corrupt data in Oracle and visa versa.
2. In Oracle there are temporary tables but they are rarely if ever used as they are not required.
3. Oracle has unlimited row level locks and not concept of pages or lock escallation.
4. The vocabulary is different. A database in SQL Server is a schema in Oracle. Get a SQL Server to Oracle dictionary
5. Oracle does not have autonumering columns. Oracle uses an object type called a sequence.
6. Oracle has trigger types not present in SQL Server
7. Oracle has packages for consolidating procedures and functions with capabilities not present in SQL Server

I would strongly suggest that you go to http://tahiti.oracle.com and look up the Concepts and Architecture documents before proceeding. And preferably hire someone with serious experience migrating applications to Oracle. I've done this quite a few times and the difference between success and failure is unlearning SQL Server and learning Oracle.

Of course, for migrations the other way a similar laundry list is possible so none if this is to say that one is better than the other - just that they are very different.

Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp Received on Mon Feb 10 2003 - 22:31:30 CST