在 Compute Engine 上部署多子网 SQL Server 2016 Always On 可用性组

本教程是系列教程中的第二篇，可帮助您使用 Microsoft Active Directory、SQL Server 2016 和 Internet Information Services (IIS) 在 Google Cloud 上部署高可用性 Windows 环境。在本教程中，您将在同一区域的不同地区中设置两个 SQL Server 实例，并将它们配置为多子网 Always On SQL Server 可用性组。

本系列教程包含以下内容：

每个教程都基于您在上一个教程中创建的基础架构。

目标

创建跨两个地区的两个网络子网。
在每个地区中部署 Microsoft SQL Server 2016 虚拟机 (VM)。
配置每个 SQL Server 实例以加入 Active Directory 网域。
创建 Windows Server 故障切换集群。
创建和配置 SQL Server bookshelf 示例数据库。
设置 SQL Server Always On 可用性组。

费用

在本教程中，您将继续使用在之前教程中配置的 Google Cloud 资源。您会使用以下收费组件：

经价格计算器估算，此环境的费用约为 $98/天，包括 SQL Server 2016 Enterprise 许可和双服务器 Active Directory 环境。

准备工作

您在本教程中使用的配置要求您能够访问一个 Windows 网域控制器和一个正在运行的 Active Directory 网域。如果您还没有此环境，请完成以下教程中的步骤：

初始化通用变量

您必须定义几个用于控制在何处部署基础架构元素的环境变量。

使用文本编辑器创建一个脚本，设置以下环境变量以指定项目 ID，以及要使用的区域和地区。

此变量将区域设置为 us-east1。如果您在上一个教程中使用了其他区域，请更改此脚本中的区域以匹配之前使用的区域。
```
region=us-east1
zone_1=${region}-b
zone_2=${region}-c
vpc_name=webappnet
project_id=your-project-id
```
将 your-project-id 替换为您正在使用的 Google Cloud 项目的 ID。
打开 Cloud Shell：

打开 Cloud Shell
将脚本复制到 Cloud Shell 窗口并运行。
设置默认区域和项目 ID，这样您就不必在每条后续命令中都指定这些值：
```
gcloud config set compute/region ${region}
gcloud config set project ${project_id}
```

创建网络基础架构

定义基础架构变量后，即可创建 SQL Server 使用的网络子网。

将新子网添加到现有虚拟私有云 (VPC) 网络：

gcloud compute networks subnets create private-sql-1 \
    --network ${vpc_name} \
    --range 10.3.0.0/24

gcloud compute networks subnets create private-sql-2 \
    --network ${vpc_name} \
    --range 10.4.0.0/24

创建内部防火墙规则，以允许您先前创建的 private-ad* 子网与新的 private-sql* 子网之间进行流量传输：
```
gcloud compute firewall-rules create allow-internal-ports-private-sql \
    --network ${vpc_name} \
    --allow tcp:0-65535,udp:0-65535,icmp \
    --source-ranges 10.4.0.0/24,10.3.0.0/24,10.2.0.0/24,10.1.0.0/24
```
注意：在生产环境中，最佳做法是保护系统当前未使用的所有端口。通过堡垒主机保护对计算机的访问也是一种最佳做法。

创建 SQL Server 实例

接下来，使用具有以下属性的 Compute Engine 预配置映像创建两个 SQL Server 2016 Enterprise 版本的虚拟机：

名称：bookshelf-sql1 和 bookshelf-sql2
启动磁盘大小：200GB
机器类型：n1-highmem-4

如果您打算在本教程之外使用此环境并需要额外的计算资源，则可以稍后为这些实例自定义机器类型。您还可以添加磁盘并调整现有永久性磁盘的大小。

在本教程中，您将为虚拟机使用 Windows Server 公共映像上的 SQL Server，这意味着您将需要为 SQL Server 的用量付费。您还可以通过软件保障涵盖的转授许可服务将现有 SQL Server 许可部署到 Compute Engine，并在任何 Windows Server 公共映像上安装 SQL Server 后应用这些许可。

使用 SQL Server 2016 Enterprise 创建 Windows Server 2016 的 Compute Engine 实例：

gcloud compute instances create bookshelf-sql1 \
    --machine-type n1-highmem-4 \
    --boot-disk-type pd-ssd \
    --boot-disk-size 200GB \
    --image-project windows-sql-cloud \
    --image-family sql-ent-2016-win-2016 \
    --zone ${zone_1} \
    --network-interface=network=${vpc_name},subnet=private-sql-1,private-network-ip=10.3.0.2,aliases=10.3.0.4/31

