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

Tutorial: Log Shipping for Disaster Recovery in Hybrid IT

Updated: November 24, 2014

This tutorial shows you how to implement SQL Server log shipping end-to-end in a hybrid IT environment. 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 a SQL Server machine running behind a VPN device in your on-premise network

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

  • A SQL Server VM deployed to the back-end subnet in Azure and joined to your on-premise AD domain

  • The on-premise SQL Server machine configured as the primary database server

  • The SQL Server VM in Azure configured as the secondary database server

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-premise. If you want to use your existing on-premise network and Active Directory, skip the relevant sections and move on to Create VPN Connection to Azure and SQL Server VM in Azure. However, the later steps are not guaranteed to work with the custom specifications of your existing on-premise 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:

Configure the on-premise domain controller with DHCP service

  1. Install Windows Server 2008 R2 on an on-premise 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 configure 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 passwordContoso!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 'SQLAgent1' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    New-ADUser -Name 'SQLAgent2' -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true
    

    CORP\SQLAgent1 and CORP\SQLAgent2 are used as service accounts for the SQL Server agent services running on the two SQL Server instance servers.

  14. 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-premise 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-premise SQL Server system.

Configure on-premise SQL Server service instance

  1. Install Windows Server 2008 R2 on an additional on-premise computer that you will use for the on-premise SQL Server instance.

  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-premise network and domain, join the computers to the respective domain of your organization. Change the $computerName variable to SQLOnPrem for the respective machines.

    $domain = "CORP"
    $domainAdmin = "CORP\Administrator"
    $password = "Contoso!000"
    
    $computerName = SQLOnPrem
    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 variable for the installation directory and install SQL Server 2012 on SQLOnPrem.

    $installDir = "<Directory of SQL Server Setup.exe>"
    $agtsvcacct = "CORP\SQLAgent1"
    $sqlAdmin = "CORP\Install"
    $password = "Contoso!000"
    
    cd $installDir
    .\Setup.exe `
        /q `
        /ACTION=Install `
        /FEATURES=SQL,SSMS `
        /INSTANCENAME=MSSQLSERVER `
        /AGTSVCACCOUNT="$agtsvcacct" `
        /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. 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
    
  11. Log out of both computers.

    Logoff.exe
    

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

