Connecting Jira applications to SQL Server 2016

These instructions will help you connect Jira to a Microsoft SQL Server 2016 database.

Before you begin

1. Create and configure the SQL Server database

When creating the database, remember your database name, user name, schema name, and port number, because you'll need them later to connect Jira to your database.

  1. Create a database for Jira (e.g. jiradb). 

    • Make sure the collation type is case-insensitive.

      Supported collation types...

      We support SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI as case-insensitive, accent-insensitive, and language neutral collation types. If your SQL Server installation's collation type settings have not been changed from their defaults, check the collation type settings.

    • SQL Server uses Unicode encoding to store characters. This is sufficient to prevent any possible encoding problems.

  2. Create a database user which Jira will connect as (e.g. jiradbuser). This user should not be the database owner, but should be in the db_owner role.

  3. Create an empty 'schema' in the database for the Jira tables  (e.g. jiraschema).

    Tell me more...

    A 'schema' in SQL Server 2016 is a distinct namespace used to contain objects and is different from a traditional database schema. You are not required to create any of Jira's tables, fields or relationships (Jira will create these objects in your empty schema when it starts for the first time). You can read more on SQL Server 2016 schemas in the relevant Microsoft documentation.

  4. Make sure that the database user has permission to connect to the database, and to create and populate tables in the newly-created schema.

  5. Make sure that TCP/IP is enabled on SQL Server and is listening on the correct port. A default SQL Server installation uses port number 1433.

  6. Make sure that SQL Server is operating in the appropriate authentication mode.

    Tell me more...

    By default, SQL Server operates in 'Windows Authentication Mode'. However, if your user is not associated with a trusted SQL connection, i.e. 'Microsoft SQL Server, Error: 18452' is received during Jira startup, you will need to change the authentication mode to 'Mixed Authentication Mode'. Read the Microsoft documentation on authentication modes and changing the authentication mode to 'Mixed Authentication Mode'

  7. Turn off the NOCOUNT option.
    1. Open SQL Server Management Studio.
    2. Go to Tools > Options > Query Execution > SQL Server > Advanced, and clear the NOCOUNT check box.
       NOCOUNT option in SQL Server Management Studio.

    3. Right-click your server in the Object Explorer, and go to Properties > Connections > Default Connections. Clear the no count option.

      Default Connections in SQL Server Management Studio.

  8. Access the Query Console by right clicking on the newly created database and selecting 'New Query'. Run the following command to set the isolation level.

    ALTER DATABASE THE-NEW-DATABASE-CREATED-FOR-JIRA  
    SET READ_COMMITTED_SNAPSHOT ON

2. Configure Jira to connect to the database

There are two ways to configure your Jira server to connect to your SQL Server database.

Database connection fields

The table shows the fields you'll need to fill out when connecting Jira to your database. You can also refer to them, and the sample dbconfig.xml file below, if you'd like to create or edit the dbconfig.xml file manually.

Setup Wizard / Configuration Tooldbconfig.xmlDescription
Hostname

Located in the <url> tag (bold text in example below): <url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The name or IP address of the machine that the SQL Server server is installed on.
Port

Located in the <url> tag (bold text in example below):
<url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The TCP/IP port that the SQL Server server is listening on. You can leave this blank to use the default port.
Database

Located in the <url> tag (bold text in example below):
<url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The name of your SQL Server database (into which Jira will save its data). You should have created this in Step 1 above.
Username

Located in the <username> tag (see bold text in example below):
<username> jiradbuser </username>

The user that Jira uses to connect to the SQL Server server. You should have created this in Step 1 above.
PasswordLocated in the <password> tag (see bold text in example below):
<password> jiradbuser </password>
The user's password — used to authenticate with the SQL Server server.
SchemaLocated in the <schema-name> tag (see bold text in example below):
<schema-name> dbo </schema-name>

The name of the schema that your SQL Server database uses. You should have created this in Step 1 above.

Sample dbconfig.xml file

For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, see Tuning database connections.

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>jiraschema</schema-name>
<jdbc-datasource>
  <url>jdbc:sqlserver://dbserver:1433;databaseName=jiradb</url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <username>jiradbuser</username>
  <password>password</password>
  <pool-min-size>20</pool-min-size>
  <pool-max-size>20</pool-max-size>
  <pool-max-wait>30000</pool-max-wait>
  <pool-max-idle>20</pool-max-idle>
  <pool-remove-abandoned>true</pool-remove-abandoned>
  <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
  <validation-query>select 1</validation-query>
  <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
  <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

  <pool-test-while-idle>true</pool-test-while-idle>
  <pool-test-on-borrow>false</pool-test-on-borrow>
</jdbc-datasource>
</jira-database-config>

Both the Jira setup wizard and database configuration tool also add the element <validation-query>select 1</validation-query> to the dbconfig.xml file, which is usually required when running Jira with default MySQL installations. See Surviving connection closures for details.

3. Schedule regular database maintenance tasks

To achieve and maintain optimal MS SQL performance, schedule daily maintenance tasks to update database statistics.

Schedule a daily maintenance task for hot tables

Hot tables are the most active tables in your database. For example, propertyentry, changeitem, and changegroup are large data tables that are used frequently and require regular updating of statistics.

To set up a daily maintenance task for hot tables, run the following command:

UPDATE STATISTICS <table.name>

Schedule a weekly maintenance task for the whole database

To set up a weekly maintenance task for the whole database, run the following command:

UPDATE STATISTICS <table.name> with fullscan

For large databases, updating statistics with fullscan might take a long time to complete. To minimize the impact on your production environment, schedule this maintenance task for off-peak hours.

For more information on how to update MS SQL Server statistics, see the official Microsoft documentation.

4. Start Jira

You should now have Jira configured to connect to your SQL Server database. The next step is to start it up!

Known issues

The following table lists known issues that might occur during the database operation or the execution of database procedures. We are aware of these issues and have planned their resolution in future releases.

IssueSolution
SQL Server doesn't allow more than 2000 parameters in a query.

This is a known limitation set by SQL Server. According to SQL Docs, a procedure can have a maximum of 2100 parameters.

The issue is tracked in the ticket JRASERVER-63290 - Getting issue details... STATUS

Feel free to leave comments on the ticket so we know your use cases better and understand how this issue is impacting your operations.

Last modified on Mar 9, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.