Create a Database Connection

Database type

Select SQL Server or Oracle.

If the database type is Oracle, ensure to install Oracle Client on your machine.

Connection type

Select either Connection string or Connection provider.

Server name

Perform one of the following:

• Enter the name of the datasource server.

• Click the Refresh icon to get a list of the available datasource servers and then select the required server.

  If the database type is Oracle, ensure that you enter the Oracle Service name, which is used to connect to the Oracle database server.

Authentication type

Provide information on the process used to validate a user. Both Windows, and Microsoft SQL Server authentication process is supported.

• For Microsoft SQL Server, the authentication type can be either Microsoft SQL Server or Windows.

User Id

Enter the Microsoft SQL Server user ID for Microsoft SQL Server type of authentication. For Windows Authentication, User Id is disabled.

Password

Enter the Microsoft SQL Server password for Microsoft SQL Server type of authentication. For Windows Authentication, Password is disabled.

Advanced settings

(Optional). You can enter any additional connection information, such as connection timeout. For example, Connection Timeout = 20

While creating a data source for a repository, use the following custom settings to set properties for connection pool:

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

where,

• 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: Internally light weight transaction mode is implemented 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 distribution is reduced to a great extent. With this feature the transaction is controlled from being escalated to a distributed transaction which is very expensive.

  The above property can be used to turn off this feature. The default value is set as True.

• connectionretrywaittime: While opening a new connection, if the database server is not available, the default behavior is to retry the connection. This setting can be used to set the wait time to 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 an SQL command and generating an error. The default value is set as 300.

  Note: These settings are case sensitive and should be mentioned only in lower case.

Database

Perform one of the following:

• Enter the name of the database.

• Click the Refresh icon to get a list of the available databases and then select the required database.