SQL 资源提供程序维护操作SQL resource provider maintenance operations

SQL 资源提供程序在锁定的虚拟机上运行。The SQL resource provider runs on a locked down virtual machine. 若要启用维护操作,需要更新虚拟机的安全性。To enable maintenance operations, you need to update the virtual machine's security. 若要使用“最低特权”原则执行此操作,可以使用 PowerShell Just Enough Administration (JEA) 终结点 DBAdapterMaintenanceTo do this using the principal of Least Privilege, you can use PowerShell Just Enough Administration (JEA) endpoint DBAdapterMaintenance. 资源提供程序安装包包含此操作的脚本。The resource provider installation package includes a script for this operation.

修补和更新Patching and updating

不能将 SQL 资源提供程序作为 Azure Stack 的一部分进行维护,因为它是一个加载项组件。The SQL resource provider isn't serviced as part of Azure Stack because it's an add-on component. Microsoft 会根据需要为 SQL 资源提供程序提供更新。Microsoft provides updates to the SQL resource provider as necessary. 发布更新的 SQL 适配器后,会提供一个脚本来应用更新。When an updated SQL adapter is released, a script is provided to apply the update. 此脚本创建新的资源提供程序 VM,并将旧提供程序 VM 的状态迁移到新 VM。This script creates a new resource provider VM, migrating the state of the old provider VM to the new VM. 有关详细信息,请参阅更新 SQL 资源提供程序For more information, see Update the SQL resource provider.

提供程序虚拟机Provider virtual machine

由于资源提供程序在用户虚拟机上运行,因此需要应用已发布的修补升级。 Because the resource provider runs on a user virtual machine, you need to apply the required patches and updates when they're released. 可以使用修补升级周期提供的 Windows 更新包将更新应用到 VM。You can use the Windows update packages that are provided as part of the patch-and-update cycle to apply updates to the VM.

更新 SQL 凭据Updating SQL credentials

你需要负责在 SQL 服务器上创建和维护 sysadmin 帐户。You're responsible for creating and maintaining sysadmin accounts on your SQL servers. 资源提供程序需要拥有这些特权的帐户才能代表用户管理数据库,但无需访问用户的数据。The resource provider needs an account with these privileges to manage databases for users, but it doesn't need access to the users' data. 如果需要更新 SQL 服务器上的 sysadmin 密码,可以使用资源提供程序的管理员界面来更改存储的密码。If you need to update the sysadmin passwords on your SQL servers, you can use the resource provider's administrator interface to change a stored password. 这些密码存储在 Azure Stack 实例上的 Key Vault 中。These passwords are stored in a Key Vault on your Azure Stack instance.

若要修改设置,请选择“浏览”>“管理资源”>“SQL 宿主服务器”>“SQL 登录”并选择用户名。 To modify the settings, select Browse > ADMINISTRATIVE RESOURCES > SQL Hosting Servers > SQL Logins and select a user name. 必须先在 SQL 实例上(必要时还需要在任何副本上)进行更改。在“设置”下,选择“密码”。 The change must be made on the SQL instance first (and any replicas, if necessary.) Under Settings, select Password.

更新管理密码

机密轮换Secrets rotation

这些说明仅适用于 Azure Stack 集成系统。These instructions only apply to Azure Stack Integrated Systems.

在 Azure Stack 集成系统中使用 SQL 和 MySQL 资源提供程序时,Azure Stack 操作员负责轮换以下资源提供程序基础结构机密以确保它们不会过期:When using the SQL and MySQL resource providers with Azure Stack integrated systems, the Azure Stack operator is responsible for rotating the following resource provider infrastructure secrets to ensure that they do not expire:

  • 部署期间提供的外部 SSL 证书。External SSL Certificate provided during deployment.
  • 部署期间提供的资源提供程序 VM 本地管理员帐户密码。The resource provider VM local administrator account password provided during deployment.
  • 资源提供程序诊断用户 (dbadapterdiag) 密码。Resource provider diagnostic user (dbadapterdiag) password.

用于轮换机密的 PowerShell 示例PowerShell examples for rotating secrets

同时更改所有机密。Change all the secrets at the same time.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DiagnosticsUserPassword $passwd `
    -DependencyFilesLocalPath $certPath `
    -DefaultSSLCertificatePassword $certPasswd  `
    -VMLocalCredential $localCreds

更改诊断用户密码。Change the diagnostic user password.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DiagnosticsUserPassword  $passwd

更改 VM 本地管理员帐户密码。Change the VM local administrator account password.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -VMLocalCredential $localCreds

更改 SSL 证书密码。Change the SSL certificate password.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DependencyFilesLocalPath $certPath `
    -DefaultSSLCertificatePassword $certPasswd

SecretRotationSQLProvider.ps1 参数SecretRotationSQLProvider.ps1 parameters

