Exporteren (0) Afdrukken
Alles uitvouwen
EN
Deze inhoud is niet beschikbaar in uw taal, maar wel in het Engels.

Tutorial: Database Mirroring for High Availability in Azure

Updated: April 1, 2014

This tutorial shows you how to implement SQL Server database mirroring for high availability end-to-end using Azure VMs in the same Azure datacenter. When implementing database mirroring in the same Azure datacenter, you can deploy the servers in an Active Directory domain, or you can do it without the Active Directory domain and establish database mirroring using certificates. The latter method saves you money by removing a domain controller and the compute hours that go along with it.

In this tutorial, you will deploy database mirroring as a high availability solution using three SQL Server virtual machines (VMs), with one VM used as the witness server. At the end of the tutorial, your SQL Server high availability solution will consist of the following elements:

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

  • Three SQL Server VMs (SQL1, SQL2, and SQL3) deployed to the back-end subnet

  • SQL1 is the principal database server

  • SQL2 is the mirror database server

  • SQL3 is the witness server

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 and Transact-SQL (T-SQL) scripting to take you quickly through each step. It assumes the following:

Create the Virtual Network and SQL Server VMs

  1. In a PowerShell window on your local computer, 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 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.

    securitySecurity Note
    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.

    $location = "West US"
    $affinityGroupName = "ContosoAG" 
    $affinityGroupDescription = "Contoso SQL HADR Affinity Group"
    $affinityGroupLabel = "IaaS BI Affinity Group"
    $workingDir = "C:\script\"
    $networkConfigPath = $workingDir + "NetworkConfig.xml"
    $virtualNetworkName = "ContosoNET"
    $storageAccountName = "<uniquestorageaccountname>"
    $storageAccountLabel = "Contoso SQL HADR Storage Account"
    $storageAccountContainer = "https://" + $storageAccountName + ".blob.core.windows.net/vhds/"
    $serviceName = "<uniqueservicename>"
    $sqlImageName = (Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP1 Enterprise*"} | sort PublishedDate -Descending)[0].ImageName 
    $availabilitySetName = "SQLHADR"
    $subnetName = "Back"
    $domainUser = "Administrator"
    $sql1ServerName = "SQL1"
    $sql2ServerName = "SQL2"
    $sql3ServerName = "SQL3"
    $dataDiskSize = 100
    $vmAdminUser = "AzureAdmin" 
    $vmAdminPassword = "Contoso!000"
    

    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 service, respectively, on the internet.

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

    • 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.

    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 and has two subnets, 10.10.1.0/24 and 10.10.2.0/24, which are the front subnet and back subnet, respectively. The front subnet is where you can place client applications such as Microsoft SharePoint, and the back subnet is where you will place the SQL Server VMs. If you change the $affinityGroupName and $virtualNetworkName variables earlier, you must also change the corresponding names below.

    <NetworkConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/ServiceHosting/2011/07/NetworkConfiguration">
      <VirtualNetworkConfiguration>
        <Dns />
        <VirtualNetworkSites>
          <VirtualNetworkSite name="ContosoNET" AffinityGroup="ContosoAG">
            <AddressSpace>
              <AddressPrefix>10.10.0.0/16</AddressPrefix>
            </AddressSpace>
            <Subnets>
              <Subnet name="Front">
                <AddressPrefix>10.10.1.0/24</AddressPrefix>
              </Subnet>
              <Subnet name="Back">
                <AddressPrefix>10.10.2.0/24</AddressPrefix>
              </Subnet>
            </Subnets>
          </VirtualNetworkSite>
        </VirtualNetworkSites>
      </VirtualNetworkConfiguration>
    </NetworkConfiguration>
    
  5. Create a storage account that is associated with the affinity group you created, then set it as the current storage account for your subscription.

    New-AzureStorageAccount `
        -StorageAccountName $storageAccountName `
        -Label $storageAccountLabel `
        -AffinityGroup $affinityGroupName
    Set-AzureSubscription `
        -SubscriptionName (Get-AzureSubscription).SubscriptionName `
        -CurrentStorageAccount $storageAccountName
    
  6. Create the first SQL Server VM, SQL1, in new cloud service and availability set.

    # Create SQL1...
    New-AzureVMConfig `
        -Name $sql1ServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer$sql1ServerName.vhd" `
        -AvailabilitySetName $availabilitySetName `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -DisableAutomaticUpdates `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 1 `
                    -LocalPort 1433 | 
                    New-AzureVM `
                        -ServiceName $serviceName `
                        –AffinityGroup $affinityGroupName `
                        -VNetName $virtualNetworkName
    

    This series of piped commands do the following things:

    • New-AzureVMConfig creates a VM configuration with the desired availability set name. The subsequent VMs will be created with the same availability set name so that they are joined to the same availability set. The command also specifies the SQL Server 2012 Service Pack 1 Enterprise image in the virtual machine gallery.

      New-AzureVMConfig 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, the next best thing is to remove write caching on the operating system disk, since you cannot remove read caching on the operating system disk.

    • Add-AzureProvisioningConfig gives the configuration parameters of a standalone Windows server.

    • Set-AzureSubnet places the VM in the Back subnet.

    • Add-AzureEndpoint adds an access endpoint so that client applications can access the SQL Server services instance on the internet.

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

  7. Run the following piped commands to create the remaining SQL Server VMs, SQL2 and SQL3.

    # Create SQL2...
    New-AzureVMConfig `
        -Name $sql2ServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer$sql2ServerName.vhd" `
        -AvailabilitySetName $availabilitySetName `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -DisableAutomaticUpdates `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 2 `
                    -LocalPort 1433 | 
                    New-AzureVM `
                        -ServiceName $serviceName
    # Create SQL3...
    New-AzureVMConfig `
        -Name $sql3ServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer$sql3ServerName.vhd" `
        -AvailabilitySetName $availabilitySetName `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -DisableAutomaticUpdates `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                New-AzureVM `
                    -ServiceName $serviceName
     
    
    

    Note the following regarding the commands above:

    • New-AzureVMConfig uses the same availability set name as SQL1, and uses the SQL Server 2012 Service Pack 1 Enterprise Edition image in the virtual machine gallery.

    • Add-AzureProvisioningConfig gives the configuration parameters of a standalone Windows server.

    • Set-AzureSubnet places the VM in the Back subnet.

    • Add-AzureEndpoint adds an access endpoint so that client applications can access the SQL Server service instance on the internet. And endpoint is not specified for SQL3 because you will use it as the witness server. Note that different ports are given to SQL1 and SQL2.

    • New-AzureVM creates the new SQL Server VM in the same cloud service as SQL1. You must place the VMs in the same cloud service if you want them to be in the same availability set.

  8. Wait for each VM to be fully provisioned and download its remote desktop file to your working directory. The for loop cycles through the new VMs and executes the commands inside the top-level curly brackets for each of them.

    Foreach ($VM in $VMs = Get-AzureVM -ServiceName $serviceName)
    {
        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"
    } 
    

Initialize the SQL Server VMs

In this section, you need to modify the default SQL Server installation on each of the three servers. 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.

Now, you are ready to start. Follow the steps below, which are identical for all three servers:

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

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

  3. Open a PowerShell window in administrator mode.

  4. 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
    

You are ready to configure database mirroring on the three servers. Keep the PowerShell windows open on all the servers for subsequent steps.

Configure Database Mirroring Using Certificates

  1. On SQL1, run SQLCMD.EXE in the PowerShell window and create and back up a server certificate.

    SQLCMD -S SQL1
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#1>';
    GO
    
    CREATE CERTIFICATE SQL1_cert WITH SUBJECT = 'SQL1 certificate';
    GO
    
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
       FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL1_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
    GO
    
    BACKUP CERTIFICATE SQL1_cert TO FILE = 'SQL1_cert.cer';
    GO 
    
  2. On SQL2, run SQLCMD.EXE in the PowerShell window and create and back up a server certificate.

    SQLCMD -S SQL2
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
    GO
    
    CREATE CERTIFICATE SQL2_cert WITH SUBJECT = 'SQL2 certificate for database mirroring';
    GO
    
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
       FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL2_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
    GO
    
    BACKUP CERTIFICATE SQL2_cert TO FILE = 'SQL2_cert.cer';
    GO
    
  3. On SQL3, run SQLCMD.EXE in the PowerShell window and create and back up a server certificate.

    SQLCMD -S SQL3
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#3>';
    GO
    
    CREATE CERTIFICATE SQL3_cert WITH SUBJECT = 'SQL3 certificate for database mirroring';
    GO
    
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
       FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL3_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
    GO
    
    BACKUP CERTIFICATE SQL3_cert TO FILE = 'SQL3_cert.cer';
    GO
    
  4. The certificates are saved to the default data directory: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA. Copy the certificates to the same directory across the three servers so that each server has the certificates from the other two servers. To do this, you can create a share folder one of the servers, or attach one of your local computer drives to the remote desktop session and use that to transfer the files.

  5. On SQL1, grant login permissions to SQL2 and SQL3 using their certificates.

    CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000';
    GO
    
    CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
    GO
    
    CREATE CERTIFICATE SQL2_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL2_cert.cer'
    GO
    
    CREATE CERTIFICATE SQL3_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL3_cert.cer'
    GO
    
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
    GO
    
  6. On SQL2, grant login permissions to SQL1 and SQL3 using their certificates.

    CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000';
    GO
    
    CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
    GO
    
    CREATE CERTIFICATE SQL1_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL1_cert.cer'
    GO
    
    CREATE CERTIFICATE SQL3_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL3_cert.cer'
    GO
    
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
    GO
    
  7. On SQL3, grant login permissions to SQL1 and SQL2 using their certificates.

    CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000';
    GO
    
    CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
    GO
    
    CREATE CERTIFICATE SQL1_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL1_cert.cer'
    GO
    
    CREATE CERTIFICATE SQL2_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL2_cert.cer'
    GO
    
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
    GO
    
  8. On SQL1, create a database and take both a full backup and a log backup.

    CREATE database MyDB1
    GO
    
    BACKUP DATABASE MyDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.bak';
    GO
    
    BACKUP LOG MyDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.log';
    GO
    
  9. Copy the backup files to SQL2 in the following directory: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP.

  10. On SQL2, restore database backups with the WITH NORECOVERY option and enable it as a mirroring partner.

    RESTORE DATABASE MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.bak' WITH NORECOVERY;
    GO
    
    RESTORE LOG MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.log' WITH NORECOVERY;
    GO
    
    ALTER DATABASE MyDB1 SET PARTNER = 'TCP://SQL1:5022';
    GO
    
  11. On SQL1, enable SQL1 as a mirroring partner and SQ3 as a witness server.

    ALTER DATABASE MyDB1 SET PARTNER = 'TCP://SQL2:5022';
    GO
    
    ALTER DATABASE MyDB1 SET WITNESS = 'TCP://SQL3:5022';
    GO
    

Congratulations! You have successfully set up database mirroring using certificates in Azure VMs. To verify that the database mirroring session is synchronized, open the database mirroring monitor. For more information on the database mirroring monitor, see Start Database Mirroring Monitor (SQL Server Management Studio).

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft