How to Replicate Data From Jira to SQL Server? | Easy 101 Guide

on Data Replication, Jira, MS SQL • June 20th, 2022 • Write for Hevo

Jira to SQL Server | Cover Image | Hevo Data

Jira — an issue and project tracking software — holds plenty of data that can prove itself vital when navigating through project status, especially when the team finds itself stuck. Replicating Jira Data to a Database such as MS SQL Server, teams can maintain a healthy outlook by analyzing Issues, Projects, Roles, and User data. And, by replicating your data from Jira to SQL Server you not only create a unified repository of all your Jira data, you truly create a source of truth using which teams can make data-driven decisions. 

In this tutorial article, we will learn how to replicate Data from Jira to SQL Server seamlessly. Chronologically, first, we will talk about Jira, then MS SQL Server, and the reason behind the whole replication process. This tutorial article will help you understand the nuances that go behind the scene when replicating data manually.

Psst… we will also chalk our way through using Hevo Data which supports both connectors natively — making the process an easy 3-step cakewalk.

Let’s begin.

Table of Contents

  1. What is Jira?
  2. What is MS SQL Server?
  3. Why Replicate Data from JIRA to SQL Server?
  4. Methods to Replicate Data From Jira to SQL Server
  5. Conclusion

What is Jira?

Fun fact: The name “Jira” was inspired by a popular Japanese fictional monster character, “Godzilla.”

Jira, an integral tool for today’s software development teams for seamless project management and task prioritization, is a product offering developed by Atlassian, an Australian software company. But, here’s an easter egg — Jira was never supposed to be a project management tool, it was developed for issue and bug tracking. Later the capabilities were scooped by constantly improving upon the current use cases and resolving pain points experienced by teams obeying Agile Team Methodologies.

As of writing this blog, Jira currently provides three internal products: Jira Core, Jira Software, and Jira Service Desk. Let’s talk about them briefly.

Jira Core: Jira Core helps teams create customizable workflow solutions for better productivity. It helps teams organize projects and tasks by providing the necessary infrastructure to automate business processes.

Jira Software: Jira Software helps Software Development Teams in big tracking projects, and managing software development tasks and products (Product Management). Jira Software is also employed by teams that use Kanban, Scrum, and agile frameworks.

Jira Service Desk: Jira Service Desk is developed for IT teams. It’s an add-on that helps support professionals for issue ticketing, and incident and change management.

What is MS SQL Server?

Currently available under two editions, Mainstream and Specialized, MS SQL Server is an RDBMS, developed and marketed by Microsoft. MS SQL Server is a host to 13 add-on services on top of its core database management system — the reason for its popularity in IT corporates. 

It is built around a key component known as a Database engine. Furthermore, this database engine is built on top of a relational engine for query processing and a storage engine for managing databases, files, pages, indexes, and other data.

Microsoft SQL Server’s Key Features are as follows:

Analytics Support: In order to make better decisions, organizations need insights into stored and managed data. By combining Microsoft SQL with Microsoft SQL Server Analysis (SSAS), a web-based analytical processing tool, you can utilize MS SQL Server to evaluate your stored data.

Multiple integrations: Using MS SQL Server Integration Services (SSIS),  it allows users to execute numerous operations on stored data — such as data extraction, transformation, migration, and loading. SSIS, in a nutshell, is an enterprise-level data integration and transformation solution.

High Performance: SQL Server is a database programming language used to create highly transactional databases. SQL queries allow you to describe your data analytically.

Accelerated Data Recovery: Microsoft SQL Server features an excellent recovery and rollback mechanism that kicks in immediately when the server crashes. The database recovery procedure makes use of Transaction Logs, which significantly reduces the wait time.

Advanced Encryption: Because organizations keep sensitive information in databases, Database Providers must provide privacy. Since 2016, Microsoft SQL Server has relied on AlwaysEncrypted, a new Encryption Technology that provides transparent Column Encryption without giving Database Administrators access to Decryption Keys.

Advanced Analytics: You may easily use the data contained in Microsoft SQL Server to do a detailed analysis based on your needs. Furthermore, you may utilize Microsoft SQL Server Analysis Service (SSAS) to excel at Data Mining activities.

Why Replicate Data from JIRA to SQL Server?

Database replication can be a one-time event or a continuous operation.

Data replication guarantees that data is easily accessible. This is especially important for multinational enterprises with many data sources. As a result, even if one source experiences a hardware failure or other difficulty, data is still accessible.

So, here are some advantages of Data Replication:

Boost Server Performance: Data replication can improve, and even accelerate server performance. Users can get data significantly faster when organizations operate multiple data copies on several servers. Furthermore, by routing all data read activities to a replica, administrators free up processing cycles on the original server for more resource-intensive writing operations.

Disaster Relief: The biggest advantage is in disaster relief and data security. It guarantees that a consistent backup is kept in the case of a disaster, hardware failure, or system breach that might endanger data. So, if a system fails for any of the reasons listed above, you may access the data from a new place.

Help with Data Analytics: Data-driven enterprises typically replicate data from several sources into data repositories such as data warehouses or data lakes. This makes it easier for the analytics team, which is distributed across many teams to work on joint projects.

Explore these ways to Replicate Data from Jira to SQL Server

Jira’s issue data is needed to be stored in a relational database. If you are installing Jira for the first time, the Jira setup process will configure a database connection to either Jira’s internal H2 or an external database like MS SQL. This guide will let you know how it is done by using two methods. Read along to know-how.

