Microsoft SQL server - j5 - 28.0 - Installation & Upgrade - Hexagon PPM

j5 Installation and Upgrade

Language
English (United States)
Product
j5
Search by Category
Installation & Upgrade
j5 Version
2019

Microsoft ODBC Driver 17.3.1.1 or later SQL Server needs to be installed so j5 can connect to a Microsoft ‘x86\msodbcsql.msi’ for a 32-bit system.

  • Database Type: Select SQLServer.

  • Database Server: The hostname or IP address of the database server. If you use a named instance, the name must be specified with the format "<host name>\<instance name>" (for example, "SQLSERVER1.EXAMPLE.COM\MSSQLSERVER"). By default, Microsoft SQL Server uses a named instance, "MSSQLSERVER".

    If you have performance issues while connecting to the database from j5, change the Database Server field from hostname to its IP address. This forces the connection to use TCP/IP instead of Named Pipes. This has made noticeable changes to the performance when the connectivity has been strained or hampered due to excessive load and additional routing overhead (for example, when using a VPN).

  • Database Name / SID: The name of the database.

  • Use Windows Authentication: Select this check box to use Windows Authentication to connect to the database. We recommend this method of authentication.

    If Windows Authentication is used, appropriate database permissions must be given to the following users:

    • The j5 service user (by default, this is NT AUTHORITY\NETWORK SERVICE)

    • Any administrators that can run the j5 Setup Wizard (i.e. the current user)

    • Any administrators that can use the j5 System Management Console

  • Username: Alternatively, if SQL Server Authentication is used, provide the user name that j5 uses to connect to the database.

  • Password: The password for the j5 database user specified for SQL Server Authentication.

  • Port (optional): Specify the port number when the database port is different to the default. When the default port is used, this box can be left blank.

  • Bypass Database Connection Validation (optional): This is available when using Windows Authentication. It bypasses validation of the database certificate to allow connections when certificates have been misconfigured.

  • Encode all parameters: When this is set to True it forces j5 to encode all parameters as VARCHAR before passing them to the database. This option should be used with caution (refer to the Encode all parameters note below).

    Encode all parameters can be used to improve performance on SQL Server. However, there are limits to its usage:

    • j5 automatically detects the correct encoding to use (based on the codepage of the database collation) when this option is selected. Not all codepages are supported. The Database Validation fails if this option is selected for a database with an unsupported codepage.

    • If your j5 database uses NVARCHAR columns in circumstances where you require j5 to store characters which cannot be encoded in the database codepage, this option should not be used, because it will cause data corruption for those characters.

The Reporting service uses the TCP/IP protocol to connect to the database. Ensure that the Server Protocol is enabled as follows:

  • Open the SQL Server Configuration Manager.

  • In the tree pane, click SQL Server Network Configuration to expand it, and then click Protocols for the relevant instance. If you specified the default instance during installation, the instance name is MSSQLSERVER

  • In the results pane, verify that, under the Status column, Enabled appears next to the name of the TCP/IP protocol. If Disabled appears, right-click TCP/IP, and then click Enable.

  • In Services, right-click SQL Server (MSSQLServer/MyInstanceName) and then click Restart.

  • In addition, it may be necessary to start the SQL Server Browser service, if SQL Server is not listening on the default TCP/IP port (1433), or you are connected to a non-default instance.