locked
I need to move a WSUS dB to another server RRS feed

  • Question

  • We are currently consolidating many SQL instances and I was wondering if the following can be accomplished.

    Is it possible to move the SUSDB dB from its original server to a shared instance of SQL on another server, so that the instance residing on the original server can be retired?  The owner of the dB is a non-standard account in that it is the ASPNET IIS system account.  I need to determine if the dB can actually be separated from its original server running the web front end of the application along with changing the owner of the dB. 

    Any help would be greatly appreciated.

    Thanks!!

    Andrea

    Tuesday, June 10, 2008 3:35 PM

Answers

  • Hi,

    See the following steps:

    Please perform a full backup of your WSUS server first.

    1.Install SQL Server 2005 (with the Server and Client Tools option) and SQL Server 2005 Service Pack 1 or higher on your new server.

    2.Stop the IIS Admin service and the Update Services service:

    •Click Start, point to Programs, point to Administrative Tools, and then click Services.

    •Right-click IIS Admin Service, and then click Stop.

    •Right-click Update Services, and then click Stop.

    3.Detach the WSUS database (SUSDB) from the orginal SQL server. You will need to use the sqlcmd utility, which can be downloaded from Feature Pack for Microsoft SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkId=70728). For more information about the sqlcmd utility, see sqlcmd Utility (http://go.microsoft.com/fwlink/?LinkId=81183).

    sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    use master
    alter database SUSDB set single_user with rollback immediate
    go
    sp_detach_db ‘SUSDB’
    go


    1.Attach SUSDB to the destination SQL instance.

    •Under the instance node, right-click Databases, select Properties, and then click Attach.

    •In the Attach Databases box, under Databases to attach, browse to the location of the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.

    2.In the SUSDB database, add the login NT AUTHORITY\NETWORK SERVICE if necessary.

    •Right-click the database, select Properties, click Permissions, and then click Add.

    •In the Select Logins or Roles dialog box, type NT AUTHORITY\NETWORK SERVICE.

    3.Edit the registry to point WSUS to the SQL instance that now holds SUSDB.

    •Click Start, click Run, type regedit, and then click OK.

    •Find the following key: HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName, and in the Value box, type [ServerName]\[InstanceName],and then click OK. If the instance name is the default instance, then simply type [ServerName].

    4.Open Services and then start the IIS Admin service and Update Services service.

    •Click Start, point to Programs, point to Administrative Tools, and then click Services.

    •Right-click IIS Admin Service, and then click Start.

    •Right-click Update Services, and then click Start.

    5.Verify that the database migration has been successful by opening the WSUS administrative console (click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0)..

     
    You might have to restart the server for these settings to take effect.
    --------------------
    Regards,
    Eric Zhang





    • Marked as answer by Eric Zhang CHN Wednesday, June 18, 2008 12:45 PM
    Friday, June 13, 2008 8:22 AM

All replies

  • Bump.

    Help?  Anyone?    
    Wednesday, June 11, 2008 2:01 PM
  • Hi,

    See the following steps:

    Please perform a full backup of your WSUS server first.

    1.Install SQL Server 2005 (with the Server and Client Tools option) and SQL Server 2005 Service Pack 1 or higher on your new server.

    2.Stop the IIS Admin service and the Update Services service:

    •Click Start, point to Programs, point to Administrative Tools, and then click Services.

    •Right-click IIS Admin Service, and then click Stop.

    •Right-click Update Services, and then click Stop.

    3.Detach the WSUS database (SUSDB) from the orginal SQL server. You will need to use the sqlcmd utility, which can be downloaded from Feature Pack for Microsoft SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkId=70728). For more information about the sqlcmd utility, see sqlcmd Utility (http://go.microsoft.com/fwlink/?LinkId=81183).

    sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    use master
    alter database SUSDB set single_user with rollback immediate
    go
    sp_detach_db ‘SUSDB’
    go


    1.Attach SUSDB to the destination SQL instance.

    •Under the instance node, right-click Databases, select Properties, and then click Attach.

    •In the Attach Databases box, under Databases to attach, browse to the location of the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.

    2.In the SUSDB database, add the login NT AUTHORITY\NETWORK SERVICE if necessary.

    •Right-click the database, select Properties, click Permissions, and then click Add.

    •In the Select Logins or Roles dialog box, type NT AUTHORITY\NETWORK SERVICE.

    3.Edit the registry to point WSUS to the SQL instance that now holds SUSDB.

    •Click Start, click Run, type regedit, and then click OK.

    •Find the following key: HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName, and in the Value box, type [ServerName]\[InstanceName],and then click OK. If the instance name is the default instance, then simply type [ServerName].

    4.Open Services and then start the IIS Admin service and Update Services service.

    •Click Start, point to Programs, point to Administrative Tools, and then click Services.

    •Right-click IIS Admin Service, and then click Start.

    •Right-click Update Services, and then click Start.

    5.Verify that the database migration has been successful by opening the WSUS administrative console (click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0)..

     
    You might have to restart the server for these settings to take effect.
    --------------------
    Regards,
    Eric Zhang





    • Marked as answer by Eric Zhang CHN Wednesday, June 18, 2008 12:45 PM
    Friday, June 13, 2008 8:22 AM
  • Hi,

    As this thread has been quiet for a while, we assume that the issue has been resolved. At this time, we will mark it as ‘Answered’ as the previous steps should be helpful for many similar scenarios.

    If the issue still persists and you want to return to this question, please reply this post directly so we will be notified to follow it up. You can also choose to unmark the answer as you wish.

    In addition, we’d love to hear your feedback about the solution. By sharing your experience you can help other community members facing similar problems.

    Thanks!
    --------------------
    Regards,
    Eric Zhang

    • Proposed as answer by Smackgod Wednesday, July 1, 2015 3:38 PM
    Wednesday, June 18, 2008 12:45 PM
  • Worked like a charm for me!! THANK YOU VERY MUCH!
    Tuesday, November 30, 2010 12:41 PM
  • Hello everyone, this information was very important to me because it allowed me to change the database from one instance to another without any problem.

    Thank you.
    Friday, October 7, 2011 9:17 AM
  • Just to be thorough in the information about this process...

    It is documented in the WSUS Operations Guide.

    See Migrate from Windows Internal Database to SQL Server.


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2011)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    My Blog: http://onsitechsolutions.spaces.live.com
    Friday, October 7, 2011 4:06 PM
  • Hi Everyone,

    I have tried these above steps without any issues but wanted to check if the same steps still holds good incase we move the DB from a named instance ( On the old SQL server) to that of a default instance on the new SQL server.

    Thank you.

    Tuesday, November 27, 2012 5:17 AM
  • Yes the above steps holds good even while moving it from a named instance on the old server to a default instance of a new SQL box.

    Wanted to update the blog entry incase someone newbie like me faces a similar issue like this.

    Thank you.

    Tuesday, November 27, 2012 12:46 PM
  • Using MS SQL 2014 server, it works if you rename SQL server.  If you move SUSDB to new MS SQL server, you must add domain\serverName$ to new SQL server login in additional to these steps.  Note you don't need to add Network Service to SQL server logins.
    Wednesday, July 1, 2015 3:43 PM