Method 1: Replicating Data from Jira to SQL Server Using Hevo Data

Hevo Data, a fully managed No-Code Data Pipeline will help you replicate data from Jira to SQL Server without writing any code. You can utilize its plug-and-play platform to set the data moving in a few minutes with 100% accuracy and zero data loss. With Hevo’s wide variety of connectors and blazing-fast Data Pipelines, you can extract & load data from 100+ Data Sources (including 40+ free sources) straight into your Data Warehouse or any Databases.

GET STARTED WITH HEVO FOR FREE

Method 2: Replicating Data from Jira to SQL Server 2019 [Atlassian Documentation]

In this method, we follow the information provided in Atlassian Documentation to replicate data from Jira to SQL Server. This method can be a bit tech extensive.

Both the methods are explained below.

Methods to Replicate Data From Jira to SQL Server

In this section, we will employ two methods to replicate data from Jira to SQL Server. Continue reading to know more:

Method 1: Replicating Data from Jira to SQL Server Using Hevo Data

Jira to SQL Server | Method 1: Using hevo to replicate data from Jira to SQL Server | Hevo Data

Hevo Data helps you directly transfer data from Jira to SQL Server in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Hevo takes care of all your data preprocessing needs required to set up a connection from Jira to SQL and lets you focus on key business activities.

Here is why using Hevo makes sense:

  • Minimal Setup: You will require minimal setup and bandwidth to load data from Jira to SQL Server using the Hevo platform. 
  • No Data Loss: Hevo architecture is fault-tolerant and allows easy, reliable, and the seamless transfer of data from Jira to SQL Server without any data loss. 
  • 100+ Native Integrations Available: Hevo platform brings data from other sources such as SDKs, Cloud Applications, Databases, and so on into Data Warehouses and Databases. So, Hevo is the right partner for all your growing data and analysis needs.
  • Automatic Schema Detection and mapping: The schema of incoming data is scanned automatically.  If there are changes detected, they are handled seamlessly and the changes are incorporated into the Database or Data Warehouse. 
  • Exceptional Support: Hevo has 24×7 Technical support through emails, calls, and chat.

Method 2: Replicating Data from Jira to SQL Server 2019 [Atlassian Documentation]

Prerequisites

  1. If you’re migrating Jira apps to another server, make an XML backup of your data. Then, as discussed in Switching databases, you will be able to move data from your old database to your new database.
  2. Unless you have just started the installation and are in the Setup Wizard, stop Jira before you begin.

Step 1: Creating and Configuring the SQL Server Database

  1. First, create a database for Jira Data. You can name it “jiradb.”
  2. Create a database user for Jira to connect to “jiradbuser”. This user should be in the db owner role rather than the database owner.
  3. Now the need for Jira tables will occur. Create an empty ‘schema’ to continue (e.g. jiraschema).
  4. Check that the database user has the authorization to connect to the database as well as to create and populate tables in the newly formed schema.
  5. Check that SQL Server has TCP/IP enabled and is listening on the right port. Port 1433 is used by default in SQL Server installations.
  6. Ascertain that SQL Server is in the proper authentication mode.
  7. Turn off the SET NOCOUNT Option. Follow the steps below to configure:
    • Open SQL Server Management Studio > Go to Tools > Options > Query Execution > SQL Server > Advanced, and click on the SET NOCOUNT check box.
Jira to SQL Server | Creating and Configuring the SQL Server Database | Hevo Data
Source: Atlassian Documentation
  • In the Object Explorer, right-click your server and select Properties > Connections > Default Connections. Remove the no count option.
  • By right-clicking on the freshly formed database and selecting ‘New Query,’ you can access the Query Console. To change the isolation level, use the following command.
ALTER DATABASE THE-NEW-DATABASE-CREATED-FOR-JIRA SET READ_COMMITTED_SNAPSHOT ON

Step 2: Configuring Jira to Connect to MS SQL

Two methods are available to connect Jira to MS SQL:

  1. Using the Jira setup wizard: If you have just installed Jira and are setting it up for the first time, use the Jira setup wizard. Your settings will be stored in the dbconfig.xml file in the home directory of your Jira application.
  2. Using the Jira configuration tool: If you already have a Jira instance, use this technique. Your settings will be stored in the dbconfig.xml file in the home directory of your Jira application.

Database Connection Fields: 

The table below lists the fields that must be filled out when connecting Jira to your database. If you want to build or update the dbconfig.xml file yourself, you can refer to them as well as the sample dbconfig.xml file below.

Setup wizard / Configuration tooldbconfig.xmlDescription
HostnameLocated 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.
PortLocated 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.
DatabaseLocated 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.
UsernameLocated 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:

See, Tuning Database Connections for further gain information on the child elements of jdbc-datasource/> beginning with pool in the dbconfig.xml file above.

<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>

Step 3: Starting Jira

Jira should now be set up to connect to your MS SQL Server database. The next step is to get it going!

Conclusion

In this article, you learned about connecting Jira to MS SQL. This article also focused on the basics of Jira, MS SQL, and why it makes sense to integrate Jira to SQL Server.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Hevo Product Video
Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) — such as Jira  — allows you to not only export data from your desired data sources & load it to the destination of your choice such as MS SQL but also transform & enrich your data to make it analysis-ready. Hevo also allows the integration of data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the process of replicating data from Jira to SQL Server in the comment section below! We would love to hear your thoughts.

Replicate Jira Data to MS SQL in Minutes