将 WSUS 数据库从 WID 迁移到 SQLMigrating the WSUS Database from WID to SQL

适用于: Windows Server 2012、Windows Server 2012 R2、Windows Server 2016Applies to: Windows Server 2012, Windows Server 2012 R2, Windows Server 2016

使用以下步骤将 WSUS 数据库 (SUSDB) 从 Windows 内部数据库实例迁移到 SQL Server 的本地或远程实例。Use the following steps to migrate the WSUS database (SUSDB) from a Windows Internal Database instance to a Local or Remote instance of SQL Server.

先决条件Prerequisites

  • SQL 实例。SQL Instance. 这可以是默认的 MSSQLServer 或自定义实例。This can be the default MSSQLServer or a custom Instance.
  • SQL Server Management StudioSQL Server Management Studio
  • 已安装 WID 角色的 WSUSWSUS with WID role installed
  • IIS (通过服务器管理器) 安装 WSUS 时,通常会包括此步骤。IIS (This is normally included when you install WSUS through Server Manager). 它尚未安装,将需要。It is not already installed, it will need to be.

迁移 WSUS 数据库Migrating the WSUS database

在 WSUS 服务器上停止 IIS 和 WSUS 服务Stop the IIS and WSUS services on the WSUS server

从 PowerShell (提升的) 中,运行:From PowerShell (elevated), run:

    Stop-Service IISADMIN
    Stop-Service WsusService

从 Windows 内部数据库分离 SUSDBDetach SUSDB from the Windows Internal Database

使用 SQL Management StudioUsing SQL Management Studio

  1. 右键单击 " SUSDB - > 任务 - > ",单击 "分离 image1Right-click SUSDB -> Tasks -> click Detach: image1
  2. 选中 " 删除现有连接 ",然后单击 "确定" (可选,如果) 存在活动的连接。Check Drop Existing Connections and click OK (optional, if active connections exist). image2image2

使用命令提示符Using Command Prompt

重要

以下步骤演示了如何使用 sqlcmd 实用工具从 Windows 内部数据库实例分离 WSUS 数据库 (SUSDB) 。These steps show how to detach the WSUS database (SUSDB) from the Windows Internal Database instance by using the sqlcmd utility. 有关 sqlcmd 实用工具的详细信息,请参阅 sqlcmd 实用工具For more information about the sqlcmd utility, see sqlcmd Utility.

  1. 打开提升的命令提示符Open an elevated command prompt
  2. 运行以下 SQL 命令,通过 sqlcmd 实用工具从 Windows 内部数据库实例分离 WSUS 数据库 (SUSDB) :Run the following SQL command to detach the WSUS database (SUSDB) from the Windows Internal Database instance by using the sqlcmd utility:
        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

将 SUSDB 文件复制到 SQL ServerCopy the SUSDB files to the SQL Server

  1. SUSDB和 SUSDB 文件从 _ ** wid data 文件夹复制 (% SystemDrive%** \ Windows \ WID \ data) 到 SQL 实例数据文件夹。Copy SUSDB.mdf and SUSDB_log.ldf from the WID Data Folder (%SystemDrive%\Windows\WID\Data) to the SQL Instance Data Folder.

提示

例如,如果 SQL 实例文件夹是 C:\Program FILES\MICROSOFT sql Server\MSSQL12。MSSQLSERVER\MSSQL,WID Data 文件夹为 C:\Windows\WID\Data, 将 SUSDB 文件从 C:\Windows\WID\Data 复制到 C:\Program Files\Microsoft SQL Server\MSSQL12。MSSQLSERVER\MSSQL\DataFor example, if your SQL Instance Folder is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL, and the WID Data folder is C:\Windows\WID\Data, copy the SUSDB files from C:\Windows\WID\Data to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data

将 SUSDB 附加到 SQL 实例Attach SUSDB to the SQL Instance

  1. SQL Server Management Studio的 " 实例 " 节点下,右键单击 " 数据库",然后单击 " 附加"。In SQL Server Management Studio, under the Instance node, right-click Databases, and then click Attach. image3image3
  2. 在 " 附加数据库 " 框中的 " 要附加的数据库" 下,单击 " 添加 " 按钮,然后找到 SUSDB 文件 (从 WID 文件夹复制) ,然后单击 "确定"In the Attach Databases box, under Databases to attach, click the Add button and locate the SUSDB.mdf file (copied from the WID Folder), and then click OK. image4.jpg  image5image4 image5

