Installing QPR ProcessAnalyzer Server

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

If you are updating an existing installation, follow instead the update instructions.


Introduction

This page describes QPR ProcessAnalyzer Server installation. Installation steps are:

  1. Check that System Requirements are fulfilled.
  2. Setup SQL Server databases.
  3. Install Roles and Features for IIS.
  4. Install .Net Runtime.
  5. Configure IIS for QPR ProcessAnalyzer.
  6. Configure appsettings.json.
  7. Go through the environment settings stored to the PA_Configuration table and set them if needed.
  8. If using SAP data import, install SAP NetWeaver RFC Library.
  9. Setup the Scripting Sandbox.
  10. Activate QPR ProcessAnalyzer server license. The activation occurs online automatically during the server startup if internet is available. If not, the activation needs to be done manually using the Activation Utility).
  11. If using Snowflake processing, install the Snowflake ODBC driver and set the SnowflakeConnectionString setting. For more information, see Snowflake Connection Configuration.

Create and Initialize Database

QPR ProcessAnalyzer needs the following three SQL Server databases:

  • Metadata database for storing system objects, such as projects, scripts, users and usage logs. Usually this database doesn't require much storage space, and queries to the database are lightweight, but still low latency in query performance is beneficial (i.e., prefer low latency over high data throughput in the storage hardware). Connection string to this database is set in the appsettings.json file.
  • Datatables database for storing data for the datatables, containing events and cases in the models and also data processed by ETL. Required storage space for this database depends on how much data is stored to the datatables. Queries to this database are heavy and data intensive, but usually low latency doesn't provide benefits (i.e., prefer high data throughput over low latency in the storage hardware). Connection string to this database will be set in the configuration table (key SqlServerConnectionString).
  • Sandbox database for running scripts written using SQL. This database is primarily needed for the SQL query processing where only temporary objects are stored, and thus the tempdb performance has a critical role. It's also possible to configure the sandbox database to permanently store database objects, and in that case also this database requires space for storage.

It's possible to connect to the SQL Server either using Windows authentication (Windows account) or SQL Server authentication.

Follow these steps to create and initialize the metadata database:

  1. Open SQL Server Management Studio and create a new database. You can use name QPR_ProcessAnalyzer. You must select <default> for the collation, so that the database collation will be same as the server collation. This is because QPR ProcessAnalyzer also uses the tempdb (which is using server collation), the collations between the QPR ProcessAnalyzer database and tempdb need to match.
  2. Create a login in Security > Logins > New Login....
  3. In the User Mapping tab, assign the login to the QPR ProcessAnalyzer database. For an easy setup to get started with, check the db_owner role. For production use, the hardened configuration need to be set.
  4. Run database setup script initializedb.sql available in the QPR ProcessAnalyzer Server release package. This script creates the database tables, primary and foreign keys, indexes and default configuration data. In addition, it takes into use snapshot isolation (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server) and simple recovery model (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server).
  5. Optionally, increase the Initial Size and Autogrowth settings of the database datafiles for better performance (in Database Properties > Files).
  6. Implement scheduled maintenance tasks for the database according to SQL Server best practices, e.g. backups and regular defragmenting of indexes.

Follow similar steps for the datatables database, except the step 4 is not applied. As an example, you can use database name QPR_ProcessAnalyzer_datatables.

Setup of the sandbox database is instructed in section Scripting Sandbox.

Install IIS Server Roles and Features

The following Windows Server Roles and Features should be enabled:

  1. On the taskbar, click Server Manager and click the Manage menu, and then click Add Roles and Features.
  2. In the Add Roles and Features wizard, click Next. Select the installation type and click Next. Select the destination server and click Next.
  3. In the Server Roles section, select the following components:
    • Internet Information Services / Web Management Tools: IIS Management Console
    • Internet Information Services / World Wide Web Services / Application Development Features: .Net Extensibility 4.8, Application Initialization, ASP.NET 4.8, ISAPI Extensions, ISAPI Filters
    • Internet Information Services / World Wide Web Services / Common HTTP Features: Default Document, Static Content (note: please do not install WebDAV Publishing because its handler mappings are not directly compatible with QPR ProcessAnalyzer)
    • Internet Information Services / World Wide Web Services / Health and Diagnostics: HTTP Logging
    • Internet Information Services / World Wide Web Services / Performance Features: Static Content Compression
    • Internet Information Services / World Wide Web Services / Security: Request Filtering
  4. In the Features section, select the following components:
    • .Net Framework 4.8 Advanced Services: ASP.NET 4.8
  5. On the Confirm installation selections page, click Install.
  6. On the Results page, click Close.

