Windows Update – Patching SQL Server 2005 Express

Background

This is the second in a series of posts in which I will try to journal some of the steps we took to address a busy hard drive.

In the previous post we spoke about how we configured svchost.exe to run on separate processes and thus we are better positioned to identify which specific services are driving I/O.

Inclusive in the services identified are Windows Management and Windows Updates.

 

Windows Update

Once we saw that Windows Update was one of the main drivers we tried to see which updates was causing the problem.

To launch the Automatic Updates we clicked on the icon on the right bottom panel.

 

Microsoft SQL Server 2005 Express Edition Service Pack 4 ( KB2463322 ) AND Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 ( KB2463322 )

Screen Shot

Outline

The screens that we encountered are:

  1. How do you want to install updates
  2. Choose Updates to install
  3. The updates are being installed
  4. Some updates could not be installed

How do you want to install the updates

Image
Initial Screen

HowDoYouWantToInstallUpdates_20170319_0230AM

 

Post Selection Screen

HowDoYouWantToInstallUpdates_20170319_0231AM

Textual

We chose “Custom Install (Advanced )” to gain better insight as to what is being installed are have more control as to which ones we can opt out of.

Choose Updates to install

Image

ChooseUpdatesToInstall_20170318_0232AM

 

Textual

Here are the updates

  1. Microsoft SQL Server 2005 Express Edition Service Pack 4 (KB2463332)
  2. Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 (KB2463332)

 

The updates are being installed

Image

TheUpdatesAreBeingInstalled_20170319_0235AM

 

Textual

Here are the first of two updates are being applied

 

The updates are being installed ( Post First Patch )

Image

TheUpdatesAreBeingInstalled_20170319_0237AM

 

Textual

Unfortunately, the first patch failed.

And, the second patch is now being applied.

 

Some updates could not be installed.

Image

SomeUpdatesCouldNotBeInstalled_20170319_0238AM

 

Textual

Here both updates failed.

 

TroubleShooting

The troubleshooting steps available to us are the following:

  1. Check Event Viewer
  2. Check Windows Update log files
    • c:\windows\WindowsUpdate.log

 

c:\windows\WindowsUpdate.log

Here are some sample entries from c:\windows\windowsUpdate.log

Entries – 2017-03-19 3:54

 

Follow Up

Opted Out

Opted out SP4

ScreenShot

Choose updates to install
Image

ChooseUpdatesToInstall

 

Textual

Unchecked both packages.

 

Hide Updates
Image

DontNotofyNeAboutTheseUpdatesAgain

 

Textual

Here we are opting out of this release not just for now, but for always.

 

Summary

A number of years ago all the cool kids wore a tee shirt that read “Got tired of patch Tuesday, went Googling”.

It is not that bad for us, we will just go the manual install path.

svchost – High IO on MS Windows 2003

 

What is svchost.exe?

Wikipedia
Link

svchost.exe (Service Host, or SvcHost) is a system process that hosts multiple Windows services in the Windows NT family of operating systems.
Svchost is essential in the implementation of so-called shared service processes, where a number of services can share a process in order to reduce resource consumption

 

Issue

Hard-drive stays busy.

Indicator

Task Manager

Image

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

SysInternals

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

TroubleShooting

Process Management

Tasklist

List all services running under svchost.exe

Tasklist – List all processes running under svchost.exe

Script

tasklist /svc /fi "imagename eq svchost.exe"

Output

Explanation
  1. We are focused on PID = 920

Process Explorer

Tasklist – Dig deeper into process svchost.exe = 920

Services

Own Process

In a nice Server Fault QA post, Peter Mortensen suggested that one could separate out the services into their own process and thus gain clearer understanding of each service resource uptake.

To do one will have to change the service configuration.

Here is the specific QA:

How to find memory usage of individual Windows services?
Link

Run as distinct Process

Syntax

SC Config Servicename Type= own

Run as shared Process

Syntax

SC Config Servicename Type= share;

Run as distinct Process

Sample Code

rem  1. "Automatic Updates"
SC Config wuauserv Type= own

rem  2. "COM+ Event System"
SC Config EventSystem Type= own

rem  3. "Computer Browser"
SC Config Browser Type= own

rem  4. "Cryptographic Services"
SC Config CryptSvc Type= own

rem  5. "Distributed Link Tracking"
SC Config TrkWks Type= own

rem  6. "Help and Support"
SC Config helpsvc Type= own

rem  7. "Logical Disk Manager"
SC Config dmserver Type= own

rem  8. "Network Connections"
SC Config Netman Type= own

rem  9. "Network Location Awareness"
SC Config NLA Type= own

rem 10. "Remote Access Connection Manager"
SC Config RasMan Type= own

rem 11. "Secondary Logon"
SC Config seclogon Type= own

rem 12. "Server"
SC Config lanmanserver Type= own

rem 13. "Shell Hardware Detection"
SC Config ShellHWDetection Type= own

rem 14. "System Event Notification"
SC Config SENS Type= own

rem 15. "System Restore Service"
SC Config srservice Type= own

rem 16. "Task Scheduler"
SC Config Schedule Type= own

