I am installing a complete new copy of Jira due to MS licensing we need it to be on one of our production databases. We have created a second instance on the database for Jira to use. The Config wizard does not allow for the specification of and Instance even the the JDBC driver allows for it and you can edit the config afterwards to point to it. I tried editing the config with the extra options I need but it will not connect has anyone made this work? If you have please enlighten me on what I am doing wrong.
Hi Scott,
Could you let us know what version of Jira you're using here? And what version of MS SQL server you are trying to connect to here?
I ask because starting with Jira 7.5 and higher, Atlassian switch what jdbc database driver we bundle for connecting to MS SQL servers, and in doing so the syntax used to connect to these databases is slight different between the old jtds driver and the newer official Microsoft driver.
In addition to that, I'd be interested to take a look at your $JIRAHOME/dbconfig.xml file. Often times the problem is in the details of the syntax here. Of course please obscure your password in this file before posting it here.
Cheers,
Andy
Hi Andrew,
This is a brand new install, the intent is to do an export of our current instance of Jira which is running on mysql and move to an instance on our enterprise instance of mssql. As it is ground up install then migrate there is not much to any of it at the moment. I tried using the instructions for setting up the sql server but it just does not like connecting to the instance.
using the IP of the server to connect 192.168.2.20
the instance is called ToolsDB1
the database is called Jira
it uses the default port for sql server.
At this point I can reinstall the latest version of jira and rerun the configuration as we have been unable to get this instance connected.
Thanks,
Scott
Scott,
There are a couple different way to try to approach this.
1) Try editing the dbconfig.xml manually and see if this helps
<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://192.168.2.20=ToolsDB1;portNumber=1433;databaseName=Jira</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>
Save that file and restart Jira. If the database is empty I'd expect Jira to still run the initial setup wizard where you get to create the admin accounts and so on.
If that doesn't work, I'd be interested to see what happens in the $JIRAINSTALL/logs/catalina.out file when Jira tries to start. It tends to give us detailed errors that might explain the problem in more detail here.
2) An alternative approach would be to trying to use the JIRA Configuration tool to set this configuration up.
This utility has a 'test connection' feature you can try out before Jira tries to start up and use your settings to see if your settings can successfully connect to your database.
Sorry it took a bit to get back to you, The solution above did not work.
Going into the configuration tool I was able to set the instance by entering in
192.168.2.20\ToolsDB1
but I had to leave the port empty as this is what Microsoft says to do for the JDBC driver.
https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017
Specifically in the purple box it says if you enter the port it will ignore the named instance.
Using this logic and hitting the test button I was able to get successfully connected to the database instance. But now Jira will not start. Oh and the setup wizard requires the port so it will never work. port should be optional as well based on the Microsoft Doc above.
Thanks for all this info. It certainly looks like the Microsoft official database driver is recommending the use of the explicit port number for optimum performance. But I understand that you don't always have control over those other systems to be able to make that kind of change.
So I was thinking we might have another way to approach this. So Jira started bundling that official MS database driver in Jira 7.5 and higher version. But before that we actually bundled the opensource JTDS driver with Jira. I would be interested to see if perhaps you can use that database driver instead of the official one to try to connect Jira to this database in this case. It might not be officially supported by Atlassian, but I have a strong inclination to believe this could work for you. Try these steps.
<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:jtds:sqlserver://<YOUR_SERVER_FQDN>/<YOUR_DB_NAME>;instance=<YOUR_INSTANCE_NAME></url> <driver-class>net.sourceforge.jtds.jdbc.Driver</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> </jdbc-datasource> </jira-database-config>
I think this could work. Please let me know the results.
It looks like you're new here. Sign in or register to get started.