Install .Net Runtime

QPR ProcessAnalyzer Server requires the .Net runtime. QPR ProcessAnalyzer 2024.1 runs in .Net 8, and earlier releases run in .Net 6. Install .Net as follows:

  1. Go to https://dotnet.microsoft.com/en-us/download/dotnet/8.0 (for .Net 8) or https://dotnet.microsoft.com/en-us/download/dotnet/6.0 (for .Net 6), and download the latest release of the package Hosting Bundle for Windows (in the ASP.NET Core Runtime section).
  2. Install the downloaded components to the QPR ProcessAnalyzer Server computer.

IIS Configuration

Here are the required IIS configurations for QPR ProcessAnalyzer Server.

Configure HTTPS connection

QPR ProcessAnalyzer requires HTTPS/TLS connection. The required certificate can be set to IIS or load balancer (if using it). QPR ProcessAnalyzer can temporarily be used with HTTP but it requires to remove the Strict-Transport-Security header.

Create IIS Application and Application Pool

The steps assume that the QPR ProcessAnalyzer is set up to the IIS Default Web Site.

  1. Download QPR ProcessAnalyzer Server package available in the downloads page.
  2. Create a directory C:\inetpub\wwwroot\QPRPA\.
  3. Copy the contents of the QPRProcessAnalyzerServer folder (previously named as Service) (but not the folder itself) from the package into the previously created directory.
  4. In the IIS Management Console, create an application pool named QPRPA (in Application Pools -> Add Application Pool...). Set .NET CLR Version to No Managed Code and select the Start application pool immediately.
  5. Go to Sites and find the web site for QPR ProcessAnalyzer. Select the previously created QPRPA directory and select Convert to Application. In the dialog, select the previously created application pool and also select Enable Preload.
  6. Set an https binding to the IIS web site (click in Edit Bindings...), unless using QPR ProcessAnalyzer through a load-balancer terminating the https connection.
  7. Create a directory C:\ProgramData\QPR Software\QPR ProcessAnalyzer\Logs\ for log files. Note that this directory will be later set to the appsettings.json file.

Set Application Pool Always Running

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Select QPR ProcessAnalyzer application pool and click Advanced Settings....
  3. Set (General) > Start Mode to AlwaysRunning. Click OK.

Enable Application Preloading

  1. Open Internet Information Services (IIS) Manager and in the left side hierarchy under Sites select the QPR ProcessAnalyzer application.
  2. Click Advanced Settings... on the right pane.
  3. Set Preload Enabled to True. Click OK.

Disable Application Pool Idle Timeout

