Install > Installation > Install and configure database > Install Microsoft SQL Server

Install Microsoft SQL Server

For more information on installing Microsoft SQL Server, see the Cloud Service Automation System and Software Support Matrix Guide for a list of supported database versions.

Guides are available on the HPE Software Support web site at: https://softwaresupport.hpe.com (this site requires a Passport ID). Select Dashboards > Manuals.

Database installation is typically done in partnership with a database administrator. Microsoft SQL Server must be installed with Mixed Mode authentication. During the installation of Microsoft SQL Server, from the Database Engine Configuration dialog, for Authentication Mode, select Mixed Mode (SQL Server authentication and Windows authentication).

Configure Microsoft SQL Server

These tasks must be completed before CSA is installed. Work with the database administrator to complete the following tasks (or see the manufacturer’s documentation for more information).

Enable TCP/IP (Required)

TCP/IP must be enabled on the Microsoft SQL Server in order for CSA to log on to the database. By default, TCP/IP may be disabled on the Microsoft SQL Server. Verify the TCP/IP configuration.

From the SQL Server Configuration Manager:

  1. Select SQL Server Network Configuration > Protocols for <instance_name>.
  2. Double-Click TCP/IP to open the TCP/IP Properties dialog.
  3. From the TCP/IP Properties dialog, select the IP Addresses tab.
  4. Verify TCP/IP is active and enabled, and verify the TCP port is set to 1433. Update any properties that are not set correctly.

Configure a Microsoft SQL Server User for CSA (Required)

An CSA database user is needed when installing CSA.

Caution The database name and username cannot contain more than one dollar sign symbol ($). For example, c$adb is a valid name but c$$adb and c$ad$b are not valid names.

To create a database user for CSA, do the following:

  1. Log on to the SQL Server as the sa user (or another user that can create logins, users and databases) using your favorite sql editor, for example, Microsoft SQL Server Management Studio.

  2. Create a login for all needed CSA databases (for example, csadbuser):

    CREATE LOGIN csadbuser WITH PASSWORD = '<csadbuser_password>';

    You must provide this database username and password when prompted for the CSA , OO and the Identity Management component database information during the installation or upgrade of CSA.

  3. Create a new database for CSA (for example, csadb) and a user (for example, csadbuser) within the database with db_owner role:

    CREATE DATABASE csadb; -- optionally you can use COLLATE option with case insensitive collation, for example, SQL_Latin1_General_Cp1_CI_AS;

    USE csadb; -- or connect to csadb in another way.

    CREATE USER csadbuser FOR LOGIN csadbuser;

    ALTER ROLE db_owner ADD MEMBER csadbuser;

    It is recommended to set the following parameters on the csadb database:

    ALTER DATABASE csadb SET ALLOW_SNAPSHOT_ISOLATION OFF;

    ALTER DATABASE csadb SET READ_COMMITTED_SNAPSHOT ON;

    Caution: CSA requires 'Case Insensitive' Collation of the database. The CSA database collation and the tempDB collation in SQL server must be the same. You must provide this database name when prompted for the CSA database information during the installation of CSA.

  4. (Optional) Create a reporting read-only user. The user is needed only if you want to use the reporting capabilities of CSA:

    CREATE LOGIN CSAReportingDBUser WITH PASSWORD ='<CSAReportingDBUser_password>';

    USE csadb; -- or you can connect to csadb in another way.

    CREATE USER CSAReportingDBUser FOR LOGIN CSAReportingDBUser;

    ALTER ROLE db_datareader ADD MEMBER CSAReportingDBUser;

    Provide this login name and password when prompted for the CSA reporting database user during the installation of CSA.

  5. Create a new database for the Identity Management component and a user (for example, csadbuser) within the database with the db_owner role:

    CREATE DATABASE idmdb;

    USE idmdb; -- or you can connect to idmdb in another way.

    CREATE USER csadbuser FOR LOGIN csadbuser;

    ALTER ROLE db_owner ADD MEMBER csadbuser;

    It is recommended to set following parameters on idmdb database:

    ALTER DATABASE idmdb SET ALLOW_SNAPSHOT_ISOLATION ON;

    ALTER DATABASE idmdb SET READ_COMMITTED_SNAPSHOT ON;

    Caution: The Identity Management component requires 'Case Insensitive' Collation of the database. The Identity Management component database collation and the tempDB collation in SQL server must be the same. Provide this database name when prompted for the Identity Management component database information during the installation of CSA.

  6. (Optional - needed only if you want to install embedded OO). Create a database for embedded Operations Orchestration (for example, csaoodb) and a user (for example, csadbuser) within the database with db_owner role.

    See the Operations Orchestration Database Guide for more information about database requirements for Operations Orchestration

    Note: OO requires 'Case Sensitive' Collation of the database (for example, SQL_Latin1_General_Cp1_CS_AS):

    CREATE DATABASE oodb COLLATE SQL_Latin1_General_Cp1_CS_AS;
    USE oodb; -- or you can connect to the oodb in another way.
     
    CREATE USER csadbuser FOR LOGIN csadbuser;
    ALTER ROLE db_owner ADD MEMBER csadbuser;

    As of the current release date of CSA in this guide, the mandatory database options for the Microsoft SQL Server for Operations Orchestration are:

    ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT:

    ALTER DATABASE oodb SET ALLOW_SNAPSHOT_ISOLATION ON;

    ALTER DATABASE oodb SET READ_COMMITTED_SNAPSHOT ON;

    Caution: Verify the latest mandatory options and follow the instructions in the Operations Orchestration Database Guide when creating the Operations Orchestration database. Provide this database name when prompted for the Operations Orchestration database information during the installation of CSA.

  7. Create a new database for the Workflow Designer component and a user (for example, csaooddbuser) within the database with the db_owner role:

    CREATE DATABASE ooddb;

    USE ooddb; -- or you can connect to ooddb in another way.

    CREATE USER csadbuser FOR LOGIN csadbuser;

    ALTER ROLE db_owner ADD MEMBER csadbuser;

    It is recommended to set following parameters on ooddb database:

    ALTER DATABASE ooddb SET ALLOW_SNAPSHOT_ISOLATION ON;

    ALTER DATABASE ooddb SET READ_COMMITTED_SNAPSHOT ON;

    Caution: The Workflow Designer requires 'Case Insensitive' Collation of the database. The Workflow Designer database collation and the tempDB collation in SQL server must be the same. Provide this database name when prompted for the Workflow Designer component database information during the installation of CSA.

