Clarity

PPM 14.3 New Install - Invalid database connection

Hello,

I am trying to install a fresh version of PPM 14.3. Installation on Windows, database is MS SQL Server 2012.

The Server health report shows that the database still has some connectivity problems.

Have also attached the properties.xml, healthreport.xml and the nsa-ca.log files.

Please help as I have been trying to solve this problem on and off for the past 2 weeks.

Thank you.

Regards,

Paul

---

Details below.

Database Installation:

1) Completed the database install of a default instance with SQL_Latin1_General_CP1_CI_AS collation.

2) Added niku user and used it to restore the niku database.

3) Ran all scripts listed in installation manual for SQL Server 2012.

4) Added ppm_dwh user and used it to restore the ppm_dwh database.

5) Ran all scripts listed in installation manual for SQL Server 2012.

6) Created the PPMDBLINK from the ppm_dwh database to the niku database and tested it. Works fine.

Admin install:

1) Installed Java, Tomcat, CCM Install-Check, Install. All went well. No errors.

Admin Config:

1) Setup database parameters. Database now says available.

2) Setup data warehouse parameters. Data warehouse no say available.

Health Report:

Show an error beside the database:

MICROSOFT SQL SERVER 2012 - 11.0.5343.0 (X64) MAY 4 2015 19:11:32 COPYRIGHT (C) MICROSOFT CORPORATION STANDARD EDITION (64-BIT) ON WINDOWS NT 6.3 <X64> (BUILD 10586: )

Can you login to the SQL server management studio?

If yes then create and ODBC data source toi your db and verify that you can access that with the same login details.

When you can do that verify that your details in CSA are the same.

The following line in the nsa-ca log

WARN 2015-12-07 18:21:29,579 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity

suggest that either your db install did not go OK or the connection details are not OK. Therefore I suggested the above. Start with SQL Server Management Studio and verify that you database is there with the tables.

The next line

ERROR 2015-12-07 18:22:35,952 [http-nio-8090-exec-6] restAdapter.JasperRestAdapter (clarity:sa:WgtGEXHrYEtyfUMfFJqEA/HrMTUp8D4X3YNthzkeV8g0:nsa.properties.nsa) sendRequest Error: Illegal character in authority at index 7: http://<my_reportserver>/reportservice/rest_v2/serverInfo

suggest that everything did not go right with the Jaspersoft configuration. Once you get connected to the database verify if the error is still there and if it is reconfigure Jaspersot.

Hello urmas,

SQL Server management studio works fine. Also, created an ODC in the System DSN localhost for both the niku and ppm_dwh database for both 32 and 64 bit connections. All connections were tested and work fine.

Also confirmed that the collation is SQL_Latin1_General_CP1_CI_AS.

Regards,

Paul

Can you remove the erroneous characters in the webURL parameter in properties.xml and try again?

Hello Steven,

Thank you for the help. Even though the fix was using the Enterprise version of SQL Server, I appreciate the heads up on the properties.xml file. I am going to go through this file to see if there are any other potential bugs waiting.

Regards,

Paul

Hi Tenden,

From the health report I see you are using the MS SQL Standard Edition and with CA PPM only MS SQL Enterprise editions are supported

Per release notes of 14.2

■ Microsoft SQL Server 2008 R2 Enterprise Edition: Any service pack5c)

■ Microsoft SQL Server 2012 Enterprise Edition: Any service pack

So please use the enterprise edition and test.

Regards

Suman Pramanik

Hello Suman,

Thank you. I will get right on it.

Regards,

Paul

Hold your horses.

It is 14.3 ain't it and not 14.2? (Clarity 14.3.0.298 ready.)

The release notes say

Sorry  SumanPramanik   Looks awfully standard to me

Hi urmas,

The one which you mentioned is Operating system, what I mentioned is the version of Database version. For MS SQL server its Enterprise only

Ref: https://docops.ca.com/ca-ppm/14-3/release-information/release-notes#ReleaseNotes-Compatibilities

