Clarity

 View Only
Expand all | Collapse all

Setting up existing Clarity disk on a new server

  • 1.  Setting up existing Clarity disk on a new server

    Posted Jul 05, 2020 02:48 PM

    This relates slightly to

    Clarity

    but is somewhat different.

    I've got Clarity on a system with two disks. The first disk contains mainly the OS and the MS SQL program files. The second disk contain Clarity and db data and log files

    What I am trying to do is to install OS  MS SQL program files.on a new disk and use the Clarity install and db on the second disk.

    What I did is created empty niku and ppm_dwh databases with the same names as before and copied the old data and log files over them.

    Run the scripts setting the database and user. Now I can log in to the niku db with Management Studio and see the data.

    Installed java and tomcat. I set the environmental variables as the should be. That is as far as I get.
    Preinstall check for upgrade says it cannot connect to the database


    This lets me to the niku database.
    The 64 bit ODBC connection tests OK.

    These are the properties

    These are the file locations


    MSSQL13.CLARITY was created with the new install of MS SQL.
    There is another copy of the logs in the Log folder as that is they were earlier.
    This is what I try to enter in CSA and to my best recollection it is the same as before


    Apparently something is very wrong

    What might that be?


  • 2.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 06, 2020 03:45 AM
    GUESSing (because I have never really understood connection strings);

    My SQL server setup is pretty much like your first attempt, but I don't specify the instance name at all (it is blank - just the hostname and port is set)

    Alternatively ; have you tried the URL connection and use the same URL as in your SQL configuration setup?


  • 3.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 06, 2020 12:58 PM
      |   view attached

    Thanks Dave.

    Tried that, but it appears that CSA and db are not in talking tems. The CSA log contains among other things

    WARN 2020-07-06 18:19:42,175 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity
    ERROR 2020-07-06 18:21:29,328 [http-nio-8090-exec-16] restAdapter.JasperRestAdapter (clarity:sa:bCBUOsc+N01pztd9nzKlyMXJEP51e9JWB/lv7NFGdSM0:nsa.properties.nsa) sendRequest Error: Illegal character in authority at index 7: http://<my_reportserver>/reportservice/rest_v2/jaasServerStatus
    ERROR 2020-07-06 18:21:35,836 [http-nio-8090-exec-17] restAdapter.JasperRestAdapter (clarity:sa:bCBUOsc+N01pztd9nzKlyMXJEP51e9JWB/lv7NFGdSM0:nsa.properties.database) sendRequest Error: Illegal character in authority at index 7: http://<my_reportserver>/reportservice/rest_v2/jaasServerStatus
    WARN 2020-07-06 18:21:42,151 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity
    ERROR 2020-07-06 18:22:36,203 [http-nio-8090-exec-17] persistence.PersistenceController (clarity:sa:bCBUOsc+N01pztd9nzKlyMXJEP51e9JWB/lv7NFGdSM0:nsa.properties.database.save) Exception due to DB Connection failure
    ERROR 2020-07-06 18:22:36,203 [http-nio-8090-exec-17] niku.nsa (clarity:sa:bCBUOsc+N01pztd9nzKlyMXJEP51e9JWB/lv7NFGdSM0:nsa.properties.database.save) Unable to persist configuration properties to database. Likely cause is database properties are not yet set or misconfigured.
    com.niku.union.config.ConfigurationException: Error persisting config: properties.xml


    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants for this Application with instance id = nsa :
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) CLARITY

    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: id: clarity
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: appId: app
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: bgId: bg
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: fileDirectory: tenantDirectory
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: status: valid
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: id: clarity
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: serviceId: niku
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: driver: com.ca.clarity.jdbc.sqlserver.SQLServerDriver
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: url: jdbc:clarity:sqlserver://W2016\CLARITY:1433;DatabaseName=niku;InsensitiveResultSetBufferSize=0;ProgramName=Clarity;DateTimeInputParameterType=dateTime
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: username: niku
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: schemaName: niku
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: serviceName: niku
    SYS 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: vendor: mssql
    WARN 2020-07-06 18:22:38,058 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity

    I starting to think that the problem is in the SQL connection settings, but not the Clarity connection settings in the properties file. When I try to change some of the TCP/IP protocal values in the SQL Server manager I get the "File is read only error and have to do that through the registry.


    Attachment(s)

    log
    nsa-ca.log   440 KB 1 version


  • 4.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 06, 2020 11:37 AM
    Hello,

    Try this way:
    MSSQL setting with instance name



  • 5.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 06, 2020 01:53 PM

    Thanks sor suggesting.

    Unfortunately the same result.




  • 6.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 07, 2020 02:28 AM
    Hostname: W2016
    Instance name: CLARITY
    Port: 1433

    You wouldn't put "\" in the hostname, and the MSSQL instance name is the value after the "\".

    This makes the assumption though that your SQL Server instance is actually running on (the default) port of 1433 though.  it might not be, since when connecting via SQL Server Management tools / ODBC, you're using the hostname\instance syntax which relies upon the SQL Browser service running (on port 1434), which will then query SQL Server by that name in order to figure out what the port is for connecting.

    If not specifically configured to 1433 (or some other specific port if that one is already in use either by another MSSQL instance/install), then it may be using another port altogether, and that may even be dynamically set instead of static.

    You'll need to adjust that under the network config of the SQL Server services in the MSSQL management software, and restart them, to get it to use a specific port if not already set that way.

    I would then encourage using another jdbc-based db query tool (e.g. dbeaver, squirrel, or better) to confirm that your MSSQL server is specifically reachable using its hostname and port before getting the CSA to accept it.  Reason being that the CSA will "swallow" many misconfiguration reasons without reporting the detailed cause back to you, leaving you with a guessing game to fix things.  The other tools will be more specific in their error messages to guide you.


  • 7.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 07, 2020 02:19 PM
    Thanks Nick
    The dbeaver and squirrel seem to be more versatile tools. than SSMS. However, they need more configuration and more files than comes with MSSQL or Clarity

    dbeaver even tells which files are missing and offers to download them
    If you download them no problem connecting with the Clarity database.



    The thing is that both tools have the same problem both on the system where the database is available and the system where it is not.



    and (not available)


    The reason I put \CLARITY there is that I need that in the login to SSMS and dbeaver

    That difference is also in the file locations

    This is where it does not connect

    This is where there no connection broblem

    So the folder structure is slightly different.


  • 8.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 07, 2020 02:38 PM
    As before, host is NOT having \CLARITY in it.  Host is literally the server's hostname, i.e. W2016

    Do not put the backslash and instance name (CLARITY) in the hostname fields.

    Then if you're running multiple instances of SQL Server on the same system (or even installed multiple instances of SQL Server but only running one at a time), they will be configured to use different ports, and you will need to find out what the correct port number is for the W2016\CLARITY instance in order for JDBC-based tools to properly use it, since they are connecting via TCP/IP with port numbers.

    The generically named W2016\CLARITY used by SMSS could be using TCP/IP (with any port), named pipes, or some other protocol.  So by itself, it's not enough information to confirm the network configuration done of the SQL Server instance to ensure it's correct (i.e. has a static port configured enabled over an enabled TCP/IP protocol).  You'll need to use SQL Server's network config section for the service(s) to set that up.  Until you know precisely which port is in use - which you should choose - chances of getting a working config are minimal for a named SQL Server instance.


  • 9.  RE: Setting up existing Clarity disk on a new server

    Posted Jul 07, 2020 02:46 PM
    Unless things have changed, the instance name in Clarity isn't even used directly.  Clarity will form a jdbc url using the hostname and port and db name.  The instance name is for the benefit of the reporting tools like business objects and jaspersoft, as they may build their connections differently and utilize the instance name.

    This is why we have to get the core TCP/IP configuration working first, and the reason to use dbeaver or squirrel or similar to do that is just to try and solicit more feedback about the error causes when Clarity can be too vague.  So don't let dbeaver or others make the connection using the SQL Browser services where it will resolve the host\instance naming to an ip and port number combination, as that isn't leveraged or usable by Clarity.


  • 10.  RE: Setting up existing Clarity disk on a new server
    Best Answer

    Posted Jul 08, 2020 03:05 PM

    I think the whole mess was created when made a new install of SQL server after having installed the new OS.
    The old SQL data files were still there and so were the SSMS files. They messed the connection.
    After cleaning the db disk of all that old data I installed SQL server again, but this time the default instance (not a named instance) and restoring the Clarity databases from the backups the database was immediately available in the CSA withou any further action.

    Thanks for all help