MSSQL Instance and Databases Marked as Absent After Discovery — How the Post-Sensor Script Works on Windows Failover ClustersSummary<!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Overview Are your MSSQL Instance or Database CI records getting marked as Absent after every Discovery run? This is likely caused by the post-sensor script "Modify Operational Status for MSFT SQL Instance CI" which runs automatically after the MSSql DB On Windows pattern completes on a Windows Server that belongs to a Failover Cluster. This article will help you understand: • Why this script runs and what it does • When this behavior is expected • When this behavior is a problem • How to fix it based on your environment ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ What Does the Post-Sensor Script Actually Do? Discovery runs against individual Windows Server targets. Once the pattern completes, the post-sensor script picks up the payload and does the following: Step 1 - Wire up relationships in CMDB The script creates these relationships based on what was discovered: • Windows Server to Cluster Node (Hosted on) • Windows Server to Cluster (Hosted on) • MSSQL Instance to Cluster (Hosted on) • MSSQL Instance to Cluster Node (Hosted on) It also updates the Cluster Node server field to point to the Windows Server where the instance was found. Step 2 - Find other instances on the same cluster The script queries the CMDB for any other MSSQL Instance records that are related to the same cluster but are NOT the instance just discovered. These are treated as instances belonging to inactive or passive nodes and become candidates for cleanup. Note: The query skips any instance where the comments field is set to always_on. More on this in the fix section below. Step 3 - Mark or delete those instances and their databases For each instance found in Step 2 the script takes action based on the global property discovery.mssql.cluster.instance.postsensor.delete: Property = string 'false' • Child Databases marked Absent (install_status = 100) first • Then Instance marked Absent (install_status = 100) Property = string 'true' • Child Databases hard deleted first • Then Instance hard deleted from CMDB ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ Is This Behavior Expected in Your Environment? Read the scenario that matches your setup: Scenario 1 - Active/Passive Cluster (SQL Failover Cluster Instance) Only one node is active at a time. The same MSSQL Instance moves between servers during failover. Discovery runs on Server A (active) • Instance A found • No other instances found on cluster • Nothing marked Absent — correct Failover happens. Server B is now active. Discovery runs on Server B • Instance B found • Script finds Instance A on same cluster • Instance A Databases marked Absent • Instance A marked Absent — correct, it is now passive This is expected and intended behaviour. The script is working correctly. Scenario 2 - Active/Active (Two Genuine Instances on Different Servers) Both servers host their own genuine active MSSQL Instance. Both belong to the same cluster. Discovery runs on Server A • Instance A found • Script finds Instance B on same cluster • Instance B Databases marked Absent — incorrect • Instance B marked Absent — incorrect Discovery runs on Server B • Instance B found • Script finds Instance A on same cluster • Instance A Databases marked Absent — incorrect • Instance A marked Absent — incorrect This repeats every discovery cycle. Both instances keep marking each other Absent. This is the problem scenario. ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ How to Fix It Are both your instances genuinely active at the same time on different servers? If yes, follow these steps: Go to the cmdb_ci_db_mssql_instance table in CMDBOpen the first affected MSSQL Instance CI recordSet the Comments field to exactly: always_onSaveRepeat for all active instance records on the same clusterRun Discovery again Why does this work? The cleanup query in the script explicitly skips any instance where comments = always_on. Once set, those instances are invisible to the cleanup logic and will never be marked Absent. Warning: Do not set comments to always_on on instances in a genuine Active/Passive cluster. The cleanup logic will stop working for those instances and stale duplicate records will remain in CMDB after a failover. ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ Quick Reference - Which Configuration Do I Need? I have an Active/Passive cluster and want to keep passive records • Set discovery.mssql.cluster.instance.postsensor.delete = false (string) in System Properties • Passive instance and databases will be marked Absent after discovery I have an Active/Passive cluster and want to remove passive records • Set discovery.mssql.cluster.instance.postsensor.delete = true (string) in System Properties • Passive instance and databases will be hard deleted after discovery I have an Active/Active or Always On environment • Set Comments = always_on on all active MSSQL Instance CI records in CMDB • Instances will never be marked Absent by the script Nothing is being cleaned up at all • Check if the above property in System Properties • If not set, the boolean fallback causes the string comparison to fail and no cleanup runs ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ Related Components Pattern: MSSql DB On Windows Post-sensor script: Modify Operational Status for MSFT SQL Instance CI Property: discovery.mssql.cluster.instance.postsensor.delete Affected function: extractSqlClusterInstanceMemberRecs Related Links<!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Microsoft SQL Server and Cluster discovery