RSS

How to migrate WSUS 3.0 from SQL Express to a remote SQL Server

16 Nov

How to migrate WSUS 3.0 from SQL Express to a remote SQL Server

September 24, 2008 By Paul Cunningham 10 Comments

In this article I will demonstrate how to migrate an existing WSUS 3.0 SP1 server from a local SQL Express instance to a remote SQL Server 2005 Database Services instance.

Firstly be aware of these limitations when deploying WSUS with a remote SQL Server instance.

  • You cannot use a server configured as a domain controller for either the front end (FE) or the back end (BE) of the remote SQL pair.
  • You cannot use a server running as a Terminal Services server for the front end of the remote SQL pair.
  • You cannot use Windows Internal Database for database software on the back-end server.
  • Both the front-end and the back-end servers must be joined to an Active Directory domain.

1. Download and install the SQLCmd tool on the WSUS server.

2. Install SQL Server 2005 "Client Tools Only" on the WSUS server so that you have access to the SQL Management Studio console.

3. Stop the IISAdmin and Update Services services in Computer Management.  Note the other services that IISAdmin will stop, usually the World Wide Web service and the HTTP SSL service.

4. Use SQLCmd to detach the SUSDB.

C:\>sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

1> use master
2> alter database SUSDB set single_user with rollback immediate
3> go

Changed database context to 'master'
Nonqualified transactions are being rolled back. Estimated rollback completion
100%
1> sp_detach_db 'SUSDB'
2> go
1> exit

5. Copy the SUSDB.mdf and SUSDB_Log.ldf files from the WSUS server to the remote SQL server.  Place them in the default locations for MDF and LDF files on the SQL server.

6. Attach the SUSDB to the remote SQL server.

7. Grant the WSUS server computer account permissions to the SUSDB on the remote SQL server.

8. Configure the WSUS server to use the remote SQL server for SUSDB by modifying the HKLM\Software\Microsoft\Update Services\Server\Setup\SQLServerName registry key.

9.Find the following key: HKLM\Software\Microsoft\Update Services\Server\Setup\wYukonInstalled. In the Value box, type 0, and then click OK.

10. Start the IISAdmin, World Wide Web Publishing Service, HTTP SSL, and Update Services services.  Or you can just reboot the server.

11. Launch the WSUS administration console to verify the WSUS server is connecting to the database successfully.  If WSUS is not working properly double-check the services in the previous step or try restarting the server.  You can also review the Application event log for WSUS errors.

http://exchangeserverpro.com/how-to-migrate-wsus-30-from-sql-express-to-a-remote-sql-server 

————————————————————————————————————————————————————————————————Additional Information.

Migrating from Windows Internal Database to SQL Server 2005

Applies To: Windows Server 2003 with SP2,Windows Server Update Services

This topic explains how to migrate the Windows Server Update Services (WSUS) database from a Windows Internal Database instance (installed by default during WSUS setup) to a full version of Microsoft SQL Server 2005.

Why you should migrate the WSUS database to SQL Server 2005

If you chose to use Windows Internal Database as the WSUS database when you set up your WSUS server, you may want to upgrade the database engine to a full installation of SQL Server 2005. SQL Server 2005 lets you administer the WSUS database through the SQL Server Management Studio.

SQL Server 2005 database requirements

  • WSUS requires SQL Server 2005 with Service Pack 1. If you use the full version of SQL Server, the database administrator should first verify that the nested triggers option is turned on before setting up the WSUS database.
  • You cannot use SQL authentication. WSUS supports Windows authentication only.

Scenarios

The topic presents the following scenarios:

  • Migrating the Windows Internal Database database to an instance of SQL Server 2005 that is running on the WSUS server
  • Migrating the Windows Internal Database database to an instance of SQL Server 2005 that is running on another server (remote SQL)
Warning
The procedures in this document use Registry Editor. Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require you to reinstall the operating system. Microsoft cannot guarantee that these problems can be resolved. Modify the registry at your own risk. Before you edit the registry, export the keys in the registry that you plan to edit, or back up the whole registry. If a problem occurs, you can then restore the registry to its previous state.

Migrating the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005 running on the WSUS server

