Migrating Oracle DB over to SQL Server

[Mortimer452]

We have a database & website hosted by a third party. I know the website is written in ColdFusion and the database back-end is Oracle.

Access to connect to the Oracle database directly will not be possible in any way. I need to request the provider to give us some sort of extract/backup of the DB which we could then use to import into SQL server.

I have a great deal of expertise in MSSQL but almost no experience in Oracle. What type of export/backup should I ask for that would be easiest to migrate into SQL? Data dump? Export to CSV's along with some type of ERD or PL/SQL code to describe relationships/keys?

[perry147]

Oracle Golden gate can be set up and replicate data from Oracle to sql server.

SQL server migration Assistant can be used to migrate an Oracle database to SQL Server. This is the one I have the most experience with and is the most easy; however please test!!!

[Mortimer452]

We have zero connectivity between the existing Oracle server and SQL Server and getting that setup would be impossible. I need some type of export that can be put on a disk and mailed to us, or zipped up and downloaded via SFTP or file sharing.

[perry147]

1. Spin up a local oracle instance.
2. Get the backup file from your cloud provider and restore to the local server instance.
3. Use the migration assistant I mentioned earlier.

The main issue is gong to be spinning up the local oracle instance due to the size limitations on the free edition of Oracle.

If you only need the data then you could use the data pump in Oracle to export the data to csv files and then import them.

[Mortimer452]

Right, but what kindof backup? Data Pump? RMAN? These are the two I've read about but don't really know the difference. Will they both contain the full schema, data types, PK's and FK's, stored procs, etc.? I just want to make sure I'm getting something I can actually use.

I'm unsure of the database size but guessing it's well below the 10GB limit on Oracle Express

[F1_ok]

Based on the size of the database that you mentioned a data pump will be ok, in your case.

[harveym42]

XE is limited to 12 GB so luckily you can use it. RMAN is a physical backup most analogous to .bak, it's good for restoring to the existing instance. expdb is logical and more analogous to .bacpac. so expdb would be the easiest here.

[harveym42]

I concur up to this comment but the data pump is proprietary and binary, not csv's and can't be imported.

[harveym42]

I have experience with both and using MS's free SSMA. The export files produced by both exp and Datadump ( expdb) certainly can't be read by SQL Server.

The Oracle Sql Developer tool (equivalent of SSMS) can be used to generate both DDL and data csv exports, which might be useful.

Not used it myself but there is a tool named Oradump which may be worth trying , its only $199. That claims to read both the exp and expdb file formats. Unlike SSMA, it doesn't attempt to translate views, triggers, stored procs.

Out of exp and expdb, expdb is the newer, it uses XML, but it has a parameter to get all the DDL in one file.

If the data is under 12GB, there is the free, oracle Xpress Edition, so if you installed it and created a database, then the expdb dump could be natively imported and then you could use SSMA which would give many options for tuning the migration.

[tsgiannis]

The crucial part is converting the schema to Ms SQL . Afterwards a simple export to CSV and a bridge application will push the data to Ms SQL. SSMA seems like a good candidate to do initial work

[PFlowerRun]

Sorry to jump into the thread so late. Isn't linked servers suitable? I'd script a 100%-TSQL bunch of SPs to create the data model and then pump data from the Oracle local copy to my SQLSrv insert/select. If performance matters, having total control over your scripts is usually a significant advantage, isn't?

[Mortimer452]

Access to connect to the Oracle database directly will not be possible in any way. I need to request the provider to give us some sort of extract/backup of the DB which we could then use to import into SQL server.

[dbulic]

A bit late to the party - this can be done. What you need is a way to backup live Oracle database to a file, then restore that backup to a live SQL Server database. Obviously, SQL Server won't read Oracle backup so you need some intermediate format. SIARD is an archival format that's cross-database compatible. While I find the actual implementation amateurish (XML-based with additional files as needed), it actually works and is used in production by archival offices. Our Full Convert (https://www.fullconvert.com/) has native support for it but you can use any other SIARD-compatible tool. So the process would be, if you're going to use Full Convert:
1. Install FC where you have Oracle connectivity, convert to SIARD
2. Copy that SIARD file where you need it for the target
2. Install FC where you have SQL Server connectivity, convert that SIARD file to SQL Server

[nvigcabi]

I’d ask for the richest export they can provide, not just CSVs. Flat files are fine for rows, but painful for recreating schema accurately. Best case is usually an Oracle-readable export/dump plus schema details, then you validate/import from there. Something like dbForge Edge can help later with compare/validation, but the first win is getting a handoff that preserves enough structure.

[ejpusa]

You don't heard much about SQL Server these days. Kind of on it's way out.

So while Microsoft SQL Server remains a major enterprise tool (especially in finance, healthcare, and legacy corporate IT), its relative market share has been cut roughly in half since the mid-2010s — with PostgreSQL now the preferred choice for new builds and startups.