Automating Windows and SQL Updates with Powershell (T-SQL Tuesday #130 – Automate Your Stress Away)

Elizabeth Noble (b|t) hosts this month series of blog posts with the title “Automate Your Stress Away”.
In case you are new to T-SQL Tuesday this is the monthly blog party started by Adam Machanic (b|t) and now hosted by Steve Jones (b|t). 

You can read more about the invite by clicking on the T-SQL Tuesday logo.

I’ll write about my struggle with the Windows and SQL Updates on my SQL Server.

We use WSUS as part of our deploying strategy for Updates, because we need control about what Updates are going to be shipped to our servers. So we ship Updates to some chosen Server before we ship them to all.

Next important part is when to install updates. As most of you know, during installation of SQL Server Updates, also with other Windows Updates, affected services will be restarted. Because of that, installation is not possible during working hours.

With Windows Server 2016 the update times can be adjusted, but not the day.

So we decided to manage all by ourselves.
We created a job that runs on two of the sundays of the month at 2 o’clock in the morning. During this job, the updates were fetched from the WSUS server, installed and the machine will be rebooted afterwards, if neccessary.
We started with one time per month but from time to time Updates didn’t get installed and it needs a second try. So we changed to two times a month.

This is the PowerShell Script we use. As I’m not an expert any help or hint for doing better is really appreciated.


# This is the modul we use
# Install-Module PSWindowsUpdate -force

# Here we are logging what Updates got installed.
get-date -Format u >> c:\WUInstall.log

Get-WUInstall -Install -AcceptAll -verbose >> c:\WUInstall.log

# most of the time when Updates are installed, a restart is neccessary;
# when restart is needed send Email and restart.

$reboot=Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending"
if ($reboot -eq $true)
{

   $subject='Windows Updates on server: <servername>'
   $sendFrom=<Mail From>
   $sendTo=<Mail to>
   $smtpServer=<SMTP Server>
   
   Send-MailMessage -From $sendFrom -To $sendTo -Subject  $subject -SmtpServer $smtpServer -Attachments 'c:\WUInstall.log'

    Restart-Computer -Force
}

Then we use the task planner of windows to start the script at the time described above.

One Problem we are struggling with in the moment is that the task planner also starts the update on saturdays at the same weekends we want it to run on sundays. We have no idea what’s going on there.

Thanks for reading,
Volker

Credits for some parts of the script go out to Eelco Drost and his article https://eelcodrost.com/2019/10/16/patching-sql-server-ag-using-sccm-and-powershell/
Thanks for sharing!

TSQL2sday #94 – SQL Server Best Practices Test with PowerShell dbatools #tsql2sday

T-SQL Tuesday #94 – Lets get all Posh!

Rob Sewell aka sqldbawithabeard (b|t) hosts this month T-SQL Tuesday and surprisingly his subject is PowerShell.

I will describe what I’ll use for testing against Best Practices Commands for SQL Server.

I got in contact with PowerShell some years ago, but was not satisfied with what needs to be done before maintaining SQL Server.

Meanwhile Microsoft has done a lot more and with the contribution from several PowerShell Experts and MVPs as Chrissy LeMaire, Claudio Silva, Rob Sewell, Constantine Kokkinos and many more, there is a module created that helps to maintain SQL Server 2005+. This is called dbatools and the website can be reached at https://dbatools.io. The Project is hosted on Github and using the commands is totally free of charge!

The community has grown to over 50 contributors and over 200 SQL Server best practices, administration and migration commands. An Overview of the commands can be found here: https://dbatools.io/functions/

Now I will describe some of the dbatools commands to Test our SQL Server against Best Practices mentioned by SQL Server Experts. You can find all of the information and Links in the description to the commands.

  • Max Memory
    • This tests the actual max memory setting against the recommended setting.
    • Test-DbaMaxMemory -SqlServer sqlserver01
      # command to set the max memory to the recommended
      Set-DbaMaxMemory -SqlServer sqlserver01
      # or to a fix value of 2 GB
      Set-DbaMaxMemory -SqlInstance sqlserver01 -MaxMb 2048
  • TempDB
    • with SQL Server 2016 you get the option to configure the TempDB Configuration during installation, with this commands you can control and fix it.
    • Test-DbaTempDbConfiguration
    • Evaluates tempdb against a set of rules to match best practices.
      The rules are:
      – Is Trace Flag 1118 enabled (See KB328551).
      – File Count: Does the count of data files in tempdb match the number of logical cores, up to 8.
      – File Growth: Are any files set to have percentage growth, as best practice is all files have an explicit growth value.
      – File Location: Is tempdb located on the C:\? Best practice says to locate it elsewhere.
      – File MaxSize Set(optional): Do any files have a max size value? Max size could cause tempdb problems if it isn’t allowed to grow.
    • Test-DbaTempDbConfiguration(Screenshot from dbatools.io – my configurations are all fine 🙂 )
    • The right configuration can be set by using the corresponding Set- command
  • Disk-Configuration
    • Test-DbaDiskAlignment
      • This command verifies that your non-dynamic disks are aligned according to physical requirements.
      • Test-DbaDiskAlignment -ComputerName sqlserver01| Format-Table
      • Test-DbaDiskAlignment
    • Test-DbaDiskAllocation
      • Checks all disks on a computer to see if they are formatted to 64k, the best practice for SQL Server disks
      • Test-DbaDiskAllocation -ComputerName sqlserver01 | Format-Table
      • Test-DbaDiskAllocation
  • PowerPlan
    • Test-DbaPowerPlan
      • The Servers Powerplan should be set to High Performance on every SQL Server
      • Test-DbaPowerPlan -ComputerName sqlserver01
      • Test-DbaPowerPlan

 

  • SPN
    • We use DNS CNAMEs for referring to our SQL Server (2). We need to adjust the SPN Settings manually. That is easy with these commands:
      Get-DbaSpn / Set-DbaSPN
  • SQL Server Name
    • We created a Single VM template where all SQL Server are created from. With CPU, Memory and Disk Layout.
    • After creating a new VM out of the template the Server Name changes but not the SQL Server Name internally. Help comes again with dbatools command: Repair-DbaServerName
      Works fine for me!

Theses are the commands we use often in our environment, there are many more to choose from on the dbatools website.

Thanks to Rob for hosting this month T-SQL Tuesday and thanks to all other attendees to convince me writing a blog post. Thanks also to Adam Bertram who chooses me writing for #PSBlogWeek. You all can attend until this friday (2017-09-15). http://psblogweek.com/

Thanks for Reading,
Volker

Base Monitoring

TSQLTuesdayT-SQL Tuesday #66: Base Monitoring

Cathrine Wilhemsen (b|t) is hosting the T-SQL Tuesday blog party of this month.

I will describe what’s my understanding of Base Monitoring for SQL Server.

Last Thursday 5/7/2015 I presented these thoughts at a PASS regional Chapter in Cologne/Germany.

“My” Base Monitoring consists of monitoring all Events that come up from too few hard disk space, memory or CPU. It is also about monitoring of locking, blocking or deadlocks, long running queries/jobs and failure in logins.
I will also do additional tasks that didn’t belong to the monitoring itself but help in failure check and reorganization of indexes and so on (…)

All this was arranged and written down after an installation of a new SQL Server.
These are the steps I did:

1. create a DBA Database for several scripts and tables (see below)
2. install the main script from dbWarden for Monitoring
3. install Integrity check and Maintenance scripts from Ola Halengren into DBA Database
4. create GetErrorLogEvent Stored Procedures and Job for Mails out of the SQL Errorlog into DBA Database
5. create Jobs for recycling ErrorLogs
6. Performance Monitoring, Event Logs with an external Tool called Host Monitor

The dbWarden Script (2.) is a great ressource for real time monitoring blocking, deadlocks, long running querys, jobs and so on – best of all: it is free of charge (!). It also brings a detailed and configurable health report of the server that will be sent every morning.

The scripts from Ola Halengren (3) are well known in the SQL Server Community for bringing us perfect maintenance tasks for index and statistic updates with rebuild online/offline or reorganize where it is neccessary.
Additionally there is a script for integrity check – and backup of course. 🙂

The GetErrorLogEvent Script (4.) was originally written by Jonathan Kehayias and published on an MSDN Archive site. I didn’t found the site any more on MSDN so the script is attached together with the presentation (Sorry, only german these days) below.

For recycling the Error Log (5.) it is only neccessary to create a job with the two commands (exec dbo.sp_cycle_errorlog and exec dbo.sp_cycle_agent_errorlog).

The only tool that is not free of charge is the last one, called Host Monitor.
It is perfect for more monitoring especially for long term monitoring.

Ressources

dbWarden
Description: http://www.sqlservercentral.com/articles/Monitoring/98106/
Download, Wiki: http://sourceforge.net/projects/dbwarden/

Ola Halengren
https://ola.hallengren.com/

Hostmonitor (€)
http://www.ks-soft.net

GetErrorLogEvents and Basis Monitoring Presentation from the PASS Chapter in Cologne (German)
BaseMonitoring_20150507

Thanks for reading,
Volker