SQL Server 2014 High Availability & Disaster Recovery in Azure Guidance for Cloud Solution Provider Program (CSP) Partners

Introduction

The Microsoft Cloud Solution Provider (CSP) program enables partners to sell Microsoft Online services to customers with additional services like support, billing and management. As part of the CSP program partners are able to create tenants, provide them Azure subscriptions and manage various services on behalf of the customer. SQL Server 2014 enables partners to provide High Availability (HA) and Disaster Recovery (DR) to their customers using Azure IaaS capabilities. This document outlines the recommended guidance that partners can leverage to successfully set up High Availability and Disaster Recovery scenarios using either SQL Server 2014 Enterprise or SQL Server 2014 Standard editions in an Azure subscription being managed via the CSP Program.

High Availability Scenarios

HA SQL Server 2014 Enterprise Edition

Both high availability and disaster recovery utilize SQL Enterprise Edition AlwaysOn Availability Group feature. This feature provides the most robust HA and DR capabilities. The HA scenario is contained within a single Azure Region and virtual network. Existing Domain Controllers and File Servers should be configured to support this SQL implementation to include permissions to the File Server to support Quorum and SQL Backups. The domain controller contains the accounts required that will be used to run the SQL Service and give access to the appropriate domain SQL Server Managers. The file server contains the file share used for SQL data.

At least two SQL servers are required. They should be placed in an Availability Set in order to leverage Azure's native availability options. They should be placed in a Resource Group to leverage Azure's provisioning and management options to include all Virtual Networks and HA components (i.e. SQL VMs). These two SQL servers are configured to use AlwaysOn on top of Windows Server Failover Clustering (WSFC).

The illustration below outlines all of the key components recommended in this guidance to support SQL Server 2014 HA using the Enterprise edition. Azure also provides ARM templates which automate the setup of the AlwaysOn Availability Group in a single region. A PowerShell script is also provided in this guidance for automated deployment of this ARM template.

An internal load balancer is used to route traffic associated to the listener. If a client is connected when a manual failover with an active primary is initiated, the client will remain connected to the node which has become a secondary. This doesn't pose any issues for read operations, but write operations will fail until the client disconnects and reconnects.

HA SQL Server 2014 Standard Edition

SQL 2014 Standard Edition AlwaysOn Failover Cluster Instances is currently not a supported scenario due to the unique networking challenges and inability to configure shared attached storage like SANs in Azure VMs. Furthermore, Azure currently does not have the ability to configure shared attached storage.

Disaster Recovery Scenarios

DR SQL Server 2014 Enterprise Edition

Both high availability and disaster recovery utilize SQL Enterprise Edition AlwaysOn feature. This feature provides the most robust HA and DR capabilities. The DR scenario expands on the HA scenario by adding DC and SQL server in another Azure region.

Existing Domain Controllers and File Servers can be used if available. The domain controller contains the accounts required. The file server contains the file share witness\backup location used for SQL data.

In addition to the two SQL servers required for the HA scenario, an additional SQL server is created in the secondary Azure Region. The SQL AlwaysOn configuration is extended to include the SQL servers in the secondary Azure Region. Although an additional DC is not needed, we recommend adding it as a best practice to support authentication in each regionally in case a disaster occurs.

DR SQL Server 2014 Standard Edition

Implementing SQL Server 2014 Disaster Recovery is possible using SQL Log Shipping between 2 or more regions.

The DR scenario adds a single DC and SQL server in each Azure region. The domain controller contains the accounts required in the secondary region. SQL Log Shipping is configured between the SQL cluster in the primary Azure Region and the SQL server in the secondary Azure region. The illustration below provides a high level view of what is described here.

The subsequent sections provide step by step guidance for each of these scenarios.

SQL Server 2014 HA in Azure IaaS (Single Region) with AlwaysOn using SQL Enterprise

This lab scenario walks the partner participant through installing and configuring a new SQL Server 2014 environment with AlwaysOn using SQL Server Enterprise Edition in Azure. To test, you will need at least 4 VM as outlined in the above architecture diagram.

Preconditions

  • CSP Tenant account with active Azure subscription.
  • Existing resource group in which to deploy resources.
  • Azure virtual machine configured as domain controller (DC).
  • Custom DNS in virtual network configured to refer to DC.
  • Azure virtual machine file server joined to the domain.
  • A service account in the domain to run SQL Server services.
  • Two Azure virtual machines each running SQL Server Enterprise 2014, domain joined and created in an availability set.

Note: We have included a number of references in the appendix to help partners with non-SQL configurations for guidance as needed and do refer to them in the steps as appropriate.

Use the table below to record the environmental data required to complete the configuration.

Prerequisite

Data

Azure Resource Group

SQLAlwaysOn

Region

West US

DNS IP

10.0.0.4

Azure VNET

Sqlneta (10.0.0.0/16)/Subnet (10.0.0.0/24)

File Share Witness\Backup

\\SQLFS01\Quorum, \\SQLFS01\BACKUP

SQL Service Account

SQLNET\SQLSERVICE

Availability Group Name

SQL Group

Availability Group Listener

SQLListener (10.0.0.20)

High level summary of the step-by-step process

  • Validate subscription and preconditions
  • Prepare data disk
  • Create and configure the Windows Failover Cluster (WSFC)
  • Enable AlwaysOn availability groups
  • Create, restore and backup databases for use with AlwaysOn
  • Create AlwaysOn Availability Group
  • Configure WSFC Client Access Point
  • Create and configure an Azure Internal Load Balancer (ILB)
  • Test the solution

Key SQL Server configuration Step-by-Step

Validate subscription and preconditions

Log into the Azure subscription and ensure the preconditions have been met.

Prepare data disk

In the settings of sqlfs01 click "Disks". Click "Attach new" then click "OK" to attach the disk.

Prepare the data disk. See appendix E for instructions.
Create a Backup Share on fs01. Give the SQL Service account full access to the share.

Create a Quorum Share on fs01. Give the computer accounts for the SQL Servers full access to the share.

Use the "Add Roles and Features" wizard to add the "Failover Clustering" feature. Be sure to accept adding the management features when prompted.
Open the SQL Server Management Studio and connect to SQL Server.
Right click the database server and click "Properties".

Click on the "Database Settings" page.

