Re: Migrating from MS Sql Server to Oracle

I would say that it is technically possible to move the tables in an hour. Just reverse engineer them with a tool such as ERwin and then rebuild from generated DDL. Moving the data is impossible to estimate as you gave no volumes. But since it is SQL Server likely you are talking less than 10 million rows so likely a day including writing the SQL*Loader control files.

But when it comes to the code ... you are not talking about migration. You are talking about a complete and total rewrite. Unless, of course, you have some over-riding urge for bad performance, corrupt data, and unhappy management and customers.

Oracle is not just SQL Server with the profits going to some guy named Larry instead of some guy named Bill. It has an entirely different model that involves multiversioning ... something that doesn't exist in SQL Server. The transaction model is different, the locking model is different, the resource issues are different, etc.

You should start buy purchasing a copy of Tom Kyte's book "expert one-on-one Oracle" and reading the first three chapters. Then, and only then, make an estimate. You don't say how many stored procedures, or what they do, or how complex, but my totally blind guess is that unless you want to be responsible for a mess ... you are talking about months.

PS: In the Oracle world ... we test stuff before we throw it over the cubicle wall. So figure in time for writing unit test scripts and actually using them.

And a word of warning about migration tools. From what I've seen they produce remarkably bad code often not understanding the differences between static cursors and ref cursors and other important issues.

Good luck.

Daniel Morgan

Mark Kessler wrote:

> Hello all,
> I am facing the problem of migrating quite a big database (+100 tables
> and stored procedures) from an MS Sql Server to an Oracle 8i Server.
>
> No I am trying to get an estimation of the necessary effort to put
> into this.
>
> Assuming the following situation: Being neither an expert in Ms Sql or
> Oracle but more an advanced user, and only starting to get inside into
> the database I have to migrate..
>
> Would you say that it this possible to accomplish the migration in a
> matter of days or weeks, when using a migration tool like the Oracle
> Migration workbench? Or do I have to expect to put hand on every
> stored procedure manually?
>
> I would appreciate any input very much.
>
> Mark Kessler
Received on Tue Mar 19 2002 - 10:58:16 CST