使用 Azure Stack Hub 创建高可用 SQL 数据库Create highly available SQL databases with Azure Stack Hub

作为 Azure Stack Hub 操作员,你可以配置服务器 VM 来承载 SQL Server 数据库。As an Azure Stack Hub Operator, you can configure server VMs to host SQL Server databases. 通过 Azure Stack Hub 创建和管理 SQL 宿主服务器后,订阅了 SQL 服务的用户可以轻松地创建 SQL 数据库。After a SQL hosting server is created and managed by Azure Stack Hub, users who have subscribed to SQL services can easily create SQL databases.

本文介绍了如何使用 Azure Stack Hub 快速入门模板创建 SQL Server AlwaysOn 可用性组,将其添加为 Azure Stack Hub SQL 宿主服务器,然后创建高可用 SQL 数据库。This article shows how to use an Azure Stack Hub quickstart template to create a SQL Server AlwaysOn availability group, add it as an Azure Stack Hub SQL Hosting Server, and then create a highly available SQL database.

学习内容:What you'll learn:

  • 基于模板创建 SQL Server AlwaysOn 可用性组。Create a SQL Server AlwaysOn availability group from a template.
  • 将 SQL Server AlwaysOn 可用性组配置为 Azure Stack Hub SQL 宿主服务器。Configure the SQL Server AlwaysOn availability group as an Azure Stack Hub SQL Hosting Server.
  • 创建高可用 SQL 数据库。Create a highly available SQL database.

将使用可用的 Azure Stack 市场项创建并配置包含两个 VM SQL Server 的 AlwaysOn 可用性组。A two VM SQL Server AlwaysOn availability group will be created and configured using available Azure Stack Marketplace items.

在开始之前,请确保已成功安装了 SQL Server 资源提供程序并且以下项在 Azure Stack 市场中可用:Before starting, ensure that the SQL Server resource provider has been successfully installed and the following items are available in Azure Stack Marketplace:

重要

要使用 Azure Stack Hub 快速入门模板,需要备齐以下所有项。All of the following are required for the Azure Stack Hub quickstart template to be used.

  • Windows Server 2016 Datacenter。Windows Server 2016 Datacenter.
  • Windows Server 2016 服务器映像上的 SQL Server 2016 SP1 或 SP2(企业版、标准版或开发人员版)。SQL Server 2016 SP1 or SP2 (Enterprise, Standard, or Developer) on Windows Server 2016 server image.
  • SQL Server IaaS 扩展 1.3.20180 或更高版本。SQL Server IaaS Extension version 1.3.20180 or higher. SQL IaaS 扩展会为所有 Windows 版本安装市场 SQL Server 项所需的必要组件。The SQL IaaS Extension installs necessary components that are required by the Marketplace SQL Server items for all Windows versions. 它允许在 SQL 虚拟机 (VM) 上配置 SQL 特定的设置。It enables SQL-specific settings to be configured on SQL virtual machines (VMs). 如果未在本地市场安装该扩展,则 SQL 的预配将会失败。If the extension isn't installed in the local marketplace, provisioning of SQL will fail.
  • 适用于 Windows 的自定义脚本扩展 1.9.1 或更高版本。Custom script extension for Windows version 1.9.1 or higher. 自定义脚本扩展是一种工具,可用于自动启动部署后的 VM 自定义任务。Custom Script Extension is a tool that can be used to automatically launch post-deployment VM customization tasks.
  • PowerShell Desired State Configuration (DSC) 2.76.0.0 或更高版本。PowerShell Desired State Configuration (DSC) version 2.76.0.0 or higher. DSC 是 Windows PowerShell 中的一个管理平台,可用于部署和管理软件服务的配置数据。DSC is a management platform in Windows PowerShell that enables deploying and managing configuration data for software services. 该平台还管理这些服务的运行环境。The platform also manages the environment in which these services run.

若要详细了解如何向 Azure Stack 市场中添加项,请参阅 Azure Stack Hub 市场概述To learn more about adding items to Azure Stack Marketplace, see the Azure Stack Hub Marketplace overview.

创建 SQL Server AlwaysOn 可用性组Create a SQL Server AlwaysOn availability group

