Eksportuj (0) Drukuj
Rozwiń wszystko
EN
Ta zawartość nie jest dostępna w wymaganym języku. Wersja w języku angielskim znajduje się tutaj.

Tutorial: Database Mirroring for Disaster Recovery in Azure

Updated: April 1, 2014

This tutorial shows you how to implement SQL Server database mirroring for disaster recovery end-to-end using Azure VMs in two different Azure datacenters. When implementing database mirroring across two Azure datacenters, you must use server certificates. The database mirroring servers cannot reside in the same Active Directory domain because there is currently no internal communication in Azure across datacenters. You must establish communication between the database servers through their public cloud service endpoints.

In this tutorial, you will deploy asynchronous database mirroring as a disaster recovery solution using two SQL Server virtual machines (VMs). At the end of the tutorial, your SQL Server high availability solution will consist of the following elements:

  • Two virtual networks, one running in the US West datacenter and the other running in the US East datacenter

  • One SQL Server VM (SQL1) deployed to the virtual network in the US West datacenter

  • One SQL Server VM (SQL2) deployed to the virtual network in the US East datacenter

  • SQL1 is the principal database server

  • SQL2 is the mirror database 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.

    $location1 = "West US"
    $location2 = "East US"
    $affinityGroupName1 = "ContosoWest"
    $affinityGroupName2 = "ContosoEast"
    $affinityGroupDescription = "Contoso SQL HADR Affinity Group"
    $affinityGroupLabel = "IaaS BI Affinity Group"
    $workingDir = "C:\scripts\"
    $networkConfigPath = $workingDir + "Network.netcfg"
    $virtualNetworkName1 = "Network1" 
    $virtualNetworkName2 = "Network2" 
    $storageAccountName1 = "<uniquestorageaccountname1>" 
    $storageAccountName2 = "<uniquestorageaccountname2>" 
    $storageAccountLabel = "Contoso SQL HADR Storage Account"
    $storageAccountContainer1 = "https://" + $storageAccountName1 + ".blob.core.windows.net/vhds/"
    $storageAccountContainer2 = "https://" + $storageAccountName2 + ".blob.core.windows.net/vhds/"
    $serviceName1 = "<uniqueservicename1>" 
    $serviceName2 = "<uniqueservicename2>" 
    $sqlImageName = (Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP1 Enterprise*"} | sort PublishedDate -Descending)[0].ImageName
    $subnetName = "Back"
    $domainUser = "Administrator"
    $sql1ServerName = "SQL1"
    $sql2ServerName = "SQL2"
    $dataDiskSize = 100
    $vmAdminUser = "AzureAdmin" 
    $vmAdminPassword = "Contoso!000" 
    

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

    • Variables $storageAccountName1, $storageAccountName2, $serviceName1, and $serviceName2 must be unique because they are used to identify your cloud storage account and cloud service, respectively, on the internet. Furthermore, you will need to use the values of $serviceName1 and $serviceName2 when setting up the database mirroring session later.

    • Names specified for variables $affinityGroupName1, $affinityGroupName2, $virtualNetworkName1, and $virtualNetworkName2 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 two affinity groups, one in US West and one in US East.

    New-AzureAffinityGroup `
        -Name $affinityGroupName1 `
        -Location $location1 `
        -Description $affinityGroupDescription `
        -Label $affinityGroupLabel
    New-AzureAffinityGroup `
        -Name $affinityGroupName2 `
        -Location $location2 `
        -Description $affinityGroupDescription `
        -Label $affinityGroupLabel
    
  4. Create the virtual networks by importing a configuration file.

    Set-AzureVNetConfig `
        -ConfigurationPath $networkConfigPath
    

    The configuration file contains the following XML document. In brief, it specifies two virtual networks called Network1 and Network2 in the two affinity groups, ContosoWest and ContosoEast, respectively. Network1 has the address space 10.10.1.0/24 and Network2 has the address space 10.10.2.0/24. If you change the variables for the affinity group names and the virtual network names earlier, you must also change the corresponding names below.

    <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 />
        <VirtualNetworkSites>
          <VirtualNetworkSite name="Network1" AffinityGroup="ContosoWest">
            <AddressSpace>
              <AddressPrefix>10.10.1.0/24</AddressPrefix>
            </AddressSpace>
            <Subnets>
              <Subnet name="Back">
                <AddressPrefix>10.10.1.0/24</AddressPrefix>
              </Subnet>
            </Subnets>
          </VirtualNetworkSite>
          <VirtualNetworkSite name="Network2" AffinityGroup="ContosoEast">
            <AddressSpace>
              <AddressPrefix>10.10.2.0/24</AddressPrefix>
            </AddressSpace>
            <Subnets>
              <Subnet name="Back">
                <AddressPrefix>10.10.2.0/24</AddressPrefix>
              </Subnet>
            </Subnets>
          </VirtualNetworkSite>
        </VirtualNetworkSites>
      </VirtualNetworkConfiguration>
    </NetworkConfiguration> 
    
    
  5. Create a storage account in each of the affinity groups you created.

    New-AzureStorageAccount `
        -StorageAccountName $storageAccountName1 `
        -Label $storageAccountLabel `
        -AffinityGroup $affinityGroupName1
    New-AzureStorageAccount `
        -StorageAccountName $storageAccountName2 `
        -Label $storageAccountLabel `
        -AffinityGroup $affinityGroupName2
    
  6. Create the first SQL Server VM, SQL1, in a new cloud service in the affinity group ContosoWest. Note that you first need to set the current storage account for your subscription.

    Set-AzureSubscription `
        -SubscriptionName (Get-AzureSubscription).SubscriptionName `
        -CurrentStorageAccount $storageAccountName1 
    New-AzureVMConfig `
        -Name $sql1ServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer1$sql1ServerName.vhd" `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -DisableAutomaticUpdates `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 1433 `
                    -LocalPort 1433 | 
                    Add-AzureEndpoint `
                        -Name "Mirroring" `
                        -Protocol "tcp" `
                        -PublicPort 5022 `
                        -LocalPort 5022 | 
                        New-AzureVM `
                            -ServiceName $serviceName1 `
                            –AffinityGroup $affinityGroupName1 `
                            -VNetName $virtualNetworkName1
    

    This series of piped commands do the following things:

    • New-AzureVMConfig creates a VM configuration with the desired availability set name. The command also specifies the SQL Server 2012 Service Pack 1 Enterprise Edition 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, as specified in the network configuration document.

    • Add-AzureEndpoint adds two access endpoints: port 1433 for client connectivity and port 5022 for database mirroring over the internet.

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

  7. Create the first SQL Server VM, SQL2, in a new cloud service in the affinity group ContosoEast. Note that you change the current storage account first.

    Set-AzureSubscription `
        -SubscriptionName (Get-AzureSubscription).SubscriptionName `
        -CurrentStorageAccount $storageAccountName2 
    New-AzureVMConfig `
        -Name $sql2ServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer2$sql2ServerName.vhd" `
        -HostCaching "ReadOnly" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -DisableAutomaticUpdates `
            -AdminUserName $vmAdminUser `
            -Password $vmAdminPassword |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 1433 `
                    -LocalPort 1433 | 
                    Add-AzureEndpoint `
                        -Name "Mirroring" `
                        -Protocol "tcp" `
                        -PublicPort 5022 `
                        -LocalPort 5022 | 
                        New-AzureVM `
                            -ServiceName $serviceName2 `
                            –AffinityGroup $affinityGroupName2 `
                            -VNetName $virtualNetworkName2
    

    These piped commands are similar to the commands for creating SQL1. See notes from the previous step for explanation.

  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)
    {
        $VM = Get-AzureVM –ServiceName $VM.ServiceName –Name $VM.Name
        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 except otherwise noted:

  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. 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 two servers so that each server has the certificates from the other two servers. Since the two SQL Server VMs reside in two different virtual networks and cannot share files readily, the easiest way to copy files across the VMs is to attach a disk drive from your local computer to the remote desktop sessions and copy the certificates to and from that attached disk drive.

  4. On SQL1, grant login permissions to SQL2 using its certificate.

    CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Sample_Login_Password!@#';
    GO
    
    CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
    GO
    
    CREATE CERTIFICATE SQL2_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL2_cert.cer';
    GO
    
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
    GO
     
    
    
  5. On SQL2, grant login permissions to SQL1 using its certificate.

    CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Sample_Login_Password!@#';
    GO
    
    CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;
    GO
    
    CREATE CERTIFICATE SQL1_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL1_cert.cer'
    GO
    
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];
    GO
     
    
    
  6. 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 
    
    
  7. Copy the backup files to SQL2 in the following directory: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP.

  8. 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://<uniqueservicename1>.cloudapp.net:5022';
    GO
     
    
    

    Note that the cloud service name, <uniqueservicename1>.cloudapp.net, is used instead of SQL1’s hostname. SQL1 and SQL2 cannot connect to each other directly and must connect to each other’s endpoint on the respective cloud services. You have already opened the default database mirroring endpoint, port 5022, when you created the SQL1.

  9. On SQL1, enable SQL1 as a mirroring partner.

    ALTER DATABASE MyDB1 SET PARTNER = 'TCP://<uniqueservicename2>.cloudapp.net:5022';
    GO
    

    Again, note that the cloud service name, <uniqueservicename2>.cloudapp.net, is used instead of SQL1’s hostname. You have already opened the default database mirroring endpoint, port 5022, when you created the SQL2.

Congratulations! You have successfully set up database mirroring across two Azure datacenters using certificates. Note that because the servers are accessing each other using the cloud service names instead of the instance names, the database mirroring monitor cannot connect to the remote server to query for status when it attempts to connect to the instance name of the remote server. To monitor the database mirroring session in the database mirroring monitor, follow the instructions below:

  1. On SQL2, enable SQL Server authentication. For more information, see Change Server Authentication Mode.

  2. On SQL2, create a sysadmin user that uses SQL Server authentication. For more information, see Create a Login.

  3. On SQL1, in SQL Server Management Studio, connect to SQL2 using <uniqueservicename2>.cloudapp.net as the server name and SQL Server Authentication as the authentication mode.

  4. From the established database engine connection to SQL2, launch the database mirroring monitor. For more information, see Start Database Mirroring Monitor (SQL Server Management Studio).

Zawartość społeczności

Dodaj
Pokaż:
© 2014 Microsoft