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

Before you start creating databases and users, ensure that the prerequisites are met.

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

• Before starting configuration of the product by using the create_bdssa_sqlserver_users.sql script, which is located in the bdssa86<servicepackVersion>external-files.zip: You can download external files from the BMC Electronic Product Distribution (EPD) website. 
• During configuration by using the create_bdssa_sqlserver_db_users.sql script, which you download by clicking the Download Database Scripts button in the postinstallation configuration wizard (see Configuring-the-product-by-using-the-console for details).

SQL Server databases and users

By default, the create_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_users.sql script creates the databases shown in the following table. You can 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 BMC Decision Support for Server Automation configuration.

SQL Server databases

The following settings are recommended:

• The database recovery model should be set 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_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_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 user names and passwords for reference during the BMC Decision Support for Server Automation configuration.

SQL Server users

To create databases and users

1. On the reports data warehouse computer, navigate to the directory in which script is located.
2. Log on to the SQL Server Management Studio. Ensure you have sysadmin privilege before you log on.
3. Locate the create_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_users.sql script and open it.
4. Edit the script as follows:
   1. Replace the following default database names with the desired names (for example, BDSSA_DW_DATABASE and BDSSA_PR_DATABASE) if you don't want to create databases with the default names specified in the SQL Server databases and users section.
      • $BDSSA_DW_DB
      • $BDSSA_ETL_WORK_DB
      • $BDSSA_ETL_MASTER_DB

      • $BDSSA_PORTAL_DB

        
        

        Note

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

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

      Note

      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, BDSSA_DW_DB on Windows. 

      CREATE DATABASE [BDSSA_DW_DB] ON  PRIMARY (NAME = N'BDSSA_DW_DB', FILENAME = N'C:\Program Files\MYSQL\BDSSA\MSSQL\DATA\\BDSSA_DW_DB.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)LOG ON (NAME = N'BDSSA_DW_DB_log', FILENAME = N'C:\Program Files\MYSQL\BDSSA\MSSQL\DATA\\BDSSA_DW_DB_log.ldf', SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
      
   3. For each of the users, replace the following default user names with the desired names (for example, BDSSA_DW_USER and BDSSA_PR_USER) if you don't want to create users with the default names specified in the SQL Server databases and users section:
      • $BDSSA_DW
      • $BDSSA_ETL_WORK
      • $BDSSA_ETL_MASTER

      • $BDSSA_PORTAL

        Note

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

   4. For each of these users, modify the PASSWORD parameter as required.

      Note

      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 value of this parameter, do not retain the '$' sign.

       

      The following example shows the LOGIN and PASSWORD parameters with sample values for the BDSSA_DW_USER user.

      CREATE LOGIN [BDSSA_DW_USER] WITH PASSWORD='password', DEFAULT_DATABASE=[BDSSA_DW_DB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
      GO
      EXEC sys.sp_addsrvrolemember @loginame = N'BDSSA_DW', @rolename = N'dbcreator'
      GO
      EXEC sys.sp_addsrvrolemember @loginame = N'BDSSA_DW', @rolename = N’sysadmin'
      
   5. Match the SQL Server collation sequence for the BMC 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 BMC 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 described in Sizing-for-new-product-installations-and-new-BMC-Server-Automation-installations or Sizing-for-new-product-installations-and-existing-BMC-Server-Automation-installations, depending on your environment.

1. Open New Query.
2. Copy the contents of the script file and paste the contents into the query window.
3. Run the script.