Moving SonarQube to other server

At first I had SonarQube installed together with Jenkins on the same server. SonarQube is quite resource demanding and it was causing slowness of the other jobs / tasks running on Jenkins.

Moving to other instance was simple as installing SonarQube on other server and configuring Jenkins to use new SonarQube instance.

Steps required

• Java runtime environment
• SonarQube binaries
• MSSQL JDBC driver (make sure you get sqljdbc_auth.dll available on PATH)
• SonarQube configuration was copied from old server to new (sonarqube/conf/*)
• SonarQube instance was registered as service and started. 
• Once the service is running then try to access it in browser, if it is not accessible then check logs (sonarqube/logs) and resolve.
• Once the web is up and running there had to be installed language plugins again. 
• Jenkins configuration have to be updated with new SonarQube url (Manage Jenkins > Configure System)

SonarQube AD authentication setup

User in SonarQube can be validated against ActiveDirectory, once the user is validated it will be automatically created which is useful if there are a lot users who are required to use the tool.

1. LDAP plugin needs to be installed in SonarQube marketplace
2. sonar.properties needs to be updated with LDAP configuration details
3. SonarQube service needs to be restarted. 
4. Go to SonarQube web. 
• If there are issues with the configuration check the logs (SonarQube\logs\web.log)

# LDAP configuration
# General Configuration
sonar.security.realm=LDAP
sonar.authenticator.createUsers=true
ldap.url=ldap://ldapserver:389
ldap.bindDn=CN=username,CN=Users,DC=domain,DC=company,DC=com
ldap.bindPassword=password

# User Configuration
ldap.user.baseDn=DC=domain,DC=company,DC=com
ldap.user.request=(&(objectClass=user)(sAMAccountName={login}))
ldap.user.realNameAttribute=cn
ldap.user.emailAttribute=mail

ADExplorer is useful to confirm AD object properties and validate the server address and credentials.

In case it is not clear what are AD server details then use guidance on SO.

More details can be found in SonarQube LDAP plugin documentation.

SonarQube MSSQL backend setup

1. Create Database
• CREATE DATABASE "sonar" COLLATE Latin1_General_CS_AS 
• It needs to be case and accent sensitive
• Create user and add permissions to user so tables can be created with SonarQube start
2. Modify SonarQube configuration to point out to database created
• Go to SonarQube\conf\sonar.properties and follow instructions in the file
• Uncomment and change connection string e.g. sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true
• In case there is not used integrated also specify sonar.jdbc.username and sonar.jdbc.password
3. In case there is used integrated connection to database used then download Microsoft JDBC driver, unzip and somewhere to the system PATH place sqljdbc_auth.dll (either 32 or 64 bit based on your operating system)
4. Restart SonarQube instance.

SonarQube installation quide 

Retrieving binary data from base64 node in xml variable

In case there is xml document with base64 encoded binary data e.g. PDF (below example has the binary data shortened)

declare @doc xml
set @doc = '<doc><PdfData>JVBERi0xLjQN</PdfData></doc>'

then there is very simple to get the binary data decoded as binary again

select CAST (@doc.value('(//PdfData/text())[1]', 'varbinary(max)') AS varchar(max))

https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql

How to find size of databases and last time they got accessed

Sometimes you may need to free some space on MSSQL server and idetify databases which are not used long time back. This is especially useful on development servers where people may restore database just for troubleshooting of some issues and then the database is not needed anymore!

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)

select db.*, last_user_seek = MAX(last_user_seek),
 last_user_scan = MAX(last_user_scan),
 last_user_lookup = MAX(last_user_lookup),
 last_user_update = MAX(last_user_update)
from (
 select 
  name,
  (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
  (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB 
 from sys.databases db
) as db
left join sys.dm_db_index_usage_stats stats on stats.database_id = db_id(db.name)
group by db.name, db.DataFileSizeMB, db.LogFileSizeMB
order by db.DataFileSizeMB desc

References:
Sql server 2008 howto query all databases sizes
How do you find the last time a database was accessed

Removing duplicate records from database table

If there is need to select or remove data from table which are duplicate by multiple fields (field1 and field2) in the below example, there can be used query like

WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY field1, field2
                                       ORDER BY ( SELECT 0)) RN
         FROM   mst_table)
DELETE FROM cte
WHERE  RN > 1;

MSSQL server - How to return values from multiple nodes in XML in single field

DECLARE @xml xml
SET @Xml = '<FIELD NAME="NAME" BASE="5179827" COUNT="4">
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="c">...</TOKEN>
</FIELD>'

SELECT
  x.query('data(TOKEN/@TEXT)') AS List
 ,x.query('distinct-values(TOKEN/@TEXT)') AS DistinctList
 , x.query('<data>
    {
     for $x in distinct-values(TOKEN/@TEXT)
     return 
      (concat($x, ","))
    }
   </data>
 ').query('data/text()') AS CommaSeparatedList
FROM @xml.nodes('/FIELD') AS d(x)

References

http://www.olcot.co.uk/sql-blogs/using-xquery-to-remove-duplicate-values-or-duplicate-nodes-from-an-xml-instance