Windows Internal Database Ponderings

What is it? Where does it reside? How do we get inside?

A quick blurb since these questions do come up occasionally.

***  Warning this may only be interesting to the inquisitive -SQL minded IT folks roaming the face of this planet  🙂  ***

Then again in case you are responsible for a i.e. WSUS server you may find this interesting as well.

For starters – the database resides – drum roll please in

  • %windir%\wid

meaning we can go and have a look at what we can find there.

  • WID\Binn
    • \Data
    • \Log

starts to look very familiar to a SQL DBA and sure enough it is.

Browsing the \Data folder you’ll see master, model, temp, msdb data and log files and in case you chose to configure WSUS to use the Windows Internal Database (WID) you’ll find SUSDB.mdf and SUSDB_log.ldf there as well.

Like SQL server we need to get permission to look into the \Log folder to find the currently in use error log. Grab a copy and examine it.

Interestingly enough Microsoft chose to stay with SQL 2014 as platform for the internal database on a Windows 2016 Server. (see below)


Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Windows Internal Database (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)


Next interesting aspect is the service account ‘NT SERVICE\MSSQL$MICROSOFT##WID’

(see below) indicating the hidden aspect of this SQL Server ($)


Logging SQL Server messages in file ‘C:\Windows\WID\Log\error.log’.
The service account is ‘NT SERVICE\MSSQL$MICROSOFT##WID’


Interesting parameters in addition to the standard -d,-l and -e. (see below)

A quick look over at Trace Flags (Transact-SQL) doesn’t shed any light on

-K, -T1617 or -W65535  all three are possibly aspects to limit WID resource demands.


Registry startup parameters:
-d C:\Windows\WID\Data\master.mdf
-l C:\Windows\WID\Data\mastlog.ldf
-e C:\Windows\WID\Log\error.log
-K
  -T 1617
  -w 65535


Followed by 2 more interesting Command Line Startup Parameters (see below)


Server      Command Line Startup Parameters:
-S “MSWIN8.SQLWID”
-s “MICROSOFT##WID”      instead of  -s “MSSQLSERVER” for a default SQL server


Next interesting aspect

The password regeneration attempt for SA was successful.


Telling us the server name in the form of machine name\SQL Server name

Server name is ‘LAB-DC\MICROSOFT##WID’.


Informing us where we could connect to

Server local connection provider is ready to accept connection on [ \\.\pipe\MICROSOFT##WID\tsql\query ].


Until we finally see below – SQL giving us the all important [Recovery is complete] meaning its ready for work – before starting  SUSDB for the WSUS server running on this server

Recovery is complete. This is an informational message only. No user action is required. Starting up database ‘SUSDB’.



Successfully connected to the WID database running our WSUS server  using SSMS 16.4.1

Important:  Your login may have to be added to the WSUS Administrators role before you can connect. In addition you may have to start SQL Server Management Studio by right clicking – Run as administrator

 

Wid for WordPress updated

Be aware if you choose to use WID as the backend for your WSUS installation the BPA (Best Practice Analyzer) for BPA will understandably raise a warning in the Performance Category like this

Source     : YourSystemName
Severity   : Warning
Category   : Performance
Title      : WSUS database should be installed on a non-system drive
Problem    : WSUS database might not be installed or it may be installed on the system drive.
Impact     : Installing WSUS database in the system drive can lead to corruption of the operating system and denial of service for any other service if the system drive runs out of disk space.
Resolution : If WSUS database is installed, move the WSUS database (SUSDB) to a non-system drive.
Compliance :
Help       : http://go.microsoft.com/fwlink/?LinkId=230524

To test this you can use PowerShell (see below)

Clear-Host

Invoke-BpaModel -ModelId Microsoft/Windows/UpdateServices

Get-BpaResult -ModelId Microsoft/Windows/UpdateServices | Select Source, Severity, Category, Title, Problem, Impact, Resolution, Compliance, Help | Where {$_.Severity -NotMatch “Information”} | Format-List

Advertisements

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