Monday 2 March 2015

 Moving the WSUS 4 Database


"If it wasn’t for that last minute, nothing would ever get done."

Why might you want to do this in the first place? Having a database on the system drive is generally a bad idea. WSUS uses the Windows Internal Database which is located in the Windows folder. Having a database on a separate volume is generally a good idea, if only for performance reasons. 

Preparation

1) Designate a new location for the database. Perhaps SSD or NAS based storage.

2) Like any WSUS administrator sooner or later realises, I had been given a database but no means to administer it.  I downloaded and installed the SQL Express Management Studio (SQL Server 2012 SP2, SQLManagementStudio_x64_ENU.exe)

3) Stop the WSUS service (services.msc)



4) Locate your WSUS Windows Internal Database (WID)

 C:\>dir c:\windows\WID\Data
 Volume in drive C has no label.
 Volume Serial Number is ****-****

 Directory of c:\windows\WID\Data

02.03.2015  22:18    <DIR>          .
02.03.2015  22:18    <DIR>          ..
02.03.2015  00:40         4.194.304 master.mdf
02.03.2015  00:40         1.048.576 mastlog.ldf
02.03.2015  00:40         2.162.688 model.mdf
02.03.2015  00:40           524.288 modellog.ldf
02.03.2015  00:40        13.107.200 msdbdata.mdf
02.03.2015  00:40           524.288 msdblog.ldf
02.03.2015  22:19     3.816.882.176 SUSDB.mdf
02.03.2015  22:19        25.296.896 SUSDB_log.ldf
02.03.2015  21:51        40.960.000 tempdb.mdf
02.03.2015  21:05        22.675.456 templog.ldf
              10 File(s)  3.927.375.872 bytes
               2 Dir(s)  112.296.820.736 bytes free

C:\>


Making the move



In Management Studio, I connected to

\\.\pipe\Microsoft##WID\tsql\query





I expanded Databases and detached the SUSSB



In this case the destination is a folder on volume d:. d:\WSUS DB

Moving the DB (elevated command prompt):


C:\>move c:\windows\wid\data\susdb* "d:\WSUS DB"
c:\windows\wid\data\SUSDB.mdf
c:\windows\wid\data\SUSDB_log.ldf
        2 file(s) moved.

C:\>



It is too soon to re-attach the db. It would result in a read error or the database would be mounted as read-only. I needed to copy the NTFS permissions (ACL) first

PS C:\Windows\WID\data> Get-ACL | fl


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Windows\WID\data
Owner  : BUILTIN\Administrators
Group  : BUILTIN\Administrators
Access : NT AUTHORITY\SYSTEM Allow  FullControl
         BUILTIN\Administrators Allow  FullControl
         NT SERVICE\MSSQL$MICROSOFT##WID Allow  Write, Delete, Read, Synchronize
         NT SERVICE\MSSQL$MICROSOFT##WID Allow  -1073676288
Audit  :
Sddl   : O:BAG:BAD:PAI(A;OICI;FA;;;SY)(A;OICI;FA;;;BA)(A;;0x13019f;;;S-1-5-80-1184457765-4068085190-3456807688-22009523
         27-3769537534)(A;OICIIO;SDGWGR;;;S-1-5-80-1184457765-4068085190-3456807688-2200952327-3769537534)



PS C:\Windows\WID\data>


This entry is most important: NT SERVICE\MSSQL$MICROSOFT##WID , but I copied the ACL as a whole.

PS C:\Windows\WID\data> Get-Acl C:\Windows\WID\Data | Set-Acl -Path 'D:\WSUS DB'
PS C:\Windows\WID\data>


In SQL Management Studio, I re-attached the database.

 



 Next I started the WSUS service.

Sources
(1) http://systemspecialist.net/2013/05/15/move-or-delete-a-wsus-4-windows-internal-database-wid-on-windows-server-2012/
(2) https://technet.microsoft.com/en-us/library/hh849810.aspx
(3) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/345031e7-88c5-40e5-bfda-3880122045e0/error-17204-fcbopen-failed-how-to-set-persmission-correctly-on-a-datalog-file-for-sql-server?forum=sqldatabaseengine