Setting up a SQL Server database, user, and schema for BMC Server Automation

This topic provides instructions for setting up a Microsoft SQL Server database, user, and schema for the fresh installation of the BMC Server Automation database. For a list of supported SQL Server database platforms, see Minimum-software-requirements.

Warning
  • These tasks should be performed by a database administrator (DBA). Perform the steps in these tasks in the exact order in which they are described. If you are not using the following scripts to create the schema, refer to them to determine the privileges required.

  • The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

To set up a SQL Server database, user, and schema (video)

The following video provides a quick demonstration of the process of setting up a SQL Server database, user, and schema.

To set up a SQL Server database, user, and schema

  1. Copy install scripts from external-files.zip to a location that you can access from the SQL Server console. For information on external-files.zip, see Obtaining-the-installation-files.
     If you extract the entire contents of external-files.zip, the relevant scripts appear in the /db_scripts/sqlserver directory.
  2. Start SQL Server Management Studio.
  3. Expand the database server.
  4. Right-click the Databases folder and choose New Database.

  5. Provide a name for the database, such as bladelogic.

    Recommendation

    • Use only Latin alphabet letters, numbers, and underscores (_) in database names. Do not begin database names with a number. Do not use hyphens (-). While database names with hyphens work in the BMC Server Automation system, they do not work in BMC BladeLogic Decision Support for Server Automation.
    • SQL Server database names are case-sensitive. You must use the same case when specifying the JDBC connections, or the load process for the BMC BladeLogic Decision Support for Server Automation data warehouse will fail.
  6. Create a user account for the database and make this user the owner of all objects in the database.

  7. Use SQL Server Authentication and create a password for the user you created in the previous step.

    Note

    Do not specify BMC Server Automation database password that begins with an equal sign (=).

  8. Set the default database to the database you created in step 4 and step 5.
  9. Set the default language for the database.
  10. Set the collation as case-insensitive for the default language.
     If you are setting up BMC Server Automation to support a language other than English, for Collation name, select the collation for your language that is case-insensitive, accent-sensitive, and (where appropriate) kana-sensitive. For example, if you are setting up BMC Server Automation for French, use French_Cl_AS. If you are setting up BMC Server Automation for Japanese, use Japanese_Cl_AS_KS.
     The new database appears under the Databases folder. You can set other options at this time too, but they are not required.
  11. To reduce the likelihood of database deadlocks, perform the following steps:
    1. Right-click the database server node and select New Query.
    2. Execute the following query:
      ALTER DATABASE <db_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
    3. Execute the following additional query:
      ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON;
  12. Exit SQL Server Management Studio.
  13. Open a command prompt window.
  14. Change (cd) to /db_scripts/sqlserver.

  15. Run the master script that corresponds to the character sets for which your database is configured, using the following syntax:
    <master_script.bat> <db_server> <db_name> <bl_user> <bl_pwd>
    Replace the variables with the following values:

    <master_script.bat>

    sqlserver_master.bat if your database is configured for char/varchar character sets or sqlserver_nchar_master.bat if your database is configured for nchar/nvarchar character sets

    <db_server>

    Name of the database server.
    If the server uses a nonstandard port, the server name and port number must be enclosed in quotation marks, as shown in the following example: "QLDS0002,1381"
    To use a named server instance, append the named instance, including the complete path, as shown in this example:
    sqlserver_master.bat <db_server>\<instance_name> <db_name> <db_user> <db_password>

    <db_name>

    Name of the BMC Server Automation database that you created in step 4 and step 5.

    <bl_user>

    Logon name for the BMC Server Automation database user

    <bl_pwd>

    BMC  Server Automation database user password

Note

When the master script runs, the following warnings might appear:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'rsave_cond_result_subtree'. The stored procedure will still be created.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'rsave_cr_group_result_subtree'. The stored procedure will still be created.

These warnings are expected and can be ignored.

Database maintenance: Recommendation for gathering statistics

As with other products which use Microsoft SQL database, some performance problems in a BMC Server Automation environment can be caused by stale SQL statistics on the BMC Server Automation schema, due to changing data volumes or changes in column values. BMC strongly recommends that you gather statistics for the core BMC Server Automation database one time per week.

Use the EXEC sp_updatestats command to run the SQL server built-in procedure against all tables in the schema, to gather statistics and optimize the database. For more information on gathering statistics, see Microsoft documentation.

Warning

Gathering statistics is a resource-intensive task and you may experience a fall in database performance while executing this task.

 

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