When the IIS application pool that runs QPR ProcessAnalyzer Server shuts down, all loaded models are dropped and script running is stopped. By default, idle application pools (i.e. QPR ProcessAnalyzer hasn't been used for some time) are shut down automatically after a certain time. To disable application pool idle timeout:

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Locate QPR ProcessAnalyzer application pool, select it and click Advanced Settings....
  3. Set Process Model -> Idle Time-out (minutes) to 0. Click OK.

Disable Application Pool Regular Recycling

When the IIS application pool that runs QPR ProcessAnalyzer Server shuts down, all loaded models are dropped and script running is stopped. By default, application pools are restarted (recycled) regularly. To disable application pool regular recycling:

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Locate QPR ProcessAnalyzer application pool, select it and click Advanced Settings....
  3. Set Recycling -> Regular Time Intervals (minutes) to 0. Click OK.

Disable Application Pool Overlapping Recycle

If using regular application pool recycling, e.g. at specific times, it's recommended to disable the overlapping recycles. When using overlapping recycles, a new instance is started before shutting down the previous, which will take much more memory. Disable the overlapping recycling as follows:

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Locate QPR ProcessAnalyzer application pool, select it and click Advanced Settings....
  3. Set Recycling -> Disable Overlapping Recycle to True. Click OK.

IIS Application Pool User Profile Loading

To enable user profile loading:

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Click QPR ProcessAnalyzer application pool and click Advanced Settings....
  3. Set Process Model > Load User Profile to True. Click OK.

The user profile loading allow access to their cryptographic store and environment variables (such as %TEMP%). Additional information can be found from Compatibility Issues with Application Pool Identities.

Disable Application Pool Pinging

Application pool pinging is a mechanism in IIS to monitor that applications are responding normally. In QPR ProcessAnalyzer, application pool pinging may cause issues when there are long running analysis calculations, because during them the application pool might not respond to a ping. As a result, IIS shuts down QPR ProcessAnalyzer application pool (that IIS considers unhealthy), and models are dropped from the memory which degrades performance.

To disable application pool pinging:

  1. Open Internet Information Services (IIS) Manager and click Application Pools in the left side hierarchy.
  2. Locate QPR ProcessAnalyzer application pool, select it and click Advanced Settings....
  3. Set Ping Enabled to False. Click OK.

More information about IIS Application Pool Pinging: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc725836(v=ws.10)

Server settings file (appsettings.json)

The appsettings.json file is the main settings file for the QPR ProcessAnalyzer Server, Go through the settings in the table below to set appropriate values to the appsettings.json. At least ProductActivationCode, FirstName, LastName, Email, LogFilePath, LicenseFilePath and SqlDatabaseConnectionString are mandatory settings to be defined.

The appsettings.json needs to be a correctly formatted json file, and thus the backslashes and quotation marks in string values need to be escaped as follows: \ → \\ and " → \".

Setting Description
ProductActivationCode QPR ProcessAnalyzer Server product license activation code. Ask your organization license activation code from customercare@qpr.com.

QPR ProcessAnalyzer Server automatically activates itself if the code is specified and there is an internet connection in the server computer. If there is no internet access, the activation need to be done manually by using the Activation Utility.

FirstName (mandatory) Contact person's first name (in the user organization) (used for QPR ProcessAnalyzer Server activation).
LastName (mandatory) Contact person's last name (used for QPR ProcessAnalyzer Server activation).
Email (mandatory) Contact person's email address (used for QPR ProcessAnalyzer Server activation).
LogFilePath Location of the QPR ProcessAnalyzer Server log file (defined as the full path with the file name). For example: C:\\ProgramData\\QPR Software\\QPR ProcessAnalyzer\\Logs\\PA.log (json escapings are included).
LicenseFilePath QPR ProcessAnalyzer Server license file path including the file name. For example C:\\ProgramData\\QPR Software\\QPR ProcessAnalyzer\\pa_service.lf (json escapings are included). The license file is created when the QPR ProcessAnalyzer Server is activated. If changing the activation code, this file needs to be deleted manually to enable reactivation.
SqlDatabaseConnectionString Define the connection string to QPR ProcessAnalyzer database in SQL Server. Check whether you are using an SQL Server default instance or a named instance, and whether an SQL Server authentication or Windows authentication is used.

Connect to SQL Server default instance using SQL Server authentication:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Connect to SQL Server default instance using Windows authentication:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connect to SQL Server named instance using SQL Server authentication:

Server=myServerName\\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Connect to SQL Server named instance using Windows authentication:

Server=myServerName\\myInstanceName;Database=myDataBase;Trusted_Connection=True;

More information about connections strings: https://www.connectionstrings.com/sqlconnection.

CorsOrigins Defines how QPR ProcessAnalyzer Server responds to the CORS preflight requests. By default, this setting is empty, meaning that CORS is not allowed. To allow CORS from all origins, specify * (note that this is the least secure option). If CORS need to be enabled for a specific site(s), they are defined as a comma separated list to this setting. More about CORS: https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS.
UseXForwardedForAsClientIp If QPR ProcessAnalyzer Server is running behind a web traffic load balancer (e.g. in Amazon Web Services), the following setting needs to be set to True. In that case, the client ip address is fetched from http request X-Forwarded-For (https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-Forwarded-For) field instead of the source of the ip packet. Client ip address is needed for logging and as a security confirmation in addition to the session id. When there is no load balancer and thus X-Forwarded-For is not set, it's important to have this setting as False.
EMSAddress Activation server address https://activation.qpr.com/ that is contacted when the QPR ProcessAnalyzer Server is activated. Do not change this setting.
UseProxyServer If needed, define the proxy server for QPR ProcessAnalyzer Server activation.
ModelLoadOnStartup Determines whether QPR ProcessAnalyzer models having setting LoadOnStartup as true are loaded automatically during server startup. By default, this setting should be set to True. This setting can be used to temporarily disable automatic model loading for all models when for example automatic model loading causes issues.
ResponsePollingInterval Time (in milliseconds) after which pending requests return with an exception, and the request continues to run in the QPR ProcessAnalyzer Server. This behavior is needed by QPR ScriptLauncher which will then make a further request to query the progress of the task. Note that the UI doesn't support this behavior, so set the limit large enough so that it's not exceeded in normal usage, e.g. 10 minutes (600000 ms). The default values is 10 minutes. NOTE: This is a legacy setting which will be removed in future.
ProxyAddress If needed, define the proxy server for QPR ProcessAnalyzer Server activation.
Country (optional) User country (used for QPR ProcessAnalyzer Server activation).
JobTitle (optional) Contact person's job title (used for QPR ProcessAnalyzer Server activation).
PhoneNumber (optional) Contact person's phone number (used for QPR ProcessAnalyzer Server activation).
Address (optional) User address (used for QPR ProcessAnalyzer Server activation).
PostalCode (optional) User postal code (used for QPR ProcessAnalyzer Server activation).
Region (optional) User region (used for QPR ProcessAnalyzer Server activation).
Organization (optional) User organization (used for QPR ProcessAnalyzer Server activation).
DropUnusedFiltersAfter

Duration after which filters that have not been used, are dropped from the memory. Defined in format HH:mm:ss or d.HH:mm:ss, for example 01:00:00 (one hour), 00:30:00 (30 minutes) or 1.00:00:00 (24 hours). When a calculation uses a filter, the filter's last used time is updated (also model's last used time is updated).

