Important

   

This space contains documentation for TrueSight Server Automation 8.9.03 and the later service packs for 8.9. For earlier releases, see BMC Server Automation 8.9.

Walkthrough: Setting up a SQL Server database

 

 

This topic walks you through the process of using Microsoft SQL Server to set up a database that will be used when installing TrueSight Server Automation on Microsoft Windows

Warning

These tasks should be performed by a database administrator (DBA). Perform the steps in the exact order in which they are described.

This topic includes the following sections:

Introduction

This topic is intended for system and database administrators preparing to install TrueSight Server Automation. The goal of this topic is to demonstrate how to use SQL Server to set up a database and login ID for that database. Access to a database is a prerequisite to running the TrueSight Server Automation unified product installer or when manually installing TrueSight Server Automation.

What does this walkthrough show?

In this walk through, we set up a database and a user login that can access the database.

When installing TrueSight Server Automation using the unified product installer, the installation program automatically populates a database schema. The installer accesses that database using connection information that is defined in this walkthrough.

If you are installing TrueSight Server Automation and its database manually (rather than by using the unified product installer), you can use this procedure to set up a database and then later run the script that populates the database schema for TrueSight Server Automation

What do I need to do before I get started?

For this walkthrough, you must have SQL Server installed and you must have credentials to access SQL Server.

 Click here for a list of supported SQL Server databases.

  • 2008 (EE, SE)
  • 2008 Cluster (EE, SE)
  • 2008 R2 (EE, SE)
  • 2008 R2 Cluster (EE, SE)
  • 2012 (EE, SE)
  • 2012 Cluster (EE,SE)
  • 2012 R2 (EE, SE)
  • 2014 (EE, SE)
  • 2016 (EE, SE)

Note

For SQL Server to support TLS 1.2, certain client components and driver updates are required, as discussed in the following Microsoft article: https://support.microsoft.com/en-us/kb/3135244

How to set up a SQL Server database and user account


 StepExample screen
1

Start the Microsoft SQL Server Management Studio application and connect to the Microsoft SQL database server.

 

2Expand the database server. Right-click the Databases folder and select New Database.
3

Provide a name for the database you are setting up and click OK to exit the New Database wizard.

 Click here to see best practices for naming.
  • 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 TrueSight Server Automation system, they do not work in TrueSight Server Automation - Data Warehouse.
  • SQL Server database names are case-sensitive. You must use the same case when specifying the JDBC connections, or the load process for the TrueSight Server Automation - data warehouse will fail.

4

Create the login credentials for the database.

Right-click the Security folder and select New > Login.

5Create log in credentials for the database.
  1. Provide a Login name (typically bladelogic), select SQL Server Authentication and enter a Password for the database. 
    Note: Do not specify a TrueSight Server Automation database password for the account that begins with an equal sign (=).
  2. Ensure that the User must change password at next login option is not selected.
  3. Review the other password settings, such as Enforce password expiration, to ensure they match your organization's standards.
  4. For Default Database, enter the name of the database you are creating. Select a language for Default Language. The database for this walkthrough has a default language of English.
 If you are setting up your database with a default language other than English, click here for collation information.

If you are setting up TrueSight Server Automation to support a language other than English, on the Database properties > Options page, select the Collation for your language that is case-insensitive, accent-sensitive, and (where appropriate) kana-sensitive. For example, if you are setting up TrueSight Server Automation for French, use French_Cl_AS. If you are setting up TrueSight 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.

For a case-insensitive collation to be effective for your new user database, ensure that the master database is set to case-insensitive collation, as well.


 

6

Assign roles to the login name.

  1. Click on User Mappings on the left. In the Users mapped to this login section, select the name of the database that you set up earlier in this procedure (in this example: test), and set the default schema to dbo.
  2. In the Database role membership section, select db_owner.
  3. Click OK.

7
  1. Expand the Databases folder.
  2. Right-click the new database server node we created, and select New Query.
8To reduce the likelihood of database deadlocks, execute the following steps:
  1. Execute the following query: 
    ALTER DATABASE <db_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
    <db_name> is the name of the database you set up earlier in this procedure.
  2. Execute the following additional query:
    ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
    <db_name>
    is the name of the database you set up earlier in this procedure. 
  3. Exit SQL Server Management Studio.

 


Wrapping it up

Congratulations. You have successfully set up a database and user in SQL Server. You are now ready to run the unified product installer.

Where to go from here

Walkthrough: Installing on Windows using the unified installer

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Mauro Ciuti

    It would be good to add "WITH ROLLBACK IMMEDIATE" on the Step 8.2 if the command takes more than a minute to execute. This doesn't mean that command will rollback if an error occurs, but will start rolling back any uncommitted operation. Since the database is being set up, there shouldn't be any risk.

    Oct 22, 2019 03:48
    1. Ranu Ganguly

      Thank you for your comment. I have updated the step.

      Oct 24, 2019 12:59
  2. Paulo Neto

    Hi, I noticed that at the step number 6 the documentation only require the "db_owner" permission, but to create the database the user must have the "DB_creator" permission either. Thank you.

    Nov 25, 2019 12:04