This page has general notes on connecting JIRA to SQL Server 2005. It supplements the official SQL Server 2005 installation documentation.
Was this helpful?
Thanks for your feedback!
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:
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.
I fixed mine by making sure I defined schema-name="dbo" in my entityengine.xml. If it that value is missing/removed, you should get the above errors even though JIRA still appears to functionally work. Depending on how your SQL Server is configured, you'll adjust this accordingly.
Matt Doar (ServiceRocket)
Has anyone run into issues with the different versions of SQL server? Specifically, did using the Workgroup SQL Server license cause you any problems?
Has anyone tried to connect using SQL Server 2005 Express? Were you successful?
Yes I have done it.
Here is my somewhat detailed how to guide: http://thesnowdrift.spaces.live.com/blog/cns!8D8061E0A7212946!193.entry
SQL Server Express may not listen on the usual port 1433 - see this knowledgebase entry.
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.
Rosie Jameson [Atlassian]
Thank you everyone for the feedback. We will investigate and update the docs as appropriate.
The documentation has been updated: http://www.atlassian.com/software/jira/docs/v3.12.1/databases/sqlserver.html
Thank you again for your input.
Here is one person's notes on getting JIRA to work with SQL Server Express:
And here are another person's notes re SQL Server Express 2005:
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"
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"
<jndi-jdbc jndi-server-name="default" jndi-name="java:comp/env/jdbc/JiraDS"/>
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.
Has anyone had any experience with connecting JIRA to a SQL 2008 database? Any notes/hints would be great.
Thanks in advance
In our company JIRA (3.11 and now 3.12) works with MS SQL 2008 from september. All works well. No problems were.
Hi, we are running Jira on sql 2000 now and want to do a restore from backup on sql 2005. Will it cause any issues? Did anybody do it?
Just an FYI if anyone is trying to run JIRA using Windows Authentication with SQL Server (2005 in this case)...
The documentation says to enter your username and password in the server.xml file. However, this username and password is only for SQL Server Authentication. If you use Windows Authentication, all you have to do is delete these two parameters from the xml file, and (assuming you followed the SSO directions in the jTDS readme, i.e. placing the ntlmauth.dll in the system path) JIRA will start up just fine.
We have a security policy that requires us to use Windows Authentication with SQL Server, and I'm sure there are others that may have the same requirement, so I figured I'd post this here.
It took me over a week to figure this out, since there was no documentation concerning it, and since Atlassian's technical support team all said Windows Authentication "should" work, but they weren't sure how to get it to work...
Leif, can you provide you the part of you server.xml file that shows the configuration for the SQL database?