Preparing your Microsoft SQL Server database before you upgrade
This section describes the steps you should perform with your Microsoft SQL Server database when you install BMC Remedy AR System or any application in the BMC Remedy IT Service Management Suite.
- Tips to remember
- To prepare your Microsoft SQL Server database
- To optimize Microsoft SQL Server 2005 (or later) after you finish installing or upgrading the AR System server
- Windows Authentication mode and Microsoft SQL Server
- To pre-create a Microsoft SQL Server database
- Purge the transaction log frequently to prevent it from filling up during installation.
- 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, make sure that you have set the following:
- Set the BMC Remedy AR System data file size to 1 GB or greater; BMC Software recommends at least 2 GB. If you are planning to install CMDB or ITSM applications in addition to BMC Remedy AR System, add 2048 MB to the data file for each additional application.
- Set the log file size to 1 GB or greater. If you plan to install CMDB or ITSM applications in addition to BMC Remedy AR System, add 2048 MB to the data file for each additional application.
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 the SQL Server database on the same computer where BMC Remedy AR System is installed, or on a remote server that is networked to the computer where you plan to install BMC Remedy AR System.
- Install SQL Server clients (that is, the drivers).
For remote installations, install the SQL Server clients on the same computer as the BMC Remedy AR System server.
- 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.
- Make sure that TCP/IP Protocol is enabled.
- View the TCP/IP Properties dialog box for your database instance, and make sure 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. This enables you to pre-size the data files to improve application performance.
- Make sure 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 2005 (or later) after you finish installing or upgrading the AR System server
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, make sure 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 the 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
- Verify the values by issuing the following command:
SELECT is_read_committed_snapshot_on FROM sys.databases where name = 'ARSystem'
Microsoft SQL Server installation can support two authentication modes:
- Windows authentication mode
- Mixed authentication mode
To find the supported authentication mode in your SQL Server environment, connect to the SQL Server instance from Management Studio > Server Properties > Security.
If only Windows authentication mode is supported, choose Windows authentication when you install the BMC Remedy AR System server.
If mixed authentication mode is supported, choose Windows authentication or SQL Server authentication when you install the BMC Remedy AR System server.
If you do not have DBA 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 pre-create the database. Otherwise, the database creation fails.
- In a Query Window, run the following command:
- Create a database, for example:
- Use the created database, for example:
- Create a user name and login, for example:
- Make the user the db_creator, for example:
Using Microsoft SQL Server with BMC Remedy AR System from the AR System documentation