Extending the MDT Database – sp_refreshview Not Working

I recently had to add a few custom properties to the computer settings in the MDT database and discovered the custom properties weren’t being read using PowerShell or during a deployment simulation. A quick look into the MDT database on the SQL server revealed this was due to the database Views not being updated with the new settings, despite running the recommended stored procedures using sp_refreshview.

In this post we’ll take a look at how to extend the database with your custom properties, the issue I experienced and the resolution.

A Quick “How To” on Adding Your Own Custom Properties

Launch SQL Server Management Studio and connect to your SQL server, expand the MDT database > Tables > File Tables > dbo.Settings > right-click on Columns and select New Column. Add your properties in the Column field like so:

pic1

Once you’ve added your custom properties, it is recommended to run the following queries against the MDT database:

EXECUTE sp_refreshview ‘[dbo].[ComputerSettings]’
EXECUTE sp_refreshview ‘[dbo].[LocationSettings]’
EXECUTE sp_refreshview ‘[dbo].[MakeModelSettings]’
EXECUTE sp_refreshview ‘[dbo].[RoleSettings]’

If you then launch the Deployment Workbench you will find the new properties in the Details tab for any given Computer or Role.

The Problem

For testing purposes I configured a couple of the new properties as shown below:

image

However, when I tried to retrieve the properties using PowerShell the properties were missing:

image

Digging into the database on the SQL server revealed the “ComputerSettings” and “RoleSettings” Views haven’t been updated despite running the sp_refreshview stored procedures. The properties were missing from the Views entirely as shown below:

pic2

 The Resolution

A “View” is simply a collection of properties/columns from multiple tables in a database. The cmdlets in the MDTDB PowerShell module such as Get-MDTComputer and also the Gather step in MDT runs a query against these Views to retrieve computer properties. An incomplete View = broken OSD :).

Fortunately fixing the offending Views are quite simple.

In the SQL Server Management Studio and expand the MDT database > Views > right-click on “dbo.ComputerSettings” and choose “Script VIEW as” > Alter To > New Query Editor Window.

In the resulting SQL query simply add your custom properties at the end of the SELECT statement as shown below:

image

Once this is done, you can repeat this process for the views “RoleSettings”, “LocationSettings” and “MakeModelSettings”. In my case only the views “ComputerSettings” and “RoleSettings” needed updating.

Once complete right-click on your MDT database and click Refresh.

Now when I run Get-MDTComputer the properties are being returned just fine:

image

 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s