Ensure that the "Data" and "Log" directories are configured with valid locations. Then click "OK".

Repeat Steps 6 - 10 while logged on to SQLSRVB

Create and configure the Windows Server Failover Cluster (WSFC)

On sqlsrva, open an Administrator PowerShell window and run: Import-Module FailoverClusters

Run: Test-Cluster sqlsrva,sqlsrvb


Run: New-Cluster -Name WFC -Node sqlsrva,sqlsrvb -StaticAddress 10.0.0.15 –NoStorage


Run: Set-ClusterQuorum -FileShareWitness \\SQLFS01\Quorum

Enable AlwaysOn availability groups

Open the SQL Server Configuration Manager and click on "SQL Server Services" Double click "SQL Server".


Click "Stop" then click "Yes" when prompted to stop "SQLSERVERAGENT". Update the Log On account to the domain service account and click the "Service" tab.

Make sure "Start Mode" is set to "Automatic" then click the "AlwaysOn High Availability" tab.


Check "Enable AlwaysOn Availability Groups" and click "OK".


Click "OK". Double click "SQL Server Agent".


Update the Log On account and click the "Service" tab.


Make sure the "Start Mode" is set to "Automatic" and click "OK".


Right click the SQL Server Agent and click "Start".


Repeat steps 16 – 23 on sqlsrvb.

Create, restore and backup databases for use with AlwaysOn