提示

也可以使用 Transact-sql 来完成此操作。This is also able to be done using Transact-Sql. 有关附加数据库的说明,请参阅 SQL 文档Please see the SQL documentation for attaching a database for its instructions.

使用前面示例中的路径 (示例) :Example (using paths from previous example):

   USE master;
   GO
   CREATE DATABASE SUSDB
   ON
       (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SUSDB.mdf'),
       (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\SUSDB_Log.ldf')
       FOR ATTACH;
   GO

验证 SQL Server 和数据库登录名和权限Verify SQL Server and Database Logins and Permissions

SQL Server 登录权限SQL Server Login Permissions

附加 SUSDB 后,通过执行以下操作,验证 NT AUTHORITY\NETWORK 服务 是否具有对 SQL Server 实例的登录权限:After attaching the SUSDB, verify that NT AUTHORITY\NETWORK SERVICE has login permissions to the instance of SQL Server by doing the following:

  1. 进入 SQL Server Management StudioGo into SQL Server Management Studio
  2. 打开实例Opening the Instance
  3. 单击 "安全"Click Security
  4. 单击 "登录"Click Logins

应该列出 NT AUTHORITY\NETWORK SERVICE 帐户。The NT AUTHORITY\NETWORK SERVICE account should be listed. 如果不是,则需要通过添加新的登录名来添加它。If it is not, you need to add it by adding New Login Name.

重要

如果 SQL 实例与 WSUS 在不同的计算机上,则应以 [FQDN] \ [WSUSComputerName] $ 格式列出 WSUS 服务器的计算机帐户。If the SQL Instance is on a different machine from WSUS, the WSUS Server's computer account should be listed in the format [FQDN]\[WSUSComputerName]$. 否则,可以使用以下步骤添加它,将NT AUTHORITY\NETWORK SERVICE替换为 WSUS 服务器的计算机帐户 ([FQDN] \ [WSUSComputerName] $) 此操作除了授予NT AUTHORITY\NETWORK 服务的权限If not, the steps below can be used to add it, replacing NT AUTHORITY\NETWORK SERVICE with the WSUS Server's computer account ([FQDN]\[WSUSComputerName]$) This would be in addition to granting rights to NT AUTHORITY\NETWORK SERVICE

添加 NT AUTHORITY\NETWORK SERVICE 并向其授予权限Adding NT AUTHORITY\NETWORK SERVICE and granting it rights
  1. 右键单击 "登录名",然后单击 "新建登录名 ... "Right Click Logins and click New Login… 图片6image6
  2. 在 "常规" 页上, (NT AUTHORITY\NETWORK SERVICE) 中填写登录名,并将默认数据库设置为 SUSDB。On the General page, fill out the Login name (NT AUTHORITY\NETWORK SERVICE), and set the Default database to SUSDB. image7image7
  3. 在 " 服务器角色 " 页上,确保选择 " 公用 " 和 " sysadmin "。On the Server Roles page, ensure public and sysadmin are selected. image8image8
  4. 在 " 用户映射 " 页上:On the User Mapping page:
    • 在 " 映射到此登录名的用户" 下:选择 SUSDBUnder Users mapped to this login: select SUSDB
    • 在 " 数据库角色成员身份: SUSDB" 下,确保选中以下内容:Under Database role membership for: SUSDB, ensure the following are checked:
      • publicpublic
      • webService image9webService image9
  5. 单击 “确定”Click OK

你现在应在 "登录名" 下看到 NT AUTHORITY\NETWORK 服务You should now see NT AUTHORITY\NETWORK SERVICE under Logins. image10image10

数据库权限Database Permissions

  1. 右键单击 SUSDBRight-click the SUSDB
  2. 选择“属性”Select Properties
  3. 单击 权限Click Permissions

应该列出 NT AUTHORITY\NETWORK SERVICE 帐户。The NT AUTHORITY\NETWORK SERVICE account should be listed.

  1. 如果不是,请添加帐户。If it is not, add the account.

  2. 在 "登录名" 文本框中,按以下格式输入 WSUS 计算机:On the Login name textbox, enter the WSUS machine in the following format:

    [FQDN] \[WSUSComputerName] $[FQDN]\[WSUSComputerName]$

  3. 验证 " 默认数据库 " 是否设置为 " SUSDB"。Verify that the Default database is set to SUSDB.

    提示

    在以下示例中,FQDN 为 Contosto.com ,WSUS 计算机名称为 WsusMachineIn the following example, the FQDN is Contosto.com and the WSUS machine name is WsusMachine:

    image11

  4. 在 "用户映射" 页上,选择 "映射到此登录名的用户" 下的SUSDB数据库。On the User Mapping page, select the SUSDB Database under Users mapped to this login

  5. 数据库角色成员资格下检查webservice : SUSDB:  image12Check webservice under the Database role membership for: SUSDB: image12

  6. 单击 "确定" 保存设置。Click OK to save settings.

    备注

    可能需要重新启动 SQL 服务,更改才能生效。You may need to restart the SQL Service for the changes to take effect.

编辑注册表以将 WSUS 指向 SQL Server 实例Edit the registry to point WSUS to the SQL Server Instance

重要

请认真遵循本部分所述的步骤。Follow the steps in this section carefully. 如果注册表修改不正确,可能会发生严重问题。Serious problems might occur if you modify the registry incorrectly. 在修改注册表之前,请备份注册表,以便在出现问题时可以还原。Before you modify it, back up the registry for restoration in case problems occur.

  1. 单击“开始”,再单击“运行”,键入“regedit& ”,然后单击“确定”。Click Start, click Run, type regedit, and then click OK.

  2. 找到以下注册表项: HKEY_LOCAL_MACHINE \software\microsoft\updateservices\server\setup\sqlservernameLocate the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName

  3. 在 " " 文本框中,键入 [ServerName] \ [InstanceName],然后单击 "确定"In the Value text box, type [ServerName]\[InstanceName], and then click OK. 如果实例名称是默认实例,则键入 [ServerName]If the instance name is the default instance, type [ServerName].

  4. 找到以下注册表项: HKEY_LOCAL_MACHINE \Software\microsoft\update Services\Server\Setup\Installed Role Services\UpdateServices-WidDatabase  image13Locate the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\Installed Role Services\UpdateServices-WidDatabase image13

  5. 将密钥重命名为 updateservices-api-Database  image41Rename the Key to UpdateServices-Database image41

    备注

    如果不更新此密钥, wsusutil.exe 将尝试为 WID 而不是已迁移到的 SQL 实例服务。If you do not update this key, then WsusUtil will attempt to service the WID rather than the SQL Instance to which you have migrated.

在 WSUS 服务器上启动 IIS 和 WSUS 服务Start the IIS and WSUS services on the WSUS server

从 PowerShell (提升的) 中,运行:From PowerShell (elevated), run:

    Start-Service IISADMIN
    Start-Service WsusService

备注

如果你使用的是 WSUS 控制台,请关闭并重新启动它。If you are using the WSUS Console, close and restart it.

警告

删除 WID 角色还会删除 (%SystemDrive%\Program Files\Update Services\Database) 的数据库文件夹,其中包含 WSUSUtil.exe 进行安装后任务所需的脚本。Removing the WID role also removes a database folder (%SystemDrive%\Program Files\Update Services\Database) that contains scripts required by WSUSUtil.exe for post-installation tasks. 如果选择卸载 WID 角色,请确保事先备份 %SystemDrive%\Program Files\Update Services\Database 文件夹。If you choose to uninstall the WID role, make sure you back up the %SystemDrive%\Program Files\Update Services\Database folder beforehand.

使用 PowerShell:Using PowerShell:

Uninstall-WindowsFeature -Name 'Windows-Internal-Database'

删除 WID 角色后,验证是否存在以下注册表项: HKEY_LOCAL_MACHINE \Software\microsoft\update Services\Server\Setup\Installed role Services\UpdateServices-DatabaseAfter the WID role is removed, verify that the following registry key is present: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\Installed Role Services\UpdateServices-Database