Create VPN Connection to Azure and 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 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.

    $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 SQL Server 2012 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-premise 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-premise 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 Establish a Site-to-Site VPN Connection.

  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 SQL Server VM in a new cloud service.

    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 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 both of the on-premise 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-premise servers in configuration when all are joined to a WSFC cluster later.

    • Add-AzureProvisioningConfig joins the VM to the on-premise 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 Azure VM until it is ready for use.

    $VMStatus = Get-AzureVM -ServiceName $serviceName -Name $sqlServerName
    
    While ($VMStatus.Status -ne "ReadyRole")
    {
        write-host "Waiting...Current Status = " $VMStatus.InstanceStatus
        Start-Sleep -Seconds 15
        $VMStatus = Get-AzureVM -ServiceName $serviceName -Name $sqlServerName
    } 
    Get-AzureRemoteDesktopFile `
        -ServiceName $serviceName `
        -Name $sqlServerName `
        -LocalPath "$workingDir$sqlServerName.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 default SQL Server installation on the new SQL Server VM you created to prepare it for joining the availability group. Specifically:

  • The VM is installed with all the SQL Server services (e.g. database engine, SSIS, SSAS, SSRS, and so on). You need to disable all SQL services other than the ones you need, namely the default MSSQLSERVER instance and the full-text search daemon.

  • You need to change the service account SQL Server Agent to CORP\SQLAgent2.

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

  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. Define the following variables.

    $desirableServices = @("SqlServer","SqlAgent","9")
    $svcaccount = "CORP\SQLAgent2"
    $password = "Contoso!000"
    

    For the variable $desirableServices, SqlServer is the service type of the database engine service, SqlAgent is the service type of the SQL Server Agent service, and 9 is the service type of the full-text search daemon.

  6. Import the SQL Server PowerShell Provider.

    Set-ExecutionPolicy -Execution RemoteSigned -Force
    Import-Module -Name "sqlps" -DisableNameChecking
    
  7. Stop all undesirable SQL services (SSIS, SSAS, SSRS, and so on) and set their start mode to Disabled.

    $wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $env:COMPUTERNAME
    $wmi.services | where {$desirableServices -notcontains $_.Type} | foreach{$_.StartMode = "Disabled"; $_.Alter(); $_.Stop();}
    
  8. Change the SQL Server Agent service account for the default instance to CORP\SQLAgent2.

    $wmi.services | where {$_.Type -eq 'SqlAgent'} | foreach{$_.SetServiceAccount($svcaccount,$password)}
    $svc = Get-Service -Name 'SQLSERVERAGENT'
    $timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
    $svc.Stop(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
    # Start the SQL Server service and wait for the service to be fully started
    $svc.Start(); 
    $svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)
    
  9. 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
    

Finally, you are ready to configure log shipping.

Configure Log Shipping

  1. Connect to SQLOnPrem again by launching the remote desktop files. To log in, use the built-in administrator account.

  2. Open a PowerShell window in administrator mode. If you have not previously logged out of SQLOnPrem, you must close the existing PowerShell window and open a new one in administrator mode to make sure that SQL Server paths are loaded in the PowerShell environment variables.

  3. Create the network share that you will use for the log shipping jobs.

    $backup = "C:\LocalBackup"
    $acct1 = "CORP\SQLAgent1"
    $acct2 = "CORP\SQLAgent2"
    
    New-Item $backup -ItemType directory
    net share backup=$backup "/grant:$acct1,FULL" "/grant:$acct2,FULL"
    icacls.exe "$backup" /grant:r ("$acct1" + ":(OI)(CI)F") ("$acct2" + ":(OI)(CI)F")
    
  4. Run the Transact-SQL script below to set up log shipping on SQLOnPrem. This script is generated from the SQL Server Management Studio (SSMS) user interface, using the following variables:

    1. Database: MyDB1

    2. Backup Directory: C:\LocalBackup

    3. Backup Directory Share Path: \\SQLOnPrem\backup

    4. Secondary Server: SQLInCloud

    5. Destination Directory for Log Backups (on SQLInCloud): C:\LocalBackup

    You will run the second part of the SSMS-generated script on SQLInCloud later.

    SQLCMD -S SQLOnPrem
    
    DECLARE @LS_BackupJobIdAS uniqueidentifier 
    DECLARE @LS_PrimaryIdAS uniqueidentifier 
    DECLARE @SP_Add_RetCodeAs int 
    
    
    EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
    @database = N'MyDB1' 
    ,@backup_directory = N'C:\LocalBackup' 
    ,@backup_share = N'\\SQLOnPrem\backup' 
    ,@backup_job_name = N'LSBackup_MyDB1' 
    ,@backup_retention_period = 4320
    ,@backup_compression = 2
    ,@backup_threshold = 60 
    ,@threshold_alert_enabled = 1
    ,@history_retention_period = 5760 
    ,@backup_job_id = @LS_BackupJobId OUTPUT 
    ,@primary_id = @LS_PrimaryId OUTPUT 
    ,@overwrite = 1 
    
    
    IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
    BEGIN 
    
    DECLARE @LS_BackUpScheduleUIDAs uniqueidentifier 
    DECLARE @LS_BackUpScheduleIDAS int 
    
    
    EXEC msdb.dbo.sp_add_schedule 
    @schedule_name =N'LSBackupSchedule_SQL11' 
    ,@enabled = 1 
    ,@freq_type = 4 
    ,@freq_interval = 1 
    ,@freq_subday_type = 4 
    ,@freq_subday_interval = 15 
    ,@freq_recurrence_factor = 0 
    ,@active_start_date = 20121127 
    ,@active_end_date = 99991231 
    ,@active_start_time = 0 
    ,@active_end_time = 235900 
    ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
    ,@schedule_id = @LS_BackUpScheduleID OUTPUT 
    
    EXEC msdb.dbo.sp_attach_schedule 
    @job_id = @LS_BackupJobId 
    ,@schedule_id = @LS_BackUpScheduleID  
    
    EXEC msdb.dbo.sp_update_job 
    @job_id = @LS_BackupJobId 
    ,@enabled = 1 
    
    
    END 
    
    
    EXEC master.dbo.sp_add_log_shipping_alert_job 
    
    EXEC master.dbo.sp_add_log_shipping_primary_secondary 
    @primary_database = N'MyDB1' 
    ,@secondary_server = N'SQLInCloud' 
    ,@secondary_database = N'MyDB1' 
    ,@overwrite = 1 
    
    Exit
    
  5. Connect to SQLInCloud again by launching the remote desktop files. To log in, use the built-in administrator account.

  6. Open a PowerShell window in administrator mode.

  7. Create the local directory that you will use for the log shipping copy and restore jobs.

    $backup = "C:\LocalBackup"
    $acct2 = "CORP\SQLAgent2"
    
    New-Item $backup -ItemType directory
    icacls.exe "$backup" /grant:r ("$acct2" + ":(OI)(CI)F")
    
  8. Run the Transact-SQL script below to set up log shipping on SQLInCloud. This is the second part of the SSMS-generated script.

    SQLCMD -S SQLInCloud
    
    DECLARE @LS_Secondary__CopyJobIdAS uniqueidentifier 
    DECLARE @LS_Secondary__RestoreJobIdAS uniqueidentifier 
    DECLARE @LS_Secondary__SecondaryIdAS uniqueidentifier 
    DECLARE @LS_Add_RetCodeAs int 
    
    
    EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
    @primary_server = N'SQLOnPrem' 
    ,@primary_database = N'MyDB1' 
    ,@backup_source_directory = N'\\SQLOnPrem\backup' 
    ,@backup_destination_directory = N'C:\LocalBackup' 
    ,@copy_job_name = N'LSCopy_SQLOnPrem_MyDB1' 
    ,@restore_job_name = N'LSRestore_SQLOnPrem_MyDB1' 
    ,@file_retention_period = 4320 
    ,@overwrite = 1 
    ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
    ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
    ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 
    
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
    BEGIN 
    
    DECLARE @LS_SecondaryCopyJobScheduleUIDAs uniqueidentifier 
    DECLARE @LS_SecondaryCopyJobScheduleIDAS int 
    
    
    EXEC msdb.dbo.sp_add_schedule 
    @schedule_name =N'DefaultCopyJobSchedule' 
    ,@enabled = 1 
    ,@freq_type = 4 
    ,@freq_interval = 1 
    ,@freq_subday_type = 4 
    ,@freq_subday_interval = 15 
    ,@freq_recurrence_factor = 0 
    ,@active_start_date = 20121127 
    ,@active_end_date = 99991231 
    ,@active_start_time = 0 
    ,@active_end_time = 235900 
    ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
    ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 
    
    EXEC msdb.dbo.sp_attach_schedule 
    @job_id = @LS_Secondary__CopyJobId 
    ,@schedule_id = @LS_SecondaryCopyJobScheduleID  
    
    DECLARE @LS_SecondaryRestoreJobScheduleUIDAs uniqueidentifier 
    DECLARE @LS_SecondaryRestoreJobScheduleIDAS int 
    
    
    EXEC msdb.dbo.sp_add_schedule 
    @schedule_name =N'DefaultRestoreJobSchedule' 
    ,@enabled = 1 
    ,@freq_type = 4 
    ,@freq_interval = 1 
    ,@freq_subday_type = 4 
    ,@freq_subday_interval = 15 
    ,@freq_recurrence_factor = 0 
    ,@active_start_date = 20121127 
    ,@active_end_date = 99991231 
    ,@active_start_time = 0 
    ,@active_end_time = 235900 
    ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 
    
    EXEC msdb.dbo.sp_attach_schedule 
    @job_id = @LS_Secondary__RestoreJobId 
    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  
    
    
    END 
    
    
    DECLARE @LS_Add_RetCode2As int 
    
    
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
    BEGIN 
    
    EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
    @secondary_database = N'MyDB1' 
    ,@primary_server = N'SQLOnPrem' 
    ,@primary_database = N'MyDB1' 
    ,@restore_delay = 0 
    ,@restore_mode = 0 
    ,@disconnect_users= 0 
    ,@restore_threshold = 45   
    ,@threshold_alert_enabled = 1 
    ,@history_retention_period= 5760 
    ,@overwrite = 1 
    
    END 
    
    
    IF (@@error = 0 AND @LS_Add_RetCode = 0) 
    BEGIN 
    
    EXEC msdb.dbo.sp_update_job 
    @job_id = @LS_Secondary__CopyJobId 
    ,@enabled = 1 
    
    EXEC msdb.dbo.sp_update_job 
    @job_id = @LS_Secondary__RestoreJobId 
    ,@enabled = 1 
    
    END 
    
    Exit
    

Congratulations! You have successfully implemented log shipping in a hybrid-IT environment, with the primary database server running on-premise and the secondary database server running in Azure. You can monitor the log shipping jobs in SQL Server Agent to make sure that the logs are successfully created on SQLOnPrem and successfully copied and restored on SQLInCloud.

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft