Unanswered: Migrating from Oracle 8i to SQL Server 2000
I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance
Oh boy. What a question. First a disclaimer to my answer, I know way too little about real Oracle practice to give you a dogmatic answer. I’m not religious about what database is better than the other. But I have, as an SQL Server professional, been contracted in to sort out the problems Oracle guys get themselves into when they convert over.
Firstly, Oracle people worry about the infrastructure of the database in a way that just doesn’t apply to SQL Server. What index is stored where in the storage, how the data is partitioned into files, this sort of stuff. By and large SQL Server does all this stuff for you. Worry about the semantics and the syntax of the SQL and focus on that. Let SQL Server do the system level stuff.
In general, Oracle offers a more feature rich and complex SQL than SQL Server. SQL Server is kind of like a reduced instruction set SQL, it makes you write long winded SQL, but it executes it better as a result of the simplicity. You wont find hierarchy support in the language for example (Connect Using is it?) – you code that yourself. So you need to identify the areas where you’ve leveraged Oracle’s richer feature set, and research the best techniques to code those up manually in SQL Server. And you must get your head out of sequence mode.
SQL Server uses automatic IDENTITY values for populating primary key integer columns. This feature is mondo humungo important for getting the performance out of SQL Server that keeps it at the top of the performance charts. If tables have IDENTITY primary keys, the indexing subsystem enforces and leverages referential relationships with awesome efficiency. This is how you must design your data schema – integer identities to enforce referential constraints. Do not under any circumstance do as I have seen two separate post-Oracle teams do – not like the fact that you don’t have sequences any more and write your own integer identity making routines. You will weep over the performance of your system. Make sure you understand what index clustering is.
Next big mess up area is what Oracle calls materialized views. You don’t have exactly that feature in SQL Server but you do have schema-bound Views. Understand the difference. Also stored procedures return stuff differently in SQL Server, you probably want the kind of functionality that user-defined functions actually deliver, when looking for a return table.
Finally, the one thing Oracle just doesn’t have – at least the last time I looked at it – was Query Analyser. This tool will be your home as long as you develop over SQL Server. It is awesomely better than anything you can get with Oracle without paying stupid amounts of money. This is the one SQL Server application you must master.
If you want one good book that will explain to you why SQL Server is the way it is in terms of the hood (both under and over) it’s Kalen Delaney. Check out Microsoft.com SQL server homepage for migration resource kits, and google, google, google.
the more efficient the ora stuff is, the less headache you're gonna have. but i strongly recommend to analyze the database structure very thoroughly before jumping into it. also, question your management one more time about the necessity to do it. i bet at least some partial recompiles of the front-end will be required in order to complete the task. considering this, does the company have enough resources to proceed with the challenge?