Set up a replicated reporting database

If you want to use a replicated database for the Service Manager Reports functionality, perform the following tasks.

Task 1: Prepare a replicated database

1. Prepare a database with the same database type and version as your production database. Follow the same instructions for your database type in Prepare your RDBMS.

   Note The two databases can reside on the same server or on different servers.

2. Perform periodic database synchronization between your production database and replicated database by using the standard database synchronization mechanism. For detailed instructions, refer to your specific database documentation.

Task 2: Configure a database connection between the Service Manager server and replicated database

Service Manager connects to the database through your RDBMS client (for example, Windows ODBC DSN defined for SQL Server). To set up the connection between your Service Manager application server and replicated database, know the name of the database and the login ID and password required to connect to the database server that you created above.

Configure the connection on the Service Manager Server host. In a horizontally scaled environment, you must do so on each of the Service Manager Server hosts. For example, if you use SQL Server, in addition to the ODBC Driver data source you configured for the production database, configure another ODBC Driver data source as a System DSN for the replicated database.

Task 3: Update the Service Manager server configuration file (sm.ini)

To do this, follow these steps:

1. In the sm.ini file, add the following parameters (the parameter values are for demonstration purposes only):

   Caution The last four lines must be inserted as a group. Do not insert any other lines between them.

   For SQL Server:

   [sqlserver]
   sqldb:940pd
   sqllogin:pdadmin/passw0rd 
   [sqlserver_replicate] 
   sqldb:940rp
   sqllogin:rpadmin/passw0rd
   dashboardonreplicatedb

   For Oracle:

   [oracle]
   sqldb:940pd
   sqllogin:pdadmin/passw0rd 
   [oracle_replicate] 
   sqldb:940rp
   sqllogin:rpadmin/passw0rd
   dashboardonreplicatedb

   Parameter	Description
   [sqlserver]	This parameter creates a section header in the initialization file for information about an Microsoft™ SQL Server database. You only need to provide this parameter if you have set sqldictionary and are using a SQL Server database.
   [sqlserver_replicate]	This parameter creates a section header in the initialization file for information about a replicated Microsoft™ SQL Server database. You only need to provide this parameter if you have created a replicated SQL Server reporting database.
   [oracle]	This parameter creates a section header in the initialization file for information about an Oracle™ database. You only need to provide this parameter if you have set sqldictionary and are using an Oracle database.
   [oracle_replicate]	This parameter creates a section header in the initialization file for information about a replicated Oracle™ database. You only need to provide this parameter if you have created a replicated Oracle reporting database.
   sqllogin	This parameter defines the user name and password that Service Manager uses to authenticate connections to the RDBMS. You must use a slash character to separate the user name and password. If you omit this parameter, then the server attempts to authenticate the connection using the user name and password of the user who started the Service Manager Server, however this feature requires the Service Manager Server and the RDBMS server to use the same operating system. If the Service Manager Server and the RDBMS server use different operating systems, then you must specify a sqllogin value.
   dashboardonreplicatedb	This parameter creates a section footer in the initialization file for information about a replicated reporting database.

2. (Optional) Add the following parameters to the sm.ini file of each Service Manager server:

   dashboardquerycache_enable:1
   dashboardquerycache_dbtime:100
   dashboardquerycache_expire:10

   Parameter	Description
   dashboardquerycache_enable	Enables Service Manager to cache report query results in the database.
   dashboardquerycache_dbtime	Defines a threshold for report query execution time that will trigger query caching (default: 100 milliseconds). Only when a query's execution time reaches or exceeds this threshold, the query results are cached in the database.
   dashboardquerycache_expire	Defines how long cached report query results will be expired in the database (default: 10 minutes). When the specified time is reached, the cached query results are expired and will be refreshed at a later time.

   Note The query cache resides on the production database. It stores the results of queries against both the production database and replicated database.

3. Save the configuration file, and restart the Service Manager server.

In a horizontally scaled system, you need to repeat this task on each of the Service Manager server.

Task 4: Configure the Reporting module to use the replicated database

To do this, follow these steps:

1. Log in to Service Manager as a system administrator.
2. Go to Reporting > Administration > Report Settings.

3. Select the Use Replicated Database by default when users create reports option.

   If this option is selected, the Use Replicated Database setting is enabled by default in each new report that users create. However, users can disable the Use Replicated Database setting in an individual report.

4. Select the Use Production Database when Replicated Database is disabled option.

   Select this option so that Service Manager can still use the production database to generate reports when the replicated database is not enabled or the database connection parameters are not defined correctly in the server configuration file (sm.ini).

5. Click Save.