Exportera (0) Skriv ut
Visa allt
EN
Det här innehållet finns inte tillgängligt på ditt språk men här finns den engelska versionen,

Tutorial: Database Mirroring for Disaster Recovery in Hybrid IT

Updated: April 1, 2014

This tutorial shows you how to implement SQL Server database mirroring for disaster recovery end-to-end in a hybrid IT environment. In this configuration, the principal database server runs on-premise and the mirror database server runs in Azure. You can implement this scenario without a VPN connection between Azure and your on-premise network if you use server certificates. Furthermore, it is possible for your principal database server to run behind a NAT device on-premise if you forward the appropriate ports on your NAT device to the server.

In this tutorial, you will deploy asynchronous database mirroring as a disaster recovery solution using a SQL Server virtual machines (VM) in Azure and an on-premise database server. At the end of the tutorial, your SQL Server high availability solution will consist of the following elements:

  • One virtual network running in the US West datacenter

  • One SQL Server machine (SQLOnPrem) deployed your on-premise network, running behind a NAT device

  • One SQL Server VM (SQLInCloud) deployed to Azure in the US West datacenter

  • SQLOnPrem is the principal database server

  • SQLInCloud 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:

Configure on-premise SQL Server service instance

  1. Install Windows Server 2008 R2 on an on-premise computer that you will use for the principal database 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. If you have not already done so, change the computer name to SQLOnPRem.

    $computerName = "SQLOnPrem"
    netdom renamecomputer $env:COMPUTERNAME /newname:$computerName /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. Specify the directory that contains the SQL Server Setup files in $installDir and install SQL Server 2012 and SQL Server Management Studio (SSMS) on the computer. Specifying SSMS feature during setup causes the command line tools and PowerShell providers to be installed.

    $installDir = "<Directory of SQL Server Setup.exe>"
    
    cd $installDir
    .\Setup.exe `
        /q `
        /ACTION=Install `
        /FEATURES=SQL,SSMS `
        /INSTANCENAME=MSSQLSERVER `
        /SQLSYSADMINACCOUNTS="Administrator" `
        /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. Exit the current PowerShell window. The next time you open a PowerShell window, SQL Server binary paths are included in the PATH environment variable so that you can use the command-line tools and SQL Server PowerShell provider to set up database mirroring.

    Exit
    
  11. Log into your on-premise NAT device as the administrator and add a port forwarding rule which points all inbound traffic on port 5022 to SQLOnPrem, port 5022. For instructions on setting up your NAT device, see the user manual from the respective manufacturer.

    Configuring port forwarding for port 5022 on NAT device enables the database mirroring traffic to flow from the Azure VM to the on-premise database server.

You are now finished with the on-premise SQL Server configuration. Next, you will configure the SQL Server VM in Azure.

Create the Virtual Network and SQL Server VMs

  1. In a PowerShell window on a computer that is connected to the internet, 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"
    $subnetName = "Back"
    

    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. Furthermore, you will need to use the value of $serviceName when you configure the database mirroring session later.

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

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

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

  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 Back subnet, or 10.1.1.0/24, is where you will place your SQL Server VM. If you change the $affinityGroupName, $virtualNetworkName, and $subnetName 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="Back">
                <AddressPrefix>10.10.1.0/24</AddressPrefix>
              </Subnet>
            </Subnets>
          </VirtualNetworkSite>
        </VirtualNetworkSites>
      </VirtualNetworkConfiguration>
    </NetworkConfiguration>
    
  5. Create a storage account that is associated with the affinity group you created and 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 SQL Server VM in a new cloud service.

    New-AzureVMConfig `
        -Name $sqlServerName `
        -InstanceSize Large `
        -ImageName $sqlImageName `
        -MediaLocation "$storageAccountContainer$sqlServerName.vhd" `
        -DiskLabel "OS" | 
        Add-AzureProvisioningConfig `
            -Windows `
            -Password $vmAdminPassword `
            -AdminUserName $vmAdminUser `
            -DisableAutomaticUpdates ` |
            Set-AzureSubnet `
                -SubnetNames $subnetName |
                Add-AzureEndpoint `
                    -Name "SQL" `
                    -Protocol "tcp" `
                    -PublicPort 1433 `
                    -LocalPort 1433 | 
                    Add-AzureEndpoint `
                        -Name "SQL" `
                        -Protocol "mirroring" `
                        -PublicPort 5022 `
                        -LocalPort 5022 | 
                        New-AzureVM `
                            -ServiceName $serviceName `
                            –AffinityGroup $affinityGroupName `
                            -VNetName $virtualNetworkName
    

    This series of piped commands do the following things:

    • New-AzureVMConfig creates a VM configuration using the SQL Server 2012 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 the on-premise database server uses the C drive for database files, you will forego moving the database files. This way, the Azure VM can be identical to the on-premise server in configuration.

    • Add-AzureProvisioningConfig specifies a standalone Windows machine, sets the administrator password, and disables automatic updates.

    • Add-AzureEndpoint adds two access endpoints: port 1433 for connectivity from on-premise client applications 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. 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 Azure VM until it is ready for use.

    $VMStatus = Get-AzureVM -name $sqlServerName
    
    While ($VMStatus.Status -ne "ReadyRole")
    {
        write-host "Waiting...Current Status = " $VMStatus.Status
        Start-Sleep -Seconds 15
        $VMStatus = Get-AzureVM -name $sqlServerName
    } 
    
    Get-AzureRemoteDesktopFile `
        -ServiceName $serviceName `
        -Name $sqlServerName `
        -LocalPath "$workingDir$sqlServerName.rdp"
    

Initialize the SQL Server VM

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 two servers. Keep the PowerShell window open for subsequent steps.

Configure Database Mirroring Using Certificates

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

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

    SQLCMD -S SQLInCloud
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
    GO
    
    CREATE CERTIFICATE SQLInCloud_cert WITH SUBJECT = 'SQLInCloud certificate for database mirroring';
    GO
    
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
       FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQLInCloud_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
    GO
    
    BACKUP CERTIFICATE SQLInCloud_cert TO FILE = 'SQLInCloud_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 SQLOnPrem, grant login permissions to SQLInCloud using its certificate.

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

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

  8. On SQLInCloud, 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://<NAT_PUBLIC_IP_ADDRESS>:5022';
    GO
     
    
    

    Note you must supply the public facing IP address for your on-premise NAT device instead of SQLOnPrem’s hostname. SQLInCloud cannot connect to SQLOnPrem directly and traffic must be forwarded to SQLOnPrem from the NAT device. You have already configured port forwarding on your on-premise NAT device for port 5022, so the ALTER DATABASE command should succeed.

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

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

    Note that the unique cloud service name, <uniqueservicename>.cloudapp.net, is used instead of SQLInCloud’s hostname. You have already opened the default database mirroring endpoint, port 5022, when you created SQLInCloud.

Congratulations! You have successfully set up database mirroring in hybrid IT using certificates. Note that because the servers are accessing each other using the cloud service name and the NAT device instead of the instance names, the database mirroring monitor cannot connect to the instance name of the remote server to query for status. To monitor the database mirroring session in the database mirroring monitor, follow the instructions below:

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

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

  3. On SQLOnPrem, in SSMS, connect to SQLInCloud using <uniqueservicename>.cloudapp.net as the server name and SQL Server Authentication as the authentication mode.

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

Gruppinnehåll

Lägg till
Visa:
© 2014 Microsoft