Migrating the WSUS database from WID to SQL Server

Running WSUS with the Windows Internal Database (WID) has been working great for us, for years, but if we want to have the database on a separate volume, if we want better performance, better management tools and especially being able to back it up using our internal or cloud backup tool, then we need to migrate it to a dedicated SQL Server instance. If you are wondering if it’s worth it, well…yes, if you already have a SQL license, if not I will just stick with the WID, because buying a SQL Server license just for this, does’t justify the costs.

1. Checking WSUS for WID or SQL instance

When using the default settings during the WSUS installation, the wizard will configure it using WID. If you inherited the network and try to find out which type of database your WSUS server is using, PowerShell comes to the rescue.

Type the bellow command in a PowerShell window and if the WID Connectivity box is checked, then you are using the built in database. If we also look at the Installed State column, we can see that it says Installed. The second thing that we can notice in the results is that the SQL Server Connectivity box is un-checked and it only says Available. This is another indicator that we are not using a dedicated SQL Server.

Get-WindowsFeature -Name UpdateServices*

Checking using PowerShell if WSUS is using WID or SQL

Another option to see if we are using a WID database or a SQL one is to look in the registry. Browse to HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft> Update Services > ServerSetup and here we have the SQLServerName registry data. If the value says MICROSOFT##WID, then we are using the built in database.

Later on, -after we migrate the database to the SQL server – we will re-visit these settings and they will be changed to our SQL server instance.

Checking using the registry if WSUS is using WID or SQL

 

2. Migrating the WSUS database

2.1. Detaching the WID database

In order for us to be able to copy the existing WID database to our SQL server, we first need to detach it from the running instance, and to do that, we can either use SQL Management Studio (SSMS) or the SQLcmd utility on our WSUS machine. I’m going to present both options in this section, but before that we need to stop the IIS and WSUS services.

Open a PowerShell window and paste the bellow two command lines to do just that.

Stop-Service WsusService
Stop-Service IISAdmin

Stopping the WSUS and IIS services before detaching the database from the WID instance

Once those services are stopped, open SQL Management Studio -that we downloaded and installed on the WSUS server- and connect to:

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

Connecting to the WID instance using SQL Management Studio

Expand the Databases folder, right-click the SUSDB database and choose Tasks > Detach.

Detaching the WSUS database from the WID instance

In the Detach Database window that pops-up, check the Drop Connections box then hit OK to detach the database. Once the database is detached it will disappear from the Databases folder in the SQL Management Studio console.

Dropping all connections to the WSUS database before detaching

Using the command line it is much faster since the download package is way smaller and quicker to install, compared to SQL Management Studio, but unfortunately we still need a utility to accomplish this. Download the SQLcmd utility, install it, then come back and run the bellow command lines to detach the WSUS database from the WID instance.

sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query
use master
GO
alter database SUSDB set single_user with rollback immediate
GO
sp_detach_db SUSDB
GO
exit

Detaching the WSUS database from the WID instance using the SQLcmd utility

All that is left now before moving to our SQL server is to copy the database and log files from the WSUS server to the SQL one. Open File Explorer and browse to C: > Windows > WID > Data. Here we can see the SUSDB.mdf and SUSDB_log.ldf files that we need to copy. Select them, then using a UNC path, copy the files to the SQL server.

Copying the WSUS database and log files     Pasting the WSUS database and log files on the SQL server

 

2.2. Attaching the WSUS database to the SQL instance

This operation is very easy since we have all the tools already installed on the SQL server and the database transferred locally.

Open the SQL Management Studio console, right-click the Databases folder then choose Attach.

Attaching a new database on the SQL server using the SQL Management Studio

In the Attach Databases window that opens up click the Add button then select the database we copied over from our WSUS server.

Selecting the WSUS database to be attached on the SQL server     Details preview of the WSUS database before attaching it on the SQL server

After a few seconds, the WSUS database will be attached to our SQL instance.

View of the WSUS database successfully attached to the SQL server instance

If you want to use a SQL query in your SQL Management Studio to attach the database, it goes like this:

 USE master;
   GO
   CREATE DATABASE SUSDB
   ON
       (FILENAME = 'C:WSUSdbSUSDB.mdf'),
       (FILENAME = 'C:WSUSdbSUSDB_Log.ldf')
       FOR ATTACH;
   GO

Change the path for the filenames with the one corresponding to yours then click the Execute button or hit F5 from your keyboard to start running the query.

Attaching the WSUS database using a SQL query

 

2.3. Configuring SQL database permissions for the WSUS server

