Release : 8.6
Component : Gen Client Server Encyclopedia
Historically it was never recommended to use a CSE with a remote database due to possible performance problems over a networked database connection. However with advances in network speeds and bandwidths that concern should largely no longer apply.
For a CSE server using SQL Server it is relatively easy to configure it to use a remote database because the CSE and CSE Configuration program (cse_config.exe) use an ODBC Data Source Name (DSN) for the database connection. Some points to note:
1. The cse_config.exe requires SQL Server Tools programs osql.exe and bcp.exe which can be obtained by installing SQL Server Management Studio (SSMS) version 17.9.1 from here:
NOTE: SSMS version 18.x no longer has osql.exe so version 17.9.1 must be installed.
After SSMS 17.9.1 install, the CSE server should be restarted to ensure that the 32-bit osql.exe directory "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn" and bcp.exe directory "C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\" are added to the PATH.
2. On the CSE server, the 32-bit ODBC Administrator program "C:\Windows\SysWOW64\odbcad32.exe" should be used to create a 32-bit DSN pointing to the remote SQL Server server and database. More details can be found in the Gen 8.6 techdocs under: Gen™ 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Configure the Environment > Prerequisites for CSE Configuration. See section "Configure Databases" -> "Create Microsoft SQL Server CSE Database".
NOTE: For this scenario where only SQL Server 32-bit tools are installed, rather than the complete SQL Server software, only a 32-bit DSN is required for successful CSE Configuration and CSE execution. When creating the DSN it is suggested to use the "SQL Server Native Client 11.0" driver ((Gen™ 8.6 > Technical Requirements > Third-Party Software Version)
3. The file "C:\Program Files (x86)\CA\Gen86\CSE\cse_msqls\loadschema.cmd" used to load the CSE schema S* tables with bcp.exe uses the local hostname for the Server "-S" parameter ("%2") and therefore this must be changed to point to the remote SQL Server hostname prior to running the cse_confg.exe program as follows:
- Go to directory "C:\Program Files (x86)\CA\Gen86\CSE\cse_msqls"
- Copy loadschema.cmd to loadschema_default.cmd as backup.
- Edit loadschema.cmd and change all occurrences of %2 to your SQL Server hostname.
After running cse_config.exe check the file loadschema.log in directory "C:\ProgramData\CA\Gen 8.6\logs\CSE" to confirm success. Also check the other files in that directory for any other possible errors.
4. If any connection related errors occur when running the cse_config.exe program then, using the 32-bit ODBC Administrator program "C:\Windows\SysWOW64\odbcad32.exe", ODBC Tracing can be enabled ("Tracing" tab) which can help to diagnose the root cause.