none
Using Database Mirroring Connection String ("Failover_Partner") in AlwaysOn Availability Group

    Question

  • Hi,

    Trying to configure Always on AG on Azure IaaS, notice that Azure doesn't support fix IP.

    So how do I connect from client and simulate note A will be automatically failover to node B if A is die?

    Found this post: http://msdn.microsoft.com/en-us/library/windowsazure/jj870962.aspx

    Try to follow what suggested as connection string.

    Data Source=ReplicaServer1;Failover Partner=ReplicaServer2;Initial Catalog=AvailabilityDatabase;

    When simulating the die of Node A (by stopping its service), hoping that Node B will automatically failover.

    But I notice that the failover partner doesn't take effect as I got this error msg:

    A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

    Any idea, folks?

    Saturday, August 03, 2013 3:13 PM

All replies

  • How did you configure your AG? This backwards compatibility mode AFAIK only works with two nodes (one Primary one replica) and read only Access on secondary disabled. And of course you do Need a Driver that supports Mirroring...

    Saturday, August 03, 2013 5:41 PM
  • I follow the hands on lab over here http://blogs.msdn.com/b/sqlalwayson/archive/2013/01/23/test-lab-create-an-alwayson-availability-group-in-windows-azure-end-to-end.aspx.

    Yes, I also only used 2 node (1 primary and 1 secondary) and disable the readonly access on secondary.

    The way I simulated failure of the primary is just simply turn off the SQL Server Services. I'm expecting the secondary will automatically take the lead (as the primary has died) but still didn't work.

    For your info, I'm using ADO.NET Client driver which I believe supported http://msdn.microsoft.com/en-us/library/5h52hef8.aspx

    Sunday, August 04, 2013 12:43 AM
  • In addition

    http://msdn.microsoft.com/en-us/library/hh510184.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, August 04, 2013 7:28 AM
  • What do you see in the errorlog on the secondary? Did it Failover? Can you see any Login Messages?

    Sunday, August 04, 2013 9:25 AM
  • Here're the screenshot of my AG setting:

    Client app con string:

    <

    add name="MyDB1ConnectionString" connectionString="Server=[IP-1],[Port-1]; Failover Partner=[IP-2],[Port-2]; Database=[DB-name];User ID=[uid];Password=[pwd]

    When I try to set the readable secondary to "no" (As shown on above picture), I got this error:

    The target database, [db-name], is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

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

    The problem goes away when I set it to "Yes" (when both primary and secondary are on). But strangely, if I stop the service of the secondary, I got another error:

    A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

    Is this normal? What I expect it shouldn't throw such error as my primary is still up and running. 

    Monday, August 05, 2013 8:37 AM