Does Foreman speak SQL? It does ;)

So, the question is – how to deploy and maintain farm of Microsoft SQL Servers? With different domains, install sources, roles, features. Should we create unattended installers for every single instance?

No, we shouldn’t.

We should use Powershell DSC – https://github.com/PowerShell/xSQLServer
With Foreman/Puppet and win_manage, we have something like:

The simplest way:

instance1:
  dsc_instancename: MSSQLSERVER
  dsc_sourcepath: "\\our.server.com\ourshare"
  dsc_sourcecredential:
    user: anonymous
    password: anonymous
  dsc_setupcredential:
    user: DOMAIN\someuser
    password: somepassword

Or, more sophisticated:

instance1:
  dsc_instancename: MSSQLSERVER
  dsc_sourcepath: "\\our.server.com\ourshare"
  dsc_sourcecredential:
    user: anonymous
    password: anonymous
  dsc_setupcredential:
    user: DOMAIN\someuser
    password: somepassword
  dsc_features: SQLENGINE
  dsc_forcereboot: true
  dsc_agtsvcaccount:
    user: DOMAIN\scvaccount
    password: somepassword
  dsc_sqlsvcaccount:
    user: DOMAIN\sqlaccount
    password: somepassword
  dsc_sqlcollation: SQL_Latin1_General_CP1_CI_AS
  dsc_sqlsysadminaccounts:
    - DOMAIN\someadmin
    - DOMAIN\someotheradmin
  dsc_securitymode: SQL
  dsc_sapwd:
  user: sa
  password: paaaswordisverysecure
  dsc_sqluserdbdir: D:\MSSQL\Data
  dsc_sqluserdblogdir: E:\MSSQL\Data
  dsc_browsersvcstartuptype: Disabled

Whole install from local source takes aprox. 400 seconds (with other OS related settings) to finish.

Hyper-V Integration Services en masse ;)

Yes, we all „love” the way Microsoft gives us new stuff (like forced updates in Windows 10). As we usually know better what and when should be applied, let’s say it is time to upgrade our guest integration services to the latest version.
I usually  express strong conviction that HV integration services should be kept up to date, the thing most sysadmins disregards. Those tools are responsible for keeping your VMs in shape, maintain communication between them and host services, so they SHOULD be always updates. Of course, as with almost everything TEST it before applying on any important environment.
As on 2016-12-31, the latest HV Integration Services version is 6.3.9600.18398

Let’s say – we would like to upgrade all our Windows machines (those BEFORE Windows 10 and Windows 2016) . We can do it manually (not cool, not cool at all), semi-automatic (no reboot) or almost … magical way – fully automatic 😉

Let’s start we have proper source added to Chocolatey:

choco source add -n=public -s"https://www.myget.org/F/public-choco" --priority=10

• semi automatic (RDP, psexec, powershell)

  choco install hvintegrationservices -y

• fully automatic (with reboot) using The Foreman and win_manage
  

  win_manage:
      chocolatey_packages:
        hvintegrationservices:
          ensure: 6.3.9600.18398
      dsc_reboot:
        dsc_reboot:
          message: Machine requested a reboot
          when: pending

 

Change local Administrator passwords. Oh … and unlock them :) On 10k+ machines :)

Administrator:
  dsc_username: Administrator
  dsc_disabled: false
  dsc_password:
    user: NT AUTHORITY\\SYSTEM
    password: P@$$w0rd

Raw, unedited The Foreman/Puppet + Win_manage.