Creating databases and users on SQL Server

This topic describes how to set up product-related databases and users for the Microsoft SQL Server database. 

• SQL Server databases and users
• To create databases and users

You can create databases and users by using one of the following methods:

• Before starting configuration of the product by using the create_tssadw_sqlserver_users.sql script, which is located in the TSSA-DW<version>-external-files.zip: You can download external files from the BMC Electronic Product Distribution (EPD) website. 
• During configuration by using the create_tssadw_sqlserver_users.sql script, which you can download by clicking the Download the scripts to create users/database/schema link in the post-installation configuration wizard.

SQL Server databases and users

By default, the create_tssadw_sqlserver_users.sql script creates the databases shown in the following table. You can also use different database names from those used in the table examples by modifying the script. Make a note of the database names for reference during the TrueSight Server Automation - Data Warehouse configuration.

SQL Server databases

The following settings are recommended:

• Set the database recovery model to Simple.
• 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.

By default, the create_tssadw_sqlserver_users.sql script creates the users shown in the following table. You can use different logon names from those used in the following table by modifying the script. Make a note of the usernames and passwords for reference during the TrueSight Server Automation - Data Warehouse configuration.

SQL Server users

To create databases and users

1. Before you start creating databases and users, ensure that the prerequisites are met.
2. On the reports data warehouse computer, navigate to the directory in which script is located.
3. Log in to the SQL Server Management Studio.
4. Locate the create_tssadw_sqlserver_users.sql script and open it.
5. Edit the script as follows:
   1. If you do not want to create databases with the 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

        Note: '$' sign in database names

        Do not retain '$' sign in database names. For example, you can find and replace all the instances of $TSSADW_DW_DB with TSSADW_DW_DATABASE.

   2. For each database, change the value of the FILENAME parameter for the .mdf and .ldf files.

      Note: '$' sign in the FILENAME parameter

      In the parameter value, 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 warehouse database, TSSADW_DW_DB 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 the default names, replace the following default user names with the desired names (for example, TSSADW_DW_USER and TSSADW_PR_USER) for each user :
      • $TSSADW_DW
      • $TSSADW_ETL_WORK

      • $TSSADW_ETL_MASTER

        Important: Using $ sign in parameter value

        In values of this parameter, do not retain the '$' sign.
        

   4. For each user, modify the PASSWORD parameter as required.

      Replacing instances of the LOGIN parameter

      • 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, remember to modify the PASSWORD parameter.

      • In the value of this parameter, do not retain the '$' sign.

      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. Match the SQL Server collation sequence for the TrueSight Server Automation database with the collation sequence for the reports 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.
   6. Ensure that adequate size is assigned to the .mdf and .ldf files. To know the size requirements, use the Sizing tools, depending on your environment. 
6. Open New Query.
7. Copy the contents of the script file and paste the contents into the query window.
8. Run the script.

9. After creating all databases, run the following commands, replacing the names of the database as needed to match your environment

   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;