Browse to the AdventureWorks Sample database page (http://msftdbprodsamples.codeplex.com) and download the full database backup.
Extract the backup and restore the database.


Set the Recovery Model of the database to Full.


Run an initial Full backup of the database on SQLSRVA


Create AlwaysOn Availability Group

Right click "AlwaysOn High Availability" and click "New Availability Group Wizard".


Click "Next".


Type a name for the group and click "Next".


Select the database and click "Next".


Add sqlsrvb as a replicas and click the "Endpoints" tab. Note the port being used for the endpoints. Add a firewall rule to sqlsrva and sqlsrvb to open that port. Click Next.


Select Full synchronization, type in the path to the backup share and click "Next".


Click "Next".


Click "Finish".


Click "Close".


Configure WSFC Client Access Point

Add a firewall rule to open port 59999. Open the Failover Cluster Manager and add a Client Access Point.


Type a name and click Next twice.


Click Finish.


Click the Resources tab, expand the new access point resource, right click the IP Address resource and click properties.


Specify a more meaningful name. Set a static IP address, one that isn't currently being used, and click "OK".


Run this PowerShell script on each of the SQL Servers. Be sure to set the Probe IP and IP Resource Name correctly.

Import-Module FailoverClusters
$net = (Get-ClusterNetwork)[0].Name
$params = @{"Address"="<Probe IP>";
    "ProbePort"="59999";
    "SubnetMask"="255.255.255.255";
    "Network"="$net";
    "OverrideAddressMatch"=1;
    "EnableDhcp"=0}
Get-ClusterResource "<IP Resource Name>" | Set-ClusterParameter -Multiple $params

Right click the Access Point and click Bring Online.


Right click the availability group and click Properties.


Click the Dependencies tab and set the Access Point as a Resource then click "OK".


Open SQL Server Management Studio, right click the new listener and click Properties.


Set the port number and click "OK".

Create and configure an Azure Internal Load Balancer (ILB)

Add a Load Balancer to the VNET in Azure. Be sure to use the same IP address as the listener.


Add a probe to the ILB.


Add your SQL Servers to a new backend pool for the ILB.

Add a Load balancing rule.


Test the solution

Install SQL Server Management Studio on a virtual machine in the virtual network, but not one of the SQL Servers.
Open SQL Server Management Studio on the client and connect to the Lister Name. Expand the Availability groups and ensure you are connected to primary.


Right click the availability group and click "Show Dashboard".


Click "Start Failover Wizard".


Click "Next".


Click "Next".


Click "Connect", connect to the secondary and click "Next".


Click "Finish" to run the failover.


Click "Close".


Close and SQL Server Management Studio on the client.

Post Conditions

You have now created an Azure High Availability environment that supports a designated availability group listener that all applications can leverage. Any additional DB that needs to be targeted for HA will need to be fully backed before they can be included in HA as shown below.

SQL Server 2014 HA+DR in Azure (Across 2 Regions) with AlwaysOn using SQL Enterprise

The following diagram identifies the key components needed for delivering SQL 2014 High Availability and Disaster Recovery in Azure IaaS across two regions. The setup leverages the HA SQL Server 2014 Enterprise Edition setup documented in section 2. One additional SQL Server on Windows Server 2012R2 will be needed for the secondary region. This implementation is exclusively in Azure and no on premise servers are needed. This procedure will focus on setting up the key elements that enable AlwaysOn in Azure across two regions (called sites below) using SQL Server 2014 Enterprise.

Figure 1 High Level Architecture

The network architecture diagram shown in figure 1 supports implementation of the prerequisite Site-to-Site (S2S) Virtual Private Network (VPN) Tunnel between two customer regions.

Preconditions

  • All components outlined in section 2 have been configured and are online.

Note: We have included a number of references in the appendix to help partners configure typical components not identified in the guidance. The procedure may refer to them in the steps as appropriate.

Use the table below to record the environmental data required to complete the configuration.

Prerequisite

Data

Azure Resource Group

SQLAlwaysOn

Region

East US

DNS IP

10.1.0.4

Azure VNET

Sqlnetb (10.1.0.0/16)/Subnet (10.1.0.0/24)

FileShare

\\FS01\SQLData

File Share Witness\Backup

\\SQLFS01\Quorum, \\SQLFS01\BACKUP

SQL Service Account

SQLNET\SQLSERVICE

Availability Group Name

SQL Group

Availability Group Listener

SQLListener

High level summary of the step-by-step process

  • Build Virtual Network for backup site
  • Build out infrastructure components in secondary region (DC\SQL)
  • Ensure new SQL server is given permissions to the existing File Server shares
  • Add new SQL Server to WSFC as a new node
  • Enable AlwaysOn on the new SQL VM
  • Delete the existing Availability Group Listener
  • Add the new SQL server to the availability group
  • Add new Availability Group Listener that includes both regions
  • Test the solution

Key SQL Server configuration Step-by-Step

Build Virtual Network for backup site

Create a virtual network called "sqlnetb". Be sure to select a secondary location for the Location. Type in a name for the virtual network and click "Create" to create the network.


Add a gateway subnet to sqlnetb.


Add a virtual network gateway to sqlneta. Set the location to that of your primary site. Type in a name for the gateway and select the primary virtual network. Click "Public IP address" and choose "Create new". Click "Create" to create the gateway.


Add a virtual network gateway to sqlnetb.

Connect the gateways. In the settings of gatea click "Connections".


Click "Add", type in a name for the connection, select gateb is the "Second virtual network gateway", type in a shared key and click "OK" to add the first connection.


Add a connection from gateb to gatea from the "Connections" list in gateb.

Build out infrastructure components in secondary region (DC\SQL)

Create a domain controller for sqlnetb called "sqlcdb". Refer to appendix B and I for details.
Create virtual machine "sqlsrvc". You can deploy to pre-built SQL Servers or deploy "Windows Server 2012 R2 Datacenter" and then install SQL Server Enterprise. See appendix G for instructions on installing SQL Server Enterprise. If deploying a prebuilt SQL Server, enable SQL Authentication in the "SQL Server settings" section.


Join sqlsrvc to the domain. See appendix F for instructions.
Use the "Add Roles and Features" wizard to add the "Failover Clustering" feature. Be sure to accept adding the management features when prompted.
Open the SQL Server Management Studio and connect to the server.
Right click the database server and click "Properties".


Click on the "Database Settings" page.


Ensure that the "Data" and "Log" directories are configured with valid locations. The click "OK".


Ensure new SQL server is given permissions to the existing File Server shares

Add permissions for SQLSRVC to the quorum file share on SQLFS01. Right click the share in the server manager and click "Properties".


Click the "Permissions" section, click "Customize permissions" then click the "Add" button.
Add the computer account for sqlsrvc, select "Full control", then click "OK" on the dialogs to close them.


Add new SQL Server to WSFC as a new node

Open the failover cluster manager on SQLSRVA. Click "Add Node" then click "Next".


Select sqlsrvc, then click "Next".


Click "Next".


Click "Next".


Click "Next".


Click "Next".


Click "Finish".


Click "Next".


Click "Finish".


Enable AlwaysOn on the new SQL VM

Open the SQL Server Configuration Manager and click on "SQL Server Services".
Double click "SQL Server".

Click "Stop" then click "Yes" when prompted to stop "SQLSERVERAGENT".
Update the Log On account and click the "Service" tab.


Make sure "Start Mode" is set to "Automatic" then click the "AlwaysOn High Availability" tab.


Check "Enable AlwaysOn Availability Groups" and click "Apply".


Click "OK". Double click "SQL Server Agent".

Update the Log On account and click the "Service" tab.


Make sure the "Start Mode" is set to "Automatic" and click "OK".


Right click the SQL Server Agent and click "Start".

Delete the existing Availability Group Listener

Right click the listener and click "Delete".


Click "OK".


Add the new SQL server to the availability group

Add sqlsrvc as a replica to the availability group. Click "Next".


Click "Connect" to connect to the existing secondary replica and click "Next".


Add sqlsrvc as a replica and click the "Endpoints" tab.


Note the port being used for the end point.


Create a new firewall rule.


Specify the end point port and click "Next".


Choose "Allow the connection" and click "Next".


Click "Next".


Type in a name for the rule and click "Finish".
In the "Add Replica" dialog click "Next".


Make sure the path is correct and click "Next".


Click "Next".


Click "Finish".


Click "Close".


Add new Availability Group Listener that includes both regions

Add a new listener that includes an IP address for each subnet.


Test the solution

Test the availability group by manually failing over to the new DR node and then back again.


Once you have completed manual failover test you are done!

Post Conditions

You have now created a DR environment that supports a designated availability group listener for both regions that all applications can leverage.

Any additional DB that needs to be targeted for DR will need to be fully backed before they can be included in HA\DR (Section 2 & 3).

HA SQL Server 2014 Enterprise Edition ARM template via Web

This lab scenario walks the partner participant through initiating a deployment of a SQL Server AlwaysOn Cluster in the Azure portal using the SQL 2014 Enterprise AlwaysOn Cluster Azure Resource Manager (ARM) template.

Preconditions

  • CSP Tenant account with active Azure subscription.

High level summary of the step-by-step process

  • Access the tenant's account
  • Start the deployment
  • Verify client connectivity
  • Test the solution

Key SQL Server configuration Step-by-Step

Access the tenant's account

Log into the partner center portal. Expand a customer entry and click "Microsoft Azure Management Portal".


Start the deployment

Click on the new button, search for "SQL Server AlwaysOn Cluster", select the item and click create.


Provide the required information. For guidance on a particular field, click the 'I' symbol where available.

Once the settings have been entered and validated. Click OK to confirm the settings and then click Create to deploy the cluster.

Verify client connectivity

To log in you can either connect to the public IP address of the rdpLoadBalancer which will connect you to the primary DC or create a new virtual machine for remote desktop access.


Install SQL Server Management Studio then attempt to connect to sqlserver-0, sqlserver-1 and the listener.


At the time writing, the Load balancing rule for the sqlLoadBalancer is not correctly configured. Floating ip is enabled and should be disabled. Delete and recreate the rule with the same settings, except that Floating ip should be disabled.
You should now be able to connect via the listener.

Test the solution

In SQL Server Management Studio, while connected to the listener, expand the Availability groups and ensure you are connected to primary.


Right click the availability group and click "Show Dashboard".


Click "Start Failover Wizard".


Click "Next".


Click "Next".


Click "Connect", connect to the secondary if prompted to do so and click "Next".


Click "Finish" to run the failover.

Click "Close".


Close and SQL Server Management Studio on the client.

Post Conditions

You have now created an Azure High Availability environment that supports a designated availability group listener that all applications can leverage.

Any additional DB that needs to be targeted for HA will need to be fully backed before they can be included in HA as shown below.

SQL Server 2014 Enterprise HA in Azure IaaS (Single Region) with AlwaysOn using Azure ARM template deployed via PowerShell

This lab scenario walks the partner participant through initiating a deployment of a SQL Server AlwaysOn Cluster through a PowerShell script using the SQL 2014 Enterprise AlwaysOn Cluster ARM template available on the Azure Management Portal. The PowerShell script is available in the Appendix K.

Preconditions

  • CSP Tenant account with active Azure subscription.
  • The subscription id and tenant id for the account are readily available.
  • Azure PowerShell is installed on your computer.
  • The script DeploySQLHA.ps1 has been saved to your computer.

High level summary of the step-by-step process

  • Access the tenant's account
  • Run the script
  • Verify client connectivity
  • Test the solution

Key SQL Server configuration Step-by-Step

Access the tenant's account

Open PowerShell.
If you haven't already logged in with your partner credentials, log in using the cmdlet Login-AzureRMAccount.


Run the following after replacing the placeholders with actual IDs: Set-AzureRMContext -TenantId '<Tenant ID>' -SubscriptionId '<Subscription ID>'


Run the script

Save administrator credentials in a PowerShell object.


Save service credentials in a PowerShell object.


Change directories to the directory in which the scrip was saved.
The script includes default values for all parameters except AdminCredential and SqlServiceAccount. To get details run: Get-Help .\DeploySQLHA.ps1 –Full


Run the script.


Verify client connectivity

To log in you can either connect to the public IP address of the rdpLoadBalancer which will connect you to the primary DC or create a new virtual machine for remote desktop access.


Install SQL Server Management Studio then attempt to connect to sqlserver-0, sqlserver-1 and the listener.


At the time writing, the Load balancing rule for the sqlLoadBalancer is not correctly configured. Floating ip is enabled and should be disabled. Delete and recreate the rule with the same settings, except that Floating ip should be disabled.
You should now be able to connect via the listener.

Test the solution

In SQL Server Management Studio, while connected to the listener, expand the Availability groups and ensure you are connected to primary.


Right click the availability group and click "Show Dashboard".


Click "Start Failover Wizard".

Click "Next".


Click "Next".


Click "Connect", connect to the secondary if prompted to do so and click "Next".


Click "Finish" to run the failover.


Click "Close".


Close and SQL Server Management Studio on the client.

Post Conditions

You have now created an Azure High Availability environment that supports a designated availability group listener that all applications can leverage.

Any additional DB that needs to be targeted for HA will need to be fully backed before they can be included in HA as shown below.

SQL Server 2014 DR in Azure with Log Shipping using SQL Standard (Across Regions)

The following diagram identifies the key components needed for delivering SQL 2014 Disaster Recovery in Azure IaaS across two regions. The setup leverages an existing Partner tenant already provisioned with a two Windows 2012R2 Domain Controller (1 in Primary and 1 in Secondary) on VMs, 2 SQL 2014 servers on Windows Server 2013R2 VMs with one in each region and a single Windows 2012R2 file server on a VM in the primary region. This implementation is exclusively in Azure and no on premise servers are needed. This procedure will focus on setting up the key elements that enable SQL Log Shipping in Azure across two regions (called sites below) using SQL Server 2014 Standard.


Figure 1 High Level Architecture

The network architecture diagram shown in figure 1 supports implementation of the prerequisite Site-to-Site (S2S) Virtual Private Network (VPN) Tunnel between two customer regions.

Preconditions

  • CSP Tenant account with active Azure subscription.
  • Existing resource group in which to deploy resources.
  • Virtual networks an two different Azure regions.
  • Azure virtual machine configured as domain controller (DC) in the primary network.
  • Custom DNS in primary virtual network configured to refer to the DC.
  • A service account in the domain to run SQL Server services.
  • Two Azure virtual machines each running SQL Server Standard 2014, domain joined. One per virtual network.
  • Virtual machine deployed in secondary network to serve as domain controller replica.

Use the table below to record the environmental data required to complete the configuration.

Prerequisite

Data

Azure Resource Group

SQLAlwaysOn

Region

West US

DNS IP

10.0.0.4

Azure VNET

Sqlneta (10.0.0.0/16)/Subnet (10.0.0.0/24)

SQL Service Account

SQLNET\SQLSERVICE

Availability Group Name

SQL Group

Availability Group Listener

SQLListener (10.0.0.20)

High level summary of the step-by-step process

  • Add a gateway between the virtual networks
  • Make the second DC a Domain Controller
  • Set up a file share on the 2nd region SQL server
  • Enable Log Shipping on SQL Server Management Studio on primary server
  • Test the solution

Key SQL Server Standard Configuration Step-by-Step

Add a gateway between the virtual networks

Add a gateway subnet to SQLNETSTNDLocal. In the settings of SQLNETSTNDLocal click Subnets. Click on "Add" to add a subnet.


Type in the name "GatewaySubnet" and click "OK" to add the new subnet.


Create a virtual network called "SQLNETSTNDRemote" for the secondary site
Make sure the address space does not overlap with the address space of SQLNETSTNDLocal and set the Location to your secondary location.


Add a virtual network gateway to SQLNETSTNDLocal.


Make sure the Location is set to your primary site. Enter in a name and select SQLNETSTNDLocal as the Virtual Network. Click on Public IP address.
Click "Create new" and type a name if desired. Click OK to confirm the name. Click Create to create the gateway.


Add a gateway subnet to SQLNETSTNDRemote. In the settings of SQLNETSTNDRemote click Subnets. Click on "Add" to add a subnet.


Type in the name "GatewaySubnet" and click OK to add the new subnet.


Add a virtual network gateway to SQLNETSTNDRemote.


Connect the gateways. In order to connect two virtual networks two connections are required, one for each direction. Open the settings page of LocalGateway. Click Connections, then click "Add" to add a new Connection.


Type in a name and select RemoteGateway for the Second virtual network gateway. Enter in a Shared key and click "OK" to add the connection. Open the settings for RemoteGateway and add a connection to the local gateway.


The connections will show up as "Succeeded", change to "Not connected" then change to "Connected". This process may take a few minutes.


Make the second DC a Domain Controller

Set the IP address for AzureDC02 as static. Open the settings of AzureDC02.
Click "Network interfaces" then select the network interface.


Click "IP addresses" then set the Private IP address settings to Static. Click Save to save the setting.


Log into AzureDC02. Once the Server Manager is open click the "Local Server" section.


Click on the Ethernet link to access the network connections window. Right click the network interface and click Status.


Click Details.


Note the IPv4 Default Gateway and the IPv4 DNS Server. Click Close then click Properties in the Ethernet Status dialog.


Double click Internet Protocol Version 4 (TCP/IPv4).


Select "Use the following IP address". Enter in the IP address set for AzureDC02. The Subnet mask is typically set to 255.255.255.0. Set the default to the value from the connection details dialog. Set the Preferred DNS server to the IP address set for AzureDC01 and the Alternate DNS server to the value that was shown in the connection details dialog. Click OK on each dialog to close them. Your connection may be briefly interrupted when the settings are applied.


Join AzureDC02 to the domain. In a command window, ping the IP address of AzureDC01 to ensure connectivity.


In the server manager window click on the Workgroup link. Click the Change button.


Choose domain and enter the domain name. Click OK and enter network credentials when prompted.


A welcome dialog will confirm the domain join. Click OK and let the computer be rebooted.


Prepare AzureDC02 to be a domain controller. See Appendix B for instructions.
Run the domain controller promotion on AzureDC02. See Appendix C for instructions.
Set the DNS Server for SQLNETSTNDRemote. In the settings for SQLNETSTNDRemote click DNS servers. Choose "Custom DNS", enter the private IP address for AzureDC02, then click Save.


Set up a file share on the 2nd region SQL server

Attach a data disk to the second SQL server. In the settings of the server click Disks.
Click "Attach new". Click OK to attach the disk.


Join the second SQL Server to the domain.
Prepare the file share on the 2nd SQL Server backup server that exist. See Appendix E for instructions on adding a disk to a file share.
Open the server manager and click the "File and Storage Services" section.


Click Shares.


Open the Tasks menu of the Shares list and click "New Share".


Click Next.


Click Next.


Enter a name for the share and click next.


Click Next.


Click "Customize permissions".


Click Add.


Click "Select a principal".


Enter the name of the account used to run SQL Server and click "Check Names". If the name resolves correctly, Click OK.


Choose "Full control" and click OK. Click OK, then click Next.


Click Create.


Click Close.


Enable Log Shipping on SQL Server Management Studio on primary server

Browse to the AdventureWorks Sample database page (http://msftdbprodsamples.codeplex.com) and download the full database backup.
Extract the backup and restore the database.


Set the Recovery Model of the database to Bulk-logged.


Click the "Transaction Log Shipping" page.


Check "Enable this as a primary database in a log shipping configuration" and click "Backup Settings".


Enter the network path to the share on the backup share and click OK.


Click Add.


Click Connect.

Enter the name of the backup server and click Connect.


Select Yes and click "Restore Options".


Enter the local path to the file share on the backup server and click OK.


Click the "Copy Files" tab, enter the network path to the backup server's share and click OK.

Click OK to start the backup and restore.

Click Close.

Test the solution

Test the disaster recovery. Shut down the virtual machines in the primary site.

Log into the backup server and open SQL Server Management Studio.

Right click the database and choose Transaction Log. Click OK to restore the database.

The database is now available and can be used as the primary in a new Log Shipping setup.

Post Conditions

You have now created a DR environment that supports the ability to quickly bring up a new primary site using log shipping.

Any additional DB that needs to be included for log shipping will need to be configured separately.

Appendices

A – Add additional Roles and Features

Open the "Add Roles and Features" wizard.

Click "Next".

Click "Next".

Make sure the correct destination computer is selected and click "Next". If you haven't added any additional servers to server manager, this will only list the local server.

Select the server roles you next to add and click "Next". You may be prompted to install additional features or receive a warning about a potential issue.

Select any features you need to add and click "Next".

Review the items to be installed and click "Install".

Click "Close" to close the dialog.

B – Installing a Domain Controller

Log onto the server and used the Add Roles and Features Wizard to add the "Active Directory Domain Services" and "DNS Server" server roles. Be sure to accept the prerequisite features. You may receive a warning that a static IP address hasn't been assigned. It is safe to ignore this warning.
See appendix A for instructions on using the wizard.
The "Active Directory Domain Services" server role will add an extra page to the Wizard. Click "Next".

The "DNS Server" server role will add an extra page to the Wizard. Click "Next".

C – DC Promo a new Domain Controller

After the installation of the "Active Directory Domain Services" role you will get a notification indicating that configuration is required. Click "Promote this server to a domain controller".

Select "Add a new forest", type in the root domain name and click "Next".

Type in a password for Directory Services Restore Mode and click "Next".

Click "Next".

Click "Next".

Click "Next".

Click "Next".

Click "Install".

D – Set the DNS settings of a Virtual Network to point to a Domain controller

In the DNS settings of the virtual network, choose "Custom DNS", enter in the IP address of the domain controller, then click Save.

E – Prepare a data disk

Right click the start button and choose "Disk Management".

You will be prompted to initialize the disk. Click "OK".

Right click the disk and choose "New Simple Volume".

Click "Next".

Click "Next".

Click "Next".

Type in a volume label and click "Next".

Click "Finish".

F – Domain Join a server (single network)

In the server manager click "Local Server". After the data loads, click the Ethernet link.

Double click the network interface. In the status dialog, click "Details".

Verify that "IPv4 DNS Server" matches the domain controller's IP address. Close the dialogs.

In the server manager window, click "WORKGROUP".

Click "Change".

Choose "Domain", type in the domain name and click "OK".
Enter domain credentials when prompted.

Close the dialogs and allow the computer to reboot.

G – Install SQL Server 2014 Enterprise

Join the computer to the domain as per appendix E.
Ensure a service account has been created to run SQL Server.
Configure the Windows Firewall as per published guidance.
Use the "Add Roles and Features" wizard to add ".NET Framework 3.5 Features". See appendix A for instructions for using the wizard.
Launch the SQL Server installation setup. Click Installation to go to the installation section.
Click "New SQL Server stand-alone installation or add features to an existing installation".

Enter a product key or choose the Evaluation.

Review the License Terms, click the check box labelled "I accept the license terms." Then click Next.

Check the checkbox labelled "Use Microsoft Update to check for updates" and click next.

Click Next.

Select Database Engine Services and Management Tools and click Next.

Set the Account Name for the SQL Server Agent and SQL Server Database Engine to the SQL service account, enter the password for the account and click Next.

Choose Mixed Mode, enter a password for the sa account, click "Add Current User", and then click next.

Click Install.

Click Close.

H – Add a new service account for operational use

In server manager, click the tools menu and choose "Active Directory Users and Computer".
In the tree pane, expand the tree and click on "Managed Service Accounts".
Right click in the object list pane. Choose Create New then choose User.
Enter the name and login name, then click Next

Enter in a password, uncheck "User must change password at next logon", check "Password never expires", then click Next.

Click Finish to close the dialog.

I – Add an additional DC to an existing domain across multiple networks

Check the current DNS server as done in appendix F.
Open the "Internet Protocol Version 4" properties dialog. Be sure to put the ip address for sqldca as the preferred DNS server and the Azure DNS IP address as the alternate DNS server.

Join the server to the domain as was done in appendix F.
Open an administrator command prompt and run "ipconfig /flushdns".
Start the domain controller promotion as was done in appendix C with the following difference.

In the "Deployment Configuration" page choose "Add a domain controller to an existing domain".
Make sure the domain is correct and that the credentials are for a domain account. Click "Next".

Continue the promotion as was done in appendix C.
Update the DNS Settings of sqlnetb to point to sqldcb.

J – Recommended Windows Firewall Settings

The following list of links outline the key recommendations identified to successfully setup the right ports based on each off the key components used in this procedure.

Scenario

Region

Technologies

Port Requirements to work in Azure (Note: The following ports listed represent an initial set of ports we use our configuration and may vary depending on each customers requirements)

SQL Server 2014 HA using Enterprise Edition

Same Region

Windows 2012R2 (including DC) – Port Requirements

Lightweight Directory Access Protocol (LDAP) TCP 389

LDAP (Secure Sockets Layer [SSL] connection) UDP\TCP 636

Global Catalogue LDAP TCP 3268

Global Catalogue LDAP SSL 3269

RPC Endpoint Mapper UDP\TCP 135

Remote Assistance RDP TCP 3389

Server Message Block (SMB) TCP 445

RRAS TCP1723 (if needed)

Windows Update – Port 80 for HTTP or HTTPS 443

Domain Name System – UDP\TCP 53

NetBios – UDP 137

NetBios Datagram Service – UDP 138

TCP 88, 389, 464, 636, 139

UTP 88, 389, 464, 139

SQL Server 2014 – Port Requirements

SQL over TCP 1433

WSFC - Port Requirements

Description

TCP/UDP

Port

User & Computer Authentication [DNS]

TCP/UDP

53

User & Computer Authentication [Kerberos]

TCP/UDP

88

Windows Time [NTP]

UDP

123

Cluster DCOM Traffic [RPC, EPM]

TCP

135

User & Computer Authentication [NetLogon, NetBIOS]

UDP

137

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

UDP

138

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

TCP

139

SNMP

UDP

161

SNMP Traps

TCP/UDP

162

User & Computer Authentication [LDAP]

TCP/UDP

389

User & Computer Authentication [SMB, SMB2, CIFS]

TCP/UDP

445

User & Computer Authentication [Kerberos Change/Set Password]

TCP/UDP

464

User & Computer Authentication [LDAP SSL]

TCP

636

Microsoft Global Catalog

TCP

3268

Microsoft Global Catalog [SSL]

TCP

3269

Cluster Network Communication

TCP/UDP

3343

WinRM 2.0 [Remote PowerShell]

TCP

5985

WinRM 2.0 HTTPS [Remote PowerShell SECURE]

TCP

5986

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}]

