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 BMC Electronic Product Distribution (EPD) location.

By default, the script creates the following database and users:

To create databases and users

1. Download the product files from this BMC Electronic Product Distribution location: TrueSight Server Automation - Data Warehouse 
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, change the database names. Ensure that the database names:
       • Contain only Latin alphabet letters, numbers, and underscores (_).
       • Do not begin with a number.
       • Do not contain hyphens (-) in the names.
       • 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. If you do not want to create users with default names, change the user names.

       Note: '$' sign must not be retained

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

    3. 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-for-TrueSight-Server-Automation-Data-Warehouse. 

       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%)
       

    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 begin with an equal sign (=) character.
       • Do not begin with an integer.
       • Do not contain spaces.
       • Do not retain the '$' sign. 
       • 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;