Creating databases and users on SQL Server

You can use the create_bdssa_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 BMC Electronic Product Distribution (EPD) location.

SQL Server databases and users

By default, the script creates the following databases:

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 script creates the following users:

SQL Server users

Creating databases and users

Do the following:

1. Ensure that the prerequisites are met.
2. Log in to the SQL Server Management Studio.
3. Navigate to the create_tssadw_sqlserver_users.sql script and edit it as follows:
   1. If you do not want to create databases with default names, change the following database names. For example, TSSADW_DW_DATABASE and TSSADW_PR_DATABASE.
      • $TSSADW_DW_DB
      • $TSSADW_ETL_WORK_DB

      • $TSSADW_ETL_MASTER_DB

        Note: '$' sign must not be retained

        Do not retain the '$' sign in database names. For example, 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 must not be retained

      In the FILENAME parameter, retain N' after the = sign. 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 default names, change the following user names. 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.

      Note: '$' sign must not be retained

      In the PASSWORD 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 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, see Sizing tools. 

4. Open New Query.
5. Copy the contents of the script file and paste the contents into the query window.
6. Run the script.

7. After creating all 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)

 https://youtu.be/sWtdrOUs6gY