Installing QPR ProcessAnalyzer Server

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

This page describes how to install QPR ProcessAnalyzer Server environment. If you are updating an existing installation, please follow the upgrade instructions. Main installation tasks are:

  1. Acquire a Windows server machine and SQL Server (see the System Requirements)
  2. Configure SQL Server for QPR ProcessAnalyzer database
  3. Install and configure Roles and Features for IIS
  4. Install QPR ProcessAnalyzer
  5. Configure IIS for QPR ProcessAnalyzer
  6. Setup Scripting Sandbox
  7. Check settings in Web.config file
  8. Check settings in PA_Configuration table in QPR ProcessAnalyzer database
  9. Setup https and signed certificate
  10. Install QPR UI

Notes:

SQL Server setup

Note: In order to perform the installation successfully, you need to know the basics about server environments. If you're not familiar with Windows Server and SQL Server, please refer, for example, to http://technet.microsoft.com/en-us/library/bb545450.aspx for additional information.

Setup Account for SQL Server Connection

It's possible to connect to SQL Server either using a Windows account (Windows authentication) or SQL Server's own logins (SQL Server authentication). Decide with option to use and setup the needed account.

Create New Database

  1. Open SQL Server Management Studio and create new database, e.g. with name QPR_ProcessAnalyzer.
  2. Add the (optionally) created Windows user as new Login in Security -> Logins.
  3. Assign the created Windows user as the db_owner role for the QPR ProcessAnalyzer database.
  4. Run database initialization scripts initializedb.sql and optimizedb.sql. These scripts are available in the PA_Deploy.zip file.
  5. If needed, change Initial size and Autogrowth size of the datafiles of the QPR ProcessAnalyzer database for better performance.
  6. Check that the default server collation is used.
  7. Implement a scheduled maintenance task that defragments fragmented indexes.

Adjust SQL Server Settings

For better performance, it's advised you adjust the following SQL Server settings. Set default fill factor to 95% for new tables. Note that you need to restart SQL Server for this to take effect (for more information, see https://msdn.microsoft.com/en-us/library/ms190470.aspx):

sp_configure 'show advanced options', 1;  
GO
RECONFIGURE;  
GO
sp_configure 'fill factor', 95;  
GO
RECONFIGURE;  
GO

Install Server Roles and Features for IIS

The following Server Roles and Features should be enabled for Microsoft Internet Information Services (IIS):

  • On the taskbar, click Server Manager, and then click the Manage menu, and then click Add Roles and Features.
  • In the Add Roles and Features wizard, click Next. Select the installation type and click Next. Select the destination server and click Next.
  • In the Server Roles section, select the following components:
  • Common HTTP Features: Default Document, Directory Browsing, HTTP Errors, Static Content
  • Health and Diagnostics: HTTP Logging
  • Performance: Static Content Compression
  • Security: Request Filtering, Windows Authentication
  • Application Development: .Net Extensibility 4.5/4.6, Application Initialization, ASP.NET 4.5/4.6, ISAPI Extensions, ISAPI Filters
  • Management Tools: IIS 6 Management Console

AddRolesAndFeaturesWizard1.JPG

  • In the Features section, select the following components:
    • .Net Framework 4.5/4.6 Features:
      • .Net Framework 4.5/4.6
      • ASP.Net 4.5/4.6
      • WCF Services: HTTP Activation, TCP Port Sharing

SelectFeaturesWizard1.JPG

  • User Interfaces and Infrastructure: Graphical Management Tools and Infrastructure, Server Graphical Shell (Only for Windows Server 2012 or earlier version)
  • Windows Powershell: Windows Powershell 2.0, 3.0 or newer if available, Windows Powershell ISE
  • Windows Process Activation Service: Process Model, Configuration APIs
  • WoW64 Support

SelectFeaturesWizard2.JPG

  • On the Confirm installation selections page, click Install.
  • On the Results page, click Close.

Install QPR ProcessAnalyzer Server

Follow these instructions to install QPR ProcessAnalyzer server using the Web Deploy. There is also available a manual installation method. The QPR ProcessAnalyzer Service can be installed either via a Microsoft Web Deploy package or manually. Note: PA service only supports default port 80 (http) and 443 (https).

  1. Install Microsoft Web Deploy 3.6 or later (Installation package: https://www.microsoft.com/en-us/download/details.aspx?id=43717).
  2. Copy files PA_Deploy.zip, Configuration.ps1 and DeployPAService.ps1 to a same folder from the Service folder.
  3. Open Configuration.ps1 into a text editor and modify settings according to your system and desired installation type. The table below lists all the settings in the file.
  4. Launch Microsoft PowerShell as an administrator and navigate to the folder containing the deployment files.
  5. Run .\DeployPAService.ps1 and input the application pool user password when requested (asked only if you chose to use custom credentials).
  6. Optional: Using the QPR ProcessAnalyzer service with HTTPS connections requires a signed SSL certificate to be installed to the server running the service. Self-signed certificates are not supported.
  7. Connect to the database with the QPR ProcessAnalyzer Excel Client, so that the database is initialized, or alternatively run database initialization scripts initializedb.sql and optimizedb.sql. These scripts are available in the PA_Deploy.zip file.
  8. Create a QPR_Admin user with a strong password to the QPR ProcessAnalyzer instance. Store the QPR_Admin user password in a secure location. Use this administrator user to grant user rights to users of the service.
Parameter name Description Example value
$webSiteName Name of the IIS Website, where QPR ProcessAnalyzer Server is installed. The Website must be created when installing QPR ProcessAnalyzer Server. "Default Web Site"
$appPath Path where the application will be created. "$webSiteName\$virtualDir$appName"
$DBConnectionString Connection string used by QPR ProcessAnalyzer Server to access the database. "Server=localhost\databaseinstancename;Database=QPR_PA;Persist Security Info=False;User ID=<SQLServerLogin>;Password=<password>"
$customAppPool Defines if custom application pool is used. $false
$appPoolName Name of the application pool used by QPR ProcessAnalyzer Server. The application pool is created by the QPR ProcessAnalyzer Server installation. "QPRPA"
$customAppPoolCredentials Defines whether the application pool will be run as a user defined in $appPoolUser setting (password for the account will be requested during the installation.) If you set $customAppPoolCredentials = $false in the configuration script, you need to create a new user to DB and name the user NT AUTHORITY\NETWORK SERVICE and grant DBO role to it. $true
$appPoolUser Defines the identity under which the application pool is run. Password for the user is requested during the installation and not stored here. "PA_user"
$logFileName Name of the QPR ProcessAnalyzer Server log file. Only the file name, not the folder path. "PAService_$svcVerMajor.$svcVerMinor.$svcVerRelease.svclog"
$paLogFileName Name of the QPR ProcessAnalyzer log file. Only the file name, not the folder path. "w3wp.txt"
$bindingsConfigSource Defines the config source file for bindings. For http connection, use Bindings_http.config; for https connection use Bindings_https.config. "Bindings_https.config"
$logFilePath Path to the service log file and QPR ProcessAnalyzer log file. "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\201X.X\Logs"
$supportLoadBalancer Defines if QPR ProcessAnalyzer service is running behind a system that hides the original client IP, such as a load balancer. If you are running the service in Amazon Web Services and wish to use the AWS load balancing, set to "True". "True"
$ActivationEMSAddress See the description of the corresponding setting in the web.config section. "https://activation.qpr.com"
$ActivUserFirstName See the description of the corresponding setting in the web.config section. "UserFirstName"
$ActivUserLastName See the description of the corresponding setting in the web.config section. "UserLastName"
$ActivUserEmail See the description of the corresponding setting in the web.config section. "system.owner@company.com"
$LicenseFilePath QPR ProcessAnalyzer Server license file path. The license file is generated automatically, when QPR ProcessAnalyzer Server is activated. The user account defined in the $appPoolUser setting must have write access to the specified folder. If this folder doesn't already exist, you have to create it. It's recommended to use folder C:\ProgramData\QPR Software\QPR ProcessAnalyzer\License\. The lisence file name is not part of this setting. "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\License\"
$licenseFileName Name of the QPR ProcessAnalyzer Server license file. The license file is generated automatically, when QPR ProcessAnalyzer Server is activated. Please, do not change this setting. "pa_service.lf"
$ProductActivationCode See the description of the corresponding setting in the web.config section.
$serverGroupPA This setting is related to Common QPR Authentication.
$serverGroupMD This setting is related to Common QPR Authentication.
$serverGroupMEA This setting is related to Common QPR Authentication.
$responsePollingInterval See the description of the corresponding setting in the web.config section. 300000

IIS Configuration

All the IIS configurations instructed in this chapter needs to be in place for QPR ProcessAnalyzer Server to work correctly.

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)

