(Applicable to ViaWorks 2.6 or later)
ViaWorks supports two different types of databases for storing its data: PostgreSQL, and Microsoft SQL Server 2012. Each requires a unique set of prerequisites.
PostgreSQL
As of version 2.6, ViaWorks only supports installing the included PostgreSQL database on the local machine. Because of this, the main requirement is that the disk space on the machine installed to is sufficient in size enough to satisfy the data that is required from the ViaWorks Application as well as the database. ViaWorks requires the drive where the Program Data is installed to have at least 60 GB of available space. See the "Planning & Pre-Installation - Hardware Requirements" section in the Installation Guide for more information.
Installing with the included PostgreSQL can be done by either choosing "Typical Install", or by choosing the PostgreSQL option in the Database Information page of the installer when "Custom Install" is chosen in the Install Options page (more information below). Choosing this option will cause the installer to both create the database and configure it with all the necessary tables, schemas, and starting data for the application to run effectively.
If "Custom Install" is chosen in the Install Options page of the installer, then the user is also able to update the name of the database that will be installed on the local machine. This is designed so the database can comply with any naming conventions the company may have. Note that this method will install the database on the local machine, and start the ViaWorks application services under a local service account that is created by the application at install time.
Microsoft SQL Server 2012
Prerequisites
Microsoft SQL Server is not installed by the ViaWorks installer. The following tasks must be completed before installing ViaWorks:
- Microsoft SQL Server must be installed
- A database with the proper settings for ViaWorks must be created
- An application service account with the proper access must be created
Microsoft SQL Server Installation
ViaWorks requires SQL Server 2012. All editions are supported, including Express.
SQL Server can be installed on the same server as ViaWorks or on a different server. If SQL Server is on a different server, it is recommended that the connectivity between the ViaWorks server and the SQL server have high throughput and low latency, else application performance will suffer.
ViaWorks Database Creation
The ViaWorks installer creates the objects (tables, views, stored procedures) in the ViaWorks database, but it does not create the database itself. This must be done before running the installer.
In order for the application to work correctly, the database must be created with the correct settings. VirtualWorks provides a script that creates the database with the required settings. It is strongly recommended that this script be used to create the database.
The script to create the database is in the ViaWorksCreateDatabase.zip file. This file contains an SQL script and a batch file to run the script. The script requires the SQLCMD tool to execute it.
The batch file takes two arguments:
- The server name (including instance if used) on which to create the database. This is required.
- The name of the database to create. This is optional and defaults to “ViaWorks” if not specified.
The batch file assumes that the SQLCMD tool is in the path. If it is not, you can set the SQL_TOOLS environment variable to specify the location of SQLCMD (trailing backslash is required).
The SQL script can be modified before running it to specify additional options for the CREATE DATABASE command (e.g., to specify file locations). However, it is strongly recommended that none of the SET options be modified as this can cause the application to behave incorrectly. If a different collation is desired, a case-sensitive (CS) collation must be used.
The SQL script also creates a role named db_executor. This role grants execute access to all stored procedures in the database. This role is required for the application service user.
Application Service Account Creation
ViaWorks uses integrated security to connect to SQL Server. This requires the use of a service account with the necessary privileges. The ViaWorks application components require read/write access to all tables and views in the database, and execute access to all stored procedures in the database.
The recommended process for creating and configuring the service account is as follows:
- Create a domain service account for the ViaWorks application.
- Create a login for this account on the SQL Server instance that hosts the ViaWorks database.
- Grant the login access to the ViaWorks database with the following roles: public, db_datareader, db_datawriter, db_executor.
The credentials for this service account will be entered in the ViaWorks installer when configuring the SQL Server connection.
Installer Requirements
The ViaWorks installer uses integrated security to connect to the database under the context of the user running the installer. This user needs to have a login to the SQL server hosting the ViaWorks database and needs access to create and modify objects in the database as well as read and write data.
The necessary access is most easily granted by giving the user access to the ViaWorks database and assigning the db_owner role. The minimum level of access necessary to run the installer can be granted by assigning the db_ddladmin, db_datareader, and db_datawriter roles.
ViaWorks Custom Install with Microsoft SQL Server
Please complete the Prerequisites and review the Installer Requirements sections before starting the install.
On the Install Options Page, choose "Custom Install" to continue to the Database Information page.
On the Database Information page, click the drop-down and select "Microsoft SQL Server 2012".
The Database Network Address refers to the SQL Server instance hosting the ViaWorks database. If the port or instance need to be specified, they must be included here.
The Name of Database field specifies the name of the database that has already been created. If it was created with a name different from the default of “ViaWorks”, enter that name here.
The Application Username, Application Password, and Application Domain refer to the service account that was described in the Prerequisites section.
Once all of the database information has been entered, click the "Verify Connection" button. This verifies that the installing user and the service account both have access to the database. If the connection is verified, the Next button is enabled. See the Troubleshooting section below for issues with "Verify Connection".
Troubleshooting
Invalid Application Service Account Credentials
This happens when the Application Username, Password, and Domain have values that do not match the Application Service User, or when they are equivalent to the installing user. It is important to use a service account here as any change in the password will mean ViaWorks will not appropriately function.
Install User Cannot Access the Database
This happens when the install user cannot access the database. The user who installs the ViaWorks product must have the access described in the Installer Requirements section in order to properly complete the install. Note: this error can happen as a result of a network issue as well, if the database is remote from the ViaWorks server.
Application Service Account Has No Access to the Database
This happens when the specified application service account does not have access to the database at all. Refer to the Prerequisites section.
Application Service Account has Inadequate Permissions to the Database
This error happens when the Application user can access the database, but does not have the necessary permissions. Refer to the Prerequisites section.
Custom Install Options
As of version 2.6, ViaWorks supports separating its components in the install. This means a user can choose to not install some components. Please note that this will require in-depth knowledge of the product, and will require that the components be available to use in a separate server. Any case in which core components are deselected will need to be done with full knowledge that it may case the product to not work as intended. Some components are required, and are not selectable.
This feature was added primarily to handle installing the connectors necessary for the environment in which ViaWorks is installed. Under the Standard Connectors tree is a list of all the fully customized connectors that ViaWorks supports. By default and with Typical Install, Exchange, File Server, and SharePoint Connectors are all installed with the product. The user can now in the Custom Install Options page of the installer, add any of the other connectors to the install, which were traditionally provided via a separate install.
Under the Database/IDS Connectors are the connectors that run on a standard database with a lower set of customization needed for the connector to run. Any of these can be included in the install as well.
See also