Configuring the GVP Reporting Server Database
The GVP 8.5 Reporting Server requires one of two supported relational database systems is installed Microsoft SQL Server or Oracle. The database and the Reporting Server can share a host or you can install the database on a separate host. This section describes how to create the GVP Reporting database schema and partitioning the database in the following topics:
- Before You Begin
- Setting Up the Database
- Partitioning CDR and Event Log Tables
- Recovery Model for Microsoft SQL Server
Before You Begin
- Genesys recommends that the VP Reporting Server is installed before you or your database administrator create a database in your Database Management System (DBMS).
- Ensure that a fully functional instance of the Microsoft SQL Server or Oracle exists in your deployment.
- Create the Reporting Server database.
ImportantThis section describes the creation of the database schema only. The setup of the Microsoft SQL Server or Oracle 10g instances is outside the scope of this document. For information about setting up these instances, see the vendor documentation.
- For installations using an Oracle database, the database administrator should grant the following system privileges to the user(s) who will own the Reporting Server schema:
- CREATE TRIGGER
- CREATE SEQUENCE
- CREATE TABLE
- CREATE PROCEDURE
- FORCE TRANSACTION
- CREATE VIEW
- CREATE SESSION
- UNLIMITED TABLESPACE
Setting Up the Database
This section includes a description of a generic procedure to create the database schemas.ImportantGenesys recommends that the user setting up an Oracle database be a different user than the one used to create the Configuration Server database.
A minor change is required when Reporting Server is deployed with an Oracle RAC database. When the hibernate.remote.database configuration option is used, the Reporting Server internally appends some parameters to the value of the hibernate.remote.url option, including the value of the hibernate.remote.database option. Therefore, ensure the hibernate.remote.url option is properly configured for use with Oracle RAC by configuring the hibernate.remote.database option with the value blank.
The table below contains the paths to the scripts that are used to create the database schema. Select the path to the script that matches the edition (standard or enterprise) of the database that you selected during installation of the Reporting Server component.
Table 30: Database Script Files Script file Default path mssql_schema.sql
<Installation_Directory>\Program Files\GCTI\gvp\VP Reporting Server 8.5\RS_App_Name\scripts\standard <Installation_Directory>\Program Files\GCTI\gvp\VP Reporting Server 8.5\RS_App_Name\scripts\enterprise
<Install_Dir>\Program Files\GCTI\gvp\VP Reporting Server 8.5\RS_App_Name\scripts\standard <Install_Dir>\Program Files\GCTI\gvp\VP Reporting Server 8.5\RS_App_Name\scripts\enterprise
<Install_root>/opt/genesys/gvp/VP_Reporting_Server_8.5/scripts\standard <Install_root>/opt/genesys/gvp/VP_Reporting_Server_8.5/scripts\enterpriseImportantThe Reporting Server supports the partitioning option for Oracle 10g or 11g enterprise version, and SQL Server 2008 enterprise versions. For all other databases, including SQL Server 2005 (enterprise or standard), use the \scripts\standard\ path to find the appropriate scripts.
Reporting Server in Partition Mode on Oracle
When the Reporting Server is installed in partitioned mode (Enterprise) on Oracle by and the default GATHER_STATS_JOB option is used to automatically gather statistics, server performance can be severely affected. In partitioned mode, the Reporting Server database uses rotating staging tables that are flushed throughout the day. These volatile tables are not suited for automatic statistics gathering.
Genesys recommends the following:
- Disable the GATHER_STATS_JOB option before you install the Reporting Server database to ensure that inaccurate statistics are not associated with the staging tables.
- Use the provided lock-stats script to lock the staging tables, for example:
This script uses the following Oracle SQL procedure:
…where the ownname option must be the name of the schema that is associated with the Reporting Server tables. The script assumes the schema name is REPORTING. You must replace all instances of REPORTING with the proper schema name.
Procedure: Setting Up a Database for the Reporting Server
There are many available query tools that SQL Server and Oracle 10 g can use to execute Structured Query Language (SQL) scripts. For example, SQL Server Management Studio is included in Microsoft SQL Enterprise edition, and Oracle SQL Developer is available on the Oracle website.
Oracle is the only supported database when the Reporting Server DBMS is installed on Linux operating systems.
Microsoft cumulative update packages for SQL Server contain the most recent hot fixes and security fixes. Ensure that the hot fix that is listed as a prerequisite for this procedure is included in the Service Pack that you have installed.
- Verify that:
- Microsoft SQL hot-fix build 3175 must be installed.
- The Microsoft SQL Server Management Studio development tool is installed on the SQL Server.
- Sun Java Runtime Environment, is installed.
- On the SQL server, select the Reporting Server database (MSSQL or Oracle) and run the appropriate script. See Table: Database Script Files for a list of DBMSs and the corresponding name and location of the initialization script files.
- Open the folder that matches your database type.
- Load and execute the initialization script that corresponds to your DBMS. For information about how to upgrade the database schemas, see Migrating to GVP 8.5.
Partitioning CDR and Event Log Tables
GVP 8.1.2 and later releases support partitioned CDR and Event Log tables in the Reporting Database. The Resource Manager, Media Control Platform, Call Control Platform, VAR CDR tables, and Event Logs table tend to grow rapidly in large-scale environments. To improve the read/write performance, these database tables can be split into multiple partitions, each of which represents a specific period of time. Partitioning is supported only in the Enterprise editions of Oracle and Microsoft SQL databases.
The Reporting Database can be configured for partitioning in the following ways:
- During the installation of Reporting Server 8.1.2, if the user selects the Enterprise edition of either product, the rs.partitioning.enabled configuration option in the persistence section is automatically set to true.
- The rs.partitioning.partitions-per-day option in the persistence section is used to change the number of partitions per day. The default value is 6, however, the value can be increased in environments that experience high call volumes.
TipWhen database partitioning is enabled, Genesys recommends that you not change the partitioning mode of operation or the number of partitions (even after the Reporting Server is started), because of issues that might arise if the database schema or stored data is changed.
- The Reporting Database 8.1.2 schemas are different for enterprise and standard editions and are located in different directories. To create the schema that is compatible with your database selection, see Procedure: Setting Up a Database for the Reporting Server, on page 407.
For more a complete list of configuration options for database partitioning, see the
GVP 8.5 User's Guide.
Recovery Model for Microsoft SQL Server
If you are installing your database on Microsoft SQL Server, Genesys recommends that you use the SQL Server Simple Recovery Model, which is a simple backup that can be used to replace your entire database in the event of a failure or to restore your database to another server. This mode enables you to do a complete backup (all of the data) or a differential backup (data that has changed since the last complete backup only).
This model is a basic recovery model. Every transaction is written to the transaction log, however, the space is reused by new transactions when previous transactions are complete and written to the data file. Since this space is reused, you cannot do a point-in-time recovery. The most recent restore point becomes either the complete backup or the latest differential backup. However, because the transaction log space is reused, the log does get continuously larger, as it does in the Full Recovery Model.
For more information about the types of data this recovery mode is used for and a complete list of backups you can use, see the MSSQL Tips web site.TipIf you choose not to use this recovery mode, Genesys recommends that you backup your transaction logs regularly.
Use one of these two methods to configure the Simple Recovery Model...
Use T-SQL to enter the following command line:
ALTER DATABASE <dbName> SET RECOVERY recoveryOption GO
where <dbName> is the name of the database.
Use SQL Server Management Studio:
- Right-click on database name and select Properties.
- Select the Options page.
- Click the Recovery model: drop-down menu and select Simple.
- Click OK to save the configuration.
Procedure: Specify a Non-schema-owner Oracle User on Reporting Server (Running Oracle Partition Mode)
Enable Reporting Server to run in Oracle partition mode, using an application user other than the schema owner. (Please edit and change "password", usernames and table space names if needed.)
Oracle documentation offers a reason for this procedure: System privileges can be very powerful, and should be granted only when necessary to roles and trusted users of the database. It is better to define application users and grant those users the necessary privileges on the schema owners objects.
- Install your Oracle database.
- Create Users, role and current schema trigger.
Note that the schema user's system privileges must be granted directly; not through a role.
- Run the script in the following link in a connection as SYSDBA:
[+] Copy the script from this link
- Run the Reporting Server script in
using the schema owner's connection.
- Run the script in the following link, using the schema owner's connection:
[+] Copy the script from this link