创建第二个实例：

gcloud compute instances create bookshelf-sql2 \
    --machine-type n1-highmem-4 \
    --boot-disk-type pd-ssd \
    --boot-disk-size 200GB \
    --image-project windows-sql-cloud \
    --image-family sql-ent-2016-win-2016 \
    --zone ${zone_2} \
    --network-interface=network=${vpc_name},subnet=private-sql-2,private-network-ip=10.4.0.2,aliases=10.4.0.4/31

配置 bookshelf-sql2 实例

接下来，您需要配置 bookshelf-sql2 实例。您需要从第二个实例开始，因为在 bookshelf-sql1 上设置数据库需要完成额外的工作，并且优先配置 bookshelf-sql2 需要更少的远程桌面协议 (RDP) 会话。

为 Windows 实例 bookshelf-sql2 创建密码：

gcloud compute reset-windows-password bookshelf-sql2 \
    --zone ${zone_2} --quiet

用户名是您的 Google 帐号用户名。请记好用户名和密码，以备将来使用。

使用之前创建的凭据，通过 RDP 连接到 SQL Server 实例。
在实例中，以管理员身份打开 PowerShell 终端。（点击开始，输入 PowerShell，然后按 Control+Shift+Enter。）

设置以下变量：

$DomainName = "example-gcp.com"
$DNS1 = "10.1.0.100"
$DNS2 = "10.2.0.100"
$LocalStaticIp = "10.4.0.2"
$DefaultGateway = "10.4.0.1"

配置主 DNS 服务器：

netsh interface ip set dns Ethernet static $DNS1

配置辅助 DNS 服务器：

netsh interface ip add dns Ethernet $DNS2 index=2

设置 IP 地址和默认网关：
```
netsh interface ip set address name=Ethernet static $LocalStaticIp 255.255.255.0 $DefaultGateway 1
```
注意：RDP 可能会断开连接几秒钟，也可能要求您重新连接。

将虚拟机添加到 Windows 网域：

Add-Computer -DomainName $DomainName `
    -Credential (Get-Credential "example-gcp\Administrator")

当系统提示您为 Administrator 帐号提供密码时，请使用在上一个教程中安装 Active Directory 林时定义的管理员凭据。对于用户名，请添加域名作为前缀，如 EXAMPLE-GCP\Administrator。

打开 SQL Server 可用性组的 Windows 防火墙端口：

netsh advfirewall firewall add rule name="5022 for Avail Groups" dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name="1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433

安装故障切换集群功能：

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

重新启动实例：
```
Restart-Computer
```
使用 EXAMPLE-GCP\Administrator 帐号的凭据，通过 RDP 连接到 SQL Server 实例。
以管理员身份打开 PowerShell 控制台窗口。
在 PowerShell 控制台窗口中，为 SQL Server 数据和日志文件创建文件夹：
```
New-Item -ItemType directory -Path C:\SQLData
New-Item -ItemType directory -Path C:\SQLLog
```
注意：在生产环境中，最佳做法是为 SQL Server 文件创建单独的磁盘。如果要这样做，请更改前面的命令以匹配您正在使用的云端硬盘和路径。

为数据库备份创建一个新文件夹并共享：

New-Item -ItemType directory -Path C:\SQLBackup
New-SMBShare -Name SQLBackup -Path C:\SQLBackup -FullAccess "Authenticated Users"

配置 bookshelf-sql1 实例

接下来，您需要配置 bookshelf-sql1 实例。

在 Cloud Shell 中，为 Windows 实例 bookshelf-sql1 创建密码：
```
gcloud compute reset-windows-password bookshelf-sql1 --zone ${zone_1}  --quiet
```
用户名是您的 Google 帐号用户名。请记好用户名和密码，以备将来使用。
使用为 bookshelf-sql1 实例创建的凭据，通过 RDP 连接到 SQL Server 实例。
以管理员身份打开 PowerShell 控制台窗口。

在 PowerShell 控制台窗口中，设置以下变量：

$DomainName = "example-gcp.com"
$DNS1 = "10.1.0.100"
$DNS2 = "10.2.0.100"
$LocalStaticIp = "10.3.0.2"
$DefaultGateway = "10.3.0.1"

配置主 DNS 服务器：

netsh interface ip set dns Ethernet static $DNS1

配置辅助 DNS 服务器：

netsh interface ip add dns Ethernet $DNS2 index=2

设置 IP 地址和默认网关：

netsh interface ip set address name=Ethernet static $LocalStaticIp 255.255.255.0 $DefaultGateway 1

将虚拟机添加到网域：

Add-Computer -DomainName $DomainName `
    -Credential (Get-Credential "example-gcp\Administrator")

当系统提示您为管理员帐号提供密码时，请使用您在上一个教程中安装 Active Directory 林时定义的管理员凭据。对于用户名，请添加域名作为前缀，如 EXAMPLE-GCP\Administrator。

添加 Windows 防火墙规则：

netsh advfirewall firewall add rule name="5022 for Avail Groups" dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name="1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433

安装故障切换集群功能：

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

重新启动实例：
```
Restart-Computer
```

配置 Windows Server 故障切换集群

使用 RDP 连接到 bookshelf-sql1 实例。当系统提示您为管理员帐号提供密码时，请使用您在上一个教程中安装 Active Directory 林时定义的管理员凭据。对于用户名，请添加域名作为前缀，如 EXAMPLE-GCP\Administrator。
以管理员身份打开 PowerShell 控制台窗口。
在 PowerShell 控制台窗口中，设置以下变量：
```
$node1 = "bookshelf-sql1"
$node2 = "bookshelf-sql2"
```
为集群设置名称：
```
$nameWSFC = "bookshelf-dbclus"
```

为集群和集群侦听器设置 IP 地址：

$ipWSFC1 = "10.3.0.4"
$ipWSFC2 = "10.4.0.4"
$ipWSListener1 = "10.3.0.5"
$ipWSListener2 = "10.4.0.5"

确保 bookshelf-sql1 SQL Server 实例已准备好加入集群：运行以下脚本，尝试连接到 SQL Server 实例：

$SQLServer1IsReady=$False
For ($i=0; $i -le 30; $i++) {
    $SqlCatalog = "master"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $node1;" + `
         "Database = $SqlCatalog; Integrated Security = True"
    try {
        $SqlConnection.Open()
        Write-Host "Connection to the server $node1 was successful"
        $SQLServer1IsReady=$True
        $SqlConnection.Close()
        break
    }
    catch {
        Write-Host "SQL server $node1 is not ready, waiting for 60s"
        Start-Sleep -s 60 #Wait for 60 seconds
    }
}
if($SQLServer1IsReady -eq $False) {
    Write-Error "$node1 is not responding. Was it deployed correctly?"
}

确保 bookshelf-sql2 SQL Server 实例已准备好加入集群：

$SQLServer2IsReady=$False
For ($i=0; $i -le 30; $i++) {
    $SqlCatalog = "master"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $node2;" + `
        "Database = $SqlCatalog; Integrated Security = True"
    try {
        $SqlConnection.Open()
        Write-Host "Connection to the server $node2 was successful"
        $SQLServer2IsReady=$True
        $SqlConnection.Close()
        break
    }
    catch {
        Write-Host "SQL server $node2 is not ready, waiting for 60s"
        Start-Sleep -s 60 #Wait for 60 seconds
    }
}
if($SQLServer2IsReady -eq $False) {
    Write-Error "$node2 is not responding. Was it deployed correctly?"
}

创建新集群：

New-Cluster -Name $nameWSFC -Node $node1, $node2 -NoStorage `
    -StaticAddress $ipWSFC1, $ipWSFC2

在两个节点上启用 Always On 可用性组功能：

Enable-SqlAlwaysOn -ServerInstance $node1 -Force
Enable-SqlAlwaysOn -ServerInstance $node2 -Force

为数据库数据和日志文件创建文件夹：

New-Item -ItemType directory -Path C:\SQLData
New-Item -ItemType directory -Path C:\SQLLog

设置数据库

本教程使用您创建的 bookshelf 示例数据库，以测试您的配置是否正常运行。

在当前的 PowerShell 控制台窗口中，运行以下 SQL 语句以创建 bookshelf 数据库：

$CreateDatabaseSQLScript =  @"
CREATE DATABASE bookshelf ON PRIMARY
    (NAME = 'bookshelf_Data', FILENAME='C:\SQLData\bookshelf_data.mdf', SIZE =
256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB )
    LOG ON (NAME = 'bookshelf_log', FILENAME='C:\SQLLog\bookshelf_log.ldf',
SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB )
GO
USE [bookshelf]
Exec dbo.sp_changedbowner @loginame = 'sa', @map = false;
ALTER DATABASE [bookshelf] SET RECOVERY FULL;
GO
BACKUP DATABASE bookshelf to disk =
'\\bookshelf-sql2\SQLBackup\bookshelf.bak' WITH INIT
GO
"@
Invoke-Sqlcmd -Query $CreateDatabaseSQLScript -ServerInstance $node1

运行以下 SQL 语句以创建 Books 表：

$CreateTableSQLScript =  @"
USE [bookshelf]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Books] (
            [Id] [bigint] IDENTITY(1,1) NOT NULL,
            [Title] [nvarchar](max) NOT NULL,
            [Author] [nvarchar](max) NULL,
            [PublishedDate] [datetime] NULL,
            [ImageUrl] [nvarchar](max) NULL,
            [Description] [nvarchar](max) NULL,
            [CreatedById] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Books] PRIMARY KEY CLUSTERED
    ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
"@
Invoke-Sqlcmd -Query $CreateTableSQLScript -ServerInstance $node1

设置以下变量：

$Domain = "example-gcp.com"
$ADControllerFQDN = "ad-dc1.$Domain"
$ServiceAccountName = "sql.service"
$ServiceAccountPrincipalName = "$ServiceAccountName@$Domain"

使用以下 PowerShell 命令激活 RSAT-AD-PowerShell cmdlet：
```
Add-WindowsFeature RSAT-AD-PowerShell
```
您需要使用远程服务器管理工具以从虚拟机创建服务帐号。

在网域中添加名为 sql.service 的数据库服务帐号：

New-ADUser -Name $ServiceAccountName `
    -Server $ADControllerFQDN `
    -SamAccountName $ServiceAccountName `
    -UserPrincipalName $ServiceAccountPrincipalName `
    -AccountPassword (Read-Host -AsSecureString "AccountPassword") `
    -PassThru | Enable-ADAccount

当系统提示您为 sql.service 帐号输入密码时，请使用安全系数高的密码并将其存储在安全的位置以备将来使用。

运行以下 SQL 语句以将 EXAMPLE-GCP\sql.service 帐号添加为数据库管理员：

$AddSQLServiceAccountScript =  @"
USE [master]
GO
    CREATE LOGIN [EXAMPLE-GCP\sql.service] FROM WINDOWS WITH
DEFAULT_DATABASE=[bookshelf], DEFAULT_LANGUAGE=[us_english]
GO
USE [bookshelf]
GO
CREATE USER [EXAMPLE-GCP\sql.service] FOR LOGIN [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_datareader] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_owner] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
USE [bookshelf]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [EXAMPLE-GCP\sql.service]
GO
"@
Invoke-Sqlcmd -Query $AddSQLServiceAccountScript -ServerInstance $node1

设置 SQL Server 可用性组

在当前实例中，设置以下变量：

$Database="bookshelf"
$BackupFolder="SQLBackup"
$SharedLocation="\\$node2\$BackupFolder"
$ListenerName="sql-listener"
$AGName="MainAG"
$PrimaryServer = "$node1.$Domain\MSSQLSERVER"
$SecondaryServer = "$node2.$Domain\MSSQLSERVER"

备份主服务器上的数据库：

Backup-SqlDatabase `
    -Database $Database -Initialize `
    -BackupFile "$SharedLocation\$Database.bak" `
    -ServerInstance $node1

在辅助服务器上恢复数据库：

Restore-SqlDatabase `
    -Database $Database `
    -BackupFile "$SharedLocation\$Database.bak" `
    -ServerInstance $node2 `
    -NORECOVERY

备份主服务器上的数据库日志：

Backup-SqlDatabase `
    -Database $Database -Initialize `
    -BackupFile "$SharedLocation\$Database.log" `
    -ServerInstance $node1 `
    -BackupAction Log

在辅助服务器上恢复数据库日志：

Restore-SqlDatabase `
    -Database $Database `
    -BackupFile "$SharedLocation\$Database.log" `
    -ServerInstance $node2 `
    -RestoreAction Log `
    -NORECOVERY

创建端点然后启动：

$endpoint1=New-SqlHADREndpoint -Port 5022 -Owner sa `
    -Encryption Supported -EncryptionAlgorithm Aes `
    -Name AlwaysonEndpoint1 `
    -Path "SQLSERVER:\SQL\$node1.$Domain\Default"
Set-SqlHADREndpoint -InputObject $endpoint1 -State Started
$endpoint2=New-SqlHADREndpoint -Port 5022 -Owner sa `
    -Encryption Supported -EncryptionAlgorithm Aes `
    -Name AlwaysonEndpoint2 `
    -Path "SQLSERVER:\SQL\$node2.$Domain\Default"
