SQL Server log shipping with Microsoft Azure VMs

Table of Contents

1Overview

1.1Scenario

1.2Assumptions

2Demo environment configuration

2.1Steps Summary

2.2Set up a Microsoft Azure virtual network (AzureVPN1)

2.2.1Access the Microsoft Azure Management Portal

2.2.2Setting up the Microsoft Azure Virtual Network

2.3Set up a point to site VPN Tunnel

2.4Create a Domain Controller in a Microsoft Azure VM (AzureDC4)

2.4.1Register a DNS server

2.4.2Create a VM with a persistent IP address

2.4.3Configure the VM as a Domain Controller

2.4.4Create SQL Server service domain accounts

2.5Create an on-premises VM for SQL Server (SqlVM1)

2.5.1Create Windows Windows 2012 R2 Datacenter VHD (SqlVM1)

2.5.2Connect to Microsoft Azure VPN

2.5.3Domain join on-premises machine (Sql1VM) to the DC on Microsoft Azure

2.5.4Install SQL Server 2014

2.6Create a SQL Server VM in a Microsoft Azure VM (SqlVM2)

2.7Set up Log Shipping

2.7.1Configure SQL Server service accounts

2.7.2Open Firewall Ports

2.7.3Set up File Share

2.7.4Set up Log Shipping

3Appendix

3.1How do I set up Microsoft Azure PowerShell management?

3.1.1Download PowerShell cmdlets for Microsoft Azure

3.1.2Setup a secure connection to Microsoft Azure

3.2How do I install Active Directory on Windows Server 2012 R2 using Server Manager?

3.3SQL Server installation with PowerShell

1

1 Overview

This document covers scenarios and steps on configuring the SQL Log shipping using Microsoft Azure Virtual Machines (VMs). The process described below focuses on a hybrid scenario with on-premises SQL server connected to a Microsoft Azure Virtual Network through a VPN tunnel.

In this demo, Log Shipping is used between an on-premises SQL Server and Microsoft Azure Virtual Machines for backup.

Note: For other cloud scenarios, see related HADR demos, “Backup to Cloud” and “SQL Server AlwaysOn."

1.1Scenario

The Hybrid Scenario offers the most value when combined with Microsoft Azure. Using Microsoft Azure for backup leverages the benefits of Microsoft Azure Service Level Agreement (SLA) to provide a reliable disaster recovery solution.

  1. This scenario will be based on on-premises SQL server and using Microsoft Azure VMs.
  2. The on-premises SQL Instance will act as the primary server.
  3. The secondary SQL Instance will be hosted on Microsoft Azure VM and can be made available to other applications as ‘Read Only’.
  4. An Active Directory server will be configured with a persistent IP address.
  5. The on-premises SQL Instance will join the Active Directory domain configured on Microsoft Azure using a VPN tunnel.
  6. To simplify the scenario the domain controller will also be used as a file share to host the shipped logs.

This scenario configuration can be visualized through the following diagram.

Figure 1 – Hybrid scenario

1.2Assumptions

S2S VPN

This demo assumes that Site-to-Site (S2S) VPN has already been configured between the Microsoft Azure subscription to be used for the demo and the customer on-premises network. Configuring a Site-to-Site VPN is out of the scope for this demonstration. If needed, the customer can use the Azure IaaS Foundations Jumpstart offering to enable connectivity. Documentation for VPN can be found at Configure a Site-to-Site VPN using the Management Portal Wizard.

Environment Cleanup

This demo uses SQL Server names SqlVM1 and SqlVM2. If these servers and server names exist, it is recommended to delete them prior to starting this demonstration. If a domain controller was create (AzureDC4), it can remain but the Computer Objects should be deleted when the servers are deleted. This will prevent any conflicts and confusion in the steps outlined below.

2 Demo environment configuration

This document describes the steps necessary for building out the demonstration. This section talks about how to setup log shipping in a hybrid scenario.

For this scenario, an on-premisesserver with SQL Server 2014 or above and a secondary instance in Microsoft AzureVirtual Machines (VMs) will be used. Both machines are joined to the same Active Directory domain. Although, not necessary, it is recommended to host an Active Directory Domain Controller in Microsoft Azure. For purposes of this demonstration, the on-premises server is joined to the DC on Microsoft Azure VM. This domain controller can serve as a file share required for log shipping.

You can also utilize previous versions of SQL Server (SQL 2008R2 and up), but the steps in this document are shown from SQL 2014 perspective.

