Maintenance Windows – Planning and Checking!

Everything requires maintenance.  A home without maintenance (i.e. neglected or abandoned) may begin to deteriorate and fall apart.  A car without maintenance (i.e. oil changes, tire rotations, etc.) may not perform optimally.  Customer relationships without maintenance (or management) may grow cold.  Computers, applications, and databases without maintenance may eventually start to run slower and even increase the risk of unplanned outages.  And that is why it is a common practice in technology departments to have a maintenance window (http://en.wikipedia.org/wiki/Maintenance_window).  However, in order to be successful at handling maintenance windows, there are two crucial pieces:

  1. Have a plan beforehand
  2. Have a process to check afterwards

In this post, I am really going to focus on what may be considered DBA work during the maintenance windows.  So, I will exclude talking about things such as network and infrastructure changes, although the overall principles will apply.

Have a plan beforehand

The first crucial piece to the success of a maintenance window is to have a plan.  It absolutely amazes me how many organizations do not plan or meet before having a maintenance window.  The basic principle is that if any change is going to be made, some basic questions should be answered, coordinated, and communicated to all parties involved.

  • What work is going to be done?  In order to answer this question, there should really be a detailed description as to how the work will be handled and how long it will take.  Also, a valid backup plan should be created with an estimate on how long it will take to rollback, in case a problem arises.
  • When will the work take place?  This question really gets at the sequence of events required.  For example, is the work dependent on other work that will be taking place during the maintenance window?  Or should this work precede a certain task?
  • Why is it necessary that this work will be done?  There should always be a reason to perform work on a computer environment.  Otherwise, you could actually be contributing to the instability of your environment by introducing changes, when they are not necessary.

What are some of the possible types of DBA work that may need to be scheduled for a maintenance window (and remember this is not intended to be an exhaustive list)?

  • Hardware and operating system upgrades and patches
  • SQL Server version upgrades (http://sqlserverbuilds.blogspot.com/)
  • Changes to accounts for SQL Server services
  • Application upgrades and/or configuration changes that impact users
  • Or any other work that may cause downtime

Have a process to check afterwards

The second crucial piece to the success of a maintenance window comes after the work is performed, which is checking that everything still works.  Again, it amazes me that some organizations don’t have processes, manually or automated, to check that their systems work.  For example, many places use automated software to patch their systems, but they don’t check the state of their systems afterwards.  So, did the server really patch itself and reboot?

Here are some basic ideas on how to check your SQL Server after a maintenance window using PowerShell:

  • Find the last time that the server rebooted

[System.Management.ManagementDateTimeconverter]::ToDateTime((Get-WmiObject -Class Win32_OperatingSystem -ComputerName "ComputerName").LastBootUpTime)

$logdate = [Datetime]::Now.AddDays(-1)
Get-EventLog -ComputerName "ComputerName" -LogName "Application" -EntryType "Error" -After $logdate

  • Check the SQL Server logs for errors using Get-SqlErrorLog from SQLPSX (SQL Server PowerShell Extensionshttp://sqlpsx.codeplex.com/)

Import-Module sqlpsx
Get-SqlErrorLog -sqlserver "ComputerName"

  • Verify that the server is responding to ping requests

$ping = New-Object System.Net.NetworkInformation.Ping
$ping.send("ComputerName",10000)

  • Verify that the default and named instances of the SQL Server and SQL Server Agent services are running

Get-WmiObject win32_service -ComputerName "ComputerName" |
Select systemname, name, state |
Where {($_.name -like "MSSQLSERVER" -or $_.name -like "MSSQL$*" -or $_.name -like "SQLSERVERAGENT" -or $_.name -like "SQLAgent$*")}

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "ComputerName"
$properties = $srv.Properties
$properties.Item("ComputerNamePhysicalNetBIOS").Value
$properties.Item("IsClustered").Value

As you can see, these are just a few ideas of how to check your environment after a maintenance window using PowerShell. Of course, I would assume that you would incorporate these code samples in some automated process that dynamically looks at all your SQL Servers, rather than running this manually for individual servers. Also, I intentionally used samples that showcased a variety of ways that PowerShell can be written (i.e. using providers, extensions, etc.).

Hopefully, this post has shown some of the pieces that are crucial to having a successful maintenance window. If you plan beforehand and have automated checking afterwards (possibly via PowerShell), then you can make maintenance windows much easier.

Advertisements
This entry was posted in General, PowerShell and tagged , . Bookmark the permalink.

One Response to Maintenance Windows – Planning and Checking!

  1. Pingback: DBA Basics – A Review for the New Year | SQL Bad Boy – Michael Lowery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s