Does anyone have any tips or docs for converting Nolio to SQL Server from Oracle?

[Legacy User]

We started using a remote Oracle db but it was too slow. Our tech team can procure and install a SQL Server db locally. I can export and import most of my Automation Studio settings but that doesn't work for the ROC. I was hoping I could transfer the Oracle datatables to the SQL Server using the Import and Export Wizard.      

[Anon Anon]

I did this as follows (note this is not recommended and indeed the vendor advised me against this course of action, so proceed with caution! or at the very least ask them for an alternative strategy), I did what you suggested, exported the projects out - do it via the command line, it's much faster - cut the instance over to the new DB, keeping the old DB on line. I then wrote Oracle statements against the old DB which wrote out insert statements with the SQL syntax and the values for the appropriate columns in the value bit of the statement. Some statements go prior to the imports e.g. the servers table, the users table. Others go afterwards e.g. the mappings of server to environment and role. I used Visio and an ODBC connection to work out the relationships between tables. It was surprisingly simple to do but we had only around 20 projects on that instance at that time and none of them complex. The other thing to note is that if you mess up a table you can get the NAC to rebuild that table at startup time...and then re-commencing hacking. This is wild and horrible hackery of the first order and I'm not proud of this posting but sometimes, "needs must".

[Legacy User]

Thanks so much for the tip. I am curious about a statement you made...."

if you mess up a table you can get the NAC to rebuild that table at

startup time".

 

How exactly do you do that?

.

 

thanks...

[Anon Anon]

Please note that I do not know what Walter is referring to below but that is NOT what I meant. There is a feature described by the vendor and it appears in another thread, "modify the wrapperContext.xml config folder in the datamanager/WEB-INF folder on the NAC and change the value    <prop key="hibernate.hbm2ddl.auto">update</prop> from update to create and restart the NAC this should force the creation of the DB tables". Now I've done this, but it was a while ago and I cannot recall the precise outcome (you need to check with the vendor) but I think that the tables that are populated are unaffected, only the dropped table is restored.

[Archer Berryman]

I have confirmed with support that switching DB types post installation is not a supported operation: https://wiki.ca.com/display/RA50/CA+Release+Automation+Prerequisites#CAReleaseAutomationPrerequisites-SupportedDatabases. This is a recent addition to the wiki documentation.

 

I only post this to confirm that there is not a simpler, supported way of making this change as an alternative to Richard's very complex (and impressive!) workaround.

[Walter Guerrero]

I would follow the steps in the link listed below:

 

http://msdn.microsoft.com/en-us/library/hh313159%28v=sql.110%29.aspx

 

This is accomplished with the SQL Server Migration Assistant (SSMA), I would strongly recommend that you take backups of everything before attempt the migration.

 

Let me know.

 

Thanks

[Legacy User]

Thanks for your help. Have you tried this procedure yourself?

 

 

thanks...

[Walter Guerrero]

Brian,

 

I have used this tool in the past, you just have to create a backup of the Oracle database schema prior to starting this process. Make sure that the Release Automation Server (NAC and NES) and agents are down.

 

Please read the instructions thoroughly prior to your start. I would use of the Oracle demo schemas to practice prior to starting this effort.

 

Thanks

[Anon Anon]

Surely you can mark this answered? ;o)

[Legacy User]

Thanks again for your help!

 

 

thanks...