Set-SqlHADREndpoint -InputObject $endpoint2 -State Started

输出如下所示：

AlwaysonEndpoint1       STARTED            5022 - for node1
AlwaysonEndpoint2       STARTED            5022 - for node2

在 PowerShell 中运行以下命令来验证配置：

$node1 = "bookshelf-sql1"
$node2 = "bookshelf-sql2"
Invoke-Sqlcmd -Query "select name, state_desc, port FROM sys.tcp_endpoints" -ServerInstance $node1
Invoke-Sqlcmd -Query "select name, state_desc, port FROM sys.tcp_endpoints" -ServerInstance $node2

如果配置正确，您会看到类似下面的输出：

name                       state_desc port
----                       ---------- ----
Dedicated Admin Connection STARTED       0
TSQL Default TCP           STARTED       0
AlwaysonEndpoint1          STARTED    5022
Dedicated Admin Connection STARTED       0
TSQL Default TCP           STARTED       0
AlwaysonEndpoint2          STARTED    5022

设置端点网址：

$EndpointUrlSQLServer1="TCP://" + $node1 + "." + $Domain + ":5022"
$EndpointUrlSQLServer2="TCP://" + $node2 + "." + $Domain + ":5022"

创建副本的内存表示：

$PrimaryReplica = New-SqlAvailabilityReplica -Name $node1 `
    -EndpointUrl $EndpointUrlSQLServer1 `
    -FailoverMode "Automatic" `
    -AvailabilityMode "SynchronousCommit" `
    -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name $node2 `
    -EndpointUrl $EndpointUrlSQLServer2 `
    -FailoverMode "Automatic" `
    -AvailabilityMode "SynchronousCommit" `
    -AsTemplate -Version 13

创建可用性组：

New-SqlAvailabilityGroup -Name $AGName `
    -AvailabilityReplica @($PrimaryReplica, $SecondaryReplica) `
    -Path "SQLSERVER:\SQL\$node1.$Domain\Default" `
    -Database $Database

将辅助实例加入可用性组：

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$node2\Default" -Name $AGName

将辅助数据库加入可用性组：

Add-SqlAvailabilityDatabase `
    -Path "SQLSERVER:\SQL\$node2\Default\AvailabilityGroups\$AGName" `
    -Database $Database

创建侦听器：

New-SqlAvailabilityGroupListener -Name $ListenerName `
    -Port 1433 `
    -StaticIp @("$ipWSListener1/255.255.255.0","$ipWSListener2/255.255.255.0") `
    -Path SQLSERVER:\Sql\$node1\Default\AvailabilityGroups\$AGName

移除不再需要的共享文件夹：

For ($i=0; $i -le 30; $i++) {
    try {
        Remove-Item \\$node2\c$\$BackupFolder -Force -Recurse -ErrorAction Stop
        Write-Host "Shared folder $SharedLocation was removed"
        break
    }
    catch {
        Write-Host "\\$node2\c$\$BackupFolder is in use waiting 1m to retry"
        Start-Sleep -s 60
    }
}

清除数据

如果您想继续学习本系列中的下一个教程（部署负载均衡 IIS Web 服务器），请保留您在本教程中创建的资源。不过，如果您不打算使用本教程中创建的环境，请清理您在 Google Cloud 上创建的资源，避免再为这些资源付费。以下部分介绍如何删除或关闭这些资源。

删除项目

在 Google Cloud 控制台中，进入管理资源页面。

转到“管理资源”

在项目列表中，选择要删除的项目，然后点击删除。

在对话框中输入项目 ID，然后点击关闭以删除项目。

删除实例

如果您不想完全删除项目，可以仅删除您创建的特定资源。

要删除 Compute Engine 实例，请执行以下操作：

在 Google Cloud Console 中，转到“虚拟机实例”页面。

转到“虚拟机实例”页面
选择要删除的实例。
在页面顶部，点击删除以删除该实例。

删除 VPC 网络

要删除 VPC 网络、子网和防火墙规则，请执行以下操作：

在 Google Cloud 控制台中，转到“VPC 网络”页面。

转到“VPC 网络”页面
选择您创建的 VPC 网络。
在页面顶部，点击删除以删除该网络。

后续步骤

继续阅读本系列的下一个教程：
- 部署负载平衡 IIS 网络服务器
详细了解 Google Cloud 上的 SQL Server：
查看设计可扩缩的高可用性工作负载的最佳实践。
探索有关 Google Cloud 的参考架构、图表和最佳做法。查看我们的 Cloud Architecture Center。