QBO3 Installation Guide

Overview

Note: this page has been deprecated in favor of our wiki.

A typical QBO3 installation includes:
  • At least 1 MSSQL database,
  • At least 1 IIS website, and
  • At least 1 installation of the qbo Queue Service Windows service
A recommended production QBO3 installation includes:
  • 2 instances of MSSQL 2012 or later
    • cold standby with log shipping is cost effective
  • 1 regression-tested QBO3 image that can be "spun up" into a virtual environment (e.g. AWS AMI)
    • this image will be used for both web and app servers
    • an app server is identical to a web server except that the "Queue Service" windows service is started, and the machine is not on a load balancer
  • 2 web servers
    • based on the above QBO3 image
    • Queue Service disabled
    • minimum of 2 CPUs and 4GB memory
  • 2 app servers
    • based on the above QBO3 image
    • Queue Service enabled
    • minimum of 2 CPUs and 4GB memory
Auto-scaling of both web and app servers can be configured to handled load that exceeds the capacity of 4 machines.

Prerequisites:
  • Database: Microsoft SQL Server running SQL 2008 or later
    • TCP/IP protocol recommend
  • Web Server: Microsoft Internet Information Server 7.5 or later 
    • must include .NET framework 4.71 or later
    • Powershell 4 recommened
    • WebDeploy 3 or later recommended
  • App Server: Microsoft Windows 2008 or later for installation of the qbo Queue Service
    • same setup as Web Server
For a DEV installation, the SQL, IIS and qbo Queue Service may run on the same server. Production installation should following standard Microsoft guidelines on scalability.

Installation order includes:
  1. Installing a QBO database
  2. Installing the IIS website
    • selection of desired plugins
    • modification of web.config and related configuration files
  3. Installing the qbo Queue Service

Database Installation

Quandis uses a Visual Studio SQL Project (.sqlproj) to manage the QBO database.  The database solution can be found in Quandis source: Trunk > qbo.3 > qbo.DB > qbo.DB.sln.  This solution may be use to publish directly to a SQL server, or to generate a script to update a target SQL server to the current version of the QBO schema. 

As of this post, the qbo.DB solution includes 4 projects:
  • qbo.Standard: this contains the core tables required for all QBO installation
  • qbo.Mortgage: this references qbo.Standard, and includes mortgage industry related tables
  • qbo.Debt: this reference qbo.Standard, and includes debt industry related tables
  • qbo.Credit: this references qbo.Standard, and included credit reporting related tables
Installing qbo.Mortage will include all qbo.Standard components.

To set up a QBO database:
  • Open the qbo.DB solution
  • Right-click on the desired theme (QBO, typically), and choose Preview or Publish
    • follow the publish wizard to connect to your target database
    • enter the locations for the files to be created for the various file groups (see below)
    • preview will generate a script that you may run manually against your DB; publish will execute the generated script
File groups

Large QBO installations can push a SQL server pretty hard. To optimize performance, Quandis has created several FileGroups to help manage SQL Server's performance. In a PROD environment, the files for each file group should be on a different physical drive.  For a DEV environment, this is far less important. However, the QBO schema will create the file groups, regardless of environment. The SQL projects in the qbo.DB solution will prompt you for a physical location for the files in each of the file groups that the schema will create. 
  • For a DEV installation, you are welcome to place all the files on the same drive.
  • For a PROD installation, strongly consider specifying a separate physical drive for each file group
File groups include:
  • FGData: contains the data for most tables
  • FGHistoryData: stored audit trail information, generally heavy on writing, light on reading
  • FGIndex: index data for most tables
  • FGHistoryIndex: stored audit trail information, generally heavy on writing, light on reading
  • FGBlobData: blob data is stored here
  • FGHistoryBlobData: stored audit trail information, generally heavy on writing, light on reading
  • FGObjectAccessData: used for row-level access control data, including a very large number of "narrow" rows
  • FGObjectAccessIndex: used for row-level access control data, including a very large number of "narrow" rows
Seeding Login Data

To log into a QBO3 database for the first time, a mimimum set of seed data must be created. This data includes an administator role and user account, along with basic permissions. QBO3 source code include a powershell script and initialization SQL script to create this data. To install, open Powershell and navigate to the Publish folder of QBO source code, and:
  • . .\qbo.ps1
  • Confirm-qboDbSetup -dbserver "mydb.mycompany.com" -dbname "mydatabasename"
Note that the Confirm-qboDbSetup function accepts the following parameters:
  • -dbServer: name or ip address of the SQL server containing the QBO database
  • -dbName: name of the database
  • -admin: (optional, defaults to admin@quandis.com) QBO3 username for initial administrative account
  • -password: (optional, defaults to password) QBO3 password for initial administrative account
The script will prompt you for credentials to connect to the data server. These credentials are SQL Server credentials, not QBO3 credentials. These SQL credentials will be encrypted and cached on the developer's machine for future deployments. Should you need to change or remove these credentials, you can do so by executing:
  • Remove-qboLogin DB-{your db server name or ip}

Web Server Installation

Quandis' application and web tiers are built in C# and ASP.NET project files. The target web server should have IIS 8+ installed, including a TLS/SSL certificate mapped to the target website. Quandis recommends publishing using Web Deploy via MSBuild, using QBO MSBuild targets. Review the qbo3.Sample.proj file in Source > Trunk > qbo.3 > Publish. This sample file will publish:
  • qbo.Core projects: including application, accounting, decision, message, attachment, security, etc.
  • qbo.Mortgage projects: including mortgage, debt, credit and skiptrace
  • qbo.Amazon projects: plugins including S3, SES, SQS interfaces
  • qbo.Queuing projects: plugins including Service Broker
  • qbo.AttachmentPlugins projects: plugins including Aspose, ABCPDF, Google, DotNetZip
  • qbo.ScorePlugins projects: including document classification
For a given client installation, a customized version of qbo3.Sample.proj (qbo3.{Client}.proj) should be created and used to manage deployments. Customization entails: 
  • Identifying an theme to use (overrides of standard XSLTs)
  • Identifying additional plugins to install
Instructions for running the MSBuild file are found in qbo3.Sample.proj, but execuring from Powershell looks something like this:

& 'C:\Program Files (x86)\MSBuild\14.0\Bin\amd64\MSBuild' .\qbo3.Sample.proj /p:"Server=1.2.3.4,User=myUserName,Pwd=secret"

Configuration File Modifications

QBO contains several configuration files that will need to be created or updated to make a QBO installation work correctly. The web.config file will be found in the root of the web application, but all other configuration files will be found in the Config folder just under the root.

By default, the QBO web projects do not including several required .config files as content. This is a pattern we adopted to allow developers to publish without worrying about overwriting a custom configuration. 

Web.config

For an initial installation, a 'base' version of web.config will be created. Settings to consider tweaking:
  • qbo.Application.Properties.Settings / BaseUrl: set this to be the base URL of your website (e.g. https://{client}.quandis.net)
  • qbo.Application.Properties.Settings / LoadBalancerHeader: this defaults to X-Forwarded-Proto (for AWS); change if your load balancer uses a different value
    • note that the HttpsRequired setting only applies if the request is not over https and there is no load balancer header present
  • qbo.Application.Properties.Settings / XsltCacheDuration: set this to 0 if you are in 'development' mode, making frequent XSLT changes
  • loggingConfiguration / listeners: modify this to use the Enterprise Library or custom logging sinks you prefer
  • system.web / machinekey: ensure that all servers in a server farm use the same machine key
ConnectionStrings.config

For an initial installation, copy the ConnectionStrings.config found in qbo.Core > Web tier > qbo.ApplicationWeb > Config to your web application's Config folder.

Typical setup include two entries:
  • qbo.Default: the connection string used by the application
  • qbo.AdHoc: an optional connection string if you wish to enable ad-hoc querys via Report.ashx/AdHoc
Credential.config

This file contains the "system" credentials used to communication between the QBO application tier an resources that required credentials. Examples include:
  • credentials to an SMTP server for outbound email processing
  • credentials to a UNC share on a corporate SAN
  • credentials to an sFTP site used for client data file exchanged 
A sample can be found in qbo.Core > Web Tier > qbo.ApplicationWeb > Config.

FileObject.config

This file determine where document images are saved. This must always be customized to your site. See a sample file in qbo.Core > Web Tier > qbo.AttachmentWeb > Config.

Typically, you should include:
  • An 'Internet' file object for reading files over HTTP
  • A 'Database' file object if you wish to copy data files to your data server for access by the SQL engine or SSIS
  • A UNCFile-based file object to support scratchpad space for user uploads
  • A 'permanent' repository such as Amazon S3, Google Drive, or your corporate SAN for user created images
MailOutbound.config

This file determine where outbound emails are sent. This must always be customized to your site. See a sample file in qbo.Core > Web Tier > qbo.MessageWeb > Config.

qbo Queue Service Installation

The qbo Queue Service is a Windows services that handles all asynchronous processing for QBO. The MSI for this service can be found in Trunk > qbo.3 > Installation.  
  • For DEV installations, you can simply install this service on the web server. 
  • For PROD installations, clone the web server machine, install the qbo Queue Service 
    • the service will read the same configuration files you set up for IIS
    • you may optionally top the Word Wide Web Publishing Service
The qbo.QueueService.exe.config file must be modified to point to the web.config file that you configured for the web site.

Verifying Installation

A given installation may be verified from Design > Specifications, Application > Configuration.  This spec will fetch a list of all installed modules, and instantiate each, do a search against the db, and report any errors encountered.  It is intended to catch:
  • Consistency between the app tier and data tier (expected tables and column structures), and
  • Verify all plugins referenced in each module's configuration file can be instantiated
Note that not all errors reported by this spec may pose a problem for you installation.  For instance, if you have configured the Entity view to ignore some modules, the Summary check for the module will fail.

Troubleshooting

  • Ensure Web Management Service is installed and running on your target web server
    • Ensure it has permission to execute runCommands: see qbo3 > Public > qbo3.Powershell.targets
  • Ensure {website}/Config/Setup.ps1 is run to set permission on the web root for error handling and configuration monitoring
    • The qbo3.Powershell.targets PsSetup target handles this by calling MSDeploy runCommand
  • Ensure {website}/Config/ConnectionStrings.config has a valid connection string to a QBO database

Comments