Clarity

Expand all | Collapse all

Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

Jump to Best Answer
  • 1.  Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-15-2019 11:26 PM
    I am getting the following error message for both Data warehousing Load Data and Access Rights.
    Is there a way to update this Jobs not Alter Databases?

    ClarityDB - isOracle? - An error occurred executing this job entry :
    Couldn't execute SQL: DECLARE @V_DBNAME VARCHAR(100);
    SELECT @V_DBNAME = DB_NAME();

    EXEC ('ALTER DATABASE ' + @V_DBNAME + ' SET RECOVERY BULK_LOGGED;');
    EXEC CMN_SAVEDROP_CREATE_MVIEWS_SP 'DWH_SEC_UNIT_BY_MODE', 'SAVE_DROP';

    [CA Clarity][SQLServer JDBC Driver][SQLServer]The operation cannot be performed on database "PPM_DWH_DEV" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
    Any help is greatly apppreciated

    thank you,
    Somio

    ------------------------------
    Somi
    ------------------------------


  • 2.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On
    Best Answer

    Posted 09-15-2019 11:33 PM
    MS SQL Server Mirroring is not currently supported https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/business-management/clarity-project-and-portfolio-management-ppm-on-premise/15-7/release-information/clarity-ppm-release-notes.html#concept.dita_8ebed6ba1fc5de89d746baa8a9be14ef035caa38_Hardware



    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 3.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-15-2019 11:49 PM
    HI Suman,
    Thankss for the documentation. the Multi-AZ is the way to go and What we would like to let you know is, The RDS Mirroring works really good with CA PPM DB, but not for DWH when we run those two jobs.
    If we can update the Jobs and remove the Alter Database for the Bulk Logged and any other alter Database scripts form those Jobs, then the DWH  Jobs will run successfully.
    The RDS or No RDS, The Multi-AZ/mirroring will work as expected if CA could update the scripts.
    Is there a way to modify those Jobs and update them ourselves?
    This is very important for us to have the High Availability and have them in the Cloud.
    Please let me know.
    Thank you,
    Somi


  • 4.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-15-2019 11:51 PM
    Hi Sowmya

    Yes we are aware of that and to ensure the performance of DWH is optimum we had to design in the way its now. Also DWH is not a real time data and you can always get back by running the job, is reason why you cannot turn off mirroring on DWH schema

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 5.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-16-2019 12:08 AM
    HI Suman,
    Would it be possible to update the SPs so that we use the Mulit-AZ on? This is the only hold up for us currently.
    Is there a workaround for us to run these jobs successfully?
    Please note that Mirroring happens at the Server/Instance Level, but not at DB level. If there is any way to get these done or if you have seen this happening, please let us know.
    thank you,
    Somi


  • 6.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-16-2019 12:23 AM
    Hi Somi

    The initial SP is just a end point and there are 100's of ETL that needs modification and we haven't tested at this point to let you know it will work or not. I am aware that mirroring is at server level and i assumed your DWH and clarity schema resides on different server.


    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 7.  RE: Data Warehouse Jobs Fail with Multi-AZ/Mirroring On

    Posted 09-16-2019 07:26 AM
    Hi Suman,
    thank you, Knowing that SQL Server has a defect and an issue with any events that altering a database with the Always on, we would like to work around the solution if there is any.
    What would be the next steps if we need to request Support/engineering team to update those two DWH JObs?
    Everything else is working as expected.
    Please advise.
    Thank you in advance,
    Somi