JIRA and MS SQL Server

For SQL Server 2005 (at least), set schema-name to "dbo" or whatever the default schema is for your "jirauser".  If you put the user in the db_owner role, then the schema-name should be "dbo".  If you do not do this, then jira may not be able to find the tables when you restart.

If you are considering SQL Server, I suggest that you try SQL Server 2005.  We recently upgraded our database server and observed an immediate, clearly obvious performance improvement.  In our environment, long running operations such as reindexing or bulk moves showed a 200%-300% improvement.  In some cases, operations that simply never finished (workflow migration) suddenly became possible again.

There is an issue when starting JIRA against SQL Server (2005 in my case) where it reports that it cannot find the database tables, but that it also cannot create them. For example:

[core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database
2003-11-06 09:33:45,265 ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "jiraaction"
2003-11-06 09:33:45,265 ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE jiraaction (ID NUMERIC NOT NULL, issueid NUMERIC, AUTHOR VARCHAR(255), actiontype VARCHAR(255), 
actionlevel VARCHAR(255), actionbody TEXT, CREATED DATETIME, actionnum NUMERIC, CONSTRAINT PK_jiraaction PRIMARY KEY (ID))
Error was: java.sql.SQLException: There is already an object named 'jiraaction' in the database.

I'm guessing the reason behind this is that the code which is looking up the metadata of the database (Java JDBC uses DataBaseMetaData) is using the database username as the schema-name also, which means that for SQL Server it cannot find the tables (because the default schema is dbo). However when it attempts to create (and indeed use) the tables (for which you don't need to specify a schema-name), they already exist.

Although this doesn't affect performance of the system, it does result in an annoying and slightly misleading message on startup.

To resolve this issue, when doing a new JIRA installation:

• create the database
• create a user
• create a schema (in ->security)
• make your user the owner of the schema (schema properties)
• make sure that in user -> properties, the User Mapping for your database has db_owner role membership and the correct default schema specified for the database.

It's obvious it works when you startup JIRA for the second time and you don't see a cascade of "doesn't exist, can't create" errors.

I saw this issue reported here also but the solution didn't help.

Has anyone run into issues with the different versions of SQL server? Specifically, did using the Workgroup SQL Server license cause you any problems?

~Matt

Has anyone tried to connect using SQL Server 2005 Express?  Were you successful?

Well, I've followed all the ideas above but still see the annoying messages described by Simon above. The steps to configure SQL server properly for Jira really ought to be part of the official documentation. Bah.

Thank you everyone for the feedback. We will investigate and update the docs as appropriate.

JIRA on SQL Server Express

Here is one person's notes on getting JIRA to work with SQL Server Express:

1. Launch the SQL Server Configuration Manager.
2. Expand "Server Network Configuration".
3. Expand Protocols for "SQLEXPRESS".
4. Enable Named Pipes.
5. Select TCP/IP in the right pane.
6. Select Properties > IP Addresses.
7. Make "Enabled" for all applicable IPs = "Yes".
8. Note the TCP port value or add if one does not exist for all applicable IPs. I used 1835.
9. Restart SQL Express(service).
10. Edit server.xml (jdbc:jtds:sqlserver://localhost:1835/jiradb )

I am new to Jira (but not completely new to the Tomcat/jdbc game)

Just wanted to say that I have installed Jira 3.12.2 (the war distribution) on an existing Tomcat 5.5.23 container in combination with SQL Server 2005. A number of other applications are running in the same container and using the same SQL Server.

The only interesting point is that I am using the Microsoft JDBC driver. The latest version (1.2) is OK. Download it from Microsoft, put the sqljdbc.jar into common\lib, and modify the lines below  in \conf\Catalina\localhost\jira.xml:

    <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
            username="jira"
            password="jira"
            driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
            url="jdbc:sqlserver://localhost;databaseName=Jira"
            />
           
To get things working in the example above:
- create a database Jira
- create a login jira with SQL server authentication, the password is jira, and uncheck the policy rules (but in a production environment, use a stronger password instead of a copy of the login name).
- add jira as user for the database Jira and and add him to the dbo_owner role.

I created only the empty Jira database and did not create a dedicated schema. So I changed the schema-name in the
\edit-webapp\WEB-INF\classes\entityengine.xml file into the default schema for a user in the dbo_owner role: schema-name="dbo". My datasource entry in this file looks like:

    <datasource name="defaultDS" field-type-name="mssql"
        schema-name="dbo"
      helper-class="org.ofbiz.core.entity.GenericHelperDAO"
      check-on-start="true"
      use-foreign-keys="false"
      use-foreign-key-indices="false"
      check-fks-on-start="false"
      check-fk-indices-on-start="false"
      add-missing-on-start="true"
      check-indices-on-start="true">
        <jndi-jdbc jndi-server-name="default" jndi-name="java:comp/env/jdbc/JiraDS"/>
    </datasource>
   
You have to do this before (re)building with ant and (re)deploying. The line schema-name="dbo" avoids startup errors like "table not foud, creating table, error while creating table because it already exists". In my case it worked even with these errors. But it is annoying to find them in your log files.

Read the MS documentation that comes with the driver how you can use a named server instance and a different port (you typically need this if you want to use the free MSDE engine aka Express edition) in the url=".." entry in the jira.xml file. Or if you want to use Windows Authentication/Integrated Security.
     
The -XX:MaxPermSize=256m seemed to be essential in the startup of the java container (see other pages and comments on this wiki). Without that, I got PermGen exceptions while trying to initialize Jira. But because I got the same kind of errors in other applications running in the same container (xwiki, magnolia, and red5) when not using this line, I guess it has nothing to do with Jira. 128m should be enough for Jira, but I am running 4 apps competing for memory.

In case anybody is wondering why I want to use the Microsoft driver instead of the jtds driver: I know the first MS release was very buggy, but the current version (1.2 for 2005) is mature.  So it is an easy way to lower the number of shops you are doing business with in a project.

Hugo