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 BMC BladeLogic Server Automation (BSA) 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:

Setting up an Oracle database for BMC Server Automation

Introduction

This topic is intended for system and database administrators preparing to install BSA. 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 BSA unified product installer or when manually installing BSA.

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 BSA 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 BSA 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 BSA.

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)

How to set up a SQL Server database and user account

	Step	Example screen
1	Start the Microsoft SQL Server Management Studio application and connect to the Microsoft SQL database server.
2	Expand 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 BMC Server Automation system, they do not work in BMC 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 Decision Support for Server Automation data warehouse will fail.
4	Create the login credentials for the database. Right-click the Security folder and select New > Login.
5	Create log in credentials for the database. Provide a Login name (typically bladelogic), select SQL Server Authentication and enter a Password for the database. Note: Do not specify a BMC Server Automation database password for the account that begins with an equal sign (=). Ensure that the User must change password at next login option is not selected. Review the other password settings, such as Enforce password expiration, to ensure they match your organization's standards. 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 BMC 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 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. 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. 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. In the Database role membership section, select db_owner. Click OK.
7	Expand the Databases folder. Right-click the new database server node we created, and select New Query.
8	To reduce the likelihood of database deadlocks, execute the following steps: 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. Execute the following additional query: `ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON; <db_name>` is the name of the database you set up earlier in this procedure. Exit SQL Server Management Studio.