I do not have a lot of great news for you here, particularly in 8.0.
All of the reporting is currently being done using Crystal reports and not the HR client. We changed the SQL authentication from Windows to mixed mode authentication
The whole concept of Mixed Mode Authentication with Microsoft SQL was not supported until 7.0(1)SR4 per the release notes. Before then, changing this setting had several negative consequences such as breaking the Historical Reporting Client. Hopefully this is not news to you; however, as you will see with the later answers in this reply, the BU is sort of cutting those customers who went "off the reservation" loose.
What I want to do is copy the db_cra database to our primary MSSQL server that houses several other custom databases used for reporting and other process.
For the Windows-based versions (through 7.0) this was supported through a Microsoft SQL DTS job. This was documented in the Historical Reporting Administrator and Developer Guide under the Exporting Data when the Cisco Unified CCX Database and the Data Warehouse use Microsoft SQL Server 2000 section.
The BU failed to provide an equivalent solution to this with the VOS-based versions (8.0 and later). There were promises - I personally stressed this point several times - but it never materialized. The only answer that has been provided after FCS has been to manually copy the tables using SQL SELECT statements at specific intervals. The recommendation has been a 10 minute interval at a minimum so this does not work for real-time reporting needs either. Needless to say, this is far less than ideal. This manual copy is also supposed to represent one of your concurrent HRC sessions from a server sizing perspective.
What is everyone else doing for custom reports on the windows based UCCX systems? Are you just changing it to mixed mode and letting the users run the reports directly from the production database?
Some certainly do. I tell my customers not to because of the mess they will be faced with when trying to upgrade. Also be aware that the HRC is not entirely useless. One of the critical roles it provides is database I/O throttling and node standby node selection for queries. Bypassing the HRC puts you in very uncharted waters, especially with a moderate volume of users [queries].
Does anyone have UCCX 8.0 in production?
Yes. There have been a fair amount of bugs in the transition. Probably no worse than CUCM 5.0(1) but they can be irritating. I would not recommend anything below 8.0(2)SU2 at a minimum. Test thoroughly and read documentation for caveats.
What are you doing for custom reports? I see that you can create an ODBC connection to the Informix database directly. Are you just using that ODBC connection to perform your reporting?
This is sort of the iceberg. Officially, your option is limited to only the following: Create custom reports for use by the HRC only. Optionally, install a copy of the HRC on a VM and save scheduled reports to a network drive. This is what we do even though it causes some pain points. Unofficially you can [manually] copy the database tables elsewhere and do what you wish with them. Some third-party tools exist to automatically pick up and distribute reports that the HRC has placed in appropriate folders.
Here are some of the lessons we have learned thus far:
- The ODBC connection and Informix SQL user are only supported for developing (not running) Crystal reports through Crystal Professional or Developer 11.0 or 11.5. The versions are dictated in the Software and Hardware Compatibility Guide.
- The same Informix SQL user has rights to create additional stored procedures because the HRC has limitations when querying. You can use AGSServer Studio to access the db_cra database. There have been several database access bugs; again upgrade to at least 8.0(2)SU2.
Make sure to read the newer Historical Reporting Administrator and Developer Guide to understand all the intricacies here. For example, you need to manually grant permissions to newly created stored procedures for the HRC to be able to execute them. - SQuirreL SQL, while listed as supported, does not appear to show you existing stored procedures. The Development Engineers blame this on the fact that it is a third-party tool and refer you to the AGS tool instead.
- If you happen to use the SQuirrel SQL client, there are several non-obvious hurdles you must overcome. The most notable is that you must separately install the IBM Informix JDBC driver and configure SQuirreL SQL to use the related JAR files.
- The connection parameters on the database are not always straight forward. IBM Informix has a lot of extra options (functionality?) that most Windows administrators are not accustomed to. Here's the quick rundown if using AGS Server Studio:
- Server: <servername>_uccx
- Host[port]: :1504
- User: uccxhruser
- Password: Set this within /appadmin under Tools > Password Management
- Database: db_cra
- Character Encoding: Unicode UTF-8
One last editorial word: The PM has been very non-committal on this whole topic. The answers are essentially "This is what we tried and will support. You're certainly welcome to self-support (i.e. no TAC) if you feel comfortable doing it and want to follow a different path."