TCP/UDP

49152-65535

Also include:

TCP Ports

135,139,445,1433,2383,3343,5022,5985,5986

UDP Ports

137,138,445,1434,2382,3343,49152-65535

SQL Server 2014 HA\DR using Enterprise Edition

Across Multiple Regions

Windows 2012R2 – Port Requirements

Lightweight Directory Access Protocol (LDAP) TCP 389

LDAP (Secure Sockets Layer [SSL] connection) UDP\TCP 636

Global Catalogue LDAP TCP 3268

Global Catalogue LDAP SSL 3269

RPC Endpoint Mapper UDP\TCP 135

Remote Assistance RDP TCP 3389

Server Message Block (SMB) TCP 445

RRAS TCP1723 (if needed)

Windows Update – Port 80 for HTTP or HTTPS 443

Domain Name System – UDP\TCP 53

NetBios – UDP 137

NetBios Datagram Service – UDP 138

TCP 88, 389, 464, 636, 139

UTP 88, 389, 464, 139

SQL Server 2014 – Port Requirements

SQL over TCP 1433

WSFC - Port Requirements

Description

TCP/UDP

Port

User & Computer Authentication [DNS]

TCP/UDP

53

User & Computer Authentication [Kerberos]

TCP/UDP

88

Windows Time [NTP]

