Windows Internal Database & “Windows Server Update Services” ( WSUS ) DB

 

Background

As part of a SQL Server Inventory exercise we have been working on since Summer 2017, noticed 3 machines running “Windows Internal Database” version 2005.

 

Patch Report

Here is a snapshot of GM’s report…

inventory_20171110_0630AM

 

Examination

Let us remote desktop to the SCCM machine and see whether GM’s fingerprinting bears true.

 

Windows Version

We ran winver to deduce the version of Windows

Command

 
winver
 

Output

winver_20171110_0508AM

 

Explanation

We are running Windows Server 2008/R2 with Service Pack 1 ( SP1 )

 

File System

Windows Internal Database are packaged as part of the main application’s installer and very little customization is allowed to the WID sub-component.

Therefore we can learn a lot based on the OS Folder where it is installed.

 

Control Panel – File Options

Let us access Control Panel \ File Options and make sure that it is configured for Administrative needs.

 

Images

Images – Before

controlPanel_folderOptions_20171110_0458PM

 

Images – After

controlPanel_folderOptions_20171110_0459PM

 

Explanation

  1. Uncheck
    • Hide extensions for known file types
    • Hide protected operating system files
  2. Checked
    • Show hidden files, folders, and drives

File System

Application Targeted Folder

Folder Win OS Application
C:\Windows\sysmsi\ssee\ Windows Server 2008 – x64 bit Windows SharePoint Services 3.0

Windows Server Update Services 3.0

 C:\Windows\WID  Windows Server 2012 – x64 bit Windows Server Update Services v 2012

Folders

Folder – C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL

SQL Server

SQL Server – Error Log

SQL Server – Error Log – File Structure

SQL Server – Error Log – File Structure – Files

SQL Server – Error Log – File Structure – File – ErrorLog

SQL Server – Error Log – File Structure – File – ErrorLog – Contents

Reviewing SQL Server’s error log gets us quite a bit of information and so let take the opportunity to take a quick cursory look.

Content

2017-10-29 21:35:33.28 Server Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Windows Internal Database (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

2017-10-29 21:35:33.28 Server (c) 2005 Microsoft Corporation.
2017-10-29 21:35:33.28 Server Authentication mode is WINDOWS-ONLY.
2017-10-29 21:35:33.28 Server Logging SQL Server messages in file 'C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG'.
2017-10-29 21:35:33.28 Server Registry startup parameters:
2017-10-29 21:35:33.28 Server -d C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\master.mdf
2017-10-29 21:35:33.28 Server -e C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG
2017-10-29 21:35:33.28 Server -l C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\mastlog.ldf
2017-10-29 21:35:34.76 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query ].
2017-10-29 21:35:34.78 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2017-10-29 21:35:34.76 spid5s Server name is 'SCCM\MICROSOFT##SSEE'. This is an informational message only. No user action is required.
2017-10-29 21:35:34.79 spid5s Starting up database 'msdb'.
2017-10-29 21:35:34.79 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-10-29 21:35:42.03 spid51 Starting up database 'SUSDB'.
2017-10-29 21:35:42.28 spid51 Recovery is writing a checkpoint in database 'SUSDB' (5). This is an informational message only. No user action is required.
Interpretation
  1. Microsoft SQL Server 2005 – 9.00.5000.00 (X64)
    • Microsoft SQL Server 2005
      • Version is 2005
    • 9.00.5000.00
      • Service Pack 4 ( SP4 )
    • X64
      • 64-bit
  2. Windows Internal Database (64-bit) on Windows NT 6.1 ( Build 7601: Service Pack 1)
    • Version is Windows Internal Database (64-bit)
    • Windows NT 6.1
      • OS  ( Windows Version – Wikipedia – Link )
        • Windows 7
        • Windows Server 2008 R2
        • Windows Home Server 2011
    • Build 7601 : Service Pack 1
      • SP1

SQL Server – SQL Server Configuration Manager

Launch sql server configuration manager to review “SQL Server Services

Image

SQL Server Configuration Manager