Yes your are right,My apologies

.

Though, do you think that would make a difference in connecting?

Hi Urmas,

Yes I have seen that in few other issues and its always better to be in supported version.

Regards
Suman Pramanik

I have not seen that in a single user test systems and in real systems have not really seen unsupported versions.

When I encountered a similar problem is was the port

At least one validated Database must exist in Multitenant mode

Hello urmas,

Thank you for the help and the link to the Multitenant mode issue.

Regards,

Paul

My earlier reply somehow didn't post all of my message text.

Anyway I replicated your error 

ERROR 2015-12-09 00:26:33,596 [http-nio-8090-exec-6] restAdapter.JasperRestAdapter (clarity:sa:qSbUCYJCiLv2O7d4mSDTVSNVyp6iAp1nXhgOmofF//s0:nsa.properties.nsa) sendRequest Error: Illegal character in authority at index 7: http://<my_machine_name-VM35204:8080/reportservice/rest_v2/serverInfo

The problem is you have some bogus characters in the properties.xml file.  

<reportServer id="jaspersoft" webUrl="http://<my_reportserver>/reportservice"

Get rid of the &lt; in front of my_reportserver and give it a shot.

Hello Steve,

The &lt; characters appear to be intentionally put in the properties.xml file. If &lt; is replaced with < character, the System Admin console fails to start. By having the &lt; characters it causes the System Admin console to bypass the loading of the Report server. When the correct information for the Report server is added via the System Admin console, there no longer is an error for the Report server.

Regards,

Paul

Paul,

Very interesting.  I think I learned something here. 

Glad you ended up getting it to work.

Cheers!

Steve

Thank you Suman.

Re-installing with SQL Server 2012 SP3 Enterprise edition fixed the problem.

Regards,

Paul

Thanks Paul. Great to hear

Still, this would be an excellent learning opportunity: How does Clarity know whether is it Standard or Enterprise if it cannot connect and can tell that there is a database in the first place?

The difference should come first in use as soon as  Clarity utilizes the features that do not exist in Standard.

If it cannot connect the issue is moot, as there are other problems in the configuration/setup beyond the db software edition.

Once the connection can be made, Clarity is able to run a query and check that the platform is supported.

Taking 13.2 for example, the health check contains the following pattern-matching values to see if the response from the DB itself meets the requirements:

  MICROSOFT SQL SERVER 2008*ENTERPRISE EDITION*||MICROSOFT SQL SERVER 2012*ENTERPRISE EDITION*

If it's not matched, then a supported database version/edition isn't available.  As in this case, where the string returned was this instead:

MICROSOFT SQL SERVER 2012 - 11.0.5343.0 (X64) MAY 4 2015 19:11:32 COPYRIGHT (C) MICROSOFT CORPORATION STANDARD EDITION (64-BIT) ON WINDOWS NT 6.3 <X64> (BUILD 10586: )

The version/vendor part matched, the edition did not.

It's not enough for Clarity to 'try' the features and then report them if they are not found; some of the features are enabled/disabled at the engine level in the database and then appear to be transparent at the user level when issuing the SQL.  Aside from the differences in performance, there are no other outward indicators that these features are not present in the engine in order to say.  This specifically comes into play with clustered indexes.. these are allowed on both editions, but are not optimized in standard edition's engine.  We need them optimized.

Hopefully I understood your request correctly.

Thanks

I must say that I am strongly in favor of using supporter versions for all types of Clarity environments, because we are not yet at the bottom of it. Some times you may want to test something and and a supported version is not available so you would like to do the test on something that you have.

In 14.3 the testing of the database enterprise version is in the install script and the install script will not run if the database version is not supported.

If you do install on another version of db (like for testing) the health report will display that.

That does not prevent the database from being available or using Clarity

So I di not quite see the light yet for the nonavailability being due to unsupported db version only.

Yes I was thinking exactly the same, I don't think it was the version of 2012 mssql but the configuration.

although not supported you can get it working on express!