Currently, up to and including BDA v220.127.116.11 Patch 3, the process of adding new versions of MS SQL Server and Patch IDs to the mssql_service_packs.txt file is completely manual. This means that each time a new patch, cumulative update, hot fix, etc. is released for MS SQL Server, not only does someone need to add it to the Patch Repository, but they also need to have this file updated. As of the 19.01 version, this file now gets updated during each new release with the patch versions and IDs that are known up to the cut off date of the BDA version update. This does not allow for the potentially numerous updates that can be released by vendors between each update.
Create a mechanism wherein adding a new patch to the patch repository automatically populates the version and Patch ID into the mssql_service_packs.txt file on the Content and Satellite server(s) (depending on whether the implementation is running with a stand-alone Content/Satellite server or a multi-mesh). Whether this happens during the patch import/creation process in the UI, or through a script run through an Action, or a scheduled process, this file should be and needs to be updated automatically.
Any MS SQL Server running a patch, Service Pack, or CU that is not populated in the mssql_service_packs.txt file will only show in the UI the highest level version that exists in the mssql_service_packs.txt file until the file is updated with the newer information and the dmanager service is recycled. This causes the metadata displayed in the UI about each node and its associated RDBMSs and databases to be unreliable.
In an ideal situation, the metadata that exists in the dstate file on an agent should be directly queried by the Satellite server and displayed to the user when browsing a node in the UI. The dstate file that exists on every dagent installation contains all the metadata that is known about each node, its RDBMSs, and the databases.
Example dstate contents for an Oracle implementation:
Example dstate.conf contents for an MS SQL Server implementation:
sqlinst=*****\*****|version=10.50.6560.0|longversion=Microsoft SQL Server 2008 R2 - 10.50.6560.0 (x64) Enterprise Edition|nt_service_name=mssql$*****|is_clustered=0