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

Requirements and recommendations

• Click here for a list of supported databases.

  The BMC Server Automation product supports the following database types and versions:

  Database type	Supported versions
  Oracle	Enterprise Edition (EE) and Standard Edition (SE): 11g R2 11g R2 RAC 12c 12c RAC (not recommended for production setup) Express Edition (XE) Note See Oracle 11.2.0.2 patch requirement if you plan to use Oracle version 11.2.0.2 as the database for BMC Server Automation.
  Microsoft SQL Server	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)

• You do not need to create the database schema if you are installing or upgrading using the unified product installer. The unified installer performs this task automatically. 

To set up a SQL Server database and user

.

	Step	Example screen
1	Start the Microsoft SQL Server Management Studio application and connect to the Microsoft SQL database server.Note: You can connect a Windows default Application Server node only to a Microsoft SQL Server database.
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. 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.
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.

_{Back to top}

To configure BMC Server Automation databases for a non-English locale (optional)

This is an optional step. To use BMC Server Automation in a locale other than English, follow these guidelines:

• Configure your BMC Server Automation databases using the settings recommended by your database vendor for that language.
•  If the database is configured for a multibyte locale or a non-LATIN-1 locale, when you set up the database schema, run the master script that corresponds to the nchar character set.

_{Back to top}

To install the SQL schema manually (not required when using unified product installer)

This section provides instructions for installing the SQL Server database schema manually.

1. Copy install scripts from the db_scripts directory in the main product .zip to a location that you can access from the SQL Server console. For information on the db_scripts directory, see Downloading-the-installation-files.
    The relevant scripts appear in the /files/configurations/dbscripts/sqlserver directory inside the BBSA<version>-<platform>64 installer zip package.
2. Open a command prompt window.
3. Change (cd) to /db_scripts/sqlserver.

4. 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:

   Variable	Value
   <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 while setting up the database
   <bl_user>	Logon name for the BMC Server Automation database user
   <bl_pwd>	BMC  Server Automation database user password

_{Back to top}

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

You can also refer to the Health and Value Dashboard for additional statistics about the database server and information you can use for troubleshooting problems. To access the dashboard, see Using-the-Health-and-Value-Dashboards.

_{Back to top}

Where to go from here

Configuring-the-file-server