Has anybody migrated an Oracle MDB to MS SQL Server?

[bowmi71]

Hi,

 

We have a customer that would like to migrate their MDB from one DBMS to another.

 

After a number of compatibility issues the decision has been made to migrate the MDB from Oracle to MS SQL Server. The idea is to migrate the data to the new MDB to avoid having two systems to report from.

 

Does anybody have any experience in doing this? Has anybody use Microsoft's SSMA to do this?

 

The MDB is currently used by UAPM and SDM.

 

Any advice or ideas would be appreciated.

 

Thanks,

Miles.

[Anon Anon]

Hi Miles,

 

If you consider to use SDM utilities, then the option is to use pdm_backup and pdm_restore commands. Tech document TEC471268 contains information about files and tables which are collected by pdm_backup command.

 

Thanks,

Naveen

[bowmi71]

Hi Naveen,

 

My understanding is that this would only export the SDM data and not the linked UAPM data which could prove problematic.

 

Regards,

Miles.

[Jon_Israel]

I believe the only way to do that would be to use some type of third party tool to export the data from oracle and move it to sql.  Outside of that I dont think there is a way to extract the entire MDB for all shared products and have it in one single backup file.  Each product will most likely have its own set of tables that it will extract.

Wish I had a magic answer for you on this one.

Thanks,

Jon Israel

Principal Support Engineer

CA Technolgies

[Zhijun He]

Hi Miles,

If you want to migrate the whole database, it would be better to consult your DBA(s).

 

If my memory is right, you should be able to export everything in Oracle database into SQL scripts, you might use those scripts to rebuild the database in SQL server. (Interpretation might be needed)

 

Regards,

Mark

[Kyle_R]

Hi Miles,

 

Could you please mark best answer as either "Helpful" or "Correct?" I think this one has probably gone as far as it will for now, and I'm marking it as Assumed Answered - although you may change this status.

 

This helps the Community when others look for information.

 

Thanks, Kyle_R.

Admin.

[hikonen]

Hi!

I need to wake up this thread again just to check if anyone has successfully done a migration of the SDM MDB from Oracle to SQL Server? We have a SDM installation that is not integrated with any other CA products so I am hoping that it will be possible to just use pdm_backup and pdm_restore. But that surely seems a too simple solution to this problem? :-)

 

We do not have access to a test environment with Oracle yet, so I am just polling the community to see if someone has any experience of doing something similar.

 

I have seen other posts that mention 3rd party tools to migrate MDB-data between database platforms, for example using SQL Server Migration Assistant (SSMA) for Oracle:

SQL Server Migration Assistant for Oracle (OracleToSQL) | Microsoft Docs 

Is that another altenative in my case?

 

I have received the following advice from CA Support:

"You may use pdm_backup, but it does not cover non-SDM tables (used by integration with other CA Products). Tecdoc TEC471268 What does pdm_backup do? What is the difference between pdm_backup and pdm_extract?"
https://support.ca.com/us/knowledge-base-articles.TEC471268.html
provides some steps on how to do that. "

 

There is also a Tecdoc that describes how to reconfigure SDM to use SQL Server instead of Oracle but that does not describe how to actually migrate the MDB-data:

How to configure Service Desk Manager to point to a different database platform then it does currently 

 

Best regards,

Heikki Ikonen

Netgain

[Kyle_R]

Hello Heikki,

 

It has been ages since I've looked into this - versions back.

 

But the first check you may want to do is:

 

1) Run a pdm_backup. Grep/filter it to pull out just the Table names.

 

2) Get a list of populated tables from the SDM Oracle database.

 

3) Compare. Are there any populated tables in Oracle that are not present in the pdm_backup? 

 

They should match - especially as you're not integrated with any other products. But I'd consider it to be a basic sanity check before starting. Trial on a dummy small database first.

 

 

The other thing is the size of the database. "Big" databases take forever using pdm_backup/restore. Much better off using DB  tools if you can.

 

 

Has anyone else done an Oracle to SQL move?

 

 

Thanks, Kyle_R.

[hikonen]

Hi Kyle_R,

Thanks for the advice, much appreciated! I forgot to say that this is a SDM 12.6 with cumulative #3 so older, legacy information may be useful.

 

Regards,

Heikki Ikonen

Netgain

[Raghu Rudraraju]

You have to watch out for table contents that you are not supposed to overwrite.

 

Unimpacted Configuration Tables - CA Service Management - 14.1 - CA Technologies Documentation 

 

So, you may want to backup these tables first (on SQL Server first,  because you are attempting to move from Oracle to SQL) and then ReLoad them after restore.

 

_R

[hikonen]

Hi Raghu,

Thanks for the tip, I'll check that. Do you know which tables should remain unimpacted for SDM 12.6 with cumulative #3?

 

Regards,

Heikki Ikonen

Netgain 

[hikonen]

Can anyone give a rough estimate or educated guess of how long the pdm_backup and pdm_restore processes may take? I know the answer to that question is 'How long is a piece of string' :-) but i thought I'd ask anyway.

The SDM MDB database in question is about 3 GB in size on the Oracle server and below is a list of the top 10 tables with the most records:

EVENT_LOG 523223
NOT_LOG 317234
USP_KPI_TICKET_DATA 286318
ACT_LOG 274655
SESSION_LOG 227213
CR_PRP 192683
USP_KPI_DATA 78237
CALL_REQ 64533
ATT_EVT 51494
ATTACHED_SLA 34316

 

Infrastructure-wise the SDM 12.6 and Oracle 11g are on separate virtual (VMware) Windows servers, each with 4 CPU cores and 16 GB RAM.

 

 

Regards,

Heikki Ikonen

Netgain

[Legacy User]

On the basis of those counts, that is a relatively small database.  It's not unusual to find act_log and not_log with several million records, especially if archive/purge has never been run.   You might find that pdm_backup will take in the order of an hour or two given adequate network and disk performance - I suspect the limiting factor is likely to be how long it takes to push the data across the network and write it to disk.  If you run the pdm_backup in verbose mode you will soon see how fast it's moving.

 

The pdm_restore will be slower - disabling SQL constraints before the load will help - but depending on how good your SQL server is, how fast the disk and so on, I think you might be pleasantly surprised.  (But I can't quite bring myself to guess a time).

 

Regards,

James

[hikonen]

Hi James,

Thanks a lot, that gives me at least a 'guesstimate' which is all I need just now. We are going to try to do the pdm_backup over the weekend. I'll post here next week to let the community know how it goes.

We cannot be the first ones to attempt this kind of migration but I cannot find any directly relevant posts of anyone else doing it. And CA Support did not have any experience of it either. So either it is trivially easy (I wish!) or it is impossible and everyone who has attempted it have given up. We'll soon know :-)

 

 

Regards,

Heikki Ikonen

Netgain

[JEROME MAYER]

Hi

 

You can import Oracle database directly in MSSQL using SQL Server Management Studio.

This must the be transaparent for SDM amd others assuming CA have same naming convention and schema in both database.

 

Just go to Tasks/ import data and select oracle as data source in the wizard.

Have a look there where the full process is well detailed:

https://dbadiary.wordpress.com/2012/10/04/data-migration-from-oracle-to-sql-server-with-ssms/

 

Not CA related but this must the be transparent for SDM and the others assuming CA have same naming convention and schema in both database.

 

Hope this help

let us know your progress

/J