locked
I cannot add SQL Host in SQL PaaS provider with the AlwaysOn option RRS feed

  • Question

  • I've already configured SQL PaaS Provider in Azure Stack ASDK.

    Adding SQL Host without AlwaysOn option selected works fine, but with the same parameters I try to add the same SQL Host, I get the following error:

    {"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.","details":[{"code":"BadRequest","message":"{\r\n \"error\": {\r\n \"code\": \"-2147467259\",\r\n \"message\": \"This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server\"\r\n }\r\n}"}]}

    subscriptions/500bbe7b-f2d9-4661-b162-xxxxxxx/resourceGroups/sqlpaas/providers/Microsoft.SQLAdapter.Admin/hostingServers/192.168.xxx.xx

    StatusMessage

    { "error": { "code": "-2147467259", "message": "This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server" } }

    
    
    Friday, January 26, 2018 10:42 AM

Answers

  • Hello,

    Thre steps are different for adding a Standalone SQL hosting servers and a Always On Availability Group.

    To add an Always On Availability Group please following the documentation below:

    Add hosting servers

    The SQL adapter RP only supports SQL 2016 SP1 Enterprise or later instances for Always On, as it requires new SQL features such as automatic seeding. In addition to the preceding common list of requirements:+

    • You must provide a file server in addition to the SQL Always On computers. There is an Azure Stack Quickstart template that can create this environment for you. It also can serve as a guide to building your own instance.
    • You must set up the SQL servers. Specifically, you must enable Automatic Seeding on each availability group for each instance of SQL Server.

    +

    <button copy"="" data-bi-name="copy">Copy </button>

    ALTER AVAILABILITY GROUP [<availability_group_name>]
        MODIFY REPLICA ON 'InstanceName'
        WITH (SEEDING_MODE = AUTOMATIC)
    GO

    On secondary instances+

    <button copy"="" data-bi-name="copy">Copy </button>

    ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
    GO

    To add SQL Always On hosting servers, follow these steps:+

    1. Sign in to the Azure Stack admin portal as a service admin
    2. Click Browse > ADMINISTRATIVE RESOURCES > SQL Hosting Servers > +Add.

    The SQL Hosting Servers blade is where you can connect the SQL Server Resource Provider to actual instances of SQL Server that serve as the resource provider’s backend.

    1. Fill the form with the connection details of your SQL Server instance, being sure to use the FQDN or IPv4 address of the Always On Listener (and optional port number). Provide the account information for the account you configured with system admin privileges.
    2. Check this box to enable support for SQL Always On Availability Group instances.

    1. Add the SQL Always On instance to a SKU. You cannot mix standalone servers with Always On instances in the same SKU. That will be determined when adding the first hosting server. Attempting to mix types afterwards will result in an error.

      

    We apologize for any inconvenience and appreciate your time and interest in Azure Stack.

    If you experience any issues with Azure Stack or the current ASDK release, please feel free to contact us.

          

     Thanks


    Gary Gallanes

    Friday, January 26, 2018 7:26 PM
  • Hello!

    I can guess where is the issue, I have to indicate the AvailabilityGroup listener IP, the problem is I've configured the 3 VMs (one DC+ File share, 2 SQL Servers) in a private virtual network.

    The nodes have a public IP but I don't know how to assign a public IP to the Availability group listener.

    From the administration portal I can only add SQL VMs with public IP, the standalone servers work fine.

    Thanks and Regards

    Jordi Muste

    Tuesday, January 30, 2018 9:53 AM
  • Hello,

    You ‘ll first need to create an internal load balancer and then configure your Availability Group Listener.

       

    Please see

    Create an Azure load balancer & Configure the listener for Always On availability groups

        

         

    Let us know how it goes.

         

    We apologize for any inconvenience and appreciate your time and interest in Azure Stack.

    If you experience any issues with Azure Stack or the current ASDK release, please feel free to contact us.

         

     Thanks


    Gary Gallanes

    Friday, February 2, 2018 1:43 AM

All replies

  • Configuring SQL Always On instances requires additional steps and involves at least three VMs (or physical machines), ensure that all the prerequisites are met as outlined in this documentation and then re-try the operation.

    ---------------------------------------------------------------------------------------------------

    Do click on "Mark as Answer" on the post that helps you, this can be beneficial to other community members.

    Friday, January 26, 2018 5:23 PM
  • Hello,

    Thre steps are different for adding a Standalone SQL hosting servers and a Always On Availability Group.

    To add an Always On Availability Group please following the documentation below:

    Add hosting servers

    The SQL adapter RP only supports SQL 2016 SP1 Enterprise or later instances for Always On, as it requires new SQL features such as automatic seeding. In addition to the preceding common list of requirements:+

    • You must provide a file server in addition to the SQL Always On computers. There is an Azure Stack Quickstart template that can create this environment for you. It also can serve as a guide to building your own instance.
    • You must set up the SQL servers. Specifically, you must enable Automatic Seeding on each availability group for each instance of SQL Server.

    +

    <button copy"="" data-bi-name="copy">Copy </button>

    ALTER AVAILABILITY GROUP [<availability_group_name>]
        MODIFY REPLICA ON 'InstanceName'
        WITH (SEEDING_MODE = AUTOMATIC)
    GO

    On secondary instances+

    <button copy"="" data-bi-name="copy">Copy </button>

    ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
    GO

    To add SQL Always On hosting servers, follow these steps:+

    1. Sign in to the Azure Stack admin portal as a service admin
    2. Click Browse > ADMINISTRATIVE RESOURCES > SQL Hosting Servers > +Add.

    The SQL Hosting Servers blade is where you can connect the SQL Server Resource Provider to actual instances of SQL Server that serve as the resource provider’s backend.

    1. Fill the form with the connection details of your SQL Server instance, being sure to use the FQDN or IPv4 address of the Always On Listener (and optional port number). Provide the account information for the account you configured with system admin privileges.
    2. Check this box to enable support for SQL Always On Availability Group instances.

    1. Add the SQL Always On instance to a SKU. You cannot mix standalone servers with Always On instances in the same SKU. That will be determined when adding the first hosting server. Attempting to mix types afterwards will result in an error.

      

    We apologize for any inconvenience and appreciate your time and interest in Azure Stack.

    If you experience any issues with Azure Stack or the current ASDK release, please feel free to contact us.

          

     Thanks


    Gary Gallanes

    Friday, January 26, 2018 7:26 PM
  • Hello!

    I can guess where is the issue, I have to indicate the AvailabilityGroup listener IP, the problem is I've configured the 3 VMs (one DC+ File share, 2 SQL Servers) in a private virtual network.

    The nodes have a public IP but I don't know how to assign a public IP to the Availability group listener.

    From the administration portal I can only add SQL VMs with public IP, the standalone servers work fine.

    Thanks and Regards

    Jordi Muste

    Tuesday, January 30, 2018 9:53 AM
  • Hello,

    You ‘ll first need to create an internal load balancer and then configure your Availability Group Listener.

       

    Please see

    Create an Azure load balancer & Configure the listener for Always On availability groups

        

         

    Let us know how it goes.

         

    We apologize for any inconvenience and appreciate your time and interest in Azure Stack.

    If you experience any issues with Azure Stack or the current ASDK release, please feel free to contact us.

         

     Thanks


    Gary Gallanes

    Friday, February 2, 2018 1:43 AM