This setting can be overridden for each model in the model settings.

Note that this setting doesn't concern Snowflake models.

Logging Settings

QPR ProcessAnalyzer uses log4net component (https://logging.apache.org/log4net/) for writing logs, which allows flexibly define how logging behaves. For example, the following settings can be set in the log4net.config file (located in the bin folder in the server application files):

Installing SAP NetWeaver RFC Library

Extracting data from SAP using the SAP RFC interface requires SAP NetWeaver RFC Library 7.50 which can be installed with the following steps:

  1. Microsoft Visual C++ Redistributable Package 2013 is required and can be downloaded from https://support.microsoft.com/en-us/topic/update-for-visual-c-2013-redistributable-package-d8ccd6a5-4e26-c290-517b-8da6cfdf4f10. Restart the computer after the Microsoft Visual C++ Redistributable Package installation.
  2. For storing the SAP NetWeaver RFC Library dll files, create folder C:\SapNwRfcLib (you may also use another location, but do not store the files in the same place as the QPR ProcessAnalyzer Server application files).
  3. Download and extract SAP NetWeaver RFC Library 7.50 package by following the instructions in https://launchpad.support.sap.com/#/notes/2573790 (SAP login credentials and permissions needed).
  4. In the extracted files, go to folder nwrfcsdk\lib, and copy all .dll files (icudt50.dll, icuin50, icuuc50.dll, libicudecnumber.dll, libsapucum.dll, sapnwrfc.dll) to the folder C:\SapNwRfcLib.
  5. Make sure that the dll files are not in the blocked state as follows: Open the each dll files Properties and in the General tab in the Security section, check the Unblocked checkbox (if that checkbox exists). (Downloaded files may be blocked by default by Windows, preventing their code from executing.)
  6. Make sure that the user account running the IIS application pool for QPR ProcessAnalyzer Server has read access to the folder C:\SapNwRfcLib.
  7. Include the folder C:\SapNwRfcLib to the Windows PATH environment variable (more information: https://superuser.com/questions/284342/what-are-path-and-other-environment-variables-and-how-can-i-set-or-use-them).