MaxTECH Maximo Technical User Group

Expand all | Collapse all

Changing from Oracle to SQL Server

  • 1.  Changing from Oracle to SQL Server

    Posted Thu January 16, 2020 04:31 PM
    We are looking into possibly switching from Oracle to SQL Server and was wanting to get feedback from anyone that has made that transition.  We would love to hear how your transition went.  If it was successful and what kinds of issues you ran into during your transition.  Approximate size of your database would be good also, so we can know if we are similar in size.  

    Thanks in Advance,

    ------------------------------
    Paula Lynn
    Application System Analyst Programmer
    Knoxville Utilities Board
    (865) 558-2347
    ------------------------------


  • 2.  RE: Changing from Oracle to SQL Server

    Posted Fri January 17, 2020 05:28 AM
    Hi Paula,

    We did a few POC's and testings recently. At the moment we're in a project converting a 5-10GB DB from SQL Server to Oracle/DB2. Experience wise we see this main challenges:
    - Making sure all DB related entries in Maximo get updated (KPI/ESCALATION/QUERY/....)
    - Transferring BLOB and CLOB Data from the old to the new DB Plattform
    - Check / Update Birt Reports using DB related Statements / Functions

    cheers


    ------------------------------
    Johann Rumpl
    Maximo Senior Consultant
    EAM Swiss GmbH
    Gais
    Switzerland
    ------------------------------



  • 3.  RE: Changing from Oracle to SQL Server

    Posted Wed January 22, 2020 11:01 AM
    Johann,

    Thanks for the information.  This will be very helpful.

    Paula

    ------------------------------
    Paula Lynn
    Application System Analyst Programmer
    Knoxville Utilities Board
    (865) 558-2347
    ------------------------------



  • 4.  RE: Changing from Oracle to SQL Server

    Posted Fri January 17, 2020 07:36 AM

    Going from Oracle/DB2 to SQL Server specifically, there are a few unique issues (in addition to the very good ones Johann has already mentioned). Maximo's supported configuration of SQL Server is case insensitive, whereas DB2 & Oracle are case sensitive. So on fields that are standard ALN fields, you could have ABC in one, Abc in another, and AbC in another, and those all be allowed. In SQL Server those will be treated as the same thing, which can be a serious issue. To be clear, it's a bad practice to do something like that in Oracle/DB2, but in all of our conversions we've identified some records like that.

    Another Oracle/DB2 to SQL Server specific thing to be aware of is that while SQL Server has support for sequences since SQL Server 2012, Maximo does not use sequences on SQL Server. They use a unique process for getting the next value, so make sure you set the proper maximum in the maxsequence table before turning on the application (it gets added to a cache, so updating after app is started will cause you to get sequence errors on startup). We see a lot of people load in the old values from DB2 or Oracle, where these values don't matter. 


    For this to be successful, the biggest piece of advice I have is to lock down configuration changes from the last test run to the production run. When the goal line is constantly moving, errors occur or data is lost. Schema related changes especially should be locked down. And if you go beyond that for reports, KPIs, etc. you can migrate all the metadata related records in advance of the outage which allows you to focus on the transactional records for the cutover.



    ------------------------------
    Steven Shull
    Director of Development
    Projetech Inc
    Cincinnati OH
    ------------------------------



  • 5.  RE: Changing from Oracle to SQL Server

    Posted Wed January 22, 2020 11:10 AM
    Steven,

    This is very interesting.  Very good information and advice.  

    Thank You

    ------------------------------
    Paula Lynn
    Application System Analyst Programmer
    Knoxville Utilities Board
    (865) 558-2347
    ------------------------------