In order for our WSUS server to be able to access the database we just attached to the SQL instance, we need to configure the proper permissions so we don’t have problems later on.

In the SQL Management Studio, expand the Security folder then right-click Logins and choose New Login.

Creating a new login on the SQL instance

The New Login window opens up. In the Login name field type your WSUS server name followed by a dollar sign at the end,

<domain name>\<WSUS server name>$

then from the Default database drop-down-box select our WSUS database.

Assigning the WSUS server to the new login

Moving to the User Mapping page, tick the box for the SUSDB database then on the Database role membership for section check db_owner and webService boxes. Click OK when done.

Assigning permissions to the database for the WSUS server

 

2.4. Re-index the WSUS database

Before moving straight to re-configuring our WSUS server, I will like to take a minute and do some maintenance on the database by re-indexing the tables, and to accomplish this, we will use an already made Transact SQL script from Microsoft Technet.

/****************************************************************************** 
This sample T-SQL script performs basic maintenance tasks on SUSDB 
1. Identifies indexes that are fragmented and defragments them. For certain 
   tables, a fill-factor is set in order to improve insert performance. 
   Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx 
   and tailored for SUSDB requirements 
2. Updates potentially out-of-date table statistics. 
******************************************************************************/ 
 
USE SUSDB; 
GO 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
 
SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO

Right-click the WSUS database and choose New Query then paste the script in the new query section that opens up. Hit the Execute button from the toolbar or F5 from the keyboard to run the script. After a few seconds or minutes -depending on the size of the database- we should have the Query executed successfully message down at the bottom.

Execute the SQL query for database tables re-indexing     View of the SQL query executed successfully

We can now close the SQL Management Studio and move over to our WSUS server so we ca point it to the new SQL instance.

 

3. Pointing WSUS to the new SQL server instance

3.1. Removing the WID Connectivity role feature and installing the SQL Server one

The first thing that we need to do on our WSUS server is to remove the WID Connectivity role feature, since it is not used anymore. To do that, open PowerShell and type:

Remove-WindowsFeature -Name UpdateServices-WidDB

Removing the WID instance from the WSUS server     View of the WID instance removed successfully

Once the removal of the WID Connectivity role feature is done, in the same PowerShell window, type the bellow command to install the SQL Server Connectivity role feature. After a few seconds we should have the WID Conectivity removed and showing as Available, and the SQL Server Connectivity showing as Installed.

Install-WindowsFeature -Name UpdateServices-DB

Installing the SQL Server Connectivity role feature on the WSUS server    View of the SQL Server Connectivity installed successfully

3.2. Pointing WSUS to the SQL server instance

We are finally at the point of creating the link between our WSUS server and the SQL server instance, and since we have all the prerequisites installed and ready, let’s make the connection. For this, we are going to use a utility called WsusUtil.exe which can be  found in our WSUS installation folder.

In a PowerShell window, change directory to C: > Program Files > Update Services > Tools folder then type the following command line:

.\WsusUtil.exe postinstall SQL_INSTANCE_NAME="<SQL Server FQDN>" CONTENT_DIR="<Your WSUS Content Directory>"

SQL_INSTANCE_NAME – is the FQDN of your SQL Server. If you have a custom named instance, you will have to put your custom SQL instance after the server FQDN (Server-SQLCustomInstance).

CONTENT_DIR – Is the directory where all your downloaded WSUS updates are sitting.

Pointing WSUS to the SQL server instance

The process will take a minute or so, and we can monitor the progress by opening the log file from the path printed on our screen.

Watching the post-install progress from the WSUS log file

Once It’s done, in the registry we should have our SQL server FQDN configured, which means that our WSUS server is now using this key value to connect to the SQL server.

Checking the registry if the SQL server name entry got updated after the post-install

In the WSUS console, we also have our updates, our computers just as before, meaning that our work paid off.

View of the WSUS console using the migrated database on new SQL server instance     View of the WSUS console using the migrated database on new SQL server instance

Summary

Moving the WSUS database to a dedicated SQL instance it’s not that difficult and I will go for it if you have the SQL license already in place. Maybe is just me, but I like having most of the services concentrated in one place. Easier for backup, easier for management and troubleshoot. Yes, the SQL server can fail, but you can always build a SQL cluster in case you need that high availability.

If you have questions, ideas, or like to share your experience about how you migrated your WSUS database in your environment, let the community know in the comments area bellow.

Want content like this delivered right to your

email inbox?


Leave a Reply

Your email address will not be published. Required fields are marked *

*

css.php