执行本部分中的步骤,通过使用 sql-2016-alwayson Azure Stack Hub 快速入门模板来部署 SQL Server AlwaysOn 可用性组。Use the steps in this section to deploy the SQL Server AlwaysOn availability group by using the sql-2016-alwayson Azure Stack Hub quickstart template. 此模板在 Always On 可用性组中部署两个 SQL Server Enterprise 或 Developer 实例。This template deploys two SQL Server Enterprise or Developer instances in an Always On Availability Group. 它创建以下资源:It creates the following resources:

  • 一个网络安全组。A network security group.
  • 一个虚拟网络。A virtual network.
  • 四个存储帐户(一个用于 Active Directory (AD),一个用于 SQL,一个用于文件共享见证,另一个用于 VM 诊断)。Four storage accounts (one for Active Directory (AD), one for SQL, one for file share witness, and one for VM diagnostics).
  • 四个公共 IP 地址(一个用于 AD,两个用于单个 SQL VM,另一个用于绑定到 SQL AlwaysOn 侦听器的公共负载均衡器)。Four public IP addresses (one for AD, two for each SQL VM, and one for public load balancer bound to SQL AlwaysOn listener).
  • 一个用于 SQL VM 的外部负载均衡器,该 VM 的公共 IP 已绑定到 SQL AlwaysOn 侦听器。One external load balancer for SQL VMs with Public IP bound to the SQL AlwaysOn listener.
  • 一个 VM (Windows Server 2016),配置为包含单个域的新林的域控制器。One VM (Windows Server 2016) configured as Domain Controller for a new forest with a single domain.
  • 两个 VM (Windows Server 2016),配置有 SQL Server 2016 SP1 或 SP2 Enterprise 或 Developer Edition 并加入了群集。Two VMs (Windows Server 2016) configured with SQL Server 2016 SP1 or SP2 Enterprise or Developer Edition and clustered. 它们必须是市场映像。These must be marketplace images.
  • 一个 VM (Windows Server 2016),配置为群集的文件共享见证。One VM (Windows Server 2016) configured as the file share witness for the cluster.
  • 一个包含 SQL 和文件共享见证 VM 的可用性集。One availability set containing the SQL and file share witness VMs.
  1. 登录到用户门户:Sign in to the user portal:

    • 对于集成系统部署,门户地址将根据解决方案的区域和外部域名而有所不同。For an integrated system deployment, the portal address will vary based on your solution's region and external domain name. 它将采用 https://portal.<region>.<FQDN> 格式。It will be in the format of https://portal.<region>.<FQDN>.
    • 对于 Azure Stack 开发工具包 (ASDK),门户地址为 https://portal.local.azurestack.externalFor the Azure Stack Development Kit (ASDK), the portal address is https://portal.local.azurestack.external.
  2. 选择“+ 创建资源” > “自定义”,然后选择“模板部署”。 Select + Create a resource > Custom, and then Template deployment.

    Azure Stack Hub 管理员门户中的自定义模板部署

  3. 在“自定义部署”边栏选项卡上,选择“编辑模板” > “快速入门模板”,然后在可用自定义模板的下拉列表中选择“sql-2016-alwayson”模板。 On the Custom deployment blade, select Edit template > Quickstart template and then use the drop-down list of available custom templates to select the sql-2016-alwayson template. 选择“确定”,然后选择“保存”。Select OK, then Save.

    在 Azure Stack Hub 管理员门户中编辑模板Edit template in Azure Stack Hub administrator portal

  4. 在“自定义部署”边栏选项卡上,选择“编辑参数”并查看默认值。 On the Custom deployment blade, select Edit parameters and review the default values. 根据需要修改这些值以提供全部所需的参数信息,然后选择“确定”。Modify the values as necessary to provide all required parameter information and then select OK.

    至少:At a minimum:

    • 为 ADMINPASSWORD、SQLSERVERSERVICEACCOUNTPASSWORD 和 SQLAUTHPASSWORD 参数提供复杂的密码。Provide complex passwords for the ADMINPASSWORD, SQLSERVERSERVICEACCOUNTPASSWORD, and SQLAUTHPASSWORD parameters.
    • 对于 DNSSUFFIX 参数,以全小写字母输入用于反向查找的 DNS 后缀(对于 ASDK 安装,此后缀为 azurestack.external)。Enter the DNS Suffix for reverse lookup in all lowercase letters for the DNSSUFFIX parameter (azurestack.external for ASDK installations).

    在 Azure Stack Hub 管理员门户中编辑参数Edit parameters in Azure Stack Hub administrator portal

  5. 在“自定义部署”边栏选项卡上,选择要使用的订阅,然后为自定义部署创建新的资源组或选择现有的资源组。On the Custom deployment blade, choose the subscription to use and create a new resource group or select an existing resource group for the custom deployment.

    接下来,选择资源组的位置(对于 ASDK 安装,请选择“本地”),然后单击“创建”。 Next, select the resource group location (local for ASDK installations) and then click Create. 系统将验证自定义部署设置,然后开始部署。The custom deployment settings will be validated and then the deployment will start.

    在 Azure Stack Hub 管理员门户中选择订阅Choose subscription in Azure Stack Hub administrator portal

  6. 在用户门户中,选择“资源组”,然后选择针对自定义部署创建的资源组的名称(在本示例中为 resource-group)。In the user portal, select Resource groups and then the name of the resource group you created for the custom deployment (resource-group for this example). 查看部署状态,确保所有部署已成功完成。View the status of the deployment to ensure all deployments have completed successfully.

    接下来,查看资源组项,并选择 SQLPIPsql<resource group name> 公共 IP 地址项。Next, review the resource group items and select the SQLPIPsql<resource group name> public IP address item. 记录负载均衡器的公共 IP 地址和完整 FQDN。Record the public IP address and full FQDN of the load balancer public IP. 你需要将此信息提供给 Azure Stack Hub 操作员,以便他们可以利用此 SQL AlwaysOn 可用性组创建一个 SQL 宿主服务器。You'll need to provide this to an Azure Stack Hub operator so they can create a SQL hosting server leveraging this SQL AlwaysOn availability group.

    备注

    模板部署需要几个小时才能完成。The template deployment will take several hours to complete.

