SCCM, SQL, DBATools, and Coffee

Warning:  This article is predicated on (A) basic reader familiarity with System Center Configuration Manager and the SQL Server aspects, and (B) nothing better to do with your time.

Caveat/Disclaimer:  As with most of my blog meanderings, I post from the hip.  I fully understand that it exposes my ignorance at times, and that can be painful at times, but adds another avenue for me to learn and grow.

marie-wilson-cooking

I don’t recall exactly when I was turned onto Ola Hallengren, or Steve Thompson, but it’s been a few years, at least.  The same could be said for Kent Agerlund, Johan Arwidmark, Mike Niehaus, and others.  None of whom I’ve yet to meet in person, but maybe some day.  However, that point in time is when my Stevie Wonder approach to SQL “optimization” went from poking at crocodiles with a pair of chopsticks, to saying “A-Ha!  THAT’s how it’s supposed to work!

As a small testament to this, while at Ignite 2016, I waited in line for the SQL Server guy at his booth, like an 8 year old girl at a Justin Bieber autograph signing, just to get a chance to ask a question about how to “automate SQL tasks like maintenance plans, and jobs, etc.”.  The guy looked downward in deep thought, then looked back at me and said “Have you heard of Ola Hallengren?”  I said “Yes!” and he replied, “he’s your best bet right now.

Quite a lot has changed.

For some background, I was working on a small project for a customer at that time focusing on automated build-out of an SCCM site using PowerShell and BoxStarter.  I had a cute little gist script that I could invoke from the PowerShell console on the intended target machine (virtual machine), and it would go to work:

  • Install Windows Server roles and features
  • Install ADK 10
  • Install MDT 2013
  • Install SQL Server 2014
  • Adjust SQL memory allocations (min/max)
  • Install WSUS server role and features
  • Install Configuration Manager
  • Install ConfigMgr Toolkit 2012 R2
  • and so on.

Since it was first posted, it went through about a dozen iterative “improvements” (translation: breaking it and fixing and improving and breaking and fixing, and repeat).

The very first iteration included the base build settings as well, such as naming the computer, assigning a static IPv4 address, DNS servers and gateway, join to an AD domain, etc.  But I decided to pull that part out into a separate gist script.

The main thing about this experiment that consumed the most time for me was:

  1. On-the-fly .INI construction for the SQL automated install
  2. On-the-fly .INI construction for the SCCM install
  3. On-the-fly SQL memory allocation configuration

Aside from the hard-coding of content sources (not included on this list), item 2 drove me nuts because I didn’t realize the “SA expiration” date property was required in the .INI file.  The amount of coffee I consumed in that 12 hour window would change my enamel coloring forever.  Chicks dig scars though, right?  Whatever.

Then came item 3.  I settled on the following chunk of code, which works…

$SQLMemMin = 8192
$SQLMemMax = 8192
...
write-output "info: configuring SQL server memory limits..."
write-output "info: minimum = $SQLMemMin"
write-output "info: maximum = $SQLMemMax"
try {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
  $SQLMemory = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ("(local)")
  $SQLMemory.Configuration.MinServerMemory.ConfigValue = $SQLMemMin
  $SQLMemory.Configuration.MaxServerMemory.ConfigValue = $SQLMemMax
  $SQLMemory.Configuration.Alter()
  write-output "info: SQL memory limits have been configured."
}
catch {
  write-output "error: failed to modify SQL memory limits. Continuing..."
}

But there’s a few problems, or potential problems, with this approach…

  1. It’s ugly (to me anyway)
  2. The min and max values are static
  3. If you change this to use a calculated/derived value (reading WMI values) and use the 80% allocation rule, and the VM has dynamic memory, it goes sideways.

Example:

