Nisarg Upadhyay
Add roles and features

Managing the Windows Internal Database (WID)

August 25, 2021 by

In this article, we are going to learn about the windows internal database. Microsoft windows server and their components use the Microsoft WID database to store their data. It is used to store the relational data of the following services:

  1. Active Directory Right management service
  2. Windows server update services (WSUS)
  3. Windows system resource manager

When you configure any of the services mentioned above, the WID Database will be installed automatically. To access the database, we can use SQL Server Management Studio (SSMS).

Administrating the windows internal database is important but except for backing up the database, shrinking, and moving the database, we must not play with it. If we made any changes in the schema, the data will break the entire application.

In this article, we will learn the following:

  1. Install windows internal database using Server manager
  2. Connect to the WID using SQL Server Management Studio
  3. Connect to the WID using PowerShell

For demonstration, I have created a virtual machine and installed Windows server 2016. To connect to the WID, I have installed SQL Server Management Studio on the VM.

Install WID using Server Manager

If you do not want to install WSUS or other services, you can install the WID using Add remove server roles. Let me show you how we can install it.

On windows server 2016, open Server Manager 🡪 Click on Manage 🡪Select Add Roles and Features.

Add roles and features

The” Add Roles and Features” Wizard begins. The first screen shows the details of the task that are going to be performed by the wizard. You can skip the first screen.

The next screen is Select installation type. We are installing an internal database on a single server so choose Role-based or feature-based installation.

Select installation type

On the Select destination server screen, select the server on which you want to install the internal database. I am installing an internal database on my workstation, so I have selected SQL01.Dc.Local.

Select destination server

On the Select server role screen, you can view the list of the server roles that we can install on the server. WID Database is a feature so click Next.

Select Server Roles

On the Select features screen, you can view the list of the features that can be installed on the server. We want to install windows internal server, so select Windows Internal Database and click on Next.

Select Features

On the Confirm installation select screen, you can view the list of the features or server roles that are going to be installed on the server. Click on Install.

Confirm installation

The installation process begins.

Installtion Process

Once feature installation completes, click on Close to end the wizard.

Installation completed

When the internal database is installed, the data files and log files of the internal database are created on C:\WID location. In our case, the data files and log files of WID are created on C:\WID\data directory.

View database file

The internal database is created while configuring the WSUS server. In our demo, I have also configured the WSUS server on my workstation.

Now, let us try to connect it. We are using SQL Server Management Studio to connect to the WID database.

Connect to Windows Internal Database

We can connect to the WID using the Named Piped connection. To read more about named pipe connections, you can read the SQL Server Network configuration article.

Open SQL Server Management Studio 🡪 On Connect to Server screen, specify the following named pipe connection string in the Server name textbox and click on Connect.

np:\\.\pipe\MICROSOFT##WID\tsql\query

Below is the screenshot of the Connect to Server screen.

Connect to Server

You can view the databases in the object explorer window. We have configured the WSUS; therefore, you can see the SUSDB in the object explorer.

View Databases

Once connected, run the following query to view the version of the SQL Server.

Version of SQL Server

Let us view the location of the data and log files of the SUSDB.

Query to view the databases

  • Note: As I explained, we must not change the schema of the internal database because it will break the application. To demonstrate, let us change the schema by creating a table in a SUSDB. Create a table named Test in the SUSDB by executing the following query

The table will be created successfully. After adding the table, restart the services using the following command.

Once services are restarted, connect to the SQL Server instance and open object explorer.

Database corrupted

As you can see, the database is in Recovery Pending that indicates the SUSDB database is not operational. When we open the Windows Server update services, it will throw the following error.

Update services

To resolve this issue, we must re-install the WSUS services on the server.

Connect to WID using PowerShell

Now, let us understand how we can connect to the Windows Internal Database using PowerShell. We are using Invoke-SQLCmd command to connect and populate the list of the databases.

Powershell command to view databases

Summary

In this article, we learned about the windows internal database and its purpose. I have covered the following topics:

  1. Install windows internal database using Server manager
  2. Connect to the WID using SQL Server Management Studio
  3. Connect to the WID using PowerShell

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
DBAtools, Utilities

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

16,360 Views