Setting Up Scripting Sandbox

Create Sandbox Database

  1. Using SQL Server Management Studio, create an empty database with e.g. name QPR_PA_SANDBOX. Change the database to use Recovery model = Simple (in the Options tab).
  2. Click Security > Server Roles > New Server Role... (in the popup menu).
    1. Define name QPR_PA_SANDBOX_ROLE for the new role.
    2. In the Securables section:
      1. For the endpoints Dedicated Admin Connection, TSQL Default TCP (*), TSQL Default VIA and TSQL Named Pipes, click Deny for the Connect permission.
      2. For the endpoint TSQL Local Machine, click Grant for the Connect permission.
      3. For the Servers click Deny for the following permissions: External access assembly, Shutdown, Unsafe assembly, View any database, View any definition and View server state.
      4. For the Servers click Grant for the permission Connect SQL.
  3. Click Security > Logins > New login... (in the popup menu).
  4. Define name QPR_PA_SB_USER and a password (store the password into a safe place).
    1. Define Default database = QPR_PA_SANDBOX.
    2. In the Server Roles tab, check the QPR_PA_SANDBOX_ROLE.
    3. In the User Mapping tab, click QPR_PA_SANDBOX and click checkboxes for db_denydatareader (**?) and db_denydatawriter.
    4. In the User Mapping tab, click master and click checkboxes for db_denydatareader (**) and db_denydatawriter.
    5. In the Securables tab, the only effective securable should be CONNECT SQL (listed in the Effective tab).
  5. Click Databases > System Databases > master > Properties (in the popup menu). In the Database Properties window, click the Permissions tab, and QPR_PA_SB_USER. Check Grant for Connect permission, and Deny for Delete, Insert, Select (*), Update and View database state permissions.
  6. Make the QPR_PA_SANDBOX database read-only as follows: Click the QPR_PA_SANDBOX database and from the popup menu click Properties. In the Options tab, change setting Other options > State > Database Read-only to True.
  7. Ensure that both the SQL Server and Windows Authentication modes are supported in the Server Properties > Security > Server Authentication.

Testing connection

For temporary connection tests with, e.g., SQL Server Management Studio, the following changes should be done:

  • (*) Instead of denying the permission, grant it for: TSQL Default TCP and Select
  • (**) Do not select db_denydatareader in: QPR_PA_SANDBOX and master

After the tests, it is highly recommended to restore the original recommended settings.

Configure QPR ProcessAnalyzer to Use Sandbox

To run QPR ProcessAnalyzer scripts, the sandbox database connection string needs to be configured to QPR ProcessAnalyzer. Setting is located in the PA_Configuration table to the SandboxDatabaseConnectionString field. Example configuration:

Server=SERVERNAME;Persist Security Info=False;User ID=QPR_PA_SB_USER;Password=<password>;Timeout=3600;Asynchronous Processing=True;Encrypt=true

In order to use a sandbox located outside the corporate domain, it is highly recommended that the communication between the QPR ProcessAnalyzer database and the sandbox database is encrypted. Therefore, an SSL certificate of the remote system must be installed as a trusted certificate into the system running the QPR ProcessAnalyzer database. The Encrypt=true and PersistSecurityInfo=false should be used whenever connecting to a database outside corporate domain. For example, a local installation where the sandbox is located on the same system as the QPR ProcessAnalyzer:

Server=localhost;Persist Security Info=False;User ID=QPR_PA_SB_USER;Password=<password>

Web.config settings

PA_Configuration database table

Installing Multiple Instances of QPR ProcessAnalyzer Service

For installing another instance of QPR ProcessAnalyzer Service, the following changes are needed to the Configuration.ps1 file:

  • $appPath: change this to the path of the new instance
  • $DBConnectionString: change this to use the database that is used with the new instance
  • $logFilePath, $logFileName and $paLogFileName: change these so that the logs of the new service instance go to their location. $logFileName is the target for the web service logs, $paLogFileName is the target for QPR ProcessAnalyzer log

It is also recommended to create a separate application pool and an application pool user for the new instance. Thus, change the following:

  • $appPoolName
  • $appPoolUser

Example: The first instance is deployed with the following configuration:

