1 Reply Latest reply on Jun 6, 2019 8:44 AM by Nico Heinze

    Migration from Oracle 18c to Azure MS SQL

    Mike Thunder New Member

      Hi all,

      I have a project that needs to be moved from on-premise Oracle to cloud Azure MS SQL database. I know that Informatica is agnostic but I want to understand how much effort will take this migration.

      We have around 20 workflows that are doing the standard Informatica flows. We received a file with 150 fields, to normalize that into Landing stage (save it to DB), we transform and apply the rules on Staging(save it to DB) and finally we're saving the final output into the database.

      Can someone give me like a small overview how much time effort will be:

      1. to change the connector from Oracle to MS SQL

      2. to update a workflow ( I will multiply by 20)

      3. if we need the update the stored procedure (even there not using Oracle custom functions) to MS SQL

      Thanks in advance.

      PS. I'm BA

        • 1. Re: Migration from Oracle 18c to Azure MS SQL
          Nico Heinze Guru

          Probably the easiest approach regarding the actual doing (I'll talk about regression tests later) is this:


          Create appropriate Azure connections and make sure they work as expected.

          Export all affected workflows to XML files (PowerCenter repository manager).

          Parsing these XML files (treat them as text files, not as XML files), replace all Oracle data types by Azure data types.

          Also replace the names of all Oracle connections by names of Azure connections.

          Re-import these patched XML files into a new repository folder and start regression testing.


          Now about regression testing.

          There are basically two approaches.

          First you can use DVO (Data Validation Option) from Informatica; it provides a GUI and some nice "tricks" to define and execute regression tests between separate databases. DVO uses PowerCenter to perform the actual load and comparison work and saves the comparison results in a relational database, if memory serves me right.

          Second you can build that stuff yourself. I'm currently building something very similar, and I can tell you it's not that easy if you want to have a good framework. But of course you can also mass-generate comparison workflows (there are various ways to do this) and hope that you won't need too many regression runs.


          One word of caution: I haven't worked with Azure on my own so far, so I don't have any real idea whether the approach with the XML files really works as described above. My suggestion is that you first try one workflow (and not the most complicated one) and, if this works fine, try to apply this approach to the other workflows as well.

          The big advantage of this approach is that you can automate it with little effort.