This demo uses the following servers and resources:

Server / Description
SqlVM1 / On-premises server with SQL Server 2014
SqlVM2 / SQL Server 2014 hosted in Microsoft AzureVM
AzureDC4 / Active Directory domain controller hosted in Window Azure VM. This domain is typically an extension of the on-premises domain. This server also hosts the file share for SQL Logs.
AzureVPN1 / The name used in this document to indicate the name of the VPN gateway for your Microsoft Azure Subscription
Subnet-1 / Microsoft Azure Virtual Network used to assign IP address to virtual machines.

2.1Steps Summary

The steps below will reference these sections as needed. The steps can be summarized as:

  1. Set up a Microsoft Azure virtual network (AzureVPN1) – optional
  2. Set up a point-to-site VPN - optional
  3. Create a domain controller in a Microsoft Azure VM (AzureDC4)
  4. Create a SQL VM on-premises (SqlVM1)
  5. Create a SQL VM in the Cloud (SqlVM2)
  6. Set up Log Shipping
  7. Set up a Microsoft Azure virtual network (AzureVPN1)

This step is optional depending upon the state of the environment.

If the scenario pre-requisites have been met, a site-to-site VPN will exist and the Microsoft Azure virtual network will already be configured in the portal. If this condition is met, this step can be skipped after taking note of the VPN name in the Microsoft Azure portal. In the scenario below, use this existing VPN name in place of AzureVPN1 when specified.

2.2.1 Access the Microsoft Azure Management Portal

  1. Go to Microsoft Azure portal and sign-in use the credentials for which the Microsoft Azure subscription is configured.

