This documentation supports the 9.0 version of BMC Remedy ITSM Deployment.

To view the latest version, select the version from the Product version menu.

Configuring the Microsoft SQL Server

This section describes the steps to configure your Microsoft SQL Server database when you install BMC Remedy Action Request (AR) System or any application from the BMC Remedy IT Service Management (ITSM) Suite.

The following topics are provided:

Before you begin

  • Back up the SQL Server log files, and then change the SQL Server Transaction Logging mode from FULL to SIMPLE.
  • If the database is not configured to extend automatically, ensure that you have have completed the following actions:
    • Set the BMC Remedy AR System data file size to 1 GB or greater; BMC Software recommends at least 2 GB. If you plan to install BMC Atrium CMDB or BMC Remedy ITSM applications in addition to BMC Remedy AR System, add 2 GB to the data file for each additional application.
    • Set the log file size to 1 GB or greater. If you plan to install BMC Atrium CMDB or BMC Remedy ITSM applications in addition to BMC Remedy AR System, add 2 GB to the data file for each additional application.

Recommendations

  • Purge the transaction log frequently to prevent it from filling up during installation.
  • BMC recommends that you set the value of the Next-ID-Block-Size: Server option in the ar.cfg or ar.conf file to 100.

To prepare your Microsoft SQL Server database

  1. Install the Microsoft SQL Server database.
    You can install on the same computer on which BMC Remedy AR System is installed, or on a remote server that is networked to the computer on which you plan to install BMC Remedy AR System.
  2. Install SQL Server clients (the drivers).
    For remote installations, install on the same computer on which the BMC Remedy AR System server in installed.
  3. Create an instance of the database.
  4. Set your SQL Server connections to allow TCP/IP:
    1. Open the SQL Server Configuration Manager.
    2. Click Network Configuration for your SQL Server instance.
    3. Ensure that TCP/IP is enabled.
    4. View the TCP/IP Properties dialog box for your database instance, and ensure that the IP Addresses tab has a TCP port number specified. (The default port is 1433.)
    5. Restart all SQL Server services to effect this change.
  5. Determine data file and log file sizes for your SQL Server database.

    Note

    During the installation, you are required to declare table sizes, which enables you to presize the data files to improve application performance.

  6. Ensure that your database can accept network communication with the parameters entered in the installation.
    The network communication will use ODBC and be able to recognize your ODBC data source.

To optimize Microsoft SQL Server after installing or upgrading the AR System server

Important

  • Perform these tasks immediately after you complete the AR System server installation or upgrade and before you install any other BMC applications (for example, BMC Atrium Core).
  • Ensure you have installed the most current service pack.
  1. Stop the AR System server to ensure that all connections to the AR System database are closed.
  2. (For a server group or a shared database) Stop all AR System instances.
    Set the following SQL Server simple parameterization and SNAPSHOT isolation parameters:

    ALTER DATABASE ARSystem SET PARAMETERIZATION SIMPLE
    ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT ON

    For example:

    alter database ARSystem set recovery simple;
    alter database ARSystem set single_user with Rollback immediate;
    alter database ARSystem set READ_COMMITTED_SNAPSHOT ON;
    alter database ARSystem set multi_user;
    alter database ARSystem set PARAMETERIZATION SIMPLE;
  3. Verify the values by issuing the following command:

    SELECT is_read_committed_snapshot_on FROM sys.databases where name = 'ARSystem'

Windows Authentication mode and the Microsoft SQL Server

Choose one of the following authentication modes when you install the BMC Remedy AR System server:

  • Windows authentication—For SQL Server environments that support only Windows authentication mode.

    When you use the Windows authentication mode with SQL Server note the following:

    • To install or upgrade the BMC Remedy AR System Server on the Windows Server, you should log on the Windows Server with the domain account used for Windows authentication mode.
    • The domain account used with Windows authentication mode should be a full administrator on the Windows Server and should have db_securityadmin and setupadmin roles in SQL Server. The domain account should be Database Owner (dbo) of the BMC Remedy AR System database.
    • When the install or upgrade completes, it is possible to deny certain policies to the domain account used with Windows authentication mode for security reasons. The DBA should ensure that denying policies should not result in the loss of functionality. For any upgrades later, all the policy restrictions should be removed and the domain account should be returned to full administrator until the upgrade completes. 
  • Windows authentication or SQL Server authentication—For SQL Server environments that support mixed authentication mode.