$mem = $(Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory
$tmem = [math]::Round($mem/1024/1024,0)
...

I know that option 2 assumes a “bad practice” (dynamic memory), but it happens in the real world and I wanted to “cover all bases” with this lab experiment.  The problem that it causes is that the values returned from a WMI query can fluctuate along with the host memory allocation status, so the 80% value can be way off at times.

Regardless, forget all that blabber about static values and dynamic tragedy.  There’s a better way.  A MUCH better way.  Enter DBATools.  DBATools was the brainchild of Chrissy LeMaire, which is another name to add to any list that has Ola’s name on it. (side note: read Chrissy’s creds, pretty f-ing impressive). There are other routes to this as well, but I’ve found this one to be most user friendly for my needs. (Feel free to post better suggestions below, I welcome feedback!)

Install-Module dbatools
$sqlHost = "cm01.contoso.com"
$sqlmem = Test-DbaMaxMemory -SqlServer $sqlHost
if ($sqlmem.SqlMaxMB -gt $sqlmem.RecommendedMB) {
  Set-DbaMaxMemory -SqlServer $sqlHost -MaxMB $sqlmem.RecommendedMB
}

This is ONLY AN EXAMPLE, and contains an obvious flaw: I’m not injecting an explicit 80% derived value for the -MaxMB parameter.  However, this can be accomplished (assuming dynamic memory is not enabled) as follows…

Install-Module dbatools
$sqlHost = "cm01.contoso.com"
$sqlmem = Test-DbaMaxMemory -SqlServer $sqlHost
$totalMem = $sqlmem.TotalMB
$newMax = $totalMem * 0.8
if ($sqlmem.SqlMaxMB -ne $newMax) {
  Set-DbaMaxMemory -SqlServer $sqlHost -MaxMB $newMax
}

Here’s the code execution results from my lab…

sqlmemory2

You might have surmised that this was executed on a machine which has dynamic memory enabled, which is correct.  The Hyper-V guest VM configuration is questionable…

hyperv_setup1.png

This is one of the reasons I opted for static values in the original script.

Thoughts / Conclusions

Some possible workarounds for this mess would be trying to detect dynamic memory (from within the guest machine) which might be difficult, or insist on a declarative static memory assignment.

Another twist to all of this, and one reason I kind of shelved the whole experiment, was a conversation with other engineers regarding the use of other automation/sequencing tools like PowerShell DSC, Ansible, and Terraform.

The final takeaway of this is to try and revisit any projects/code which are still in use, to apply newer approaches when it makes sense.  If that means shorter code, improved security and performance, more capabilities, greater abstraction/generalization (for reuse), or whatever, it’s good to bring newer ideas to bear on older tools.  In this example, it was just replacing a big chunk of raw .NET reflection code with cleaner and more efficient PowerShell module code.  Backing out 10,000 feet, the entire gist could be replaced with something more efficient.

More Information

DBATools – twitterslackyoutubegithub

Ola Hallengren – web  (Ola doesn’t tweet much, yet)

My Twitter list of super awesometacular increditastical techno-uber genius folks – HERE

Back to my coffee.  I hope you enjoyed reading this!  Please post comments, thoughts, criticisms, stupid jokes, or winning lottery numbers below.  If nothing else, please rate this article using the stars above? – Thank you!

6 Things to Avoid when Building an SCCM Site System

MFfn7

These are based on actual, real, true events, which I’ve been asked to help resolve in some capacity over the past three weeks:

  1. Do not let someone create your VM using an unknown template which contains leftover remnants of a previous SCCM site installation, and dozens of unknown changes for which the site admin has no knowledge what happened.
  2. Do not let someone create your VM and join it to an AD domain under an OU with a bunch of linked GPO’s which are undocumented.
  3. Do not let your boss approve another department’s request to take ownership of your SCCM SQL Server instance without prior discussion or them being advised as to what SCCM is.
  4. Do not let another engineer start building the site before you’ve provided him/her with the design document.  Especially when it includes Intune integration and they go ahead and set Intune as it’s own MDM authority, without discussing anything with you in advance.
  5. Do not recommend an SCCM site installation to a customer after a sales person insisted it was the “perfect fit” for their 10 desktop computers, when all they wanted was to manage software updates.
  6. Do not recommend to a customer that they’re fine with allowing their Primary site server VM, running on a Hyper-V failover cluster, to fail over another node, on another cluster, on another continent.

SCCM Query: Devices by IP Gateway

stupid-people-188

While working on a project involving IP subnet reassignments and SCCM site boundaries, and factoring in the HW inventory scan cycles, with the nature of roaming vs. fixed device types, along with the phase of the moon, current stock market index, the daylight saving time offset, the menstrual cycles of our local male politicians, and my dog’s sleep patterns, I found this to be somewhat useful in tracking down stray devices.

SELECT DISTINCT [DefaultIPGateway0], COUNT(*) AS QTY
FROM [dbo].[v_GS_NETWORK_ADAPTER_CONFIGURATION]
WHERE [IPEnabled0]=1
GROUP BY DefaultIPGateway0
ORDER BY QTY DESC

Notes from the Field: Windows Server 2016

mine_detector

So far, I’ve completed quite a few in-place upgrades to System Center Configuration Manager “current branch”.  However, more recently, I’ve stepped into quite a few upgrades to SQL Server 2016 and Windows Server 2016.  Some of those included SCCM hosts, AD Domain Controllers, Hyper-V hosts, and SQL Server hosts. Here’s some quick notes:

  • Hyper-V hosts: shutdown all guests.  If you don’t have backups, make them now or use the Export feature (which can be slow as hell).
  • If you get an error trying to invoke the setup.exe from a mounted ISO, restart the host and try it again.  In fact, prior to any upgrade, shut down all applications cleanly and do a standard shutdown/restart and wait a few minutes before starting the upgrade process.
  • NIC teams have to be removed/broken prior to the upgrade from 2012 or 2012 R2.
  • SCEP clients have to be uninstalled (if installed already).  If you’re using another AV product, shut it off and uninstall it prior to the upgrade.  Then reinstall later if necessary.
  • When upgrading an AD Domain Controller or member server which has RSAT ADDS tools installed, you will need to remove the “Identity Management for UNIX components” role service prior to the upgrade.
  • I recommend transferring FSMO roles to other DC’s while each is being upgraded, but you can skip this if you have a high testosterone level and a fat bank account.
  • If you’re using a vanilla ISO to perform the upgrade, and it’s now January 2017, and you haven’t obtained a patched version, expect 40-60 minutes of updates and several restarts along the way.
  • After the upgrade is done, if you had NIC teams and either continue without them, or recreate them, some Hyper-V guests may display an error when trying to start them which indicates a lack of permissions on the network interface.  To resolve, open the settings, (or use PowerShell), change the NIC association, click Apply, then change it back and click Apply.  The guest should start fine afterwards.
  • SQL Server hosts, double check the service configurations (accounts, start modes, etc.)  Verify them after the upgrade as well.

So far, so good.

CMWT Notes / Updates

Thank you for all the kind words and responses!  I’m really surprised and humbled at the same time.  I realize that there are still some rough edges in the current build, which I’m working on and will post as soon as I can.

I really do want to thank Chris DeCarlo for helping me beat this thing with a baseball bat to see if it could still walk away.  He helped me quickly nail down some problems that needed fixing before it was ready.

desk_spill_large

I’d like to take a minute to provide some answers and responses to questions and comments I’ve received thus far…

Known Issues

  • The SQL connection error upon starting CMWT is due to a bug in the global.asa file.  There’s a line near the top that shows Const ConfigFile = “F:\CMWT\_config.txt” which should be replaced with ConfigFile = Server.MapPath(“_config.txt”)
  • The CMWT log features linked from Administration, may not show much yet.  The next build turns that feature on, which is explained below.
  • Nested AD domains will require modifying the _config.txt key assignments for LDAP paths.  For example, the default shows dc=<<DOMAIN>>,dc=<<DOMSUFFIX>>, but if your domain is something like sales.contoso.com, then you’ll need to add/insert another dc=<<DOMAIN>>, or “dc=sales,dc=contoso,dc=com”.

Upcoming Features

  • CMWT Event Logging, is intended to capture activity such as enabling or disabling AD user accounts, modifying AD user groups, adding and removing SCCM collection members, and so on.
  • Database Index Fragmentation report will be in the next build.  I also have a defrag option (manual, not scheduled), but that needs more testing.  Right now it’s kind of a shotgun blast and may not be efficient enough for real benefit.  The SQL statement I’m using is:
    “EXEC sp_MSforeachtable @command1=””print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)””;”  The downsides so far are the excessive processing time (when the overall fragmentation aspect is more than 50% above 60%, which I admit is my own fault, but not at all uncommon at customer sites I visit), and the results may not bring the aspect down to 0%, but still improves to around 15%.
  • Collection Tools – which was in a much older version, was removed.  I will be working on putting it back, hopefully soon.

