[SQLServer][Alwayson]

yuanqiao

　　. 环境准备
　　虚拟机：ssag-bj-ad-01, ssag-bj-fc-01, ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-ad-01, ssag-sh-fc-01, ssag-sh-sql-01
　　. 主域控制器
　　ssag-bj-ad-01安装 服务器角色 Active Directory 域服务，
　　. 辅助域控制器
　　ssag-sh-ad-01安装 服务器角色 Active Directory 域服务 
　　. 加入域
　　ssag-bj-fc-01, ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-fc-01, ssag-sh-sql-01
　　. 安装 故障转移集群
　　ssag-bj-fc-01, ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-fc-01, ssag-sh-sql-01
　　. 创建故障转移集群
　　集群核心资源，IP地址指定，重新联机
　　dns服务器中创建A记录
　　加入集群时，“所有符合条件的存储添加到集群选项中”这个选项一定去除
　　. 安装数据库，开启alwayson
　　ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-sql-01
　　. 服务运行改为域用户
　　SQL Server，SQL Server代理
　　ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-sql-01
　　. 开启 Named Pipes 服务
　　ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-sql-01
　　. 添加域用户为数据库管理员
　　ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-sql-01
　　. 添加防火墙规则，允许SQLServer程序，如果有NLB，增加59999端口
　　ssag-bj-sql-01, ssag-bj-sql-02, ssag-sh-sql-01
　　# Define variables
$ServiceName = "<MyCloudService>" # the name of the cloud service that contains the availability group nodes
$AGNodes = "<VM1>","<VM2>","<VM3>" # all availability group nodes containing replicas in the same cloud service, separated by commas
　　# Configure a load balanced endpoint for each node in $AGNodes, with direct server return enabled
ForEach ($node in $AGNodes)
{
Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name "ListenerEndpoint" -Protocol "TCP" -PublicPort 1433 -LocalPort 1433 -LBSetName "ListenerEndpointLB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
}
　　. 加入alwayson
　　备份数据库，还原Norecovery模式
　　. 创建可用性组侦听器
　　# Define variables
$ClusterNetworkName = "<ClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP Address resource name
$CloudServiceIP = "<X.X.X.X>" # Public Virtual IP (VIP) address of your cloud service
　　Import-Module FailoverClusters
　　# If you are using Windows Server 2012 or higher, use the Get-Cluster Resource command. If you are using Windows Server 2008 R2, use the cluster res command. Both commands are commented out. Choose the one applicable to your environment and remove the # at the beginning of the line to convert the comment to an executable line of code.
　　# Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}
# cluster res $IPResourceName /priv enabledhcp=0 overrideaddressmatch=1 address=$CloudServiceIP probeport=59999 subnetmask=255.255.255.255
　　https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-ext-listener
　　https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-int-listener
　　出现目标主体名称不正确，无法生成 SSPI 上下文错误
　　setspn -L T-BJ-SQL-02
　　setspn -D MSSQLSvc/T-BJ-SQL-05.teld.local:1433 T-BJ-SQL-05