Migrate Oracle database to SQL Server 2008

Question

Post reply

Migrate Oracle database to SQL Server 2008

gary1

SSC-Insane

Points: 21152
More actions
October 6, 2010 at 11:12 am

#229132

Hi,
We are planning to Migrate Oracle database to SQL Server 2008 and want to use SSIS to complete the migration.
Could you please advice me what are steps involved in it?
Can we directly create an SSIS package from BIDS by creating Datasource Oracle database as Source and SQL Server 2008 as Destination?
Is there any book that explains this process?
Could you please share your experience in Migrating Oracle database to SQL Server 2008?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

SQL has created some ready-to-use Oracle Migration Assistants to make this kind of transformation a lot easier.

http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx

you can do it with SSIS, but this handles a lot of the twists and tricks that you'd end up learning via trial and error.

We actually use this tool for a database we get about once a month from an external source; we have not had any issues with it, other than knowing that some procedures in oracle have no equivilents, and have to be re-written, but tables/views etc come over fine.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gary1 SSC-Insane Points: 21152 More actions · Answer 2

SQL has created some ready-to-use Oracle Migration Assistants to make this kind of transformation a lot easier.
http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx

Thanks,

So if we Use SSMA, then there is NO need to use SSIS?

I discussed with couple of DBA's and they said initially we need to use SSMA and after that we need to use SSIS so after using SSMA and what is the role of SSIS? In which cases, we use SSIS after SSMA?

Thanks

Lowell SSC Guru Points: 323504 More actions · Answer 3

I like to think of SSIS as the universal tool, which can connect to all kinds of datasources, and you can write code to do anything you want. lots of options, nothing prebuilt for you, but import wizards can carry you a long way.

i think, in your case, a single, special tool can be used to migrate from Oracle...if you need more control, you can use the universal tool and it's steep learning curve, or you can use the special tool and click {next} a half a dozen times;

both get you where you want to go, but ease of use is a deciding factor.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gary1 SSC-Insane Points: 21152 More actions · Answer 4

What is the latest version of SSMA?

We are migrating Oracle 11g database to SQL Server 2008 R2. So which version of SSMA works best for us

Thanks for your help

Lowell SSC Guru Points: 323504 More actions · Answer 5

looks like version 4.2 is for "all" versions of SQL 2008; no special version just for SQL 2008 R2:

there are two different downloads for 2005 vs 2008 though, both marked as verson "4.2"

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9dfb1773-5594-44a9-869f-a891629f80a5&displaylang=en

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

gmamata7 (10/6/2010)
What is the latest version of SSMA?
We are migrating Oracle 11g database to SQL Server 2008 R2. So which version of SSMA works best for us

Always use the last version.

In my experience migration of schemas and data will be a walk in the park.

The fun begins with code migration, be prepared to re-write from scratch all code that does something more complex than "select column from table".

I'll put Cursors and Triggers on the top of my list of concerns - better to have at hand a good pl/sql developer to "read" Oracle code and a good t-sql developer to "write" the new (translated) code.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

Lowell (10/6/2010)
looks like version 4.2 is for "all" versions of SQL 2008; no special version just for SQL 2008 R2:
there are two different downloads for 2005 vs 2008 though, both marked as verson "4.2"
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9dfb1773-5594-44a9-869f-a891629f80a5&displaylang=en

Hi Lowell

why did you changed your pic of old(that dog) it was nice when i came long time back i will just see the queries of the post and replies for them by you by seeing your pic then it is lowell (i think of myself) now it is not Possible for me to identify.

Thanks
Parthi

gary1 SSC-Insane Points: 21152 More actions · Answer 8

Thank you,

I have downloaded the latest version of SSMA Microsoft SQL Server Migration Assistant for Oracle v4.2 (I believe this is the latest version) from the below link.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9dfb1773-5594-44a9-869f-a891629f80a5&displaylang=en

Here, we need to install the below two components after unzipping the SSMA 2008 for Oracle 4.2.zip

1. SSMA 2008 for Oracle 4.2.exe

2. SSMA 2008 for Oracle 4.2 Extension Pack.exe

But the SSMA 2008 for Oracle 4.2 Extension Pack.exe is NOT supported on Windows XP as mentioned in the above link:

Note: SSMA for Oracle Extension Pack is not supported on Windows XP.

But I have only Windows XP machine now:hehe:

Question:

If we want to work an Windows XP machine, then can we skip installing SSMA 2008 for Oracle 4.2 Extension Pack.exe? How does this effect in Migration process?

What is the use of installing SSMA 2008 for Oracle 4.2 Extension Pack.exe?

Thanks

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

You could install it on your sql server instead of your desktop, and then remove it when done.

Lowell SSC Guru Points: 323504 More actions · Answer 10

parthi-1705 (10/6/2010)
Hi Lowell
why did you changed your pic of old(that dog) it was nice when i came long time back i will just see the queries of the post and replies for them by you by seeing your pic then it is lowell (i think of myself) now it is not Possible for me to identify.

yeah it's easy to familiarize the avatar picture to the forum member; i thought it was time for a change; the picture of my dog(debbie) was up for a pretty long time, but i was trolling for "ugly" pictures, and found this colorful bug face from a wierd spider, so i switched.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gary1 SSC-Insane Points: 21152 More actions · Answer 11

Hi,

I have performed the steps from 1 to 6 successfully. But I did not understand how to do step 7, Map Oracle database to SQL Server database schemas.

Could you advice me how to Map Oracle database to SQL Server database schemas?

1. Install the SSMA for Oracle client components.
2. Install the SSMA for Oracle server components.
3. Open SSMA and obtain a license, as prompted.
4. Create a new SSMA project.
5. Connect to the Oracle database server.
6. Connect to an instance of SQL Server 2008.
7. Map Oracle database to SQL Server database schemas.
8. Optionally, assess the database objects for conversion.
9. Convert Oracle database objects into SQL Server objects.
10.Load converted database objects into SQL Server.
11. Migrate data to SQL Server 2008.
12. If it is necessary, update your database applications.

Thanks

Laura Grob SSC Eights! Points: 916 More actions · Answer 12

Is it possible to just use this tool to migrate the data? Working with a third party vendor that is supposed to be supplying a conversion from oracle to sql - wondering since I have the SQL scripts to create the tables in MSSQL if I can just use the data migration part of the tool?

arvindreddy33 Grasshopper Points: 11 More actions · Answer 13

Hi

In my project i have to migrate the data from oracle to SQL Server 2008 and i need your help in that . saw your postings in this site and came to know that you have done the same migration.

my question's are

1) have you used SSIS anywhere in your migration process.

2) what all we need to use in order to accomplish this migration?

3) can we complete the entire process by using SSMA.

your answers will be off great help

thanks in advance.

James.Caravan SSC Rookie Points: 30 More actions · Answer 14

Hi there
I'm looking for the "Sql Server Migration Assistant For Oracle V2.0" . I'm needing this version because it allows installation on Windows XP.
Places that I have seen this version have a broken download link.
thank for your help
JC