UDP

123

Cluster DCOM Traffic [RPC, EPM]

TCP

135

User & Computer Authentication [NetLogon, NetBIOS]

UDP

137

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

UDP

138

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

TCP

139

SNMP

UDP

161

SNMP Traps

TCP/UDP

162

User & Computer Authentication [LDAP]

TCP/UDP

389

User & Computer Authentication [SMB, SMB2, CIFS]

TCP/UDP

445

User & Computer Authentication [Kerberos Change/Set Password]

TCP/UDP

464

User & Computer Authentication [LDAP SSL]

TCP

636

Microsoft Global Catalog

TCP

3268

Microsoft Global Catalog [SSL]

TCP

3269

Cluster Network Communication

TCP/UDP

3343

WinRM 2.0 [Remote PowerShell]

TCP

5985

WinRM 2.0 HTTPS [Remote PowerShell SECURE]

TCP

5986

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}]

TCP/UDP

49152-65535

IAlso include:

TCP Ports

135,139,445,1433,2383,3343,5022,5985,5986

UDP Ports

137,138,445,1434,2382,3343,49152-65535

SQL Server 2014 HA using Standard Edition

Same Region

Windows 2012R2 – Port Requirements

Lightweight Directory Access Protocol (LDAP) TCP 389

LDAP (Secure Sockets Layer [SSL] connection) UDP\TCP 636