To find the supported authentication mode for your SQL Server environment, connect to the SQL Server instance from Management Studio > Server Properties > Security.

To create a referential Microsoft SQL Server database for the installer

If you do not have database administrator privileges, your database administrator must create an empty database so that you are not asked for database information during the installation.

Important

Create the folder (for example, c:\data) before you create the database; otherwise, database creation fails.

  1. In a Query Window, run the following command:

    use tempdb
  2. Create a database; for example:

    CREATE DATABASE "ARSystem" ON (NAME = "ARSystem_data", FILENAME = 'c:\data\ARSys.mdf', SIZE = 500MB) LOG ON (NAME = "ARSystempt_log", FILENAME = 'c:\data\ARSysLog.ldf', SIZE = 100MB)
    CREATE LOGIN "ARAdmin"WITH PASSWORD = 'AR#Admin#', DEFAULT_DATABASE = ARSystem
  3. Use the created database; for example:

    use ARSystem
  4. Create a user name and logon; for example:

    CREATE USER "ARAdmin" FOR LOGIN "ARAdmin"
  5. Make the user the db_creator; for example:

    sp_addrolemember 'db_creator', 'ARAdmin'

Considerations for Microsoft SQL Server 2012 or above

If you are using Microsoft SQL Server 2012 or above database you will need to create synonyms for the trace_xe_action_map and trace_xe_event_map system procedure before running database consistency checker utility.

To create the synonyms

  1. Open SQL Server Management Studio (of relevant version).
  2. Open a new query window.
  3. Choose the "ARSystem" database.
  4. Execute the following SQL statements:
    create synonym trace_xe_action_map for sys.trace_xe_action_map;
    create synonym trace_xe_event_map for sys.trace_xe_event_map;

Upgrading to Microsoft SQL Server 2014

With version 9.0 Service Pack 1, Microsoft SQL Server 2014 is supported. To upgrade to Microsoft SQL Server 2014, consider the following:

Configuring Microsoft SQL AlwaysOn solution with AR System

For implementing Microsoft SQL AlwaysOn with AR Sytem, you must consult the database administrator.

With Service pack 1 and later, you can configure Microsoft SQL AlwaysOn solution with AR System.

BMC has tested the  Microsoft SQL AlwaysOn solution where three nodes, each running single an instance of SQL Server, participate in a single Windows Server Failover Cluster (WSFC) that spans across two data centers. This is a non-shared solution, and the nodes don’t share any storage with another node. Each node runs a single instance of SQL Server and has its own copy of the data.

Note

As a client, AR System supports multi-subnet failover. Thus, the AR System server can connect to any SQL AlwaysOn solution that requires a multi-subnet failover.

To configure Microsoft SQL AlwaysOn with AR System during installation

  1. During installation, you must select the Enable Client Connectivity for Always On solution check box so that the BMC Remedy AR System server can connect to the SQL server Always on solution. Also for AR System DB host name and for AR System DB instance name specify the SQL AlwayOn listener details.
  2. Add the AR System database to the Availability databases. This will create a copy of AR System database in all the instances of  AlwaysOn solution.
  3. Create an AR system database user on remaining instances ( Refer to KB https://support.microsoft.com/en-us/kb/918992).

To configure Microsoft SQL AlwaysOn with AR System manually

  1. Update SQL-Server-Always-On parameter to T in the ar.cfg file. See,  ar.cfg or ar.conf options S-Z  in BMC Remedy AR System documentation.
  2. Db-Host-Name parameter with the SQL AlwayOn listener details.
  3. Restart the server.
  4. Add the AR System database to the Availability databases. This will create a copy of AR System database in all the instances of  AlwaysOn solution.
  5. Create an AR system database user on remaining instances ( Refer to KB https://support.microsoft.com/en-us/kb/918992).

Related topics

Using Microsoft SQL Server with BMC Remedy AR System

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

Comments

  1. Nikhil p shah Shah

    Thank You Hemant , magic is very good. I wish i can see more magic over..

    Mar 25, 2015 06:16
  2. Francisco Candia

    Page about ALTER DATABASE ARSystem SET PARAMETERIZATION FORCED needs to be updated.

    Oct 05, 2015 12:24