Installing QPR ProcessAnalyzer Server

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

This page describes, how to install QPR ProcessAnalyzer 2022.6 and later releases (.Net 6 based). To install older (.Net Framework based) QPR ProcessAnalyzer release, click here.

If you are updating an existing system, follow 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 6.
  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 scripts initializedb.sql and optimizedb.sql, available in the PA_Deploy.zip package. The initializedb.sql script creates database tables, primary and foreign keys, indexes and default configuration data. The optimizedb.sql script take 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 steps 5 and 6 are not applied. 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 6 Runtime

QPR ProcessAnalyzer server requires the below mentioned .Net 6 runtime components. Install them as follows:

  • Go to https://dotnet.microsoft.com/en-us/download/dotnet/6.0, and download following packages:
    • In the ASP.NET Core Runtime section, the Hosting Bundle for Windows.
    • In the .NET Desktop Runtime section, the x64 version for Windows.
  • Install the Hosting Bundle and the .NET Desktop Runtime to the machine running QPR ProcessAnalyzer server.

IIS Configuration

Here are the required IIS configurations for QPR ProcessAnalyzer Server.

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 QPR ProcessAnalyzer Server installation package to the created directory (folder containing the appsettings.json file).
  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)

Go through the settings in the table below to set appropriate values. At least settings ProductActivationCode, FirstName, LastName, Email, LogFilePath, LicenseFilePath and SqlDatabaseConnectionString need to be set for the system to work.

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.
LicenseFilePath QPR ProcessAnalyzer Server license file path including the file name. For example C:\ProgramData\QPR Software\QPR ProcessAnalyzer\pa_service.lf. 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 Determines after which time (in milliseconds) a pending requests in QPR ProcessAnalyzer Server are returned back with an exception containing an operation id. The request continues to run in the QPR ProcessAnalyzer Server, and QPR ProcessAnalyzer ScriptLauncher is then able to make a further request to query the progress of the task. Note that the Web UI doesn't support this behavior, so set the limit large enough so that it's not reached in normal usage, e.g. 10 minutes (600000 ms).
UseProxyServer If needed, define the proxy server for QPR ProcessAnalyzer Server activation.
Country (optional) User organization 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 organization address (used for QPR ProcessAnalyzer Server activation).
PostalCode (optional) User organization postal code (used for QPR ProcessAnalyzer Server activation).
Region (optional) User organization region (used for QPR ProcessAnalyzer Server activation).

Logging Settings

QPR ProcessAnalyzer uses log4net for log writing (https://logging.apache.org/log4net/) allowing to flexibly define log writing related settings. For example, the following settings can be set in the log4net.config file (located in the same folder as appsettings.json):

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).