Global Catalogue LDAP TCP 3268

Global Catalogue LDAP SSL 3269

RPC Endpoint Mapper UDP\TCP 135

Remote Assistance RDP TCP 3389

Server Message Block (SMB) TCP 445

RRAS TCP1723 (if needed)

Windows Update – Port 80 for HTTP or HTTPS 443

Domain Name System – UDP\TCP 53

NetBios – UDP 137

NetBios Datagram Service – UDP 138

TCP 88, 389, 464, 636, 139

UTP 88, 389, 464, 139

SQL Server 2014 – Port Requirements

SQL over TCP 1433

WSFC - Port Requirements

Description

TCP/UDP

Port

User & Computer Authentication [DNS]

TCP/UDP

53

User & Computer Authentication [Kerberos]

TCP/UDP

88

Windows Time [NTP]

UDP

123

Cluster DCOM Traffic [RPC, EPM]

TCP

135

User & Computer Authentication [NetLogon, NetBIOS]

UDP

137

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

UDP

138

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

TCP

139

SNMP

UDP

161

SNMP Traps

TCP/UDP

162

User & Computer Authentication [LDAP]

TCP/UDP

389

User & Computer Authentication [SMB, SMB2, CIFS]

TCP/UDP

445

User & Computer Authentication [Kerberos Change/Set Password]

