Clarity

 View Only
Expand all | Collapse all

CA PPM Default Database

  • 1.  CA PPM Default Database

    Posted Sep 14, 2019 03:19 PM
    Hi Team,
    I did not see in any of the 15.6.1 documentation that the Security Login have to have the CA PPM Database as the Default.
    Could you please confirm if this is a requirement?
    Thanks in advance,
    Somi

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


  • 2.  RE: CA PPM Default Database

    Posted Sep 15, 2019 04:53 AM
    For some reason I cannot find anything with search.
    This is what I got once from 15.3 with pdf download (cannot find anything like that now with search)
    Configure a CA PPM Database with Microsoft SQL Server

    Instead of Oracle, you can use SQL Server. After you install SQL Server, complete the following tasks
    in the order presented to import and configure the database for use with CA PPM.
    Set up valid login credentials for CA PPM. This login name and password are the values that
    you specify in CSA to log in to the database. The login name and password can be any
    combination.
    Set the Microsoft SQL Server Database Schema name as niku. This name must be the Default
    Schema for the login user you created on the Microsoft SQL server.
    Import the database backup image. On the CA PPM installation media, locate the MSSQL
    database image in the <root>/database folder.
    Save the database backup to a temporary location on the database server.
    Use the Restore Database tool in Microsoft SQL Server Management Studio to restore the
    base image.
    To database: niku
    From device: c:\<temporary location you saved the database image to> \mssql_base.db
    Verify the import, and remove the temporary folder and its contents.
    Associate the imported database with your SQL server security user by running the following
    pl/sql as the sa user:
    USE niku
    ALTER USER niku WITH LOGIN=<your security user>
    To grant the VIEW SERVER STATE to the <your security user> user, use the following
    command:
    GRANT VIEW SERVER STATE to <your security user>
    Review the Imported SQL Server Database Options
    Important! Mixed collations settings are not supported. If you change the server-level
    default from SQL_Latin1_General_CP1_CI_AS to another collation, create a new SQL Server
    instance with the correct collation SQL_Latin1_General_CP1_CI_AS before creating the CA
    PPM database.
    The import procedure creates the database with the options in the following table. Review these
    options to verify that they meet your requirements.
    Option Value
    ARITHABORT
    ANSI NULLS
    QUOTED IDENTIFIER
    ON
    To apply, execute:
    ALTER DATABASE <database>
    SET ARITHABORT ON
    ALTER DATABASE <database>
    SET ANSI_NULLS ON
    ALTER DATABASE <database>
    SET QUOTED_IDENTIFIER ON
    Compatibility level MS SQL 2012: 110
    To apply, execute:
    EXEC SP_DBCMPTLEVEL <database>, 110
    MS SQL 2014: 120
    To apply, execute:
    EXEC SP_DBCMPTLEVEL <database>, 120
    Database Schema
    Name
    niku
    The login name can be anything, but the default schema name for the login
    user must be
    niku
    Option Value
    Database user roles db_owner
    The database user must be able to alter the CA PPM-schema and otherwise
    own the database.
    READ COMMITTED
    SNAPSHOT
    ON
    To apply, execute:
    ALTER DATABASE <database>
    SET READ_COMMITTED_SNAPSHOT ON
    VIEW_SERVER_STATE Granted to database user. To apply, execute:
    GRANT VIEW SERVER STATE to niku
    REMOTE QUERY
    TIMEOUT
    sp_configure 'remote query timeout',0
    reconfigure with override


    There are some posts where these steps are listed, too. but you would have to use search to find them.



  • 3.  RE: CA PPM Default Database

    Posted Sep 15, 2019 02:24 PM
    I could not locate that in the table of contents of the documentation.
    For discussions one including the db steps is  is
    https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?MessageKey=9d2a20d1-3887-4611-812e-a8b9a37680f6&CommunityKey=7f0cbca3-5f93-4d44-a369-1a8ce98f5578&tab=digestviewer#bm9d2a20d1-3887-4611-812e-a8b9a37680f6


  • 4.  RE: CA PPM Default Database

    Posted Sep 15, 2019 05:53 PM
    Thank you Martti, Thats a big help.
    Now we are using an RDS with MS SQl 2016 and working like a champ.
    the only catch is the DWH database and it is showing me some errors.
    The operation cannot be performed on database "DBDBDB" 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.

    Is there a way to set some permissions here?
    I know we have lot of Customers that are using RDS at this moment. Any help is greatly appreciated.


  • 5.  RE: CA PPM Default Database

    Broadcom Employee
    Posted Sep 15, 2019 11:33 PM
    Duplicate of https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1747&MessageKey=67b800a9-9ba6-497c-88de-ad59f86865e9&CommunityKey=7f0cbca3-5f93-4d44-a369-1a8ce98f5578&tab=digestviewer&ReturnUrl=%2fenterprisesoftware%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3d7f0cbca3-5f93-4d44-a369-1a8ce98f5578%26tab%3ddigestviewer

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



  • 6.  RE: CA PPM Default Database

    Broadcom Employee
    Posted Sep 15, 2019 11:34 PM
    Installation of clarity is documented here https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/business-management/clarity-project-and-portfolio-management-ppm-on-premise/15-7/installing-and-upgrading/install-clarity-ppm.html#concept.dita_c5fddf2f732699e579d4affe4d72b1178454ee97_ConfigureacappmDatabasewithMicrosoftSQLServer

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



  • 7.  RE: CA PPM Default Database
    Best Answer

    Posted Sep 16, 2019 03:40 AM
    I was looking at a September 11 download of the the documentation of the September 11 edition which looks like this

    While today the September 11 edition looks like

    Which explains why search with Configure... did not find it

    Table of contents looks like this


    While in the old 15.3 download it looks like this

    The conclusion is that is is there, you just have to know where it is. Forget the search and TOC.
    This ain't the first time either.