My SonarQube is running on VM instance of Microsoft Azure. Now the SonarQube's database is MySQL, and I'm trying to change MySQL to Azure SQL Database, but SonarQube couldn't connect Azure SQL Database by some erroes which occured at ActiveRecord component. I know SonarQube doesn't support Azure SQL Database officially and support SQL Server below, nevertheless Azure SQL Database is compatible with SQL Server, so It's may possible to connect Azure SQL Database. Please tell me how to connect Azure SQL Database if anybody knows.
sonar.properties:
sonar.jdbc.username=sonar@<server>
sonar.jdbc.password=<password>
sonar.jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
jdbc:jtds:sqlserver://<server>.database.windows.net:1433/<databasename>;ssl=require
error trace:
org.jruby.rack.RackInitializationException: no connection available
from arjdbc/jdbc/RubyJdbcConnection.java:517:in `set_native_database_types'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection.rb:61:in `initialize'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/adapter.rb:31:in `initialize'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection_methods.rb:6:in `jdbc_connection'
from org/jruby/RubyKernel.java:2227:in `send'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `new_connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in `checkout_new_connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in `checkout'
from org/jruby/RubyKernel.java:1519:in `loop'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `checkout'
from jar:file:/opt/sonar/web/WEB-INF/lib/jruby-complete-1.7.9.jar!/META-INF/jruby.home/lib/ruby/1.8/monitor.rb:191:in `mon_synchronize'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in `checkout'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in `connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in `retrieve_connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in `retrieve_connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in `connection'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:440:in `initialize'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:401:in `up'
from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:383:in `migrate'
from /opt/sonar/web/WEB-INF/lib/database_version.rb:62:in `upgrade_and_start'
from /opt/sonar/web/WEB-INF/lib/database_version.rb:74:in `automatic_setup'
from /opt/sonar/web/WEB-INF/config/environment.rb:239:in `(root)'
from org/jruby/RubyKernel.java:1094:in `load'
from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:1:in `(root)'
from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:25:in `load_environment'
from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails_booter.rb:79:in `load_environment'
at org.jruby.rack.RackInitializationException.wrap(RackInitializationException.java:29)
at org.jruby.rack.RackApplicationFactoryDecorator.init(RackApplicationFactoryDecorator.java:98)
at org.jruby.rack.RackServletContextListener.contextInitialized(RackServletContextListener.java:50)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4939)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5434)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.jruby.exceptions.RaiseException: (ConnectionNotEstablished) no connection available
at arjdbc.jdbc.RubyJdbcConnection.set_native_database_types(arjdbc/jdbc/RubyJdbcConnection.java:517)
at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection.rb:61)
at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/adapter.rb:31)
at RUBY.jdbc_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection_methods.rb:6)
at org.jruby.RubyKernel.send(org/jruby/RubyKernel.java:2227)
at RUBY.new_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:223)
at RUBY.checkout_new_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:245)
at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:188)
at org.jruby.RubyKernel.loop(org/jruby/RubyKernel.java:1519)
at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:184)
at RUBY.mon_synchronize(jar:file:/opt/sonar/web/WEB-INF/lib/jruby-complete-1.7.9.jar!/META-INF/jruby.home/lib/ruby/1.8/monitor.rb:191)
at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:183)
at RUBY.connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:98)
at RUBY.retrieve_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:326)
at RUBY.retrieve_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:123)
at RUBY.connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:115)
at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:440)
at RUBY.up(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:401)
at RUBY.migrate(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:383)
at RUBY.upgrade_and_start(/opt/sonar/web/WEB-INF/lib/database_version.rb:62)
at RUBY.automatic_setup(/opt/sonar/web/WEB-INF/lib/database_version.rb:74)
at RUBY.(root)(/opt/sonar/web/WEB-INF/config/environment.rb:239)
at org.jruby.RubyKernel.load(org/jruby/RubyKernel.java:1094)
at RUBY.(root)(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:1)
at RUBY.load_environment(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:25)
at RUBY.load_environment(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails_booter.rb:79)
Thanks
It's possible, I just discovered, after spending 6 pretty frustrating hours on this issue. Here are the issues I encountered and the solutions I came up with for each:
Couldn't connect to the Azure SQL Server database -- connection would just time out. The solution was to add ";ssl=require" to the connection URL and -Djsse.enableCBCProtection=false (see Connecting to Azure SQL DB via JTDS on an Android device) to the server's java parameters (in conf/wrapper.conf). Here's what my database configuration looks like:
sonar.jdbc.username=<username>@SERVER
sonar.jdbc.password=<password>
sonar.jdbc.url=jdbc:jtds:sqlserver://SERVER.database.windows.net:1433/DATABASE;ssl=require;SelectMethod=Cursor
I also upgraded the jTDS driver in extensions/jdbc-driver/mssql from 1.2.8 to 1.3.1, but that might not be necessary.
SonarQube creates some tables without clustered indexes, which Azure SQL Server doesn't support (see Tables without a clustered index are not supported in this version of SQL Server). To solve that issue, I needed to install SonarQube locally using a local SQL Server Express instance. After doing that I ran the following DDL to convert the problematic tables to use clustered indexes:
CREATE UNIQUE CLUSTERED INDEX Idx_schema_migrations ON dbo.schema_migrations(version);
CREATE UNIQUE CLUSTERED INDEX Idx_groups_users ON dbo.groups_users(user_id,group_id);
Finally, now that the database is Azure-compatible, inside SSMS I right-clicked on the database, selected Tasks/Deploy Database to SQL Azure, and deployed it to my Azure server.
SonarQube's database upgrader doesn't support SQL Server 2014 (you get a "Deprecated feature 'DROP INDEX with two-part name' is not supported in this version of SQL Server" error while it's upgrading from 4.3 to 4.4). I had to do a fresh install to get around that issue, though it's probably possible to either figure out the necessary upgrade DML/DDL yourself or upgrade SonarQube's ActiveRecord JDBC MSSQL adapter (hoping they'll do that in their next version).
Simple, huh? :p