TCP/UDP

464

User & Computer Authentication [LDAP SSL]

TCP

636

Microsoft Global Catalog

TCP

3268

Microsoft Global Catalog [SSL]

TCP

3269

Cluster Network Communication

TCP/UDP

3343

WinRM 2.0 [Remote PowerShell]

TCP

5985

WinRM 2.0 HTTPS [Remote PowerShell SECURE]

TCP

5986

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}]

TCP/UDP

49152-65535

IAlso include:

TCP Ports

135,139,445,1433,2383,3343,5022,5985,5986

UDP Ports

137,138,445,1434,2382,3343,49152-65535

SQL Server 2014 HA\DR with Log Shipping using Standard Edition

Across Multiple Regions

Windows 2012R2 – Port Requirements

Lightweight Directory Access Protocol (LDAP) TCP 389

LDAP (Secure Sockets Layer [SSL] connection) UDP\TCP 636

Global Catalogue LDAP TCP 3268

Global Catalogue LDAP SSL 3269

RPC Endpoint Mapper UDP\TCP 135

Remote Assistance RDP TCP 3389

Server Message Block (SMB) TCP 445

RRAS TCP1723 (if needed)

Windows Update – Port 80 for HTTP or HTTPS 443

Domain Name System – UDP\TCP 53

NetBios – UDP 137

NetBios Datagram Service – UDP 138

TCP 88, 389, 464, 636, 139

UTP 88, 389, 464, 139

SQL Server 2014 – Port Requirements

SQL over TCP 1433

WSFC - Port Requirements

Description

TCP/UDP

Port

User & Computer Authentication [DNS]

TCP/UDP

53

User & Computer Authentication [Kerberos]

TCP/UDP

88

Windows Time [NTP]

UDP

123

Cluster DCOM Traffic [RPC, EPM]

TCP

135

User & Computer Authentication [NetLogon, NetBIOS]

UDP

137

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

UDP

138

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service]

TCP

139

SNMP

UDP

161

SNMP Traps

TCP/UDP

162

User & Computer Authentication [LDAP]

TCP/UDP

389

User & Computer Authentication [SMB, SMB2, CIFS]

TCP/UDP

445

User & Computer Authentication [Kerberos Change/Set Password]

TCP/UDP

464

User & Computer Authentication [LDAP SSL]

TCP

636

Microsoft Global Catalog

TCP

3268

Microsoft Global Catalog [SSL]

TCP

3269

Cluster Network Communication

TCP/UDP

3343

WinRM 2.0 [Remote PowerShell]

TCP

5985

WinRM 2.0 HTTPS [Remote PowerShell SECURE]

TCP

5986

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}]

TCP/UDP

49152-65535

IAlso include:

TCP Ports

135,139,445,1433,2383,3343,5022,5985,5986

UDP Ports

137,138,445,1434,2382,3343,49152-65535

Additional helpful Links:

 

K – PowerShell script for SQL Server 2014 Enterprise AlwaysOn High Availability setup

<#
.DESCRIPTION
Deploys the Microsoft Azure gallery item 'SQL Server AlwaysOn Cluster'.
The deployment includes the following virtual machines.

 2 Domain controllers (primary and backup)
 2 SQL Servers
 1 File Share Witness

 A sample database is created and used to create an availability group for high availability.
 Access to the database is achieved primarly through the listener.

.EXAMPLE
DeploySQLHA.ps1 -AdminCredential $admincred -SqlServiceAccount $svccred

Runs the deployment using the defaults and the provided credentials.

.LINK 
https://portal.azure.com/#create/sqlvm.sqlserveralwaysonsqlvmsample

.PARAMETER  AdminCredential
Administrator account for all virtual machines. This account will also be the domain administrator account for new domain deployment.

.PARAMETER  AvailabilityGroupListenerName
An internal listener will be created with this name.

.PARAMETER  AvailabilityGroupListenerPort
The port number on which the availability group listener will listen.

.PARAMETER  AvailabilityGroupName
The name for the availability group.

.PARAMETER  DcStorage
The storage type for the domain controller's virtual hard drive.

.PARAMETER  DcVmSize
The size of the virtual machines for the primary and secondary domain controllers.

.PARAMETER  DomainName
Fully qualified domain name of the Active directory domain.

.PARAMETER  FSWVmSize
The size of the virtual machines for the file share witness virtual machine.

.PARAMETER  Location 
Azure datacenter location.

.PARAMETER  NumbersOfDisks
Data disks to be added to SQL Server virtual machines. Can select sizes between 1 and 4 TB.

.PARAMETER  PatchDay
SQL Server virtual machine automated patching schedule.

.PARAMETER  PatchHour
SQL Server virtual machine maintenance starting hour.

.PARAMETER  ResourceGroup
The name for the resource group.

.PARAMETER  SqlServerVersion
The version of SQL Server (2012 or 2014) to runl on the servers.

.PARAMETER  SqlServiceAccount
Credentials for the service account to use to run SQL Server.

