Connecting JIRA to SQL Server 2005

1. Create a database user which JIRA will connect as (e.g. jirauser). Note that jirauser should not be the database owner, but should be in the db_owner role. (See SQL Startup Errors for details.)
2. Create a database for JIRA to store issues in (e.g. jiradb).
3. Create an empty 'schema' in the database (e.g. jiraschema) for the JIRA tables. Please note that a 'schema' in SQL Server 2005 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 2005 schemas in the relevant Microsoft documentation.
4. Ensure that the user has permission to connect to the database, and create and populate tables in the newly-created schema.
5. Turn off the SET NOCOUNT option. (The JIRA on MS SQL Server document provides details on the errors that occur if SET NOCOUNT is set.) 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 2005. Ensure that the SET NOCOUNT option is not selected:

     

1. Download the SQL Server JDBC driver from JTDS (recommended, assumed below), or I-net software (commercial).
   Note

   Microsoft have their own JDBC driver but we strongly recommend avoiding it after receiving many reports of intermittent disconnections (JRA-5760, JRA-6872), workflow problems (JRA-8443) and Chinese character problems (JRA-5054).
2. Add the SQL Server JDBC driver jar (jtds-[version].jar) to the common/lib/ directory.

1. Edit conf/server.xml (if you are using JIRA Standalone) and customise the username, password, driverClassName and url parameters for the Datasource. (If you are using JIRA WAR/EAR, edit the appropriate file on your application server; e.g. for Tomcat, edit conf/Catalina/localhost/jira.xml.)

   <Server port="8005" shutdown="SHUTDOWN">
   
     <Service name="Catalina">
   
       <Connector port="8080"
         maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
         enableLookups="false" redirectPort="8443" acceptCount="100"
         connectionTimeout="20000" disableUploadTimeout="true" />
   
       <Engine name="Catalina" defaultHost="localhost">
         <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">
   
           <Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false">
             <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
               username="[enter db username]"
               password="[enter db password]"
               driverClassName="net.sourceforge.jtds.jdbc.Driver"
               url="jdbc:jtds:sqlserver://localhost:1433/jiradb"
               [ delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis params here ]
               />
   
             <Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction"
               factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/>
             <Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>
           </Context>
   
         </Host>
       </Engine>
     </Service>
   </Server>

   (Note: if you can't find this section at all, you've probably got the wrong file — search for mentions of 'jira' in the files under conf/.)

2. If you are using JIRA Standalone, edit conf/server.xml, and delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis attributes (which are only needed for HSQL, and degrade performance otherwise).

1. Edit atlassian-jira/WEB-INF/classes/entityengine.xml (if you are using JIRA Standalone) or edit-webapp/WEB-INF/classes/entityengine.xml (JIRA WAR/EAR), and change the field-type-name attribute to mssql. (If you forget to do this and start JIRA, it may create database tables incorrectly. See this page if this happens to you.).

2. Change schema-name="PUBLIC" to the name of the schema associated with the database (i.e. the schema you created in step 1.3 above), e.g. schema-name="jira". Note that the schema must exist in the database before you perform this step.

   , where for MS SQL 2000 schema-name="PUBLIC" should be deleted.<-->

   <!-- DATASOURCE - You will need to update this tag for your installation.
   
   -->
     <datasource name="defaultDS" field-type-name="mssql"
         schema-name="jira"
         helper-class="org.ofbiz.core.entity.GenericHelperDAO"
       check-on-start="true"
       use-foreign-keys="false"
       ...
       

You should now have an application server configured to connect to a database, and JIRA configured to use the correct database type. If you are using JIRA Standalone, start it up and watch the logs for any errors. If you are using the JIRA WAR/EAR distribution, rebuild and redeploy the webapp in your application server.

Have experiences to share with SQL Server and JIRA? We welcome your thoughts. Please see the user-contributed MS SQL Server notes.