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

Before you begin

• If you're Migrating Jira applications to another server, create an export of your data as an  backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.
• Stop Jira before you begin, unless you just started the installation and are running the Setup Wizard.
  
  

1. Create and configure the SQL Server 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 2017 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 2017 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.
      
      
      

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

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.

• Using the Jira setup wizard  — Use this method, if you have just installed Jira and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your Jira application home directory.

  Show me how to do this...

  The Jira setup wizard will display when you access Jira for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to SQL Server.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.

• Using the Jira configuration tool  — Use this method, if you have an existing Jira instance. Your settings will be saved to the dbconfig.xml file in your Jira application home directory.

  Show me how to do this...

  1. Run the Jira configuration tool as follows:
     
     • Windows: Open a command prompt and run config.bat in the bin sub-directory of the Jira installation directory.

     • Linux/Unix: Open a console and execute config.sh in the bin sub-directory of the Jira installation directory.

       This command might fail with the error as described in Unable to Start Jira applications Config Tool due to No X11 DISPLAY variable was set error. If it happens, refer to this article for the workaround.

  2. Navigate to the Database tab and set Database type to SQL Server.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
  5. Restart Jira. 

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.

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>

3. Schedule regular database maintenance tasks 

UPDATE STATISTICS <table.name>

UPDATE STATISTICS <table.name> with fullscan

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.