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:
- Back up the SQL Server log files, and then change the SQL Server Transaction Logging mode from FULL to SIMPLE.
- If the database is notconfigured 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.
- 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.
- 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.
- Install SQL Server clients (the drivers).
For remote installations, install on the same computer on which the BMC Remedy AR System server in installed.
- Create an instance of the database.
- Set your SQL Server connections to allow TCP/IP:
- Open the SQL Server Configuration Manager.
- Click Network Configuration for your SQL Server instance.
- Ensure that TCP/IP is enabled.
- 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.)
- Restart all SQL Server services to effect this change.
Determine data file and log file sizes for your SQL Server database.
During the installation, you are required to declare table sizes, which enables you to presize the data files to improve application performance.
- 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.
- 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).
- If you are using Microsoft SQL Server 2005, ensure you have installed the most current service pack.
- Stop the AR System server to ensure that all connections to the AR System database are closed.
(For a server group or a shared database) Stop all AR System instances.
Set the following SQL Server forced parameterization and SNAPSHOT isolation parameters:
ALTER DATABASE ARSystem SET PARAMETERIZATION FORCED ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT ON
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 FORCED;
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.
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.
Create the folder (for example, c:\data) before you create the database; otherwise, database creation fails.
In a Query Window, run the following command:
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
Use the created database; for example:
Create a user name and logon; for example:
CREATE USER "ARAdmin" FOR LOGIN "ARAdmin"
Make the user the db_creator; for example:
sp_addrolemember 'db_creator', 'ARAdmin'