rem 17. "Telephony"
SC Config TapiSrv Type= own

rem 18. "Terminal Services"
SC Config TermService Type= own

rem 19. "Themes"
SC Config Themes Type= own

rem 20. "Windows Audio"
SC Config AudioSrv Type= own

rem 21. "Windows Firewall/Internet Connection Sharing (ICS)"
SC Config SharedAccess Type= own

rem 22. "Windows Management Instrumentation"
SC Config winmgmt Type= own

rem 23. "Wireless Configuration"
SC Config WZCSVC Type= own

rem 24. "Workstation"
SC Config lanmanworkstation Type= own

rem End.

 

Remediation

Once we ran the code to start all the aforementioned svchost.exe services in their own process space, restarted the machine.

SysInternals – Process Explorer

Took the SysInternal’s Process Explorer, arranged based on IO, and noticed that WMI is the culprit.

Images

svchost.exe – Services

Here are the services that are using our cited svchost.exe process.

Services

Took to Control Panel, services applet to stop that service and see if it things slow down.

Dependent Services

Reviewed Dependent Services

And, I really will rather than not stop the local system firewall service.  And, start to wonder why so busy anyways.

But, all that will wait another post as it is Saturday and I have errands to run.

Dedicated

Dedicated to Peter as in Mortensen.

 

References

  1. How to find memory usage of individual Windows services?
    Link
  2. How do I discover which process is making my hard drive go crazy? (need disk io equivalent of task manager’s cpu % column)
    Link
  3. YongRhee ( MSFT )
    • How to troubleshoot Service Host (svchost.exe) related problems?
      Link

SQL Server – Integration Services – Network Flow and Rules

Background

Wanted to cover the Network Ports that are used by Microsoft’s Integration Services.

Network Trace

Wireshark

Port Mapper ( Port 135 )

Network Flow

rpc-20160106-1111pm-cleanedup

 

Explanation

  1. From Ephemeral Port ( 57916) connect to Server Port 135
  2. Request from client to server to issue RemoteCreateInstance
  3. Authenticate User
    • via NTLMSSP_AUTH
    • Pass in username

 

Integration Services

Network Flow

ssis-20170106-1122pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57917) connect to SQL Server Integration Services Component
  2. This is important has it depends on how thru Component Services the Integration Services Component’s endpoint is configured

 

Component – Microsoft SQL Server Integration Services [NN.MM]

Using Component Services, let us review the Component’s endpoint configuration

 

Configuration

Here are our choices:

  1. Disable Protocol sequence
  2. Use default endpoints
  3. Use static endpoint
  4. Use intranet range of dynamic endpoints
  5. Use internet range of dynamic endpoints

 

Digging Deeper
  1. Disable Protocol sequence
    • Disable Network
  2. Use default endpoints
    • Use ephemeral ports
  3. Use static endpoint
    • Use static endpoint
  4. Use intranet range of dynamic endpoints
    • Use endpoint’s defined for Intranet
  5. Use internet range of dynamic endpoints
    • Use endpoint’s defined for Internet

 

Our Choice

To streamline our conversation with the Firewall team, we chose to use a static endpoint

dcomendpointconfigurations-usestaticport

 

 

NetLogonSAMAccount

Network Flow

rpcnetlogon_20170107_1206am-brushed-up

 

Explanation

This area covers the Network Authentication.

We did not have to make special care in our environment and so I can not cover in details.

But, please keep it mind when connecting between hosts that are not in the same Active Directory Domain, etc.

 

Component – Windows Management & Instrumentation ( WMI )

Network Flow

wmi-20170106-1144pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57919) we connect to the port we dedicated to WMI
  2. This is important has it depends on how thru Component Services the WMI Component’s endpoint is configured
Configuration

Using Component Services, we will configure Windows Management and Instrumentation to listen on a specific port

wmi-configure-staticport

 

Network Listening Ports

Resource Monitor

On newer MS Windows Oses, you will be well served to remote connect to the Integration Services host, and run Resource Monitor

MsDtsSrvr.exe

msdtssrvr-20170107-0617am

Explanation

We can see that MsDtsSrvr.exe is:

  1. listening on Network Port 50000
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

RPCC – svchost (winmgmt)

svchost-rpcss-20170107-0640am

 

Explanation

We can see that svchost.exe ( RPCSS ) is:

  1. listening on Network Port 135
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, RPCSS is being processed by a svchost.exe process.

 

Windows Management & Instrumentation – svchost (winmgmt)

 

svchost-exe-winmgmt-20170107-0630am

 

Explanation

We can see that svchost.exe ( winmgmt ) is:

  1. listening on Network Port 50090
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, winmgmt is being processed by a svchost.exe process.

 

Tabulated View

Objective Filter
 RPC Port Mapper Port 135
 Microsoft Integration Services Ephemeral Ports
Static Port
Intranet range of dynamic endpoints
Internet range of dynamic endpoints
 Network Authentication Ephemeral Ports
 Windows Management and Instrumentation ( WMI ) Ephemeral Ports
Static Ports
Intranet range of dynamic endpoints
Internet range of dynamic endpoints