参数Parameter 说明Description
AzCredentialAzCredential Azure Stack 服务管理员帐户凭据。Azure Stack Service Admin account credential.
CloudAdminCredentialCloudAdminCredential Azure Stack 云管理域帐户凭据。Azure Stack cloud admin domain account credential.
PrivilegedEndpointPrivilegedEndpoint 用于访问 Get-AzureStackStampInformation 的特权终结点。Privileged Endpoint to access Get-AzureStackStampInformation.
DiagnosticsUserPasswordDiagnosticsUserPassword 诊断用户帐户密码。Diagnostics user account password.
VMLocalCredentialVMLocalCredential MySQLAdapter VM 上的本地管理员帐户。Local administrator account on the MySQLAdapter VM.
DefaultSSLCertificatePasswordDefaultSSLCertificatePassword 默认 SSL 证书 (*pfx) 密码。Default SSL Certificate (*pfx) password.
DependencyFilesLocalPathDependencyFilesLocalPath 依赖项文件本地路径。Dependency files local path.

已知问题Known issues

问题:机密轮换日志。Issue: Secrets rotation logs.
如果机密轮换自定义脚本在运行时失败,则不会自动收集机密轮换的日志。The logs for secrets rotation aren't automatically collected if the secret rotation custom script fails when it is run.

解决方法Workaround:
使用 Get-AzsDBAdapterLogs cmdlet 收集所有资源提供程序日志,包括 C:\Logs 中保存的 AzureStack.DatabaseAdapter.SecretRotation.ps1_*.log。Use the Get-AzsDBAdapterLogs cmdlet to collect all resource provider logs, including AzureStack.DatabaseAdapter.SecretRotation.ps1_*.log, saved in C:\Logs.

更新虚拟机操作系统Update the virtual machine operating system

使用以下方法之一更新虚拟机操作系统。Use one of the following methods to update the virtual machine operating system.

  • 使用当前进行了修补的 Windows Server 2016 Core 映像安装最新的资源提供程序包。Install the latest resource provider package using a currently patched Windows Server 2016 Core image.
  • 在安装或更新资源提供程序期间安装 Windows 更新包。Install a Windows Update package during the installation of, or update to, the resource provider.

更新虚拟机 Windows Defender 定义Update the virtual machine Windows Defender definitions

更新 Windows Defender 定义:To update the Windows Defender definitions:

  1. Windows Defender 定义下载 Windows Defender 定义更新Download the Windows Defender definitions update from Windows Defender Definition.

    在定义更新页上,向下滚动到“手动下载并安装定义”。On the definitions update page, scroll down to "Manually download and install the definitions". 下载“适用于 Windows 10 和 Windows 8.1 的 Windows Defender Antivirus”64 位文件。Download the "Windows Defender Antivirus for Windows 10 and Windows 8.1" 64-bit file.

    或者,使用此直接链接下载/运行 fpam-fe.exe 文件。Alternatively, use this direct link to download/run the fpam-fe.exe file.

  2. 与 SQL 资源提供程序适配器虚拟机的维护终结点建立 PowerShell 会话。Create a PowerShell session to the SQL resource provider adapter virtual machine's maintenance endpoint.

  3. 使用维护终结点会话将定义更新文件复制到虚拟机。Copy the definitions update file to the virtual machine using the maintenance endpoint session.

  4. 在维护 PowerShell 会话中,运行 Update-DBAdapterWindowsDefenderDefinitions 命令。On the maintenance PowerShell session, run the Update-DBAdapterWindowsDefenderDefinitions command.

  5. 安装定义之后,我们建议使用 Remove-ItemOnUserDrive 命令删除定义更新文件。After you install the definitions, we recommend that you delete the definitions update file by using the Remove-ItemOnUserDrive command.

用于更新定义的 PowerShell 脚本示例。PowerShell script example for updating definitions.

可以编辑并运行以下脚本来更新 Defender 定义。You can edit and run the following script to update the Defender definitions. 将脚本中的值替换为环境中的值。Replace values in the script with values from your environment.

# Set credentials for local admin on the resource provider VM.
$vmLocalAdminPass = ConvertTo-SecureString "<local admin user password>" -AsPlainText -Force
$vmLocalAdminUser = "<local admin user name>"
$vmLocalAdminCreds = New-Object System.Management.Automation.PSCredential `
    ($vmLocalAdminUser, $vmLocalAdminPass)

# Provide the public IP address for the adapter VM.
$databaseRPMachine  = "<RP VM IP address>"
$localPathToDefenderUpdate = "C:\DefenderUpdates\mpam-fe.exe"

# Download the Windows Defender update definitions file from https://www.microsoft.com/en-us/wdsi/definitions.
Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?LinkID=121721&arch=x64' `
    -Outfile $localPathToDefenderUpdate

# Create a session to the maintenance endpoint.
$session = New-PSSession -ComputerName $databaseRPMachine `
    -Credential $vmLocalAdminCreds -ConfigurationName DBAdapterMaintenance
# Copy the defender update file to the adapter virtual machine.
Copy-Item -ToSession $session -Path $localPathToDefenderUpdate `
     -Destination "User:\"
# Install the update definitions.
Invoke-Command -Session $session -ScriptBlock `
    {Update-AzSDBAdapterWindowsDefenderDefinition -DefinitionsUpdatePackageFile "User:\mpam-fe.exe"}
# Cleanup the definitions package file and session.
Invoke-Command -Session $session -ScriptBlock `
    {Remove-AzSItemOnUserDrive -ItemPath "User:\mpam-fe.exe"}
$session | Remove-PSSession

收集诊断日志Collect diagnostic logs

若要从锁定的虚拟机收集日志,可以使用 PowerShell Just Enough Administration (JEA) 终结点 DBAdapterDiagnosticsTo collect logs from the locked down virtual machine, you can use the PowerShell Just Enough Administration (JEA) endpoint DBAdapterDiagnostics. 此终结点提供以下命令:This endpoint provides the following commands:

  • Get-AzsDBAdapterLogGet-AzsDBAdapterLog. 此命令创建资源提供程序诊断日志的 zip 包,并将文件保存在会话的用户驱动器上。This command creates a zip package of the resource provider diagnostics logs and saves the file on the session's user drive. 可以不带任何参数运行此命令,收集过去四小时的日志。You can run this command without any parameters and the last four hours of logs are collected.
  • Remove-AzsDBAdapterLogRemove-AzsDBAdapterLog. 此命令删除资源提供程序 VM 上的现有日志包。This command removes existing log packages on the resource provider VM.

终结点要求和过程Endpoint requirements and process

安装或更新资源提供程序时,将创建 dbadapterdiag 用户帐户。When a resource provider is installed or updated, the dbadapterdiag user account is created. 此帐户用于收集诊断日志。You'll use this account to collect diagnostic logs.

Note

dbadapterdiag 帐户密码与部署或更新提供程序期间在虚拟机上创建的本地管理员所用的密码相同。The dbadapterdiag account password is the same as the password used for the local administrator on the virtual machine that's created during a provider deployment or update.

若要使用 DBAdapterDiagnostics 命令,请与资源提供程序虚拟机建立远程 PowerShell 会话,然后运行 Get-AzsDBAdapterLog 命令。To use the DBAdapterDiagnostics commands, create a remote PowerShell session to the resource provider virtual machine and run the Get-AzsDBAdapterLog command.

使用 FromDateToDate 参数设置日志收集的时间跨度。You set the time span for log collection by using the FromDate and ToDate parameters. 如果未指定上述一个或两个参数,将使用以下默认值:If you don't specify one or both of these parameters, the following defaults are used:

  • FromDate 为当前时间之前的四个小时。FromDate is four hours before the current time.
  • ToDate 为目前时间。ToDate is the current time.

用于收集日志的 PowerShell 脚本示例。PowerShell script example for collecting logs.

以下脚本演示如何从资源提供程序 VM 收集诊断日志。The following script shows how to collect diagnostic logs from the resource provider VM.

# Create a new diagnostics endpoint session.
$databaseRPMachineIP = '<RP VM IP address>'
$diagnosticsUserName = 'dbadapterdiag'
$diagnosticsUserPassword = '<Enter Diagnostic password>'

$diagCreds = New-Object System.Management.Automation.PSCredential `
        ($diagnosticsUserName, (ConvertTo-SecureString -String $diagnosticsUserPassword -AsPlainText -Force))
$session = New-PSSession -ComputerName $databaseRPMachineIP -Credential $diagCreds `
        -ConfigurationName DBAdapterDiagnostics

# Sample that captures logs from the previous hour.
$fromDate = (Get-Date).AddHours(-1)
$dateNow = Get-Date
$sb = {param($d1,$d2) Get-AzSDBAdapterLog -FromDate $d1 -ToDate $d2}
$logs = Invoke-Command -Session $session -ScriptBlock $sb -ArgumentList $fromDate,$dateNow

# Copy the logs to the user drive.
$sourcePath = "User:\{0}" -f $logs
$destinationPackage = Join-Path -Path (Convert-Path '.') -ChildPath $logs
Copy-Item -FromSession $session -Path $sourcePath -Destination $destinationPackage

# Cleanup the logs.
$cleanup = Invoke-Command -Session $session -ScriptBlock {Remove- AzsDBAdapterLog }
# Close the session.
$session | Remove-PSSession

后续步骤Next steps

添加 SQL Server 宿主服务器Add SQL Server hosting servers