Creating databases and users on SQL Server

You can use the create_sqlserver_users.sql script to create databases and users for the SQL Server database. This script is available in the TSSA-DW<version>-external-files.zip file on the TrueSight Server Automation - Data Warehouse BMC Electronic Product Distribution (EPD) location.

By default, the script creates the following databases:

SQL Server databases

Database name

Purpose

TSSADW_DW_DB

To store the data warehouse tables.

TSSADW_ETL_MASTER_DB

To store information required by Oracle Data Integrator (ODI) during extract, transform, and load (ETL) process.

TSSADW_ETL_WORK_DB

By default, the script creates the following users:

SQL Server users

Database user

Responsibilities

TSSADW_DW

  • Is the owner, dbo, of the data warehouse database.
  • Has TSSADW_DW_DB as the default database.
  • Is assigned the sysadmin role.
  • Uses JDBC connectivity and executes ETL jobs by reading data from the TrueSight Server Automation database across the database link.

TSSADW_ETL_MASTER

  • Is the owner, dbo, of the ETL master repository database.
  • Has TSSADW_ETL_MASTER_DB as the default database.
  • Is assigned the dbcreator role.
  • Uses JDBC connectivity for ETL job execution.

TSSADW_ETL_WORK

  • Is the owner, dbo, of the ETL work repository database.
  • Has TSSADW_ETL_WORK_DB the default database.
  • Is assigned the dbcreator role.
  • Uses JDBC connectivity for ETL job execution.

To create databases and users

  1. Download the product files from the TrueSight Server Automation - Data Warehouse BMC Electronic Product Distribution location.
  2. Extract the TSSA-DW<version>-external-files.zip file to a directory outside the C:\Program Files directory to avoid any file sharing conflicts.
  3. While creating the databases, ensure that:
    • The SQL Server data warehouse uses the TCP/IP protocol.
    • The SQL Server databases do not have any Unicode requirements.

  4. Configure the SQL Server remote query timeout.

    Do the following:
    1. On SQL Server database instance, right-click the instance name, and select Properties.
    2. In the left pane, click Connections.
    3. In the Remote server connections area, in the Remote query timeout field, enter 0 as the query timeout period.
    4. Click OK.

  5. Set the database recovery model to Simple. For more information, see Change the Recovery Model of a Database (SQL Server) in the Microsoft documentation.

  6. Set the server-level setting of Maximum Server Memory to 80% of the physical memory on the server. For example, if the server has 6 GB of memory, set Maximum Server Memory to 4.7 GB. For more information, see Server Memory Configuration Options in the Microsoft documentation.

  7. Ensure that the data warehouse schema type aligns with the TrueSight Server Automation database in one of the following ways:
    • If you have one or more TrueSight Server Automation databases that use the CHAR schema, the data warehouse must use the CHAR schema.
    • If you have one or more TrueSight Server Automation databases that use the NCHAR schema, the data warehouse must use the NCHAR schema.

    • If you have multiple TrueSight Server Automation databases and some use the CHAR schema and others use the NCHAR schema, the data warehouse must use the NCHAR schema. 

      To determine the schema type used by the TrueSight Server Automation database, do the following:
      1. Log in to the SQL Server Management Studio as any user. For example, system.

      2. Run the following command:

        SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SYSTEM_PROPERTY' AND COLUMN_NAME = 'NAME’;

        For the CHAR type schema, VARCHAR2 is returned as the output. For the NCHAR type schema, NVARCHAR2 is returned as the output.

      To determine the schema type used by the TrueSight Server Automation - Data Warehouse database, do the following:
      1. Log in to the SQL Server Management Studio as any user. For example, system.

      2. Run the following command:

        SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SYSTEM_PROPERTY' AND COLUMN_NAME = 'NAME’;

        For the CHAR type schema, VARCHAR2 is returned as the output. For the NCHAR type schema, NVARCHAR2 is returned as the output.

  8. Ensure that the data warehouse uses the same character set as TrueSight Server Automation database or its subset character set. For example, if the TrueSight Server Automation database uses LATIN-1, the data warehouse must use the same character set or its subset character set.
  9. If you are using non-English databases, such as Asian customers, ensure that you use the NCHAR character set. 
  10. As a database administrator, log in to the SQL Server Management Studio.
  11. In the SQL Server Management Studio, navigate to the ..\TSSA-DW<version>-external-files\Sqlserver\create_sqlserver_users.sql script and edit it as follows:

    1. If you do not want to create databases with default names, replace the following default database names with the desired names (for example, TSSADW_DW_DATABASE and TSSADW_PR_DATABASE).

      • $TSSADW_DW_DB
      • $TSSADW_ETL_WORK_DB
      • $TSSADW_ETL_MASTER_DB

      Ensure that the database names:

      • Do not retain the '$' sign in database names. For example, you can find and replace all the instances of $TSSADW_DW_DB with TSSADW_DW_DATABASE.
      • Contain only Latin alphabet letters, numbers, and underscores (_).
      • Do not begin with a number.
      • Do not contain hyphens (-) in the names.

    2. For each database, change the value of the FILENAME parameter for the .mdf and .ldf files. Ensure that adequate size is assigned to the .mdf and .ldf files. To know the size requirements, see Sizing tools. 

      Note: '$' sign must not be retained

      In the FILENAME parameter, retain N' after the = sign and do not retain the '$' sign. 

      The following example shows the FILENAME parameter with a sample path for the .mdf and .ldf files of the TSSADW_DW_DB database on Windows.
      CREATE DATABASE [TSSADW_DW_DB] ON  PRIMARY (NAME = N'TSSADW_DW_DB', FILENAME = N'C:\Program Files\MYSQL\TSSA-DW\MSSQL\DATA\\TSSADW_DW_DB.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)LOG ON (NAME = N'TSSADW_DW_DB_log', FILENAME = N'C:\Program Files\MYSQL
      \TSSA-DW\MSSQL\DATA\\TSSADW_DW_DB_log.ldf'      , SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)

    3. If you do not want to create users with following default names, replace the following default user names with the desired names for each user (for example, TSSADW_DW_USER and TSSADW_PR_USER):

      • $TSSADW_DW
      • $TSSADW_ETL_WORK
      • $TSSADW_ETL_MASTER

      Note: '$' sign must not be retained

      Do not retain the '$' sign in user names.

    4. For each user, modify the PASSWORD parameter as required. When you replace instances of the LOGIN parameter, the PASSWORD parameter is also set to the same value as LOGIN. After modifying the LOGIN parameter, modify the PASSWORD parameter. Ensure that the passwords:

      • Do not retain the '$' sign. 
      • Do not begin with an equal sign (=) character.
      • Do not begin with an integer.
      • Do not contain spaces.
      • Do not use the following special characters:
        • Ampersand (&)
        • Double quotes (" ")
        • Pipe (|)
        • Less than sign (<)
        • Greater than sign (>)
        • Single quotes (' ')
        • Forward slash (/)
        • Semicolon (;)
      The following example shows the LOGIN and PASSWORD parameters with sample values for the TSSADW_DW_USER user.
      CREATE LOGIN [TSSADW_DW_USER] WITH PASSWORD='password', DEFAULT_DATABASE=[TSSADW_DW_DB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
      GO
      EXEC sys.sp_addsrvrolemember @loginame = N'TSSADW_DW', @rolename = N'dbcreator'
      GO
      EXEC sys.sp_addsrvrolemember @loginame = N'TSSADW_DW', @rolename = N’sysadmin'

    5. Ensure that the SQL Server collation sequence for the TrueSight Server Automation database matches with the collation sequence for the data warehouse as follows: 

      1. Search for COLLATE SQL_Latin1_General_CP1_CI_AS and remove comments, -- characters, from this line.
      2. Replace SQL_Latin1_General_CP1_CI_AS with the collation sequence for the TrueSight Server Automation database.

      For more information, see View Collation Information in the Microsoft documentation.

  12. Open New Query.
  13. Copy the contents of the script file and paste the contents into the query window.
  14. As a database administrator, run the script.

  15. After creating all the databases, run the following commands. If you have changed the database names, use the new names in the following commands.

    ALTER DATABASE TSSADW_DW_DB SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER DATABASE TSSADW_DW_DB SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE TSSADW_ETL_WORK_DB SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER DATABASE TSSADW_ETL_WORK_DB SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE TSSADW_ETL_MASTER_DB SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER DATABASE TSSADW_ETL_MASTER_DB SET READ_COMMITTED_SNAPSHOT ON;

How to video

Creating databases and users on SQL Server for TrueSight Server Automation - Data Warehouse (5:42)

icon-play.png https://youtu.be/sWtdrOUs6gY

 


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*