Use the following steps to migrate the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005.

  1. Install SQL Server 2005 with the Server and Client Tools option and SQL Server 2005 Service Pack 1 or a later version on your WSUS server.
  2. Click Start, point to Programs, point to Administrative Tools, and then click Services.
  3. Right-click IIS Admin Service, and then click Stop.
  4. Right-click Update Services, and then click Stop.
  5. Run the following SQL command to detach the WSUS database (SUSDB) from the Windows Internal Database instance, by using the sqlcmd utility. This utility 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

  6. In SQL Server Management Studio, under the instance node, right-click Databases, select Properties, and then click Attach.
  7. In the Attach Databases box, under Databases to attach, locate the susdb.mdf file (by default, this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.
  8. In this step, you will verify that NT AUTHORITY\NETWORK SERVICE has login permissions to the instance of SQL Server and to the WSUS database. If it does not, you will have to add it to both locations. This account should also be a member of the webService role on the WSUS database.
    • To verify permissions on the instance of SQL Server, in SQL Server Management Studio, open the instance and select Security, and then Logins. The NT AUTHORITY\NETWORK SERVICE account should be listed as a login. If it is not, it should be added.
    • To verify permissions on the database, right-click the database, select Properties and then click Permissions. The NT AUTHORITY\NETWORK SERVICE account should be listed as a login. If it is not, it should be added.
    • To verify members of the webService role, under the WSUS database, select Roles, right-click webService, and then select Properties. The NT AUTHORITY\NETWORK SERVICE account should be listed as a member of this role. If it is not, it should be added.
  9. In this step, you will edit the registry to both point WSUS to the instance of SQL server that now holds the WSUS database and recognize the new database for future WSUS updates. If you have not already done this, export the keys in the registry that you plan to edit, or back up the whole registry.
    1. Click Start, click Run, type regedit, and then click OK.
    2. 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, type [ServerName].
    3. Find the following key: HKLM\Software\Microsoft\Update Services\Server\Setup\wYukonInstalled. In the Value box, type 0, and then click OK.
  10. Click Start, point to Programs, point to Administrative Tools, and then click Services.
  11. Right-click IIS Admin Service, and then click Start.
  12. Right-click Update Services, and then click Start.
  13. Verify that the database migration was successful by opening the WSUS administrative console. (Click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0.)
Note
You might have to restart the server for these settings to take effect.

Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server

The goal of this scenario is to take the WSUS database running in a Windows Internal Database instance on the WSUS server and move and upgrade it to an instance of SQL Server 2005 that is running on a remote server. Only a full SQL Server 2005 database may be used in a remote SQL installation. Each step, where appropriate, indicates the server on which you perform the procedure.

Remote SQL scenario limitations

  • You cannot use a server configured as a domain controller for either the front end or the back end of the remote SQL server pair.
  • You cannot use a server that is running as a Terminal Services server for the front end of the remote SQL server pair.
  • You cannot use Windows Internal Database for database software on the back-end server.
  • Both the front-end and the back-end servers must be joined to an Active Directory directory service domain.

Prerequisites

Front end server starting configuration:

  • Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system
  • WSUS with Windows Internal Database

Back end server starting configuration:

  • Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system
  • SQL Server 2005
To migrate the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005 on a remote server
  1. On the front end server: Install Microsoft SQL Server 2005 with the Server and Client Tools option and SQL Server 2005 Service Pack 1 or a later version. This step will enable you to use the SQL Server Enterprise Manager on the front end server.
  2. On the front end server:
    1. Click Start, point to Programs, point to Administrative Tools, and then click Services.
    2. Right-click IIS Admin Service, and then click Stop.
    3. Right-click Update Services, and then click Stop.
  3. On the front end server: Run the following SQL command to detach the WSUS database by using the sqlcmd utility. The utility 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

  4. On the front end server: copy the SUSDB.mdf and SUSDB_log.ldf files from the front end server to the back end server.
  5. On the back end server:
    1. To attach SUSDB to the destination instance of SQL server, under the instance node, right-click Databases, select Properties, and then click Attach.
    2. In the Attach Databases box, under Databases to attach, locate the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.
  6. On the back end server:
    • To verify permissions on the instance of SQL Server, in SQL Server Management Studio, open the instance ,select Security, and then Logins. The front end server machine account should be listed as a login. If it is not, it should be added.
    • To verify permissions on the database, right-click the database, select Properties, and then click Permissions. The front end server machine account should be listed as a login. If the server account is not listed, it should be added.
    • To verify members of the webService role, under the WSUS database, select Roles, right-click webService, and then select Properties. The front end server machine account should be listed as a member of this role. If the server account is not listed, it should be added.
  7. On the front end server: In this step, you will edit the registry to point WSUS to the destination instance of SQL and to recognize the new database for future WSUS updates. If you have not already done so, export the keys in the registry that you plan to edit, or back up the whole registry.
    1. Click Start, click Run, type regedit, and then click OK.
    2. Find the following key: HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName. In the Value data box, type [BEName]\[InstanceName], and then click OK. If the instance name is the default instance, type [BEName].
      Note
      When typing [BEName], do not add the domain name before the name.

    3. Find the following key: HKLM\Software\Microsoft\Update Services\Server\Setup\wYukonInstalled. In the Value box, type 0, and then click OK.
  8. On the front end server:
    1. Click Start, point to Programs, point to Administrative Tools, and then click Services.
    2. Right-click IIS Admin Service, and then click Start.
    3. Right-click Update Services, and then click Start.
  9. On the front end server: Verify that the database migration was successful by opening the WSUS administrative console. (Click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0).
    Note
    You might have to restart the front end server in order for these settings to take effect.

     

    http://technet.microsoft.com/en-us/library/cc708558(WS.10).aspx 

 

Advertisements
 
1 Comment

Posted by on November 16, 2010 in Wsus

 

One response to “How to migrate WSUS 3.0 from SQL Express to a remote SQL Server

  1. Dinesh Kulkarni

    March 4, 2014 at 5:39 pm

    Thanks Rajesh, above write-up helped me lot

     

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

 
%d bloggers like this: