Multiple SQL Server Instances

Forsta Plus supports multiple SQL Server instances handling survey databases. This allows administrators to set up additional SQL resources if the performance of the primary server should be insufficient. The additional server instance can be installed as a new named instance on the same physical server as the primary instance, or on a separate physical server.

Some manual steps are required to attach additional SQL Server instances to Forsta Plus, and some points should be taken into consideration before starting.

SQL Server Collation

Forsta Plus features that require reading or writing from multiple databases simultaneously will assume that the SQL collation is identical on all instances. Once SQL Server has been installed, the collation cannot be changed, so make sure to select the same collation for additional servers as the primary. This is equally important both for adding new instances to an existing environment, and when installing a SQL Server to migrate the existing SQL Server databases to.

Note: If a new SQL Server instance has been installed with the wrong collation, it is necessary to re-install SQL Server. If the databases are put into use, collation conflicts may render the site in an unusable state.

SQL Server Logins

Forsta Plus will use the same encrypted login credentials stored in the system configuration database when connecting to secondary servers, so SQL Server logins must be created with identical user names and passwords as the primary server. On the primary server this is done automatically during installation of Forsta Plus, but for secondary servers it must be done manually by the database administrator. We recommend using Microsoft’s own procedures for migrating the logins while retaining the user SID’s so that no remapping of these are needed afterwards if databases are moved between the instances: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server.

Linked Servers

In order for Forsta Plus to read or write data from multiple instances, the servers must be mutually set up as linked servers to provide server awareness between the instances. These linked server objects are created automatically by the deployment steps during installation of Forsta Plus, but needs to be taken into consideration for SQL migrations.

System, Survey and Hub Databases

If installing on a system that already has more than one instance of SQL Server intended for Forsta Plus usage, the first server (the one given during Forsta Plus installation) must always host the Forsta Plus system databases. Subsequent SQL servers are typically added to host additional survey and hub databases.

Creating Aliases for the New Database Server

If the primary database server uses an alias (for instance: 'ForstaDB'), setting up a new alias on all application servers with a similar alias (i.e. 'ForstaDB2') would be recommended. If IP addresses or NetBIOS names are used for connecting to SQL Server, this step is not required. Note that we strongly recommend configuring Forsta Plus to connect to SQL using local SQL aliases or DNS CNAME’s. The reason for this is that connection strings are hard coded into survey packages, so if you at a later time migrate to a new database server with a new IP or name, you need to relaunch all your surveys in order for them to work.