JIRA service dependency on SQL Server service is not sufficient

[Joe Schmoe]

JIRA service is installed on the same Windows Server 2003 where SQL Server hosting JIRA database is installed. I already made JIRA service dependent on SQL Server service for startup.

However, after server is restarted when I try to access JIRA in the browser I get:

HTTP Status 404 -

---

type Status report

message

description The requested resource () is not available.

---

Apache Tomcat/6.0.32

When I look at STDOUT log the reason is clear - it says:

2012-06-02 11:21:38,723 main ERROR [atlassian.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: java.sql.SQLException: Cannot open database "JIRA" requested by the login. The login failed.

This is in spite of the fact that I already made JIRA service startup dependent on SQL Server.

Then I restart JIRA service manually and it starts working.

What other service should I make JIRA depended on in order for it to start correctly after reboot?

Is there some kind of "retry connecting to the database if it is not available" setting I can use in JIRA?

[Joe Schmoe · Accepted solution]

I found why making JIRA dependent on SQL Server is not enough.

This is the sequence of events from Event Log:

9:18:16 - OS start
9:18:33 - SQL Server entered running state
9:18:55 - Starting up database JIRA

So JIRA database doesn't become available for 22 seconds after SQL Server enters running state. Apparently JIRA service tries to connect before JIRA database is available.

It takes about 80 seconds for JIRA service to start when database is available.

It takes about 80 seconds for Confluence service to start when database is available (I run Confluence on the same server and it uses JIRA for user authentication).

As a kludge I set JIRA and Confluence services for Manual Startup (they both are made dependent on SQL Server service and Confluence is made dependent on JIRA serivce) and run this Powershell script on OS startup:

Start-Sleep -s 60
sc.exe start JIRA010612193624
Start-Sleep -s 90
sc.exe start Confluence020612094212

If you want to use it for yourself you may need to adjust delays.

The real solution is not to make JIRA/Confluence services dependent on SQL Server service and one another (this is just a kludge) because they may all run on different computers but instead how to make them to retry connection to the database if it fails on startup. This is somewhat different question and deserves it's own post.

[Alessandro Tondo]

+1 for talking about a simple retry loop

[Kevin Mote]

you might also try changing the "Startup Type" of the JIRA service from "Automatic" to "Automatic (Delayed)". This will force the JIRA service to wait until all Automatic services have started (plus 120 seconds, by default, although I believe this duration is configurable).

[Norman Abramovitz]

You did not say if you are using LDAP ro some other user domain interface, but if you are then include that dependency.

Maybe choose another service that is dependent upon sql server already wich should provide the delay you seem to need.

[Joe Schmoe]

I use SQL server authentication for JIRA if this is what you were asking about.

[Norman Abramovitz]

yes, that is what I asking about.

[Joe Schmoe]

I tried adding validation query as described here: https://confluence.atlassian.com/display/JIRA050/Surviving+Connection+Closures but it doesn't help.