migrate Oracle to sqlserver

Hi

I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.

Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5

How good is the migration assistant from m$$ for
migrating from oracle to sql2k5

Does it migrate data as well as procedural code ?

Do you anything for me to watch out for best parctices
migration guides

You opinion would be higly appreciated

regards
db2hrishyThere are a number of things to be aware of, the transition can be challenging, and once you've done one migration it will forever change how you view both Oracle and MS-SQL.

First and foremost, Oracle requires a row-oriented view of data in order to acheive good performance, and often to do anything at all. MS-SQL is exactly the opposite in that you need to abandon the row-oriented view of data (especially using cursors) in order to achieve good performance. This is a huge mind shift, and it is usually the hardest thing for a long time Oracle DBA to master.

Oracle provides PL/SQL, which spans several layers of the OSI model. MS-SQL provides many tools that together offer more functinality than PL/SQL, but they are many tools instead of just one tool. This can be a blessing or a curse depending on how you look at things.

The migration assistant provided by Microsoft is good, but like any generic migration tool it has both costs and benefits. Since I'm familiar with both Oracle and MS-SQL and have only had to migrate from Oracle to MS-SQL, I generally don't bother using any software assistant at all. As the auto makers are fond of saying: your milage may vary!

SQL-2005 provides two tools that make the migration of the data almost trivial. One tool that I couldn't live without for coversions is a Linked Server (http://support.microsoft.com/kb/280106). Another tool that is very powerful and you need to at least understand before you start is SSIS (http://msdn2.microsoft.com/en-us/library/ms141026.aspx) because it can offer simple solutions to otherwise noxious problems. There are more soltuions than just these two, but these are more than enough for 99.95% of the migrations that I've ever seen.

Schema migration can be a challenge, depending on how you/your business handles schema objects. If you have no existing migration controls (no SCCS, just brew the schema directly in production), then migration is relatively easy. If you have strict migration controls, then you need to determine the political issues, but technically it isn't going to be a big challenge. Unfortunately, most folks sit between the two extremes so the conversion can be charitably described as challenging. We might need to start a whole new thread for that discussion!

I don't know of anything that will migrate procedural code in a way that will suit me. I've seen many code migrators, a few of them produce working solutions, but I can't recommend any of them. I think that code needs to be manually migrated.

Anywho, I'll let you chew on this diatribe for the moment, but I've got lots more opinions if you are interested in them... Keep in mind that an opinion and a buck will get you a cup of coffee though (in other words, everybody has an opinion, but you can't trade an opinion for much of anything)!

-PatP|||Hi Phat

Thank you very much for taking time out and responding :-)

I have migrated (attempted) or tried migrating Db2 from mainframes to oracle and failed miserably :-) as i grossly underestimated the effort and here i am again trying to be brave :-)

The insights and experience some of you might provide is much valued by me.

I had a look SSMA(sqlserver migration assistant) and it claims to migrate Data as well as code anybody has working experience with this ?

For code migration i.e pl/sql
http://www.swisssql.com/
but it all depends on what the management thinks and if i can convince them for the $$ etc

For data migration i am thinking of SSIS or some open source ETL tool.

I have few questions though

1)Does anybody have a Migration template ?approach
for doing this sort of a activity which can be shared?
2)How good is SSMA for migrating data and PL/SQL to T-SQL
3)is SSIS free with SQL2k5 ?
4) When i migrate say a million row table from oracle to sql2k5 how do i ensure that all rows have been migrated and all the migrated rows are correct?

regards
Hrishy