July 12, 2008 at 5:27 pm
I am assigned to convert Oracle 9i databases to Sql server 2000/2005 at my new job as dba. Oracle database server is a linked server. I googled for document that has steps to do it and could not find anything satisfying. Please let me know if you know anything.
July 13, 2008 at 4:28 am
I worked on a project to migrate frm a Oracle 7.3 database to SQL Server 2005. I took help from
1. Microsoft SQL Server Migration Assistant for Oracle (SSMA) - to
convert the source.
2. SQL Server Integration Services (SSIS) - to migrate the data.
You can download SSMA from
http://www.microsoft.com/sql/solutions/migration/default.mspx
Note that this is more of a development than dba task as you have convert the backend scripts also not only migrate the data.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 13, 2008 at 6:37 am
There is also a massive project planning document free from Microsoft on this very thing. You can find it here: http://www.microsoft.com/sql/techinfo/whitepapers/MigrOracleSQL2005.mspx
And of course, there are many consultants here who do this as part of their practice.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 13, 2008 at 12:34 pm
Thank you for your response.
I have worked as sql server developer in the past but not much knowledge of Oracle. Can you tell me how much you need to know about oracle?
July 13, 2008 at 1:42 pm
Well it depends on the nature of the database. Knowledge of PL/SQL will help you to understand the business logic of the backend procedures which you have to convert to T-SQL. It should not be that difficult to analyse the structure of the Oracle tables and views. You should also know how to connect to the Oracle instances and browse the schemas.
You are lucky to have the task of only the database mgration. You should take it passionately.
You can imagine the challenge if you were asked to migrate the application also.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 13, 2008 at 9:49 pm
Thanks alot
July 14, 2008 at 1:01 am
Hi
you can Use SSMA SQL Server Migration Assistance will help you to migrate i already use it before and it soo usefull and easy to use
July 17, 2008 at 9:31 pm
Can we work directly from production database or need to make copy of it? I found out that we are getting rid of oracle and we don't have development environment. Is there any chance of corrupting data if we work from production? Also, can you guys suggest me any exercise before I play with production data? Please advise.
July 18, 2008 at 6:34 am
There is no harm or risk to the production Oracle database in the process of migrating the data to the SQL Server.
You should plan as follows if your intention is to migrate the data from Oracle to SQL Server.
1. If the Oracle database is really 'production' and the data
is of dynamic nature (keeping changing) then you should
decide a cutoff time after which the Oracle database
will no longer be the production database.
2. Don't allow any transaction in the Oracle database from
that cutoff point.
2. Start migrating the data from Oracle to SQL Server. You
can take a backup of the Oracle database before
the migration process starts.
3. After the migration is completed you need to say goodbye
to Oracle (You will not use it for any production activity
any longer. It will be just a historical entity. You can
backup and keep it as an archive then stop the Oracle
instance and decommission it.)
4. After the migration is completed the SQL Server database
will become your production database. You should be
ready before hand to create any users that will have
access to the new SQL Server database with the required
privilages
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 18, 2008 at 6:45 pm
rbarryyoung (7/13/2008)
There is also a massive project planning document free from Microsoft on this very thing. You can find it here: http://www.microsoft.com/sql/techinfo/whitepapers/MigrOracleSQL2005.mspxAnd of course, there are many consultants here who do this as part of their practice.
Have you checked out this document yet? It's really going to answer a lot of your questions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2008 at 1:33 pm
Mazharuddin - Thank you for your insight.
rbarryyoung - I am starting to read the document but there are a lot of information on it. I am a slow reader so its taking forever.
I appreciate all of guys input.
August 21, 2008 at 6:40 pm
If you are using SSMA4Oracle, you are in read-only mode and the process presents no danger to the Oracle production instance.
Note that you may have trouble with integers and possibly other numeric types.
Oracle 10 (don't know about 7) stores integers as type number(38) which is implicitly number(38,0) (no decimial places). This needs to be 'type mapped' to integer in SSMA.
One of the great things about SSMA is that pulls all the rows it can and then stops. The record after the last row pulled from source contains the problem.
It also gives pretty good clues about what is wrong.
You are very unlikely to complete an entire conversion on the first pass.
Once you have it all converted, you can test your application against the SQL Server test db. Once the app is working against that, you'd pull the entire Oracle db into a production database in SQL Server, direct the app to it and shutdown Oracle connections.
Note that Oracle (10 at least) has datatypes that are not supported in ss2005 but are in ss2008. That necessitates different versions of SSMA for different versions of SQL Server. You can't use 'SSMA for ss2008' to build a ss2005 target.
August 22, 2008 at 8:45 am
Thanks for the very informative feedback Scott. Yes I agree with you, data or code migration from Oracle to SQL Server has to be taken very carefully and has to be tackeled step by step.
I remember a bottleneck I faced when migrating a date field data from Oracle 7.0 to SS 2005. The date value was less then 1/1/1753 and as we kinow as of SS 2005 it can not accept date value below 1/1/1753. This limitation is taken care of in SQL Server 2008.
Could you please einform what are the datatype in Oracle 10 which are not supported in SS2005
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 22, 2008 at 12:03 pm
Mazharuddin Ehsan (8/22/2008)
I remember a bottleneck I faced when migrating a date field data from Oracle 7.0 to SS 2005. The date value was less then 1/1/1753 and as we kinow as of SS 2005 it can not accept date value below 1/1/1753. This limitation is taken care of in SQL Server 2008.
Yes, and this is an issue that the TO_CHAR() function can also help mitigate. One of the issues we have encountered is date values entered without a validity check to prevent dropped characters in the year. We get a significant number of dates in the range of 190-208. When the display format is set to dd/mm/yy, these are not obvious. But, of course, ss2005 chokes on them. This, in particular, has forced us to use a traditional ETL approach of creating a staging version of the database where we can parse, identify and edit (or discard) these anomalies before moving records to production.
Mazharuddin Ehsan (8/22/2008)
Could you please einform what are the datatype in Oracle 10 which are not supported in SS2005
The new Spatial datatype is one. I haven't checked thoroughly (and won't now) but I think the hierarchical type may also be supported by Oracle.
One further suggestion: Down the Oracle SQL Developer from Oracle (free). This tool allows you to look at all Oracle objects from an Oracle perspective. This has been immensely helpful to me.
Use it read-only against production objects, of course.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply