销售电话: 1-800-867-1380

Tutorial: AlwaysOn Availability Groups in Hybrid IT (PowerShell)

更新时间: 2014年6月

This tutorial shows you how to implement an AlwaysOn Availability Group in a hybrid environment spanning on-premises and Microsoft Azure. At the end of the tutorial, your SQL Server AlwaysOn solution will consist of the following elements:

  • A virtual network containing multiple subnets, including a front-end and a back-end subnet

  • A domain controller and two SQL Server VM running behind a VPN device in your on-premises network

  • A site-to-site VPN connection between the Microsoft Azure VPN device and your on-premises VPN device

  • A SQL Server VM deployed to the back-end subnet in Azureand joined to your on-premises Active Directory domain

  • A 3-node Windows Server Failover Cluster (WSFC) multi-site cluster with the Node Majority quorum model

  • An availability group with two synchronous-commit replicas on-premises and one asynchronous-commit replica in Azure

This tutorial includes instructions how to set up the solution end-to-end, including setting up a dummy private network (10.0.0.0/24) and a dummy Active Directory domain (corp.contoso.com) on-premises. If you want to use your existing on-premises network and Active Directory, skip the relevant sections and move on to the “Create VPN Connection to Azure and a SQL Server VM in Azure” section. However, the later steps are not guaranteed to work with the specifications of your existing on-premises configuration.

This tutorial is intended to show you the steps required to set up the described solution above without elaborating on the details of each step. Therefore, instead of showing you the GUI configuration steps, it uses PowerShell scripting to take you quickly through each step. It assumes the following:

  • You already have an Azure subscription.

  • There is a supported VPN device with a public-facing IP address at the edge of your on-premises network. For more information, see About VPN Devices for Virtual Network.

  • You have installed the Azure PowerShell cmdlets.

  • You have downloaded KB 2494036, which you will need to install on your availability group nodes for proper quorum configuration in a multi-subnet WSFC cluster.

  • You already know how to provision a SQL Server VM from the virtual machine gallery using the GUI. For more information, see Provisioning a SQL Server Virtual Machine on Azure

  • You already have a good understanding of AlwaysOn Availability Groups for on-premises solutions. For more information, see AlwaysOn Availability Groups (SQL Server).

  • For simplicity, Contoso!000 is the same password used throughout the tutorial.