Nature Eats the Stupid. Mankind Promotes Them

Idiocracy-3

Case 1

Customer wants to move entire datacenter into Azure.  All of it.  Nothing “on premises” whatsoever.  Actually, not “wants”, but “insists, emphatically”.  This includes file and print services, DHCP, DNS and WINS, and Active Directory.  When informed about limitations with regards to network infrastructure (DHCP in particular), and shown proof-of-concept for print services, etc. they ask what can be done.  The recommendation is to put some on-prem services to improve responsiveness, provide failover, etc. etc..  “But, we don’t want on-prem anything?   You need to tell us how to make this work!”

Case 2

Customer installs SCCM 2012 R2 on a physical server with undersized resources (memory, CPU, disk space, disk isolation, networking, yada yada yada) and installs SQL Server on a separate machine, where it coexists with about six (6) other instances.  Performance sucks mud through a straw.  We explain to customer the limitations on the SCCM/SQL licensing use-cases, and how it helps to collocate SCCM and SQL on the same host, AND that it can be virtualized for greater flexibility, etc.

Customer response “I know for certain that Microsoft DOES NOT virtualize SCCM!”

Case 3

Customer sets SQL Server file auto-growth to 1 MB on all databases.  Enough said.  But they also have a maintenance plan that runs a DB shrink on all of the databases on the SCCM instance as well.

