Gen 8.6 CSE configuration for remote SQL Server database

Products

• Gen

Issue/Introduction

How to configure the Gen 8.6 CSE to use a remote SQL Server database i.e. the database is on a separate remote server to where the CSE software will be installed, configured and run.

Environment

Release : 8.6
Component : Gen Client Server Encyclopedia

Resolution

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 may be reduced.

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: Release notes for SQL Server Management Studio (SSMS) > Previous SSMS releases
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: 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 (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.

Additional Information

1. The CSE service CSESvcMD is created with an internal dependency on having a service for a default SQL Server instance (MSSQLSERVER) running on the same server as the CSE.
Therefore with the SQL Server instance being remote this error will be displayed when trying to start the service: "Error 1075: the dependency service does not exist or has been marked for deletion". Even if the registry key for CSESvcMD  ("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CSESvcMD") has DependOnService changed to remove MSSQLSERVER, after the server is restarted the CSESvcMD service will still fail to start with "Error 1053: The Service did not Respond to the Start or Control Request in a Timely Fashion". That is because the service has some built-in code to check for a database engine (messages will be displayed in file C:\ProgramData\CA\Gen 8.6\logs\CSE\CSESvcMD.log).
The DependOnService requirement for a MSSQLSERVER service can be handled by creating a dummy service named MSSQLSERVER using the Windows Resource Kit tool SRVANY.EXE. See the following link for further information: Create a User-Defined Service
NOTE: It appears that the Windows NT Resource Kit and Windows 2003 Resource Kit Tools (rktools.exe) are no longer available directly from Microsoft. However, this web archive link can be used to download rktools.exe: https://web.archive.org/web/20070119115200/https://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe
Alternatively, a zip file containing the required instsrv.exe and srvany.exe files has been attached to this article. Please note that Broadcom takes no support responsibility for the use of those 2 files.
For details on a support implementation of the dummy service MSSQLSERVER see KB article: Gen 8.6 CSE configuration for remote SQL Server database

Support tested the dummy service creation in-house successfully on a 64-bit Windows Server 2016 VM as follows:
 "Windows Server 2003 Resource Kit Tools" downloads file rktools.exe which when executed on 64-bit Windows is installed into directory "C:\Program Files (x86)\Windows Resource Kits\Tools"
Follow steps in "How to Create a User-Defined Service" with some slight differences:
a. Run this command to create dummy service MSSQLSERVER
"C:\Program Files (x86)\Windows Resource Kits\Tools\instsrv.exe" MSSQLSERVER "C:\Program Files (x86)\Windows Resource Kits\Tools\srvany.exe"
b. Run Registry Editor (regedt32.exe) and locate the following sub-key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
c. From the Edit menu, click New->Key. Type the following and click OK:
Key Name: Parameters
Class : <leave blank>
d. Select the Parameters key.
e. From the Edit menu, click New->String Value. Type the following and click OK:
Value Name: Application
Data Type : REG_SZ
String : C:\Windows\notepad.exe
f. The MSSQLSERVER key and Parameters sub-key should now look like this:




From Windows Services verify the new service MSSQLSERVER will start successfully (after creation it was already set to start Automatic.)

Reduce Message Dispatcher (iefmd) "Database startup wait time" set in CSE Configuration down to 10 seconds because the dummy service will not take long to start.
To do this run regedt32.exe again and locate this key:
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CA\CA Gen\8.6\CSE\Message Dispatcher
Change "MD Time Out" to be:
/mdtimeout=10

The CSESvcMD service is also set to start Automatic.
Restart the server to verify that CSESvcMD automatic start is successful now that the dependency service MSSQLSERVER has been added i.e. after restart verify that:
 - Windows Services shows that both services MSSQLSERVER and CSESvcMD are started.
 - Windows Task Manager Details tab shows the expected CSE iefmd.exe process plus 7 srv*.exe processes i.e. srvcoord.exe, srvid.exe, srvlock.exe, srvuga.exe, srvms.exe, srvcons.exe, srvvc.exe. NOTE: The srvany.exe process for the dummy service will also be displayed
The CSE can then be used successfully.


2. If it is not planned to use the default instance of SQL Server on the remote server i.e. it is planned to use a named instance, then that is not officially supported but can be achieved following the above steps with a similar workaround for loadschema.cmd which is documented in this KB article: Gen Client Server Encyclopedia (CSE) using SQL Server named instance