Create Farm Database

Use the Create AVEVA Work Tasks Farm Database page to provide New Database details. Select the Connection String option to establish a connection with the database. This connection information is encrypted and stored.

You can enter either Windows or Microsoft SQL Server user settings for authentication.

Note:
- The Farm database password does not support the special characters & ; and =.
- While setting up Farm Database with Windows Authentication, do not enter FQDN in the Domain Name field.
- If the Farm database is configured with Windows Authentication, the user attempting the installation must have the sysadmin role on the database server.

To create Farm Database

1. Enter Database Title.

2. Select Connection String.

   See Connection Provider later in this topic, if you have selected the Connection Provider option.

   • Enter the following details:

     Server Name:

     Provide information about the database server on which the Farm database can be installed.

     If the Database Type is SQL Server, then you can specify an IPv6 address for the Server Name.

     If SQLExpress Instance need to be used as a Database instance of the Farm database, then Server Name should be <server name>\SQLExpress.

     To use SQL Server AlwaysOn Availability Groups, enter the SQL AlwaysOn Group name or the IP address, and the port number (separated by comma). For example: localhost,1455

   • Authentication Type

     This field is used to provide information on the process used to validate a user. AVEVA Work Tasks solution supports Windows and SQL Server authentication process.

     Example: Windows
     If Windows Authentication is selected, the user configured for the Farm configuration will be used to access the database. This is the authentication recommended by AVEVA Work Tasks.

   • Login

     This field is used to enter the SQL login credential for SQL Server type of authentication. For Windows Authentication, this field appears disabled.

   • Password

     This field is used to enter the SQL password for SQL Server type of authentication. For Windows Authentication, this field appears disabled.

   • Advanced Settings

     This is an optional field. It is used to enter any additional connection information, such as connection Timeout. Example: Connection Timeout = 20.

     While creating a data source for repository, use the following custom settings to set properties for connection pool. These settings are case sensitive and should be mentioned only in lower case.

     encrypt=true; enableltm=true; connectionretrywaittime=30; maxconnectionretries=10; sqlcommandtimeout=300;

     encrypt: Ensures secure communication between AVEVA Work Tasks Server and SQL Server when a certificate is installed on the SQL Server for SSL encryption.

     enableltm: AVEVA Work Tasks internally implements light weight transaction mode by reusing the connection which is already open within the same transaction. With this implementation a connection which is used from a transaction is returned back to connection pool only when the transaction is completed. Since the same connection is used within the transaction, the transaction becoming distributed is reduced to a great extent. With this feature AVEVA Work Tasks control the transaction from escalating to a distributed transaction which is very expensive. This property can be used to turn off this feature. The default value is set as True.

     connectionretrywaittime: While opening a new connection, if the data base server is not available, the default behavior is to retry for the connection. This setting can be used to set the wait time for retry for a new connection. The default value is set as 30 Seconds. The value should be set in seconds.

     maxconnectionretries: While opening a new connection, if the data base server is not available, the default behavior is set to retry for a new connection. This setting can be used to set the retry count. The default value is set infinite.

     sqlcommandtimeout: Sets the wait time before terminating the attempt to execute a SQL command and generating an error. The default value is set as 300.

3. Click Test Connection to ensure database connectivity.

4. Click Next.

   The message AVEVA Work Tasks Farm is Created appears.

5. Click OK.

AlwaysOn Availability Groups feature of Microsoft SQL Server

The AlwaysOn Availability Groups feature of Microsoft SQL Server is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in Microsoft SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise.

After you configure the AVEVA Work Tasks, you must add the created Farm Database to the SQL Server AlwaysOn Availability Groups. Contact your Database Administrator to add the database to SQL Server AlwaysOn Availability Groups.

Notes:

- The authentication user needs different rights assigned for different scenarios. If a user wants to create database (Farm or Repository Data Source Database), Authentication User needs to have minimum 'dbcreator' access rights on Database Server Instance.

- If database is already created, user needs to have following permissions on Database Server:

- SELECT, INSERT, UPDATE, and DELETE permissions.

- EXECUTE permission for Stored Procedures.

To choose Connection Provider

Complete the following steps to choose a connection provider:

6. Enter Database Title.

7. In the Database Type box, select a database type.

8. Select Connection Provider.

   This option is used to provide connection related information from a Dynamic Link Library (dll). Connection related details are encrypted and stored in the library.

9. Enter the following details:

   • Assembly Path:

     Click the icon to browse and choose the required dll file.

     If custom properties (enableltm, connectionretrywaittime, maxconnectionretries and sqlcommandtimeout) are required to be used in Assembly connection string, then user should select Use existing Farm database while creating a the Farm. See Creating or Using an Existing Database.

     See the topic Implementation of IDBConnectionString Interface under Data source Related Code Samples sub chapter in the Build Business Process section for creating the assembly. Make sure that, there should be only one class implementing IDBConnectionString interface within this assembly.

10. Click Test Connection to ensure database connectivity.

11. Click Next.

12. The message AVEVA Work Tasks Farm is created appears.

13. Click OK.