$appPath = "Default Web Site\QPRPAInstanceOne"
$DBConnectionString = "Server=localhost;DataBase=QPR_PA_InstanceOne;Persist Security Info=False;User ID=<SQLServerLogin>;Password=<password>"
$appPoolName = "QPRPAPoolInstanceOne"
$customAppPoolCredentials = $true
$appPoolUser = "DOMAIN\TestUserInstanceOne"
$logFilePath = "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\2014.3\Logs\InstanceOne\"
$logFileName = "PAServiceInstanceOne.svclog"
$paLogFileName = "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\2014.3\Logs\PAInstanceOneLog.txt"

The second instance can be configured as follows:

$appPath = "Default Web Site\QPRPAInstanceTwo"
$DBConnectionString = "Server=localhost;DataBase=QPR_PA_InstanceTwo;Persist Security Info=False;User ID=<SQLServerLogin>;Password=<password>"
$appPoolName = "QPRPAPoolInstanceTwo"
$customAppPoolCredentials = $true
$appPoolUser = "DOMAIN\TestUserInstanceTwo"
$logFilePath = "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\2014.3\Logs\InstanceTwo\"
$logFileName = "PAServiceInstanceTwo.svclog"
$paLogFileName = "C:\ProgramData\QPR Software\QPR ProcessAnalyzer\2014.3\Logs\PAInstanceTwoLog.txt"

Updating QPR ProcessAnalyzer Server to Newer Version

Follow these instructions to update an existing QPR ProcessAnalyzer Server to a newer version:

  1. Download the QPR ProcessAnalyzer Server package (available in the downloads page).
  2. Extract the package to a temporary folder (e.g. C:\temp\PA_Deploy\).
  3. Stop the IIS web site where QPR ProcessAnalyzer is running.
  4. Go to the ProcessAnalyzer Server IIS root folder (e.g. C:\inetpub\QPRPA) and backup the Web.config file.
  5. Delete all files in the ProcessAnalyzer Server root folder.
  6. Copy all files in the extracted PA_Deploy.zip package to the IIS root folder for the ProcessAnalyzer Server (e.g. from C:\temp\PA_Deploy\ to C:\inetpub\QPRPA\).
  7. Open the new Web.config file located in the IIS root folder of QPR ProcessAnalyzer Server (e.g. C:\inetpub\QPRPA\Web.config) using a text editor, and compare it with the backup Web.config file to transfer settings from the old installation to the new. Check carefully at least the following sections:
    • In the bindings section, check that the row in the new file is same as in the old file, i.e. either of the following:
      • <bindings configSource="Bindings_https.config" />
      • <bindings configSource="Bindings_http.config" />
    • In the connectionStrings section, copy the connectionString attribute value.
    • In the sharedListeners section, check attribute initializeData (log file location).
    • In the system.runtime.caching section, check the properties cacheMemoryLimitMegabytes, physicalMemoryLimitPercentage and pollingInterval.
    • In the applicationSettings section, copy all settings values (in thevalue tags). There are settings in the two subsections Qpr.ProcessAnalyzer.Common.Properties.Settings and Qpr.ProcessAnalyzer.Service.Properties.Settings.

Uninstalling QPR ProcessAnalyzer Server

Removing using Web Deploy

If you have used the web deploy package to install the QPR ProcessAnalyzer Service, uninstalling QPR ProcessAnalyzer Service can be done by running the Uninstall.ps1 script. The uninstall script will need the configuration file (e.g. Configuration.ps1) used when the service was installed. If you have lost the configuration file or it doesn't have the original values, you can remove the QPR ProcessAnalyzer Service manually by following these steps:

  1. Open Internet Information Services (IIS) Manager, and locate QPR ProcessAnalyzer application in the left side hierarchy.
  2. Select Deploy -> Delete Application and Content. NOTE! Verify that the prompt says Delete Application and Content instead of Delete Web Site and Content because otherwise you accidentally trying to delete the whole web side.
  3. Remove the application pool created for QPR ProcessAnalyzer Server (if any other applications are not using it).

Removing Manually

  1. Shut down the QPR ProcessAnalyer IIS application pool.
  2. Remove all applications from the virtual directory.
  3. Remove the virtual directory that contained the applications.
  4. Remove the IIS application pool.
  5. Backup the database.
  6. Remove the database, taking care to not excise existing backups.
  7. Remove the database login.
  8. Remove the Windows user account.

Troubleshooting QPR ProcessAnalyzer Server Installation