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.
Original Message:
Sent: 09-15-2019 04:53 AM
From: Martti Kinnunen
Subject: CA PPM Default Database
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.
Original Message:
Sent: 09-14-2019 03:18 PM
From: sowmya gadang
Subject: CA PPM Default Database
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
------------------------------