Configure the on-premises domain controller with DHCP service

  1. Install Windows Server 2008 R2 on an on-premises computer that you will use as the domain controller and the DHCP server.

  2. Connect the computer to the Internet and install all updates.

  3. Disconnect the computer from the Internet and connect it to the same subnet as the VPN device.

  4. Open a Windows PowerShell window in administrator mode.

  5. Define the following PowerShell variables that you will use to initialize this computer:

    $dcName = "DC1"
    $dcIP = "10.0.0.1"
    $subnetMask = "255.255.255.0"
    $gatewayIP = "10.0.0.2"
    $password = "Contoso!000"
    
    
  6. Configure the IP address for the domain controller on the computer as 10.0.0.1, with the subnet mask 255.255.255.0 and gateway IP address 10.0.0.2.

    netsh interface ip set address name="Local Area Connection" static $dcIP $subnetMask $gatewayIP 1
    
  7. Rename the computer to DC1.

    netdom renamecomputer $ENV:COMPUTERNAME /newname:$dcName /usero:Administrator /passwordo:$password /force /reboot
    
  8. Once the computer restarts, log in again and define the following PowerShell variables that you will use to configure this computer as a domain controller:

    $domainSuffix = "corp.contoso.com"
    $domainNetBIOS = "CORP"
    $password = "Contoso!000"
    
  9. Run DCPROMO.EXE to create an Active Directory domain called corp.contoso.com in a new domain forest.

    dcpromo.exe `
        /unattend `
        /ReplicaOrNewDomain:Domain `
        /NewDomain:Forest `
        /NewDomainDNSName:$domainSuffix `
        /ForestLevel:4 `
        /DomainNetbiosName:$domainNetBIOS `
        /DomainLevel:4 `
        /InstallDNS:Yes `
        /ConfirmGc:Yes `
        /CreateDNSDelegation:No `
        /SafeModeAdminPassword:$password 
    

    Once the computer restarts, it becomes the domain controller of the newly created corp.contoso.com domain.

  10. Log into the domain controller as CORP\Administrator with the password Contoso!000. This is the same password you will use for the rest of the tutorial.

  11. Open a Windows PowerShell window in administrator mode.

  12. Define the following PowerShell variables to help you configure the domain.

    $dcName = "DC1"
    $dcIP = "10.0.0.1"
    $gatewayIP = "10.0.0.2"
    $scopeIP = "10.0.0.0"
    $scopeName = "Corpnet"
    $scopeStart = "10.0.0.100"
    $scopeEnd = "10.0.0.150"
    $domainSuffix = "corp.contoso.com"
    $pwd = ConvertTo-SecureString "Contoso!000" -AsPlainText -Force
    
  13. Import the Active Directory PowerShell Module and create a number of users.

    Import-Module ActiveDirectory
    
    New-ADUser -Name 'Install' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    New-ADUser -Name 'SQLSvc1' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    New-ADUser -Name 'SQLSvc2' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    New-ADUser -Name 'SQLSvc3' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    

    CORP\Install is used to install SQL Server, create the WSFC cluster, and configure the availability group. CORP\SQLSvc1, CORP\SQLSvc2, and CORP\SQLSvc3 are used as service accounts for the three SQL Server instances.

  14. Give CORP\Install the permissions to create computer objects in the domain.

    Cd ad:
    $sid = new-object System.Security.Principal.SecurityIdentifier (Get-ADUser "Install").SID
    $guid = new-object Guid bf967a86-0de6-11d0-a285-00aa003049e2
    $ace1 = new-object System.DirectoryServices.ActiveDirectoryAccessRule $sid,"CreateChild","Allow",$guid,"All"
    $corp = Get-ADObject -Identity "DC=corp,DC=contoso,DC=com"
    $acl = Get-Acl $corp
    $acl.AddAccessRule($ace1)
    Set-Acl -Path "DC=corp,DC=contoso,DC=com" -AclObject $acl 
    

    The GUID specified above is the GUID for the computer object type. The CORP\Install account needs the Read All Properties and Create Computer Objects permission in order to create the Active Directory objects for the WSFC cluster. The Read All Properties permission is already given to CORP\Install by default, so you do not need to grant it explicitly. For more information on permissions needed to create the WSFC cluster, see Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory.

  15. Configure the DHCP service with a new scope for your private subnet. The gateway IP address is set to be 10.0.0.2, which you will configure the on-premises VPN device to use.

    Import-Module ServerManager
    Add-WindowsFeature DHCP
    Set-Service dhcpserver -StartupType automatic
    Start-service dhcpserver
    netsh dhcp server add scope $scopeIP 255.255.255.0 $scopeName $scopeName
    netsh dhcp server scope $scopeIP set state 0
    netsh dhcp server scope $scopeIP add iprange $scopeStart $scopeEnd
    netsh dhcp server scope $scopeIP set optionvalue 003 IPADDRESS $gatewayIP
    netsh dhcp server scope $scopeIP set optionvalue 006 IPADDRESS $dcIP
    netsh dhcp server scope $scopeIP set optionvalue 015 STRING $domainSuffix
    netsh dhcp server scope $scopeIP set state 1
    netsh dhcp add server $dcName.$domainSuffix $dcIP
    

Now that you have a functional TCP/IP network running on your private subnet with a functional Active Directory domain. You are ready to configure your on-premises SQL Server system.

Configure on-premises SQL Server service instances

  1. Install Windows Server 2008 R2 on two additional on-premises computers that you will use for the on-premises availability group replicas in SQL Server AlwaysOn.

  2. Connect the computer to the Internet and install all updates.

  3. Disconnect the computer from the Internet and connect it to the same subnet as the VPN device.

  4. Open a Windows PowerShell window in administrator mode.

  5. Join the computers to the CORP domain, which you created previously. If you are using your existing on-premises network and domain, join the computers to the respective domain of your organization. Change the $computerName variable to SQLOnPrem1 or SQLOnPrem2 for the respective machines.

    $domain = "CORP"
    $domainAdmin = "CORP\Administrator"
    $password = "Contoso!000"
    $computerName = <SQLOnPrem1 or SQLOnPrem2>
    netdom join $env:COMPUTERNAME /domain:$domain /userd:$domainAdmin /passwordd:$password 
    netdom renamecomputer $env:COMPUTERNAME /newname:$computerName /userd:$domainAdmin /passwordd:$password /force /reboot
    

    The computers reboot automatically after these commands are run.

  6. Once the computers finish rebooting, log in as the local administrator again and open a Windows PowerShell window in administrator mode.

  7. Set the variables for each respective computer and install on each computer. For SQLOnPrem1, use CORP\SQLSvc1 as the service account; for SQLOnPrem1, use CORP\SQLSvc2.

    $installDir = "<Directory of SQL Server Setup.exe>"
    $svcacct = "<CORP\SQLSvc1 or CORP\SQLSvc2>"
    $sqlAdmin = "CORP\Install"
    $password = "Contoso!000"
    
    cd $installDir
    .\Setup.exe `
        /q `
        /ACTION=Install `
        /FEATURES=SQL,SSMS `
        /INSTANCENAME=MSSQLSERVER `
        /SQLSVCACCOUNT="$svcacct" `
        /SQLSVCPASSWORD="$password" `
        /AGTSVCACCOUNT="$svcacct" `
        /AGTSVCPASSWORD="$password" `
        /SQLSYSADMINACCOUNTS="$sqlAdmin" `
        /IACCEPTSQLSERVERLICENSETERMS
    
  8. Open the firewall for remote access of SQL Server.

    netsh advfirewall firewall add rule `
        name='SQL Server (TCP-In)' `
        program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' `
        dir=in `
        action=allow `
        protocol=TCP
    
  9. Enable the TCP protocol on the default instance and restart the SQL Server service.

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
    $wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $env:COMPUTERNAME
    $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].IsEnabled = "True"
    $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].Alter()
    $svc = Get-Service -Name 'MSSQLSERVER'
    $timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
    $svc.Stop(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
    $svc.Start(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout) 
    
  10. Install the Failover Clustering Wiindows feature.

    Import-Module ServerManager
    Add-WindowsFeature Failover-Clustering
    
  11. Install KB 2494036. This hotfix enables you to assign custom node weights to the WSFC nodes and must be installed after Failover Clustering is installed. The command assumes that the MSU file is in the same directory as the SQL Server Setup.exe file.

    .\Windows6.1-KB2494036-v2-x64.msu /passive
    
  12. Add CORP\Install as a local administrator. You will use this user to configure the WSFC cluster and the availability group.

    net localgroup administrators "$sqlAdmin" /Add
    
  13. Log out of both computers.

    Logoff.exe
    

You are now finished with the on-premises private subnet and SQL Server configuration. Next, you will configure the VPN connection between your on-premises private subnet and an Azure network, and then create a domain-joined SQL Server VM in Azure.

Create VPN Connection to Azure and a SQL Server VM in Azure

  1. In a PowerShell window on a computer that is connected to the internet, import the Azure module, download a publishing settings file to your machine, and connect your PowerShell session to your Azure subscription by importing the downloaded publishing settings.

    Import-Module `
        "C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\Azure\Azure.psd1"
    Get-AzurePublishSettingsFile
    Import-AzurePublishSettingsFile <publishsettingsfilepath> 
    

    The Get-AzurePublishgSettingsFile command automatically generates a management certificate with Azure and downloads it to your machine. A browser will be automatically opened and you are prompted to enter the Microsoft account credentials for your Azure subscription. The downloaded .publishsettings file contains all the information you need to manage your Azure subscription. After saving this file to a local directory, import it using the Import-AzurePublishSettingsFile command.

    security安全 备注
    The publishsettings file contains your credentials (unencoded) that are used to administer your Azure subscriptions and services. The security best practice for this file is to store it temporarily outside your source directories (for example in the Libraries\Documents folder), and then delete it once the import has completed. A malicious user gaining access to the publishsettings file can edit, create, and delete your Azure services.

  2. Define a series of variables that you will use to create your cloud IT infrastructure.

    $workingDir = "C:\workingDirectory\"
    $location = "West US"
    $affinityGroupName = "ContosoAG"
    $affinityGroupDescription = "Contoso SQL HADR Affinity Group"
    $affinityGroupLabel = "IaaS BI Affinity Group"
    $virtualNetworkName = "ContosoNET" 
    $localNetworkName = "ContosoNETLocal"
    $networkConfigPath = $workingDir + "NetworkConfig.xml"
    $storageAccountName = "<uniquestorageaccountname>"
    $storageAccountLabel = "Contoso SQL HADR Storage Account"
    $storageAccountContainer = "https://$storageAccountName.blob.core.windows.net/vhds/"
    $sqlImageName = (Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP1 Enterprise*"} | sort PublishedDate -Descending)[0].ImageName
    $serviceName = "<uniqueservicename>"
    $sqlServerName = 'SQLInCloud'
    $vmAdminUser = "AzureAdmin" 
    $vmAdminPassword = "Contoso!000"
    $dnsSettings = New-AzureDns -Name 'LocalDC' -IPAddress '10.0.0.1'
    $domainName= 'corp'
    $domainSuffix = 'corp.contoso.com'
    $subnetName = 'CloudBack'
    $domainUser = 'Administrator'
    $dataDiskSize = 100
    

    Pay attention to the following to ensure that your commands will succeed later:

    • Variables $storageAccountName and $serviceName must be unique because they are used to identify your cloud storage account and cloud server, respectively, on the internet.

    • Names specified for variables $affinityGroupName, $virtualNetworkName, and $localNetworkName are configured in the virtual network configuration document that you will use later.

    • $sqlImageName specifies the updated name of the VM image that contains Service Pack 1 Enterprise Edition.

    • For simplicity, Contoso!000 is the same password used throughout the entire tutorial.

  3. Create an affinity group.

    New-AzureAffinityGroup `
        -Name $affinityGroupName `
        -Location $location `
        -Description $affinityGroupDescription `
        -Label $affinityGroupLabel
    
  4. Create a virtual network by importing a configuration file named NetworkConfig.xml.

    Set-AzureVNetConfig `
        -ConfigurationPath $networkConfigPath
    

    The configuration file contains the following XML document. In brief, it specifies a virtual network called ContosoNET in the affinity group called ContosoAG, and it has the address space 10.10.0.0/16. The CloudBack subnet, or 10.1.1.0/24, is where you will place your SQL Server VM. The GatewaySubnet subnet, or 10.1.254.0/24, is used by Azure to place a virtual VPN device. The subnet name GatewaySubnet cannot be changed. If you change the $affinityGroupName, $virtualNetworkName, and $localNetworkName variables earlier, you must also change the corresponding names below. Also, the document configures the IP address of your on-premises DNS server, 10.0.0.1, and makes reference to this DNS server in the DnsServersRef element.

    <NetworkConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/ServiceHosting/2011/07/NetworkConfiguration">
      <VirtualNetworkConfiguration>
        <Dns>
          <DnsServers>
            <DnsServer name="LocalDC" IPAddress="10.0.0.1" />
          </DnsServers>
        </Dns>
        <LocalNetworkSites>
          <LocalNetworkSite name="ContosoNETLocal">
            <AddressSpace>
              <AddressPrefix>10.0.0.0/24</AddressPrefix>
            </AddressSpace>
            <VPNGatewayAddress>24.12.228.169</VPNGatewayAddress>
          </LocalNetworkSite>
        </LocalNetworkSites>
        <VirtualNetworkSites>
          <VirtualNetworkSite name="ContosoNET" AffinityGroup="ContosoAG">
            <AddressSpace>
              <AddressPrefix>10.1.0.0/16</AddressPrefix>
            </AddressSpace>
            <Subnets>
              <Subnet name="CloudBack">
                <AddressPrefix>10.1.1.0/24</AddressPrefix>
              </Subnet>
              <Subnet name="GatewaySubnet">
                <AddressPrefix>10.1.254.0/24</AddressPrefix>
              </Subnet>
            </Subnets>
            <DnsServersRef>
              <DnsServerRef name="LocalDC" />
            </DnsServersRef>
            <Gateway profile="Small">
              <ConnectionsToLocalNetwork>
                <LocalNetworkSiteRef name="ContosoNETLocal" />
              </ConnectionsToLocalNetwork>
            </Gateway>
          </VirtualNetworkSite>
        </VirtualNetworkSites>
      </VirtualNetworkConfiguration>
    </NetworkConfiguration>
    
  5. Create the virtual network gateway and wait for the gateway to finish provisioning.

    New-AzureVNetGateway -VNetName $virtualNetworkName
    
    $gateway = Get-AzureVNetGateway -VNetName $virtualNetworkName
    While ($gateway.State -ne "Provisioned")
    {
        write-host "Waiting...Current State = " $gateway.Status
        Start-Sleep -Seconds 15
        $gateway = Get-AzureVNetGateway -VNetName $virtualNetworkName
    }
    
    "IP Address   : " + $gateway.VIPAddress
    "Preshared Key: " + (Get-AzureVNetGatewayKey -VNetName $virtualNetworkName -LocalNetworkSiteName $localNetworkName).Value
    

    The virtual network gateway takes some time to prevision. Once it is provisioned, the public IP address of the gateway and the preshared key are displayed in your PowerShell window.

  6. Configure your on-premises VPN device using the public IP address of the virtual network gateway and the preshared key. To use the configuration script specific to your VPN device, see About VPN Devices for Virtual Network.

    At the end of this step, your VPN connection should be fully connected. For more information, see Configure a Virtual Network Gateway in the Management Portal.

  7. Continuing with the PowerShell window on your Internet-connected computer, create a storage account that is associated with the affinity group you created.

    New-AzureStorageAccount `
        -StorageAccountName $storageAccountName `
        -Label $storageAccountLabel `
        -AffinityGroup $affinityGroupName
    Set-AzureSubscription `
        -SubscriptionName (Get-AzureSubscription).SubscriptionName `
        -CurrentStorageAccount $storageAccountName
    
  8. Create the DC server in new cloud service and availability set.

    New-AzureVMConfig `
        -Name $sqlServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer$sqlServerName.vhd" `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -WindowsDomain `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword `
            -DisableAutomaticUpdates `
            -Domain $domainName `
            -JoinDomain $domainSuffix `
            -DomainUserName $domainUser `
            -DomainPassword $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 1 `
                    -LocalPort 1433 | 
                    New-AzureVM `
                        -ServiceName $serviceName `
                        -AffinityGroup $affinityGroupName `
                        -VNetName $virtualNetworkName `
                        -DnsSettings $dnsSettings
    

    This series of piped commands do the following things:

    • New-AzureVMConfig creates a VM configuration using the Service Pack 1 Enterprise Edition image in the virtual machine gallery. It also sets the operating system disk to read-caching only (no write caching). It is recommended that you migrate the database files to a separate data disk that you attach to the VM and configure it with no read or write caching. However, since both of the on-premises database servers use the C drive for database files, you will forego moving the database files. This way, the Azure VM can be identical to the on-premises servers in configuration when all are joined to a WSFC cluster later.

    • Add-AzureProvisioningConfig joins the VM to the on-premises Active Directory domain you created.

    • Add-AzureEndpoint adds access endpoints so that client applications can access the SQL Server service instance on the internet.

    • New-AzureVM creates a new cloud service and creates the new Azure VM in the new cloud service.

  9. Wait for the new VM to be fully provisioned and download the remote desktop file to your working directory. The while loop continues to poll the new VM until it is ready for use.

    $VM = Get-AzureVM -name $sqlServerName
    write-host "Waiting for " $VM.Name "..."
    
    # loop until the VM status is "ReadyRole"
    While ($VM.InstanceStatus -ne "ReadyRole")
    {
        Write-Host "  Current Status = " $VM.InstanceStatus
        Start-Sleep -Seconds 15
        $VM = Get-AzureVM -ServiceName $VM.ServiceName -Name $VM.InstanceName
    }
    Write-Host "  Current Status = " $VM.InstanceStatus
    
    # Download remote desktop file
    Get-AzureRemoteDesktopFile -ServiceName $VM.ServiceName -Name $VM.InstanceName -LocalPath "$workingDir$($VM.InstanceName).rdp"
    

Next, you will connect to the new SQL Server VM, SQLInCloud, and initialize it.

Initialize SQL Server VM in Azure

In this section, you need to modify the defaultSQL Server installation on the new SQL Server VM you created to prepare it for joining the availability group. Specifically:

  • The TCP protocol is already enabled on the SQL Server VM. However, you still need to open the firewall for remote access of SQL Server.

  • You need to change the default SQL Server service instance to use the domain account CORP\SQLSvc3 as the service account.

  • You need to add CORP\Install as a sysadmin role in the default database and as the machine administrator.

  • (ContosoSQL1 and ContosoSQL2 only) You need to add NT AUTHORITY\System as a login with the following permissions:

    • Alter any availability group

    • Connect SQL

    • View server state

  • You need to install the Failover Clustering Windows feature and KB 2494036.

  1. Connect to SQLInCloud by launching the remote desktop files. Use the machine administrator’s username AzureAdmin and password Contoso!000, which you specified when creating the VMs.

  2. Verify that the computers have been successfully joined to corp.contoso.com.

  3. Wait for the SQL Server installation to finish running the automated initialization tasks before proceeding.

  4. Open a PowerShell window in administrator mode.

  5. Open the firewall for remote access of SQL Server.

    netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP
    
  6. Add CORP\Install as local administrator.

    net localgroup administrators "CORP\Install" /Add
    
  7. Define the following variables.

    
    $svcaccount = "CORP\SQLSvc3"
    $password = "Contoso!000" 
    
  8. Import the SQL Server PowerShell Provider.

    Set-ExecutionPolicy -Execution RemoteSigned -Force
    Import-Module -Name "sqlps" -DisableNameChecking
    
  9. Change the default SQL Server service instance to use CORP\SQLSvc3 as the service account.

    $wmi.services | where {$_.Type -eq 'SqlServer'} | foreach{$_.SetServiceAccount($svcaccount,$password)} 
    $svc = Get-Service -Name 'MSSQLSERVER'
    $timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
    $svc.Stop(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
    $svc.Start(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)
    
  10. Add CORP\Install as the sysadmin role for the default SQL Server instance.

    Invoke-SqlCmd -Query "EXEC sp_addsrvrolemember 'CORP\Install', 'sysadmin'" -ServerInstance "."
    
  11. Add NT AUTHORITY\System as a login with the three permissions described above.

    Invoke-SqlCmd -Query "CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS" -ServerInstance "."
    Invoke-SqlCmd -Query "GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "." 
    Invoke-SqlCmd -Query "GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
    Invoke-SqlCmd -Query "GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
    
  12. Install the Windows Failover Clustering feature.

    Import-Module ServerManager
    Add-WindowsFeature Failover-Clustering 
    
  13. Download KB 2494036 to SQLInCloud and install it. This hotfix enables you to assign custom node weights to the WSFC nodes and must be installed after Failover Clustering is installed. The command assumes that the MSU file is in the current directory of the PowerShell window.

    .\Windows6.1-KB2494036-v2-x64.msu /passive
    
  14. Log out of SQLInCloud.

    logoff.exe
    

Finally, you are ready to configure the availability group. You will use the SQL Server PowerShell Provider to perform all of the work on SQLInCloud.

Create the Availability Group

  1. Connect to SQLOnPrem1 again by launching the remote desktop files. Instead of logging in using the machine account, log in using CORP\Install.

  2. Open a PowerShell window in administrator mode.

  3. Define the following variables:

    $server1 = "SQLOnPrem1"
    $server2 = "SQLOnPrem2"
    $server3 = "SQLInCloud"
    $serverFSW = "DC1"
    $acct1 = "CORP\SQLSvc1"
    $acct2 = "CORP\SQLSvc2"
    $acct3 = "CORP\SQLSvc3"
    $password = "Contoso!000"
    $clusterName = "Cluster1"
    $timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
    $db = "MyDB1"
    $ag = "AG1"
    $backupShare = "\\$serverFSW\backup"
    $quorumShare = "\\$serverFSW\quorum" 
    
  4. Download CreateAzureFailoverCluster.ps1 from Create WSFC Cluster for AlwaysOn Availability Groups in Azure VM to the local working directory. You will use this script to help you create a functional WSFC cluster. For important information on how WSFC interacts with the Azure network, see SQL Server High Availability and Disaster Recovery in Azure Virtual Machines.

  5. Change to your working directory and create the WSFC cluster with the downloaded script.

    Set-ExecutionPolicy Unrestricted -Force
    .\CreateAzureFailoverCluster.ps1 -ClusterName "$clusterName" -ClusterNodes "$server1", "$server2", "$server3"
    
  6. (On DC1) Create two directories on the on-premises domain controller, one as the file share witness and one as the backup directory to support the availability group configuration later.

    $backupDir = "C:\backup"
    $quorumDir = "C:\quorum"
    $acct1 = "CORP\SQLSvc1"
    $acct2 = "CORP\SQLSvc2"
    $acct3 = "CORP\SQLSvc3"
    $clusterName = "Cluster1"
    
    #Create share folder for quorum configuration
    New-Item $quorumDir -ItemType directory
    net share quorum=$quorumDir "/grant:CORP\$clusterName$,FULL"
    icacls.exe "$quorumDir" /grant:r ("CORP\$clusterName$" + ":(OI)(CI)F")
    
    #Create backup directory and grant permissions for the SQL Server service accounts
    New-Item $backupDir -ItemType directory
    net share backup=$backupDir "/grant:$acct1,FULL" "/grant:$acct2,FULL" "/grant:$acct3,FULL"
    icacls.exe "$backupDir" /grant:r ("$acct1" + ":(OI)(CI)F") ("$acct2" + ":(OI)(CI)F") ("$acct3" + ":(OI)(CI)F")
    
  7. Back on SQLOnPrem1, in the open PowerShell window, set the WSFC cluster to use the Node and File Share Majority quorum model and configure SQLInCloud with a node weight of 0.

    Set-ClusterQuorum -NodeAndFileShareMajority $quorumShare 
    (Get-ClusterNode $server3).NodeWeight = 0
    
  8. Import SQL Server PowerShell Provider.

    Set-ExecutionPolicy RemoteSigned -Force
    Import-Module "sqlps" -DisableNameChecking
    
  9. Enable AlwaysOn Availability Groups for the default SQL Server instances on SQLOnPrem1, SQLOnPrem2, and SQLInCloud.

    # SQLOnPrem1
    Enable-SqlAlwaysOn `
        -Path SQLSERVER:\SQL\$server1\Default `
        -Force
    # SQLOnPrem2
    Enable-SqlAlwaysOn `
        -Path SQLSERVER:\SQL\$server2\Default 
        -NoServiceRestart
    $svc = Get-Service -ComputerName $server2 -Name 'MSSQLSERVER'
    $svc.Stop()
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
    $svc.Start(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)
    # SQLInCloud
    Enable-SqlAlwaysOn `
        -Path SQLSERVER:\SQL\$server3\Default 
        -NoServiceRestart
    $svc = Get-Service -ComputerName $server3 -Name 'MSSQLSERVER'
    $svc.Stop()
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
    $svc.Start(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)
    
  10. Create a database on SQLOnPrem1 called MyDB1, take both a full backup and a log backup, and restore them on SQLOnPrem2 and SQLInCloud with the WITH NORECOVERY option.

    Invoke-SqlCmd -ServerInstance $server1 -Query "CREATE database $db"
    Backup-SqlDatabase -ServerInstance $server1 -Database $db -BackupFile "$backupShare\db.bak"
    Backup-SqlDatabase -ServerInstance $server1 -Database $db -BackupFile "$backupShare\db.log" -BackupAction Log
    Restore-SqlDatabase -ServerInstance $server2 -Database $db -BackupFile "$backupShare\db.bak" -NoRecovery
    Restore-SqlDatabase -ServerInstance $server2 -Database $db -BackupFile "$backupShare\db.log" -RestoreAction Log -NoRecovery
    Restore-SqlDatabase -ServerInstance $server3 -Database $db -BackupFile "$backupShare\db.bak" -NoRecovery
    Restore-SqlDatabase -ServerInstance $server3 -Database $db -BackupFile "$backupShare\db.log" -RestoreAction Log -NoRecovery
    
  11. Create the availability group endpoints on the three SQL Server instances and set the proper permissions on the endpoints.

    $endpoint = 
        New-SqlHadrEndpoint MyMirroringEndpoint `
        -Port 5022 `
        -Path "SQLSERVER:\SQL\$server1\Default"
    Set-SqlHadrEndpoint `
        -InputObject $endpoint `
        -State "Started"
    $endpoint = 
        New-SqlHadrEndpoint MyMirroringEndpoint `
        -Port 5022 `
        -Path "SQLSERVER:\SQL\$server2\Default"
    Set-SqlHadrEndpoint `
        -InputObject $endpoint `
        -State "Started"
    $endpoint = 
        New-SqlHadrEndpoint MyMirroringEndpoint `
        -Port 5022 `
        -Path "SQLSERVER:\SQL\$server3\Default"
    Set-SqlHadrEndpoint `
        -InputObject $endpoint `
        -State "Started"
    
    Invoke-SqlCmd -ServerInstance $server1 -Query "CREATE LOGIN [$acct2] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server1 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct2]"
    Invoke-SqlCmd -ServerInstance $server1 -Query "CREATE LOGIN [$acct3] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server1 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct3]"
    Invoke-SqlCmd -ServerInstance $server2 -Query "CREATE LOGIN [$acct1] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server2 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct1]"
    Invoke-SqlCmd -ServerInstance $server2 -Query "CREATE LOGIN [$acct3] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server2 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct3]"
    Invoke-SqlCmd -ServerInstance $server3 -Query "CREATE LOGIN [$acct1] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server3 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct1]"
    Invoke-SqlCmd -ServerInstance $server3 -Query "CREATE LOGIN [$acct2] FROM WINDOWS"
    Invoke-SqlCmd -ServerInstance $server3 -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct2]" 
    
  12. Create the availability replicas.

    $primaryReplica = 
        New-SqlAvailabilityReplica `
        -Name $server1 `
        -EndpointURL "TCP://$server1.corp.contoso.com:5022" `
        -AvailabilityMode "SynchronousCommit" `
        -FailoverMode "Automatic" `
        -Version 11 `
        -AsTemplate
    
    $secondaryReplica1 = 
        New-SqlAvailabilityReplica `
        -Name $server2 `
        -EndpointURL "TCP://$server2.corp.contoso.com:5022" `
        -AvailabilityMode "SynchronousCommit" `
        -FailoverMode "Automatic" `
        -Version 11 `
        -AsTemplate
    
    $secondaryReplica2 = 
        New-SqlAvailabilityReplica `
        -Name $server3 `
        -EndpointURL "TCP://$server3.corp.contoso.com:5022" `
        -AvailabilityMode "AsynchronousCommit" `
        -FailoverMode "Manual" `
        -Version 11 `
        -AsTemplate 
    
    
  13. Finally, create the availability group and join the secondary replicas to the availability group.

    New-SqlAvailabilityGroup `
        -Name $ag `
        -Path "SQLSERVER:\SQL\$server1\Default" `
        -AvailabilityReplica @($primaryReplica,$secondaryReplica1,$secondaryReplica2) `
        -Database $db
    Join-SqlAvailabilityGroup `
        -Path "SQLSERVER:\SQL\$server2\Default" `
        -Name $ag
    Add-SqlAvailabilityDatabase `
        -Path "SQLSERVER:\SQL\$server2\Default\AvailabilityGroups\$ag" `
        -Database $db
    Join-SqlAvailabilityGroup `
        -Path "SQLSERVER:\SQL\$server3\Default" `
        -Name $ag
    Add-SqlAvailabilityDatabase `
        -Path "SQLSERVER:\SQL\$server3\Default\AvailabilityGroups\$ag" `
        -Database $db 
    

Congratulations! You have successfully implemented SQL Server AlwaysOn by creating an availability group that spans on-premises and Azure. However, you still need to manually create the availability group listener. For more information, see Tutorial: Listener Configuration for AlwaysOn Availability Groups.

By default, the Service Pack 1 Enterprise Edition image in the virtual machine gallery contains all SQL Server services and features, and on the on-premises SQL Server machines, you have only installed the SQL Server Engine service and SSMS. In a production environment, you should keep the availability replicas identical in configuration, including the SQL Server services and features. However, for focus and brevity, steps for doing so are omitted from the tutorial.

本文是否对您有所帮助?
(1500 个剩余字符)
感谢您的反馈
显示:
© 2014 Microsoft