Case 4

Contractor has to hire subcontractor to handle surge gap in staffing for an SCCM installation.  Customer sets up work area in a conference room.  Customer comes back a few hours later and finds subcontractor asleep with both feet on table, and two more laptops logged into other customer environments.   Subcontractor is shown the door.  However, not before having installed an entire CAS hierarchy using anti-best-practices methods which end up requiring an entire site rebuild months later. (Phil – I know you know who I’m talking about, but I can’t give any other hints)

Case 5

Customer connects a third-party “asset management and help desk” application to SCCM by using a direct SQL connection.  The app is configured to write back to the SCCM site through SQL.  Shit starts imploding on day 1.

Case 6

Customer needs to upgrade site with a remote Secondary site.  Contractor remotes in and does the upgrade.  After all is settled out, everything works fine.  Next day, customer says all indicators have turned red in the console.  Contractor remotes in and finds a ton of errors in site system logs on both ends, as well as Windows event logs on the Secondary.  Most point to domain authentication issues.

  • Contractor,  “What changed since the day before?”
  • Customer, “Nothing at all.”
  • Contractor, “Are you sure?”
  • Customer, long pause “Well, nothing besides the recovery”
  • Contractor, “Recovery?”
  • Customer, “The local tech said the CPU was running high so he reverted the VM to an earlier snapshot to fix it.”
  • Contractor, long pause, “Ummm, ok. Well. Reverted to when?”
  • Customer, “A snapshot from February.”
  • Contractor, “It’s now September.” (this was when it occurred)
  • Customer, “Is that a problem?”
  • Silence followed by a very long inhale and slow exhale “Let’s start over, shall we?” …