CMCB Staggered ADR question

Question

Hello,

We have developed a PowerShell-based automated SQL 2017 cluster patching solution. The solution remotely triggers the 'Available' updates on the remote CMCB Agent through CCM WMI SDK. The updates are going to be deployed through ADR with the SQL CU KBs and applicable OS updates advertised as 'Available' as it helps us sequence the cluster nodes patching order to fit in with the SQL Availability Group failover requirement.

Here comes the challenge - We have 3 environments - DEV, TEST and Prod.

For DEV, we create an ADR which downloads the SQL CU and OS updates immediately after the Patch Tuesday and it deploys all the applicable updates as 'Required' so they get installed as part of DEV server maintenance window. No problem here.

For TEST environment however, we would like the monthly ADR to download only those SQL CUs that were released the previous month (i.e. 30 days ago) and not the current month's SQL CU (this is to await DEV SQL CU testing results). Just wanted know what is the easiest way to do it. The ADR deployment will be targeted to all the TEST servers with 'Available' deployment so our above PowerShell orchestration solution will trigger the installation from our remote Scheduler server as per the Change Schedule in the right sequence. (Please note in this case the OS updates are not a problem as we will have a separate OS ADR which should download monthly updates - no 30-days postponement in this case unlike SQL CU)

The Prod environment is exactly like the TEST above except that the SQL CUs should be more than 60 days old.

Are you able to help me with the setting up of ADR and its associated deployment? Is this something that needs to be set up on the ADR or should we deal with it as part of deployment (playing with the deadline etc.)? Like I mentioned above, we use 'Available' deployment for TEST and Prod, so we take control of the update installation-triggering through our custom PowerShell solution, so there will be no CMCB-deployment defined deadline as such.

Thanks, Steve

Answer 1

Hi, @Steve D' Souza
Thank you for posting in Microsoft Q&A forum.
It seems ADR cannot add a Search Criteria to filter the updates 30 days ago.
However, when we filter the update in All Software Updates from SCCM console, we can add a criteria to filter the updates one month ago:

Maybe we can only filter the SQL CUs from All Software Updates and create and deploy the SUG manually.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.