Connecting Jira applications to Azure SQL

These instructions will help you connect Jira Server or Jira Data Center to an Azure SQL database.

Before you begin

1. Create an Azure SQL database

Create an Azure SQL database. See Quickstart: Create a single database in Azure.

Requirements

  • Collation: When creating the database, make sure to set the right collation in Additional settings, as you won't be able to change it later. Collation types supported by Jira are SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI.

2. Allow Jira to connect to the database

You need to add the IP address of your Jira server to the database's firewall rules to allow Jira to connect to your Azure SQL database. See Azure SQL database firewall rules.

3. Configure Jira to connect to the database

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

tip/resting Created with Sketch.

Finding connection strings

When connecting Jira to the database, you’ll need to provide connection details, such as hostname, port number, and database name. You can find them in the Azure portal by opening your deployed database and going to Connection strings.


  • 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 Microsoft 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:
    2. Navigate to the Database tab and set Database type to Microsoft 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 sections below describe the fields you'll need to fill out when connecting Jira to your database. 


In Setup Wizard/Configuration tool...
FieldDescription / Example
Hostname

The name or IP address of the Azure SQL server. 

Example: sqlserver.database.windows.net

Port

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

Default: 1433

Database

The name of your Azure SQL database (into which Jira will save its data).

Example: jiradb

Username

The user that Jira uses to connect to the SQL Server server.

Example: jiradbuser@sqlserver

PasswordThe user's password — used to authenticate with the Azure SQL server.
Schema

The name of the schema that your Azure SQL database uses. 

Default: dbo

In dbconfig.xml...
FieldDescription / Example
Hostname

The name or IP address of the Azure SQL server. 

-

<url>jdbc:sqlserver://;serverName=sqlserver.database.windows.net;

portNumber=1433;databaseName=jiradb</url>

Port

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

-

<url>jdbc:sqlserver://;serverName=sqlserver.database.windows.net;

portNumber=1433;databaseName=jiradb</url>


Database

The name of your Azure SQL database (into which Jira will save its data).

-

<url>jdbc:sqlserver://;serverName=sqlserver.database.windows.net;

portNumber=1433;databaseName=jiradb</url>

Username

The user that Jira uses to connect to the SQL Server server.

-

<username>jiradbuser</username>

Password

The user's password — used to authenticate with the Azure SQL server.

-

<password>yourpassword</password>

Schema

The name of the schema that your Azure SQL database uses.

-

<schema-name> dbo </schema-name>

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>dbo</schema-name>
  <jdbc-datasource>
    <url>jdbc:sqlserver://;serverName=sqlserver.database.windows.net;portNumber=1433;databaseName=jiradb</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>adminsql@sqlserver</username>
    <password>T3ddybear</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <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-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

4. 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 database statistics, see the official Microsoft documentation.

Last modified on May 31, 2023

Was this helpful?

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