Troubleshooting the Add Azure Replica Wizard in SQL Server 2014


SQL Server 2014 introduced a new feature that automates the creation of a new AlwaysOn replica hosted in Microsoft Azure. This replica is added to an existing availability group in your environment and connected to your existing AlwaysOn deployment via VPN.

 

 

Index:

    • Introduction:
    • Pre-requisites:
    • Known issues:
      • Add Azure Replica Wizard – Virtual Network drop down is empty
      • Provisioning new virtual machine fails with error ‘The geo-location constraint…is invalid’ from Add Azure Replica:
      • Validation Error ‘Checking if the cluster name resource is online’ Fails
      • The Add Replica to Availability Group wizard fail during group validation. It fails on:    “Checking if the cluster name resource is online”
      • SQL Add Azure Replica Wizard Fails on Step ‘Configuring Endpoints.’ With error 53 (The network path was not found) and Step ‘Provisioning Windows Azure VM with error 1722 (The RPC server is unavailable)
      • SQL Add Azure Replica Wizard fails with ‘The hosted service name is invalid’
      • SQL Add Azure Replica Wizard Fails on Step ‘Joining secondary replicas to availability group’
      • VPN cannot be behind a NAT (Network Address Translation) device.
    • Troubleshooting Tips

Introduction:

As part of this new feature, the Add Azure Replica wizard will create a new Virtual Machine in Microsoft Azure using an image that already has SQL Server 2014 installed. During the creation (Provisioning phase) the new Virtual Machine will join your current on premises domain. In addition after the provisioning phase completes it will install and configure the Windows Failover Clustering feature, and configure SQL Server to be a new secondary replica to your existing on premises availability group.

There is a high level of automation to complete the tasks performed by the SQL Server Add Azure Replica Wizard. As a result there are some unexpected failures that can occur, and this blog will cover some of the known common issues. In addition to covering the common failures we will present you with some basic troubleshooting steps to try and resolve any issue that is not covered in this Blog.

Note: This Blog does not provide a Step by Step guide to using the Add Azure Replica Wizard. The link below is a tutorial that can walk you through the steps inside the new SQL Server 2014 Add Azure Replica Wizard.

Tutorial: Add Azure Replica Wizard

 

Pre-requisites:

Listed below are some of the prerequisites that are required for a successful replica deployment using the Add Azure Replica Wizard. For a more comprehensive list go to the following:

Use the Add Replica to Availability Group Wizard (SQL Server Management Studio)

 

  1. The Add Azure Replica Wizard must be executed from the host that is the current primary replica.
  2. Note your current OS version and SQL Server version. During the wizard you will need to ensure you select the correct Virtual Machine Image. If you are running Windows 2012 you need to select the same OS version since Windows Failover Cluster does not allow for mixed OS versions. (Example, Windows 2012 and Windows 2012R2 cannot be part of the same cluster or availability group).
  3. If a server instance that you select to host an availability replica is running under a domain user account and does not yet have a database mirroring endpoint, the wizard can create the endpoint and grant CONNECT permission to the server instance service account. However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a Local account, you must use certificates for endpoint authentication, and the wizard will be unable to create a database AlwaysOn endpoint on the server instance. In this case, we recommend that you create the database endpoints manually before you launch the Add Replica to Availability Group Wizard.
  4. The specified Windows user account specified in the Add Azure Replica Wizard must have privileges to Add Workstations to the Domain (Create Computer Objects) as well as have local administrator privileges on each node of the current Windows Failover Cluster that is hosting the AlwaysOn availability group.
  5. You will need to specify a network share in order for the wizard to create and access backups.
  6. For the primary replica, the account used to start the Database Engine must have read and write file-system permissions on a network share. For secondary replicas, the account must have read permission on the network share.

 

 

Known issues:

Issue:

Add Azure Replica Wizard – Virtual Network drop down is empty

Cause:

After the SQL 2014 Add Azure Replica Wizard was shipped a significant architecture change was made in the Windows Azure environment, specifically with Network functionality.

Prior to the hotfix mentioned in the fix section below, the SQL Add Azure Replica Wizard depended on Affinity Groups, currently the Affinity Groups are no longer being used by Microsoft Azure environment resulting in the error you see below:

image

Previously, when creating a virtual network (VNet) you were required to associate the VNet with an affinity group, which was in turn, associated with a Region. This requirement has changed. Now VNets are associated directly with a Region (Location) in the Management Portal. This allows you more freedom when creating your VNets.

For more information on this change click the link below:
About Regional VNets and Affinity Groups for Virtual Network

Fix:

To resolve this issue prior to running the SQL Server Add Azure Replica Wizard apply CU5 or later CU / Service Pack for SQL 2014. As an alternative you can download a patch that was created prior to the release of CU5. For more information click on the link below:
FIX: Add Azure Replica wizard cannot enumerate Azure Virtual Network in SQL Server 2014

 

Issue:

Provisioning new virtual machine fails with error ‘The geo-location constraint…is invalid’ from Add Azure Replica:

Cause:

SQL Server Add Azure Replica make reference to a Virtual Network configured with Affinity Group setting. As mentioned above in the previous issue, the Affinity Group option is deprecated currently in Microsoft Azure environments.

 

This issue will manifest itself with the following errors:

The wizard fails and clicking the Error link reports ‘geo-location constraint’ error like below,

clip_image001

You can look at the Add Azure Replica Wizard log (found in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard reports:

 

In this log you will see an error similar to the following:

Attempting to provision Windows Azure VM ‘vmname’ resulted in an error. (Microsoft.SQLServer.Management.HadrTasks)
Additional Information:
OperationID:1190164893f0120f97861e2cd5c47c8f, Status=Failed, Code=400, Details=The geo-location constraint specified for the hosted service is invalid.
(Microsoft.SQLServer.Management.HadrTasks)

Fix:

To resolve this issue prior to running the SQL Server Add Azure Replica Wizard apply CU5 or later CU / Service Pack for SQL 2014. As an alternative you can download a patch that was created prior to the release of CU5. For more information click on the link below:

FIX: Add Azure Replica wizard cannot enumerate Azure Virtual Network in SQL Server 2014

 

 

 

Issue(s):

Validation Error ‘Checking if the cluster name resource is online’ fails

The Add Replica to Availability Group wizard fail during group validation. It fails on:

“Checking if the cluster name resource is online”

clip_image002

Error is – “Access is denied.”

clip_image003

From Details you can see:

clip_image005

Cause:

As part of the validation process, the Add Replica wizard tries to connect to the local windows cluster and ensure that the cluster network name is online and available. When trying to perform this check an error can result for several reasons. Below in the Fix section we have we have listed the top reasons for failure.

Fix:

To resolve this issue verify the following items below:

1. The Cluster Network Name is not in an online state.

Verify it is online and not offline or failed by using the Failover Cluster Manager and looking at the Core Resources. Below is an Example where the Cluster Network Name resource is offline resulting in the error above.

clip_image006

2. The current logged in user does needs to have full control permissions to the cluster

To connect to and manage a Windows Failover cluster, there is an Access List that is maintained by the Windows Failover cluster. Ensure that the account you are using to launch the Add Azure Replica wizard has the correct permissions. You can view these permissions by looking at the Failover Clustering properties dialog box. Below is an Example:

clip_image007

3. SSMS (SQL Server Management Studio) should be launched as Administrator

Even if the account does have administrative privileges to the Windows Failover Cluster, this error can still occur if you do not launch the SQL Server Management Studio as an Administrator.

To run SSMS as Administrator Just right click and select the option “Run as administrator”

clip_image008

Ensure that you see (Administrator) in the SSMS title bar.

clip_image010

 

Issue:

SQL Add Azure Replica Wizard Fails on Step ‘Configuring Endpoints.’ With error 53 (The network path was not found) and Step ‘Provisioning Windows Azure VM with error 1722 (The RPC server is unavailable)

Some of the symptoms can be found below:

clip_image002[5]

clip_image003[5]

clip_image005[5]

clip_image006[5]

clip_image008

Cause:

During provisioning of the Azure Virtual Machine the SQL Add Azure Replica Wizard passes the –JoinDomain option during creation of the Virtual Machine.

The Virtual Machine however was unable to join the domain since it could not resolve the domain name. By default Azure will use its Internal Name Resolution for all machines in your specific VNET. The on premises domain or domain created in another VNet will not resolve correctly and will require that you provide your own DNS servers. For more information:

Azure Name Resolution (DNS)

Fix:

Create a DNS Entry for your current VNET or the VNet that will host your new VM created by the SQL Add Azure Replica Wizard. Below is some examples on how to do this.

Note: For this example, we have created a test-vpn-aar VNet as you can see below, and our on premises DNS server is 10.0.1.1

 

First Open the Manage.WindowsAzure.com portal and go to networks:

clip_image009

After you find your Virtual Network as we have done below, add a DNS Server entry to this one VNET. In this case we are adding the name OnPremDSN (Does not matter the actual name, just one you will recognize. However, the IP must be valid to one of your DNS servers. In our case it is 10.0.1.1 and will most likely be different in your environment.

clip_image011

clip_image013

clip_image015

Click Save

clip_image016

For more information:

Specifying a DNS Server in a Virtual Network Configuration File

 

Issue:

SQL Add Azure Replica Wizard fails with ‘The hosted service name is invalid’

clip_image002[7]

clip_image003[7]

clip_image005[7]

Cause:

The hosted Service Name or in this case the Cloud Service name cannot contain any ‘_’ (Underscore) characters. During the SQL Add Azure Replica Wizard attempts create a unique Cloud Service or Hosted Service name by combining the following AlwaysOn, Vnet Name, Availability Group Name, and GUID separated by a ‘-‘ (dash) character.

For example:

Virtual Network (Vnet) – Demo_Vnet

Availability Group – Demo_AG

Will result in trying to create a Cloud Service or Hosted Service with the name:

AlwaysOn-Demo_Vnet-Demo_AG-00000000000000000000000000000000.Cloudapp.Net

Fix:

There is no way to rename a virtual network or availability group. The only option will be to delete the virtual network or availability group and recreate without using the ‘_’ (underscore) character.

 

Issue:

SQL Add Azure Replica Wizard Fails on Step ‘Joining secondary replicas to availability group’

 

 

clip_image001[5]

clip_image002

clip_image004

clip_image005

clip_image007

Cause:

This error can occur if you have a listener defined for the Availability group prior to running the Add Azure Replica Wizard

Fix:

The only work around for this issue is to create the SQL AlwaysOn Listeners after you create the Availability Group. If you have a SQL Listener already defined, To avoid this issue, remove the existing Configure SQL AlwaysOn Listeners and then re-run the Add Azure Replica.

 

Issue:

VPN cannot be behind a NAT (Network Address Translation) device.

Cause:

Currently this is by design. Site to Site (S2S) is not supported when implemented behind a NAT Device (Home routers etc)

Fix:

In order to create a VPN between your On Premises network and Microsoft Azure, you must have a routing device or Microsoft Windows running RRAS (Remote Routing and Access Service) directly connected to the internet.

 

 

 

 

 

Troubleshooting Tips

 

Disable Azure Virtual Machine Cleanup

When trying to troubleshoot failures with the Add Azure Replica Wizard, you should enable the feature “Disable Azure Virtual Machine Cleanup”

When the Add Azure Replica fails it automatically cleans up the added Azure virtual machine. This makes it very difficult to troubleshoot different types of failures etc. So for troubleshooting purposes ONLY, you can disable the automatic cleanup on failure by adding the following registry key.

NOTE:

The wizard sets the key back to 0 once it has been used to disable cleanup. That means that subsequent uses of the wizard will clean up the virtual machine automatically.

Do Disable the cleanup of the Azure Environment add the following registry key prior to running the Add Azure Replica Wizard.

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\120\Tools\Client\CreateAGWizard
Value Name: CleanupDisabled
Value Type: DWORD

Set it to 1 to disable cleanup.

Connect to the newly provisioned Azure Replica virtual machine

For troubleshooting purposes it may be necessary to connect to the newly provisioned Azure virtual machine. Once the virtual machine is reported in the Azure Virtual Machine portal as ‘Running’ you can connect to the virtual machine one of two ways:

Use Remote Desktop to Connect to the provisioned virtual machine via its IP address

From the server you launched the SQL Server Add Azure Replica wizard, try to connect to the Azure virtual machine using Remote Desktop. To acquire the virtual machine’s IP address, use Azure Management Portal and bring up the Dashboard view for your Azure virtual machine.

Execute Remote Desktop, connecting with the vm’s IP address:

mstsc /v:10.0.2.x

Where 10.0.2.x is the Azure virtual machine’s IP address you acquired from the Azure Management portal for that virtual machine. You can also get hits from the Add Azure Replica log.

OR

Create a remote desktop endpoint on the Azure virtual machine

By default, the newly added Azure virtual machine has no Remote Desktop endpoint created. Create the endpoint using Azure Management Portal under the virtual machine’s Endpoints link.

Click Add and choose to Add a Stand-alone Endpoint. Then use Remote Desktop to make a connection to the Azure virtual machine from your host machine.

clip_image001[7]

Review the Add Azure Replica log for information on wizard failure.

Locate the Add Azure Replica Wizard log in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard on the on premise server you launched and ran the Add Azure Replica Wizard on.

Note:

Every time you run the Add Azure Replica Wizard, the logs will be overwritten. So it is important that you save them off as quickly as possible.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Known Issues / Fixes & Workarounds

ISSUE Virtual network drop down is empty – no Azure virtual networks are listed

SOLUTION Install the following hotfix:

FIX: Add Azure Replica wizard cannot enumerate Azure Virtual Network in SQL Server 2014

 

 

 

ISSUE Provisioning new virtual machine fails with error ‘The geo-location constraint…is invalid’ from Add Azure Replica:

The wizard fails and clicking the Error link reports ‘geo-location constraint’ error like below,

The Add Azure Replica Wizard log (found in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard reports:

Attempting to provision Windows Azure VM ‘vmname’ resulted in an error. (Microsoft.SQLServer.Management.HadrTasks)
Additional Information:
OperationID:1190164893f0120f97861e2cd5c47c8f, Status=Failed, Code=400, Details=The geo-location constraint specified for the hosted service is invalid.
(Microsoft.SQLServer.Management.HadrTasks)

 SOLUTION Install the following hotfix:

FIX: Add Azure Replica wizard cannot enumerate Azure Virtual Network in SQL Server 2014

 

ISSUE – ‘Checking if the cluster name resource is online’ fails with Access Denied

The validation page may fail while checking the Cluster name resource:

image

Clicking the Error link reports “Access is Denied.”

image

SOLUTION Launch SQL Server management Studio with elevated privileges

image

 

 

ISSUE Add Azure Replica Wizard Fails and reports ‘The RPC server is unavailable.’

The provisioned Azure virtual machine may not successfully join the domain, failing silently. If this occurs, while the wizard attempts to use WMI to configure ports on the virtual machine, clicking the Error links may report the following errors:

image

 

image

From the Add Azure Replica Wizard log (found in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard.

2014-11-12T19:55:40.525        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Azure Replica VM Role:Provisioning
2014-11-12T19:56:10.529        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Checking Azure Replica VM Role state…
2014-11-12T19:56:12.099        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Azure Replica VM Role:ReadyRole
2014-11-12T19:56:12.100        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Retrieving virtual machine private IP address…
2014-11-12T19:56:12.555        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      OperationId=2c307b5092792c08b8d2d15103925553, Status=Succeeded, Code=200, Details=OK
2014-11-12T19:56:12.555        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Retrieved virtual machine private IP address 10.0.16.5
2014-11-12T19:56:12.566        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Establishing remote PowerShell session. Host:10.0.16.5, Port:5986, Username:AZsqlnode\cmathews
2014-11-12T19:56:12.567        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.EnableFirewallPorts
2014-11-12T19:56:12.567        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.RetryOperation
2014-11-12T19:56:12.568        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.EnableFirewallCommand
2014-11-12T19:56:12.569        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = ‘135,49152-65535’ remoteip = any profile = Domain
2014-11-12T19:56:38.890        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Ok.
2014-11-12T19:56:38.890        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodExit       AlwaysOnWizard.EnableFirewallCommand [0ms]
2014-11-12T19:56:39.891        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodExit       AlwaysOnWizard.EnableFirewallPorts [0ms]
2014-11-12T19:56:39.891        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.InitializeDataDisksManager
2014-11-12T19:56:39.891        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.RetryOperation
2014-11-12T19:56:39.895        5344 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.WMIHelper.Connect
2014-11-12T19:56:39.895        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMIHelper connection setup for remote connection:\\10.0.16.5\root\cimv2
2014-11-12T19:56:39.896        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMIHelper establishing connection attempt #1
2014-11-12T19:57:05.507        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMI connection failed due to the following error The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
2014-11-12T19:57:15.509        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMIHelper establishing connection attempt #2
2014-11-12T19:57:41.016        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMI connection failed due to the following error The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
2014-11-12T19:57:51.017        5344 SSMS_HadrTasks                 AlwaysOnWizard                           Information      WMIHelper establishing connection attempt #3

SOLUTION Add your on-premise DNS server IP address to your Azure virtual network

1 In your Azure Portal, click Networks in the left pane, and in the right pane, click your Azure virtual network name.

image 

2 Click the Configure link.

image

3 Add your on-premise DNS server under ‘dns servers.’

image

 

ISSUE Add Azure Replica fails with ‘The hosted service name is invalid’ if your Azure virtual network or on-premise availability group name has an ‘_’ underscore character in it.

image

In the below output (From the Add Azure Replica Wizard log found in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard), observe that the availability group is ‘ag’, and the Azure virtual network name is ‘VPN_virtual’ which results in the failure ‘The hosted service name is invalid.’ Here the DNS name is being set to the following which includes both the availability group name ‘ag’ and the virtual network name ‘VPN_virtual’: AlwaysOn-VPN_virtual-ag-c393bede3b5348298a2f55cc260563fd.cloudapp.net.

Since Azure does not support underscore in the DNS name this fails.

2014-11-05T15:11:50.507        3388 SSMS_HadrTasks                 AlwaysOnWizard                           MethodExit       AlwaysOnWizard.ValidatePageController:CheckValidationStepsExecutionStatus [0ms]
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Availability Group: ag

2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information          Server instance name: 2012N5AZ
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information              Virtual Machine Name: 2012N5AZ
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Image: SQL Server 2014 RTM Enterprise on Windows Server 2012 R2
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Location: East US
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Virtual Machine Size: Large (4 cores, 8 GB Memory)
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Virtual Network: VPN_virtual (East US)
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Virtual Network Subnet: Subnet-1(10.0.2.1/25)
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Cluster Name: PrimaryCluster.SQLTEST.EDU
2014-11-05T15:11:52.150        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information                  Windows Azure Hosted Service (DNS name): AlwaysOn-VPN_virtual-ag-c393bede3b5348298a2f55cc260563fd.cloudapp.net

2014-11-05T15:11:57.372        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work item Provisioning Windows Azure VM ‘2012N5AZ’. started
2014-11-05T15:11:57.388        3388 SSMS_HadrTasks                 AlwaysOnWizard                           MethodEnter      AlwaysOnWizard.CreateHostedService
2014-11-05T15:11:57.845        3388 SSMS_HadrTasks                 AlwaysOnWizard                           Information      OperationId=07a5f1f8e6fb27f482ed47e8b49ae136, Status=Failed, Code=400, Details=The hosted service name is invalid.
2014-11-05T15:11:57.845        3388 SSMS_HadrTasks                 AlwaysOnWizard                           CriticalError    [*] OperationId=07a5f1f8e6fb27f482ed47e8b49ae136, Status=Failed, Code=400, Details=The hosted service name is invalid.

SOLUTION Do not use underscore (‘_’) in the name of your availability group or Azure virtual network name.

 

ISSUE Add Azure Replica fails during Join operation is availability group has pre-defined listener.

If you run the Add Azure Replica wizard when your on premise availability group has a listener already defined, and that listener has only been created in the context of the on premise network, the Add Azure Replica Wizard may fail during the join operation.

The following appears in the Add Azure Replica wizard when join fails:

2014-12-23T13:23:13.666        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work item Adding secondary replicas to availability group ‘Cluster02AG’. started
2014-12-23T13:23:14.053        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work item Adding secondary replicas to availability group ‘Cluster02AG’. stopped
2014-12-23T13:23:14.053        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work Item:Adding secondary replicas to availability group ‘Cluster02AG’., Details:Completed!
2014-12-23T13:23:14.073        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work item Joining secondary replicas to availability group ‘Cluster02AG’. started
2014-12-23T13:23:14.773        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Information      Work item Joining secondary replicas to availability group ‘Cluster02AG’. stopped
2014-12-23T13:23:14.773        3036 SSMS_HadrTasks                 AlwaysOnWizard                           Error            [*] Work Item:Joining secondary replicas to availability group ‘Cluster02AG’., Details:Joining secondary replica to availability group resulted in an error.

 

 

 

The Add Azure Replica warns you in the Specify Replica dialog box that the listener will ‘not be configured.’

SQL

SOLUTION Temporarily remove the availability group listener to run the Add Azure Replica wizard.

 

ISSUE VPN Cannot be behind a NAT (network address translation) device

In order for this to work your RRAS server has to be directly connected to the Internet. You cannot have it behind a NAT’d connection. For more information see:

Tutorial: Create a Cross-Premises Virtual Network for Site-to-Site Connectivity

The VPN device cannot be located behind a network address translator (NAT) and must meet the minimum device standards. See About VPN Devices for Virtual Network for more information. In this case your RRAS Server is your VPN Device

This link below is how to setup the Gateway in an environment similar to what you are trying to do.

Connect an On-premises Network to Azure via Site to Site VPN and Extend Active Directory onto an IaaS VM DC in Azure

 

Troubleshooting Tips

Disable Azure Virtual Machine Cleanup When the Add Azure Replica fails it automatically cleans up the added Azure virtual machine. For troubleshooting purposes, you can disable cleanup by adding the following registry key. NOTE the wizard sets the key back to 0 once it has been used to disable cleanup. That means that subsequent uses of the wizard will clean up the virtual machine automatically.

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\120\Tools\Client\CreateAGWizard
Value Name: CleanupDisabled
Value Type: DWORD

Set it to 1 to disable cleanup.

Connect to the newly provisioned Azure Replica virtual machine For troubleshooting purposes it may be necessary to connect to the newly provisioned Azure virtual machine. Once the virtual machine is reported in the Azure Virtual Machine portal as ‘Running’ you can connect to the virtual machine one of two ways:

Use Remote Desktop to Connect to the provisioned virtual machine via its IP address From the server you launched the SQL Server Add Azure Replica wizard, try to connect to the Azure virtual machine using Remote Desktop. To acquire the virtual machine’s IP address, use Azure Management Portal and bring up the Dashboard view for your Azure virtual machine.

Execute Remote Desktop, connecting with the vm’s IP address:

mstsc /v:10.0.2.x

Where 10.0.2.x is the Azure virtual machine’s IP address you acquired from the Azure Management portal for that virtual machine. You can also get hits from the Add Azure Replica log.

OR

Create a remote desktop endpoint on the Azure virtual machine By default, the newly added Azure virtual machine has no Remote Desktop endpoint created. Create the endpoint using Azure Management Portal under the virtual machine’s Endpoints link. Click Add and choose to Add a Stand-alone Endpoint. Then use Remote Desktop to make a connection to the Azure virtual machine from your host machine.

image

Review the Add Azure Replica log for information on wizard failure. Locate the Add Azure Replica Wizard log in <Users>\<user name>\AppData\Local\SQL Server\AddReplicaWizard on the on premise server you launched and ran the Add Azure Replica Wizard on.

 

Additional Information and Guidelines

For more information and guidelines on using the SQL Server 2014 Add Azure Replica wizard, see:

Use the Add Azure Replica Wizard (SQL Server)

For a tutorial of the Add Azure Replica wizard, that includes a walk-through using screenshots of each dialog box:

Tutorial: Add Azure Replica Wizard


Comments (1)

  1. Omar says:

    Add Azure Replica is a complex part and this is a great help to resolve issues that blocks our users. Thanks you!

Skip to main content