I have Crystal Reports installed and working against Oracle server. When opening Toad and checking "Top Session Finder" I see that Crystal (cms.exe) constantly has open connections with the database.
Why does cms.exe create so much connections against the database? can it be limited? what is their purpose?
Can I set Crystal Server to open connection only when it needs them and close them when it's done using it?
The cms.exe process stands for Central Management Server, it is one of the core pieces of the Crystal Server platform.
Why does cms.exe create so much connections against the database? can it be limited?
If you open the CMC (Central Management Console) and select Servers and open the properties page for the Central Management Server, you should see an option System Database Connections Requested.
The option's purpose, explained by the Administrator's Guide:
Specifies the number of CMS system database connections that the CMS attempts to establish. If the server cannot establish all of the requested database connection, the CMS continues to function but at a reduced performance, since fewer concurrent requests can be served simultaneously. The CMS will attempt to establish additional connections, until the requested number of connection is established.
what is their purpose?
Again, taken from the Administrator's Guide:
The CMS maintains security and configuration information, directs service requests to servers, manages auditing, and maintains the CMS system database.
In other words: your Crystal Server environment cannot function without the CMS. Shut it down and your whole environment stops working.
It always needs open connections to the database in order to serve requests quickly. While you can limit the number of connection, doing so might impact the performance of your Crystal Server environment.