Deep Dive How to Migrate a WSUS Database from WID to SQL in SCCM

In this video guide, we will be covering how to migrate WSUS Database (SUSDB) from Windows Internal Database instance to SQL Server instance. Accompanying blog post available at setupconfigmgr... #WSUS #SCCM #ConfigMgr
Topics in Video:
Overview of WID limitation and why you want to move to SQL - • Deep Dive How to Migra...
Review WSUS configuration and how to validate if you are using WID for the SUSDB - • Deep Dive How to Migra...
Detach SUSDB from the WID instance - • Deep Dive How to Migra...
Copy WSUS database (SUSDB.mdf and SUSDB_log.ldf) from default WID install path and attach in SQL Instance - • Deep Dive How to Migra...
Run one-time re-indexing in SQL of the SUSDB since it likely was never done in the WID instance - • Deep Dive How to Migra...
Remove the WID Connectivity role service (UpdateServices-WidDB) - • Deep Dive How to Migra...
Install the SQL Server Connectivity role service (UpdateServices-DB) - • Deep Dive How to Migra...
Run WsusUtil postinstall command and reconfigure WSUS to use a SQL Server instead of WID - • Deep Dive How to Migra...
Validate WSUS console loads and shows the exact same data as before the move - • Deep Dive How to Migra...
Validate SCCM software update point sync works from WSUS - • Deep Dive How to Migra...
Commands and Notes:
Connect to WID instance in SQL Management Studio of sqlcmd (Server 2012 or Newer): \\.\pipe\MICROSOFT##WID\tsql\query
Connect to WID instance in SQL Management Studio of sqlcmd (Server 2008 R2 or Older): \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Powershell command to see WSUS installed role services: Get-WindowsFeature -Name UpdateServices*
Powershell command to remove WSUS WidDB: Remove-WindowsFeature -Name UpdateServices-WidDB
Powershell command to install WSUS SQL Database Connectivity: Install-WindowsFeature -Name UpdateServices-DB
WsusUtil command: WsusUtil.exe postinstall SQL_INSTANCE_NAME=”SCUP.CONTOSO.LOCAL” CONTENT_DIR=”J:\WSUS”
SQL_INSTANCE_NAME and CONTENT_DIR should be changed to for your environment details
Helpful Resources:
sqlcmd Utility - docs.microsoft...
Migrating the WSUS Database from WID to SQL - docs.microsoft...
Re-index the WSUS 3.0 Database - gallery.techne...

[@pedrohenriqueconstantino827]

video more precious, clearer and more objective than this one doesn't exist. Excellent, congratulations!

[@PatchMyPC]

Thanks for watching!

[@Hollywood-xb7xm]

Went thru your video and performed this task with zero issues. Thank you so much for the video's, learning a lot.

[@PatchMyPC]

Thanks for watching

[@ParmodKumar-wi1li]

Crystal clear. Wonderful work as always. I wait for your videos like a bonus :)

[@PatchMyPC]

Thanks for watching!

[@ariksan]

Thank you for that. Some joker (not me) pre-configured WSUS with WID on a server that slated to become the SUP and another joker (me) did not check before the site install.

[@PatchMyPC]

Thanks for watching.

[@BadDudesVsFletch]

Very impressed - clear and precise instructions, nice one. I had to move my DB to another server so i had to research permissions etc - apart from that spot on :D

[@PatchMyPC]

Thanks for watching

[@stang7043]

Awesome tutorial. Clean and to the point. I did run into some problems though. I was getting connection refused errors in WCM.log. If anyone uses an unc path as their content folder you should check in iis physical path under the content folder in basic or advanced settings. Also, I had to edit authentication for the iis content folder under anonymous to use the app pool account instead of IIS_IURS.

[@PatchMyPC]

Definitely need to make those changes in IIS for UNC WSUS Content good catch. I do specifically cover that in this guide as well setupconfigmgr.com/using-a-shared-wsus-database-for-multiple-sups-in-sccm

I enjoyed every single explanation I did on your video. Congratulations! Just only one question: My SQL Server is a remote server other than yours because you use a local server. Because that you didn't provide user name and password to conect to SUSDB in your server. What can I do to inform username and password instead of using my own logged on username?

[@ashwing2927]

Awesome Demo..thanks a lot

[@PatchMyPC]

You're welcome! Thanks for watching.

[@overthrow3640]

EPIC!!!! thanks!!!

[@PatchMyPC]

Glad you liked it!

[@josiasperez03]

Hello after all those steps wsus downloads updates but client pcs are not downloading the updates stays in 0% , take in mind it was working ok before movin to sql, long story sort there was an error in wid database that the only solutions was to migrate to sql.

[@PatchMyPC]

WSUS with SCCM?

[@josiasperez03]

@@PatchMyPC only wsus wServer 2012 , if the downloading doesnt succeeds it throws Downlaod error 0% 0x80070020

[@josiasperez03]

@@PatchMyPC hello

[@HeyRadu]

Hi Justin, great tutorial as always ! Thank you.
Is the WSUS database supported on a SQL Always ON migration scenario as well ?

[@PatchMyPC]

Not sure about that one, I suspect it would probably work fine, but I haven't checked the docs to see if it's supported.

[@ehabgalal9181]

Thank you for this great video
But sorry to ask you something outside this video
I have noticed that third party update like 7zip and chrome are shown in WSUS
But this is not my case in my environment
I am using patchmypc with publish software

[@PatchMyPC]

In this lab, we had the option to make third-party updates visible in WSUS in the advanced tab of our service. This doesn't need to be configured if you are using SCCM to deploy updates though. - Justin

[@ehabgalal9181]

@@PatchMyPC Thank you for your clarification

[@michaelchakrabarty7625]

I don't have the Server listed in SQL services (mssqlserver) so I cannot connect to it using SQL studio. There are plenty of other SQL services like Sophos,Net2 etc, but not the MSSQLServer service. What do I need to enable/install to make the server itself connectable in SQL studio?

[@PatchMyPC]

You need to install SQL server, covered in the first how to install sccm video I did

[@Doyle2k5]

How do you have things such as 7-zip, Chrome, and Java in WSUS?

[@PatchMyPC]

patchmypc.com

[@veikoplays]

No need for "SQL Server Connectivity" feature? I did the migration, all works, just wondering...

[@PatchMyPC]

SQL connectivity should be enabled for WSUS if you use SQL

[@travismkirk]

Hey Justin, I am getting the following error when trying to attach my SUSDB.mdf and SUSDB_Log.ldf An Error has occurred when attaching the database. Click on the hyperlink in the messages column for details. Any ideas?

[@travismkirk]

I found out my answer. The database SUSDB cannot be opened because it is version 782. This server only supports version 706 and earlier. A downgrade path is not supported.

[@PatchMyPC]

Thanks for the followup

[@cobaink]

This didnt work for me \\. \pipe\MICROSOFT##WID\tsql\query

[@PatchMyPC]

www.ajtek.ca/wsus/how-do-i-connect-to-the-windows-internal-database-wid/

[@SMeyerNYC]

Microsoft should just link to you lol

[@PatchMyPC]

:)