The Microsoft Azure management portal can be found at: ( If you do not have a Microsoft Azure account, visit Microsoft Azure 3-Month free trial.

2.2.2 Setting up the Microsoft AzureVirtual Network

  1. From the portal, select the button from the bottom of the window.
  2. Select Network Services, then Virtual Network, and then Custom Create.

  1. On the custom create wizard, the Name field, enter ‘AzureVPN1’.
  2. In the Affinity Group dropdown, select Create new affinity group and the screen will change as shown below.
  3. In the Region dropdown choose an appropriate region for your demo.
  4. In the Affinity GroupName field, enter AzureAG1
  5. Select the Next () button.
  6. In the server selection dropdown list, select the DNS server ‘AzureDNS1’ and select the next () button.

  1. In the Starting IP dropdown, select 192.168.0.0 for both the Address Space and Subnet as shown below.

  1. Change the CIDR (Address Count) field to ‘/27 (32)’, and select the Complete () button.
  2. Select the Networks tab, and verify that the new VPN is created successfully.

2.3Set up a point to site VPN Tunnel

This step is optional and only needed for testing the scenario. If the site-to-site VPN is configured, you can skip this step.

You can test the log shipping scenario without a site-to-site VPN by using a point-to-site VPN instead. If you need to build a point-to-site VPN, you can follow the steps documented at Configure a Point-to-Site VPN using the Management Portal Wizard.

2.4Create a Domain Controller in a Microsoft Azure VM (AzureDC4)

You will be creating a Domain Controller (DC) in subsequent steps for this demo, so you will need a VM to host the DC. This VM will also act as the DNS server. The VM must have a persistent IP address. The following steps will guide you through the creation of this VM using the Microsoft Azure portal interface.

2.4.1 Register a DNS server

  1. From the portal, select the button from the bottom of the window.
  2. Now, select Network Services, then Virtual Network, then Register DNS Server, as shown below:

In the Name field, enter AzureDNS1.

In the DNS Server IP Address field enter 192.168.0.4 as shown above.

  1. Select the Register as DNS Server button.
  2. In the Networks tab, verify that the newly registered DNS server was created successfully.

2.4.2 Create a VM with a persistent IP address

  1. Select Compute, then Virtual Machine, then From Gallery.
  1. Select Window Server 2012 R2 Datacenter from the image gallery.
  1. Provide the VM configuration details, and select the next () button.
  1. Enter the configuration details shown below, and select the next () button.
  1. Accept the defaults on the final step of the Wizard and select the Complete () button.
  2. In the Virtual Machines tab, verify that the VM have been created successfully.

More information on assigning static IP addresses to Microsoft Azure VMs can be found at Allocating Static IP Addresses to your VMs.

Note: To receive the IP address 192.168.0.4 for the Domain Controller VM, this VM should be the first VM created on the virtual network or be set to use a static IP via Set-AzureStaticVNetIP cmdlet described in the next step

  1. Optionally, to guarantee this VM keeps the IP address assigned, even on de-allocation, run the following cmdlet in Microsoft Azure PowerShell (version 0.7.3 or higher):

Get-AzureVM “AzureDC4” | Set-AzureStaticVnetIP –IpAddress “192.168.0.4”

2.4.3 Configure the VM as a Domain Controller

  1. Using Remote Desktop, log in to the VM (AzureDC4). If you have not connected to the VM before you can use the button on the bottom of the screen to initiate the connection. More information on connecting to Microsoft Azure VMs can be found inHow to Log on to a Virtual Machine Running Windows Server.
  1. Launch Windows PowerShell as an administrator and implement the following and continue to Step 8:

Get-WindowsFeature Active Directory (AD)-Domain-Services | Install-WindowsFeature

  1. Alternately, launch the Server Manager, select option 2, Add roles and features to launch the Add Roles and Features Wizard.
  2. In the Installation Type screen, select the Role-based or feature-based installation radio button.
  3. On the Server Selection screen, choose the current server: AzureDC4.
  4. On the Server Roles screen, selectActive Directory Domain Services.
  5. For the remaining steps of the wizard, use the default values, and initiate the installation.
  6. After the AD installation, promote the server as Domain Controller Server by implementing the following PowerShell, you will be prompted for the password.:

# verify the following script is correct for your environment

Import-Module ADDSDeployment

Install-ADDSForest `

-CreateDnsDelegation:$false `

-DatabasePath "C:\Windows\NTDS" `

-DomainMode "Win2012R2" `

-DomainName "AD.AzureVPN1.net" `

-DomainNetbiosName "AD" `

-ForestMode "Win2012R2" `

-InstallDns:$true `

-LogPath "C:\Windows\NTDS" `

-NoRebootOnCompletion:$false `

-SysvolPath "C:\Windows\SYSVOL" `

-Force:$true

  1. Alternately, implement the following steps in the Server Manager. Click “Promote this server to a domain controller.”
  2. In the configuration wizard, select the Add a new forest radio button.
  3. Use ‘AD.AzureVPN1.net’ for the Active Directoryroot domain name.
  4. Download an new RDP connection file for AzureDC4 after the promotion to a DC has completed and server as restarted. Connect to the AzureDC4 to verify your connection.
  1. Note: After promoting the server to a domain controller, remember to specify the domain when attaching to the VM from Remote Desktop. Instead of logging in using a local machine account you will use the domain account of the same name. In the above example you need to log in as user: AD\AzureDC4Admin

2.4.4 Create SQL Server service domain accounts

The SQL Servers participating in this scenario need to be configured to use domain accounts.

  1. Use your computer to log in to the AzureDC4 (Active Directory) VM.

Create the following domain accounts:

  1. AD\sqladmin
  2. AD\sqlvm1dbaccount
  3. AD\sqlvm1agentaccount
  4. AD\sqlvm2dbaccount
  5. AD\sqlvm2agentaccount

The following script can be used to create the accounts using the Active Directory GUI or the following PowerShell:

Import-Module ActiveDirectory

# use the appropriate pwd for your accounts

$pwd = (ConvertTo-SecureString "Your Pwd" -AsPlainText -Force)

New-ADUser -Name ‘sqladmin’ -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true

New-ADUser -Name ‘sqlvm1dbaccount’ -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true

New-ADUser -Name ‘sqlvm2dbaccount’ -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true

New-ADUser -Name ‘sqlvm1agentaccount’ -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true

New-ADUser -Name ‘sqlvm2agentaccount’ -AccountPassword $pwd -PasswordNeverExpires $true -ChangePasswordAtLogon $false -Enabled $true

Once completed, verify thatthe following domain accounts are present in the domain

Account / Description
AD\sqladmin / SQL Admin account
AD\sqlvm1dbaccount / SqlVM1 Database services account
AD\sqlvm1agentaccount / SqlVM1 Agent service account
AD\sqlvm2dbaccount / SqlVM2 Database services account
Ad\sqlvm2agentaccount / SqlVM2 Agent service account

2.5Create an on-premises VM for SQL Server (SqlVM1)

This demo requires one on-premises SQL Server. This server may be physical or virtual. If the server exists, configure the server using the instructions in the next sections.

Otherwise, use the customer’s standard operating procedures for creating a server. The servers must use Windows Server 2012 operating system (or higher) and SQL Server 2014.

This demo is written using Windows Server 2012 R2

  • For Windows Server, download the evaluation from:
  • For SQL Server 2014, download the evaluation from:

This server will be referred to as SqlVM1. The synopsis of the steps detailed in this section is:

  1. Use a customer-provided image or download the evaluation image and install the operating system.
  2. Join the server to the domain created in Section 2.4.3
  3. Install SQL Server 2014

Details on these steps are below.

2.5.1 Create Windows Windows 2012 R2 Datacenter VHD (SqlVM1)

  1. Download Windows Server 2012 R2 Datacenter evaluation VHD version and extract the VHD from the downloaded file (vhd_server_serverdatacentereval_en-us.exe) into a location of your choice.
  1. Open Hyper-V Manager and create a new virtual machine and attach the vhd from step 1 above. Follow the steps from starting with the section titled Create a New Virtual Machine & Attach the VHD.
  2. Name the virtual machine SqlVM1, depending on your version of Host OS and version of Hyper-V Manger, you may be given the option to choose the generation of this virtual machine, select “Generation 1”. Microsoft Azure currently does not support “Generation 2” virtual machines.
  3. Select 3584 MB (3.5 GB) of memory, and attach to your local internet connection.
  4. Use an existing virtual hard disk that you downloaded and extracted in step 1.
  5. Complete the wizard, and start the virtual machine (SqlVM1). Except the license terms work through the wizard accepting the suggest defaults.
  6. Create a local account as an Admin (SQLVM1Admin) and specify an Admin password like (pass@word1).
  7. Verify network access and then ensure that you have run Windows Update to patch this virtual machine.
  8. Connect to Microsoft Azure VPN

If the prerequisites for this jumpstart have been completed, you should find the customer’s network has a functioning VPN connection available, and your on-premises machines should be able to access Azure network resources.

If this is not the case, or you wish to walk through this demo without a site to site VPN, you can configure a point to site VPN following these instructions at or

2.5.3 Domain join on-premises machine (Sql1VM) to the DC on Microsoft Azure

  1. Using your computer, log in to the SqlVM1 and join the machine to the domain ‘AD.AzureVPN1.net’, ensure that your computer name is SqlVM1.
  1. When prompted for credentials, give the AD Admin account credentials (AD\AzureDC4Admin if you used the values specified earlier in this document) and restart the server
  2. Install SQL Server 2014
  1. We recommend that you download the installation International Standards Organization (ISO) file to the host machine and not to the SQLVM1 file system.
  1. Mount SQL Server 2014RTM Enterprise ISO image in Hyper-V Manager or other hypervisor manager.

The easiest way to install SQL Server is to following the steps from

Ensure that you have a VPN connection established from the on-premises server to Microsoft Azure.

Alternatively, SQL Server can be quickly installed using PowerShell. A sample script for installing SQL Server using PowerShell can be found in Appendix 3.3.

2.6Create a SQL Server VM in a Microsoft Azure VM (SqlVM2)

Using a similar process describedwhen creating the domain controller, you will now create an additional VM from the image gallery to host SQL server instances.

  1. Create a new VM from the image gallery using the SQL Server 2014 RTM Enterprise Windows Server 2012 R2 image.
  1. Name the VM ‘SqlVM2’ and use default values for all other selections unless specified differently below.
  2. On the second step of the wizard,in the Region/Affinity Group/Virtual Network dropdown, select AzureVPN1. In the Virtual Network Subnets select Subnet-1.
  1. Complete the remaining steps using default values.
  2. Verify that the VM was created successfully.
  1. Connect to the SQL VM with Remote Desktop. If you have not connected to the VM before you can use the button on the bottom of the screen to initiate the connection. More information on connecting to Microsoft Azure VMs can be found here.
  1. Once you are connected to SqlVM2, join it to the domain ‘AD.AzureVPN1.net’ by using the following PowerShell cmdlet:

Add-Computer –DomainName “AD.AzureVPN1.net”

Alternately, use the GUI by following the screenshots below:

  1. At the prompt for credentials, give the AD Admin accountcredentials (if you used the values above this will be AzureDC4Admin) and allow the server to restart.

2.7Set up Log Shipping

2.7.1 Configure SQL Server service accounts

Repeat the following steps for both the on-premises instance SqlVM1, and the Microsoft Azure VM SqlVM2.

  1. Using your computer, log in to the SQL VM and configure the SQL Service and SQL Agent Service log on credentials to use the domain accounts just created.