启用自动种子设定Enable automatic seeding

在模板成功部署并配置 SQL AlwaysON 可用性组之后,必须在可用性组中的每个 SQL Server 实例上启用自动种子设定After the template has successfully deployed and configured the SQL AlwaysON availability group, you must enable automatic seeding on each instance of SQL Server in the availability group.

当你创建可以自动设定种子的可用性组时,SQL Server 会自动为该组中的每个数据库创建次要副本,不需要你进行任何其他的手动干预。When you create an availability group with automatic seeding, SQL Server automatically creates the secondary replicas for every database in the group without any other manual intervention necessary. 此度量值可确保 AlwaysOn 数据库的高可用性。This measure ensures high availability of AlwaysOn databases.

使用这些 SQL 命令为 AlwaysOn 可用性组配置自动种子设定功能。Use these SQL commands to configure automatic seeding for the AlwaysOn availability group. 根据需要,将 <PrimaryInstanceName> 替换为主实例 SQL Server 名称,将 <SecondaryInstanceName> 替换为辅助实例 SQL Server 名称,将 <availability_group_name> 替换为 AlwaysOn 可用性组名称。Replace <PrimaryInstanceName> with the primary instance SQL Server name, <SecondaryInstanceName> with the secondary instance SQL Server name and <availability_group_name> with the AlwaysOn availability group name as necessary.

在主 SQL 实例上:On the primary SQL instance:

ALTER AVAILABILITY GROUP [<availability_group_name>]
    MODIFY REPLICA ON '<PrimaryInstanceName>'
    WITH (SEEDING_MODE = AUTOMATIC)
GO

ALTER AVAILABILITY GROUP [<availability_group_name>]
    MODIFY REPLICA ON '<SecondaryInstanceName>'
    WITH (SEEDING_MODE = AUTOMATIC)
GO

主 SQL 实例脚本

在辅助 SQL 实例上:On secondary SQL instances:

ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
GO

辅助 SQL 实例脚本

配置包含的数据库身份验证Configure contained database authentication

将包含的数据库添加到可用性组之前,请确保在托管可用性组可用性副本的每个服务器实例上,包含的数据库身份验证服务器选项已设置为 1。Before adding a contained database to an availability group, ensure that the contained database authentication server option is set to 1 on every server instance that hosts an availability replica for the availability group. 有关详细信息,请参阅包含的数据库身份验证For more information, see contained database authentication.

使用以下命令为可用性组中的每个 SQL Server 实例设置包含的数据库身份验证服务器选项:Use these commands to set the contained database authentication server option for each SQL Server instance in the availability group:

EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

设置包含的数据库身份验证

配置 Azure Stack Hub SQL 宿主服务器Configure an Azure Stack Hub SQL Hosting Server

创建并正确配置 SQL Server AlwayOn 可用性组后,Azure Stack Hub 操作员必须将其配置为 Azure Stack Hub SQL 宿主服务器。After the SQL Server AlwayOn availability group has been created and properly configured, an Azure Stack Hub operator has to configure it as an Azure Stack Hub SQL Hosting Server.

在创建 SQL AlwaysOn 可用性组的资源组 (SQLPIPsql<resource group name> ) 时,请确保使用先前记录的 SQL 负载均衡器的公共 IP 或完整 FQDN。Be sure to use the public IP or full FQDN for the public IP of the SQL load balancer recorded previously when the SQL AlwaysOn availability group's resource group was created (SQLPIPsql<resource group name>). 此外,你还需要知道用于访问 AlwaysOn 可用性组中的 SQL 实例的 SQL Server 身份验证凭据。In addition, you need to know the SQL Server authentication credentials used to access the SQL instances in the AlwaysOn availability group.

备注

此步骤必须由 Azure Stack Hub 操作员从 Azure Stack Hub 管理员门户运行。This step must be run from the Azure Stack Hub administrator portal by an Azure Stack Hub operator.

使用 SQL AlwaysOn 可用性组的负载均衡器侦听器公共 IP 和 SQL 身份验证登录信息,Azure Stack Hub 操作员可以使用 SQL AlwaysOn 可用性组创建 SQL 宿主服务器With the SQL AlwaysOn availability group's load balancer listener public IP and SQL authentication login information, an Azure Stack Hub operator can create a SQL Hosting Server using the SQL AlwaysOn availability group.

此外,请确保你已创建计划和套餐,使得用户可以创建 SQL AlwaysOn 数据库。Also ensure that you have created plans and offers to make SQL AlwaysOn database creation available for users. 操作员需要将 Microsoft.SqlAdapter 服务添加到计划并专门为高可用数据库创建新配额。The operator will need to add the Microsoft.SqlAdapter service to a plan and create a new quota specifically for highly available databases. 有关创建计划的详细信息,请参阅服务、计划、套餐和订阅概述For more information about creating plans, see Service, plan, offer, subscription overview.

提示

部署 SQL Server 资源提供程序之前,无法将 Microsoft.sqladapter 服务添加到计划中。The Microsoft.SqlAdapter service won't be available to add to plans until the SQL Server resource provider has been deployed.

创建高可用 SQL 数据库Create a highly available SQL database

在 Azure Stack Hub 操作员创建、配置并添加 SQL AlwaysOn 可用性组作为 Azure Stack Hub SQL 宿主服务器之后,订阅中包含 SQL Server 数据库功能的租户用户可以创建支持 AlwaysOn 功能的 SQL 数据库。After the SQL AlwaysOn availability group has been created, configured, and added as an Azure Stack Hub SQL Hosting Server by an Azure Stack Hub operator, a tenant user with a subscription including SQL Server database capabilities can create SQL databases supporting AlwaysOn functionality. 他们可以按照本部分中的步骤创建这些数据库。They can create those databases by following the steps in this section.

备注

从 Azure Stack Hub 用户门户以订阅中提供了 SQL Server 功能(Microsoft.SQLAdapter 服务)的租户用户身份运行这些步骤。Run these steps from the Azure Stack Hub user portal as a tenant user with a subscription providing SQL Server capabilities (Microsoft.SQLAdapter service).

  1. 登录到用户门户:Sign in to the user portal:

    • 对于集成系统部署,门户地址将根据解决方案的区域和外部域名而有所不同。For an integrated system deployment, the portal address will vary based on your solution's region and external domain name. 它将采用 https://portal.<region>.<FQDN> 格式。It will be in the format of https://portal.<region>.<FQDN>.
    • 对于 Azure Stack 开发工具包 (ASDK),门户地址为 https://portal.local.azurestack.externalFor the Azure Stack Development Kit (ASDK), the portal address is https://portal.local.azurestack.external.
  2. 选择“+ 创建资源” > “数据 + 存储”,然后选择“SQL 数据库”。 Select + Create a resource > Data + Storage, and then SQL Database.

    提供所需的数据库属性信息。Provide the required database property information. 此信息包括名称、排序规则、最大大小,以及要用于部署的订阅、资源组和位置。This info includes name, collation, maximum size, and the subscription, resource group, and location to use for the deployment.

    在 Azure Stack Hub 用户门户中创建 SQL 数据库

  3. 选择“SKU”,然后选择要使用的相应 SQL 宿主服务器 SKU。Select SKU and then choose the appropriate SQL Hosting Server SKU to use. 在此示例中,Azure Stack Hub 操作员创建了“企业高可用性”SKU,以支持 SQL AlwaysOn 可用性组的高可用性。In this example, the Azure Stack Hub operator has created the Enterprise-HA SKU to support high availability for SQL AlwaysOn availability groups.

    在 Azure Stack Hub 用户门户中选择 SKU

  4. 选择“登录名” > “创建新的登录名”,然后提供要用于新数据库的 SQL 身份验证凭据。 Select Login > Create a new login and then provide the SQL authentication credentials to be used for the new database. 完成后,选择“确定”,然后选择“创建”以开始数据库部署过程。When finished, select OK and then Create to begin the database deployment process.

    在 Azure Stack Hub 用户门户中创建登录名

  5. 当 SQL 数据库部署成功完成时,查看数据库属性以发现用于连接到新的高可用数据库的连接字符串。When the SQL database deployment completes successfully, review the database properties to discover the connection string to use for connecting to the new highly available database.

    在 Azure Stack Hub 用户门户中查看连接字符串

后续步骤Next steps

更新 SQL 资源提供程序Update the SQL resource provider