SQL Server Configuration Manager – Properties – Service
Image

Explanation

  1. Binary Path
    • C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Binn\sqlservr.exe -sMICROSOFT##SSEE
      • Instance’s name is MICROSOFT##SSEE
  2. Name
    • Windows Internal Database (MICROSOFT##SSEE)

 

Installed Programs

Control Panel – Add or Remove Programs

Image

Explanation

  1. Applications Installed
    • Name :- Windows Server Update Services 3.0 SP2
    • Date :- 7/29/2015
    • Version :- 3.2.7600.226

 

SQL Server – SQLCMD

Let us quickly use sqlcmd to get metadata

Connect to SQL Server Instance using sqlcmd

Command

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

Output

sqlcmd – Version using @@version

SQL

select [@@version] = @@version
go

Output

Explanation
  1. Version is MS SQL Server v2005 – 9.00.5000 ( x64 )
    • Version :- MS SQL Server v2005
    • Version # :- 9.00.5000
      • SP4
    • Bitness
      • x64

sqlcmd – list databases using sys.databases

SQL

select name, create_date from sys.databases
go

Output

Explanation
  1. Databases
    • System
      • master, tempdb, model, msdb
    • User / Application
      • susdb
        • susdb created on 2015-July-29th

Summary

Confirmed that GM’s patch report is indeed accurate.

We have a few Windows Internal databases on our network.

Will complete the steps of aligning each of them to specific Products, Vendors, and in-house support engineers.

 

References

  1. Microsoft
    • Microsoft Developer
      • VedMS
        • Following is the steps to connect to these Windows internal database on Windows 2012 machine. This to access the WAP configuration from the ADFS configuration database.
          Published On :- 2014-August-19th
          Link
  2. Edgewood Solutions LLC ( mssqltips.com )
    • Edwin Sarmiento
      • Administering your Windows Internal Database MICROSOFT##SSEE instance
        Link
  3. System Specialist .NET
    • Move or Delete a WSUS 4 Windows Internal Database (WID) on Windows Server 2012
      Link

DataStore.edb

Background

An alarm was raised by our monitoring software.

An alarm is raised whenever a drive free space falls below 10%.

Combed the drive using SpaceSniffer and found out that the DataStore.edb file on C:\Windows\SoftwareDistribution\DataStore is larger than usual.

 

Image

Here it is clocking in at 1.3 GB

 

TroubleShooting

SysInternals

Process Monitor

Overview

Let us see if we can use SysInternal’s Process Monitor and determine which processes are accessing the DataStore.edb file.

Filter

Clause
  1. Path
    • Begins with C:\Windows\SoftwareDistribution\DataStore
Image

 

Capture

Image
 
Event Properties
Event Properties – Create File – Event

Event Properties – Create File – Event – Property
  1. Desired Access :- Read Attributes, Synchronize
  2. ShareMode :- Read, Write
Event Properties – Create File – Event

Image

Details

  1. Path :- C:\Windows\System32\svchost.exe
  2. Command Line :- C:\Windows\System32\svchost.exe -k netsvcs
  3. User :- NT AUTHORITY\SYSTEM

 

Services

Knowing that svchost.exe is a host for many services, which one is netsvcs?

Services Applet

Image

Explanation

We see it is the “Windows Update” service.

 

Process Explorer

Overview

Which program has datastore.edb opened?

Process Explorer Search

Menu Find

Using the menu item”Find Handle or DLL…”, sought for datastore.edb

Handle or DLL substring

 

Process Explorer Results

Here is the result from searching for DataStore.edb

What process is is using the marked PID

Our marked PID is 1012

WIthin Process Explorer ordered by Process ID, PID, and looked for our identified process ID, 1012.

What process is is using the marked PID

Right clicked on that Process and from the drop down menu chose the Properties item.

Here are the services that are using that running within the identified process.

 

Summary

Though DataStore.edb is principally used by the Windows Update Service, because svchost.exe is a shared process, it is going to take more than stopping Windows Update Service to prune / clean out the DataStore.edb file.

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.