Create a Filegroup for LOBs

(Optional – performance optimization) If you extensively attach documents to CSA artifacts, it might be convenient for performance reasons to separate the table CSA_DOCUMENT (containing attached documents) to a different filegroup on a standalone disk. Discuss this option with your database administrator to discover if it might be suitable in your case.

Example: Moving the CSA_DOCUMENT table to a non-default filegroup:

  1. Create a filegroup with a file:

    ALTER DATABASE csadb ADD FILEGROUP csa_lob_group;

    ALTER DATABASE csadb ADD FILE (NAME = csa_lob_file, FILENAME= 'D:\DATA\csa_lob_file.mdf', SIZE = 3GB, FILEGROWTH = 10%) TO FILEGROUP csa_lob_group;

    Note: Please change FILENAME and SIZE parameters accordingly.

  2. Create a new table CSA_DOCUMENT_NEW as a mirror of the original CA_DOCUMENT table, that overrides the filegroup option:

    use csadb;

    create table CSA_DOCUMENT_NEW (

    CONSUMER_VISIBLE tinyint,

    CONTENT image,

    CONTENT_LENGTH numeric (19,0),

    DOC_ORDER int,

    HEIGHT nvarchar(255),

    MIME_TYPE nvarchar(255),

    URL nvarchar(255),

    WIDTH nvarchar(255),

    UUID nvarchar(255) not null,

    ARTIFACT_CONTEXT_ID nvarchar(255) not null,

    ARTIFACT_CONTEXT_TYPE_ID nvarchar(255),

    DOCUMENT_TYPE_ID nvarchar(255) not null,

    primary key (UUID)

    ) ON csa_lob_group;

    create index FKB7B1E7C97F204E54_i on CSA_DOCUMENT_NEW (ARTIFACT_CONTEXT_ID);

    create index FKB7B1E7C915AC76B9_i on CSA_DOCUMENT_NEW (ARTIFACT_CONTEXT_TYPE_ID);

    create index FKB7B1E7C9E7C20D41_i on CSA_DOCUMENT_NEW (DOCUMENT_TYPE_ID);

    alter table CSA_DOCUMENT_NEW add constraint FKB7B1E7C97F204E54 foreign key (ARTIFACT_CONTEXT_ID) references CSA_ARTIFACT;

    alter table CSA_DOCUMENT_NEW add constraint FKB7B1E7C915AC76B9 foreign key (ARTIFACT_CONTEXT_TYPE_ID) references CSA_CATEGORY;

    alter table CSA_DOCUMENT_NEW add constraint FKB7B1E7C9E7C20D41 foreign key (DOCUMENT_TYPE_ID) references CSA_CATEGORY;

    alter table CSA_DOCUMENT_NEW add constraint FKB7B1E7C98A34BFD7 foreign key (UUID) references CSA_ARTIFACT;

    Note: For reference, see the latest definition of the CSA_DOCUMENT table and related indexes and constraints in CSA_HOME\scripts\create-mssql-schema.sql.

  3. Copy the data from the the original CSA_DOCUMENT table to CSA_DOCUMENT_NEW:

    INSERT INTO CSA_DOCUMENT_NEW (CONSUMER_VISIBLE, CONTENT, CONTENT_LENGTH, DOC_ORDER, HEIGHT, MIME_TYPE, URL, WIDTH, UUID, ARTIFACT_CONTEXT_ID, ARTIFACT_CONTEXT_TYPE_ID, DOCUMENT_TYPE_ID)
    SELECT CONSUMER_VISIBLE, CONTENT, CONTENT_LENGTH, DOC_ORDER, HEIGHT, MIME_TYPE, URL, WIDTH, UUID, ARTIFACT_CONTEXT_ID, ARTIFACT_CONTEXT_TYPE_ID, DOCUMENT_TYPE_ID FROM CSA_DOCUMENT;

  4. Drop the original table CSA_DOCUMENT:

    DROP TABLE CSA_DOCUMENT;

  5. Rename CSA_DOCUMENT_NEW back to CSA_DOCUMENT.

    EXEC sp_rename 'CSA_DOCUMENT_NEW', 'CSA_DOCUMENT';

Send Help Center feedback