Connecting Jira applications to SQL Server 2008
1. Create and Configure the SQL Server Database
- Create a database for Jira to store issues in (e.g.
jiradb
).
Remember your database name, as it will be used to configure Jira's connection to this database in subsequent steps.- Collation type must be case-insensitive. 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.
- Collation type must be case-insensitive. We support '
- Create a database user which Jira will connect as (e.g.
jiradbuser
).
Remember your database user name, as it will be used to configure Jira's connection to this database in subsequent steps.- This database user should not be the database owner, but should be in the
db_owner
role.
- This database user should not be the database owner, but should be in the
- Create an empty 'schema' in the database (e.g.
jiraschema
) for the Jira tables.
Remember this database schema name, as it will be used to configure Jira's connection to this database in subsequent steps.- A 'schema' in SQL Server 2008 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 2008 schemas in the relevant Microsoft documentation.
- Ensure that the database user has permission to connect to the database, and create and populate tables in the newly-created schema.
- Ensure that TCP/IP is enabled on SQL Server and listening on the correct port (which is 1433 for a default SQL Server installation).
Remember this port number, as it will be used to configure Jira's connection to this database in subsequent steps. Jira does not support dynamic port assignment.- Read the Microsoft documentation for information on how to enable a network protocol (TCP/IP) and how to configure SQL server to listen on a specific port.
- Ensure that SQL Server is operating in the appropriate authentication mode.
- 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'
- Turn off the SET NOCOUNT option. To turn off SET NOCOUNT:
- Open SQL Server Management Studio and navigate to Tools > Options > Query Execution > SQL Server > Advanced. The following screenshot displays the configuration panel for this setting in MSSQL Server 2008. Ensure that the SET NOCOUNT option is not selected:
- You will also need to access the Server > Properties > Connections > Default Connections properties box and clear the no count option.
Access the Query Console by right clicking on the newly created database and selecting 'New Query'. Execute the following command to set the isolation level.
ALTER DATABASE THE-NEW-DATABASE-CREATED-FOR-JIRA SET READ_COMMITTED_SNAPSHOT ON
- Open SQL Server Management Studio and navigate to Tools > Options > Query Execution > SQL Server > Advanced. The following screenshot displays the configuration panel for this setting in MSSQL Server 2008. Ensure that the SET NOCOUNT option is not selected:
2. Configure Your Jira Server to Connect to Your SQL Server 2008 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 home directory. - 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 home directory.
Instructions for each configuration method
Jira setup wizard
The Jira setup wizard will display when you access Jira for the first time in your browser.
- In the first screen, 'Configure Language and Database', set Database Connection to My own database.
- Set Database Type to SQL Server.
- Fill out the fields, as described in the Database connection fields section below.
- Test your connection and save.
Jira configuration tool
- Run the Jira configuration tool as follows:
- Windows: Open a command prompt and run
config.bat
in thebin
sub-directory of the Jira installation directory. - Linux/Unix: Open a console and execute
config.sh
in thebin
sub-directory of the Jira installation directory.
This may fail with the error as described in our Unable to Start JIRA applications Config Tool due to No X11 DISPLAY variable was set error KB article. Please refer to it for the workaround.
- Windows: Open a command prompt and run
- Navigate to the Database tab and set Database type to SQL Server.
- Fill out the fields, as described in the Database connection fields section below.
- Test your connection and save.
- Restart Jira.
Database connection fields
Setup Wizard / Configuration Tool | dbconfig.xml | Description |
---|---|---|
Hostname | Located in the | The name or IP address of the machine that the SQL Server server is installed on. |
Port | Located in the | 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 | 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 | The user that Jira uses to connect to the SQL Server server. You should have created this in Step 1 above. |
Password | Located 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. |
Schema | Located 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:jtds:sqlserver://dbserver:1433/jiradb</url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</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. Start Jira
You should now have Jira configured to connect to your SQL Server database. The next step is to start it up!
Congratulations, you now have Jira connected to your SQL Server database.