Configuring the Microsoft SQL Server

This section describes the steps to configure your Microsoft SQL Server database before you install Remedy Action Request (AR) System or any application from the 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. However, if you select FULL recovery model to support AlwaysOn or DB mirroring, make sure that the log size is within the size limit. Purge the transaction log regularly to prevent it from exceeding the maximum size limit during installation.
• If the database is not configured to extend automatically, ensure that you have have completed the following actions:
  • Set the Remedy AR System data file size to at least 2 GB. If you plan to install BMC Atrium CMDB or Remedy ITSM applications in addition to 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 Remedy ITSM applications in addition to Remedy AR System, add 2 GB to the data file for each additional application.

To prepare your Microsoft SQL Server database

1. Install the Microsoft SQL Server database on a remote server that is networked to the computer on which you plan to install Remedy AR System.
   For testing purposes, you can install the database on the same computer on which Remedy AR System is installed.
2. Install SQL Server clients (the drivers).
   For remote installations, install on the same computer on which the 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 Remedy AR System server uses JDBC to communicate with the database.

Windows Authentication mode and the Microsoft SQL Server

Choose one of the following authentication modes when you install the 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 Remedy AR System server on the Windows Server, log on to the Windows Server with the domain account that is used for Windows authentication mode.
• The domain account used with Windows authentication mode must be a full administrator on the Windows Server and must have db_securityadmin and setupadmin roles in SQL Server. The domain account must be Database Owner (dbo) of the Remedy AR System database.
• When the install or upgrade completes, you can deny certain policies to the domain account used with Windows authentication mode for security reasons. However, the DBA must ensure that denying policies do not result in the loss of functionality. For any upgrades later, all the policy restrictions must be removed and the domain account must 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 precreate a Microsoft SQL Server database for Remedy AR System

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.

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 dbcreator; for example:

   sp_addrolemember 'dbcreator', 'ARAdmin'

6. 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 full;
   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;

7. Verify the values by issuing the following command:

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

Considerations for Microsoft SQL Server 2012 or later

If you are using Microsoft SQL Server 2012 or later database you will need to create synonyms for the trace_xe_action_map and trace_xe_event_map system procedure before Running the 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

To upgrade to Microsoft SQL Server 2014, consider the following:

• You can upgrade to Microsoft SQL Server 2014 from the following versions:

  • Microsoft SQL Server 2005 SP4 or later

  • Microsoft SQL Server 2008 SP3 or later 

  • Microsoft SQL Server 2008 R2 SP2 or later.

  • Microsoft SQL Server 2012 SP1 or later

• As a prerequisite you need .net Framework 3.5 Service Pack 1. 

• For more reference information, refer to the following links:
  • https://msdn.microsoft.com/en-us/library/bb677622.aspx
  • https://msdn.microsoft.com/en-us/library/ms143506.aspx

Configuring Microsoft SQL AlwaysOn solution with Remedy AR System

You can configure Microsoft SQL AlwaysOn solution with Remedy AR System.

BMC has tested the Microsoft SQL AlwaysOn solution where three nodes, each running a single 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 do not share any storage with another node. Each node runs a single instance of SQL Server and has its own copy of the data.

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 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. Complete the entire Remedy ITSM Suite installation.
3. Add the Remedy AR System database to the Availability databases. This will create a copy of AR System database in all the instances of AlwaysOn solution.
4. Create a Remedy 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 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