.PARAMETER  SqlStorage
The storage type for the SQL Server's virtual hard drives.

.PARAMETER  SqlVMSize
The size of the virtual machines for the primary and secondary SQL Server.

.PARAMETER  StorageOptimization
Choose the type of workload for which you would like to optimize the storage. 
 OLTP    => Transactional Processing - Optimize the storage for transaction latency
 DW      => Data Warehousing         - Optimize the storage for query processing, analysis, and throughput. 
 GENERAL => General                  - Not optimized for any specific type of workload

.PARAMETER  TemplateUri
The URI of the template.

.PARAMETER  VirtualNetworkName
The name for the virtual network. The address range will be 10.0.0.0/16.

.SYNOPSIS
Run a deployment of SQL Server Enterprise with High Availability using availability groups.
#>
Param([Parameter(Mandatory=$true)][System.Management.Automation.PSCredential]$AdminCredential,
      [Parameter()][ValidateScript({
          if ($_ -notmatch '^[a-z0-9A-Z -]{1,20}$')
          {
              throw("Only letters, numbers and - are allowed, and the value must be 1-20 characters long.")
          }
          return $true
      })][System.String]$AvailabilityGroupListenerName = "Contoso-listener",
      [Parameter()][ValidateRange(1,65535)][System.Int32]$AvailabilityGroupListenerPort = 1433,
      [Parameter()][ValidateScript({
          if ($_ -notmatch '^[a-z0-9A-Z -]{1,20}$')
          {
              throw("Only letters, numbers and - are allowed, and the value must be 1-20 characters long.")
          }
          return $true
      })][System.String]$AvailabilityGroupName = "Contoso-ag",
      [Parameter()][ValidateSet("Standard_LRS","Standard_GRS","Standard_RAGRS")][System.String]$DcStorage = "Standard_GRS",
      [Parameter()][ValidateSet("Standard_D2","Standard_D3","Standard_D4","Standard_D11","Standard_D12","Standard_D13","Standard_D14")][System.String]$DcVmSize = "Standard_D3",
      [Parameter()][ValidateScript({
          if ($_ -notmatch '^([a-z0-9]+\\.)?([a-z0-9][a-z0-9-]*\\.)+[a-z]{2,6}$')
          {
              throw("Must be a valid fully-qualified domain name.")
          }
          return $true
      })][System.String]$DomainName = "contoso.com",
      [Parameter()][ValidateSet("Standard_DS1","Standard_DS2","Standard_DS3")][System.String]$FSWVmSize = "Standard_DS1",
      [Parameter()][System.String]$Location = "West US",
      [Parameter()][ValidateRange(1,4)][System.String]$NumbersOfDisks = 1,
      [Parameter()][ValidateSet("Never","Everyday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")][System.String]$PatchDay = "Sunday",
      [Parameter()][ValidateRange(0,23)][System.Int32]$PatchHour = 2,
      [Parameter()][ValidateScript({
          if ($_ -notmatch '^[()._0-9a-zA-Z-]{1,63}[()_0-9a-zA-Z-]$')
          {
              throw("The resource group name must contain only letters, numbers, dashes, underscores, periods (except at the end of the name), left porenthesis and right parenthesis.")
          }
          return $true
      })][System.String]$ResourceGroup = "SQLHA",
      [Parameter()][ValidateSet("SQL2012SP2-WS2012R2","SQL2014-WS2012R2")][System.String]$SqlServerVersion = "SQL2014-WS2012R2",
      [Parameter(Mandatory=$true)][System.Management.Automation.PSCredential]$SqlServiceAccount,
      [Parameter()][ValidateSet("Premium_LRS","Standard_LRS","Standard_GRS","Standard_RAGRS")][System.String]$SqlStorage = "Premium_LRS",
      [Parameter()][ValidateSet("Standard_DS1","Standard_DS2","Standard_DS3","Standard_DS4","Standard_DS11","Standard_DS12","Standard_DS13","Standard_DS14")][System.String]$SqlVMSize = "Standard_DS3",
      [Parameter()][ValidateSet("DW","GENERAL","OLTP")][System.String]$StorageOptimization = "GENERAL",
      [Parameter()][System.String]$TemplateUri = "https://gallery.azure.com/artifact/20151001/sqlvm.sqlserveralwaysonsqlvmsample.1.0.3/Artifacts/mainTemplate.json",
      [Parameter()][ValidateScript({
          if ($_ -notmatch '^[a-z0-9A-Z]{1,10}$')
          {
              throw("Only letters and numbers are allowed, and the value must be 1-10 characters long.")
          }
          return $true
      })][System.String]$VirtualNetworkName = "autohaVNET"
)
$params = @{
      "adminPassword" = $AdminCredential.GetNetworkCredential().Password;
      "adminUsername" = $AdminCredential.UserName;
      "adVMSize" = $DcVmSize;
      "autoPatchingDay" = $PatchDay;
      "autoPatchingStartHour" = $PatchHour.ToString();
      "dcStorageAccountType" = $DcStorage;
      "domainName" = $DomainName;
      "location" = $Location;
      "numberOfSqlVMDisks" = $NumbersOfDisks;
      "sqlAOAGName" = $AvailabilityGroupName;
      "sqlAOListenerName" = $AvailabilityGroupListenerName;
      "sqlAOListenerPort" = $AvailabilityGroupListenerPort.ToString();
      "sqlServerServiceAccountPassword" = $SqlServiceAccount.GetNetworkCredential().Password;
      "sqlServerServiceAccountUserName" = $SqlServiceAccount.UserName;
      "sqlServerVersion" = $SqlServerVersion;
      "sqlStorageAccountType" = $SqlStorage;
      "sqlVMSize" = $SqlVMSize;
      "virtualNetworkName" = $VirtualNetworkName;
      "witnessVMSize" = $FSWVmSize;
      "workloadType" = $StorageOptimization
}
if (-not (Get-AzureRmResourceGroup | Where-Object ResourceGroupName -EQ $ResourceGroup))
{
    New-AzureRmResourceGroup -Name $ResourceGroup -Location $Location
}
New-AzureRMResourceGroupDeployment -Name $ResourceGroup -ResourceGroupName $ResourceGroup -TemplateParameterObject $params -TemplateUri $TemplateUri -Verbose