none
Multisite clustering and AGs RRS feed

  • Question

  • Hi all,

    I have been researching WIN 2016 features and new AGs enhancements lately. Need your guidance on the following requirement and proposed hypothesis for my virtual lab purpose. I'd like to ensure that my interpretation of AG concepts is accurate. Appreciate further comments and alternate approach if my hypothesis is not applicable.

    OS: Win2016; SQL; 2016 EE

    Requirement:

    Let's assume I have 4 nodes(N1,N2,N3,N4), 2 nodes in Location A (site 1) and other 2 in location B (site 2). App1 is accessing (A,B,C) databases in site1 for all their operations and App2 is accessing (X,Y,Z) databases in site 2. In my case, all sites act as primary to their business apps. All write operations should be independent to each site whereas reads could be load-balanced. 

    Following are my proposals based on my understanding about FCI/AGs and Strech Clusters

    -- Using FCI and AGs

    1) Create single WSFC between 4 nodes 

    2) Create and configure FCI-1 on nodes N1 and N2

    3) Create and configures FCI-2 on nodes N3 and N4

    4) Create AG-1 and AG listener from site1 to site 2 on databases (A,B,C) - readable secondaries (async or sync)

    5) Similarly AG-2 and listener on X,Y,Z databases from site2 to site1 

    Hypothesis: It has HA between N1 and N2 within site1 and at the same time I have secondary replicas pointing to site2. I could also setup Readable Routing list (FCI-2) to make all reads load- balanced across two FCIs. Vice-versa applies to another set of databases from site2 to site1.



    Harsha



    • Edited by HarshaV Wednesday, December 7, 2016 12:00 PM
    Wednesday, December 7, 2016 11:59 AM

Answers

  • Thanks Shashank!

    Any recommendations on articles or blogs for reference ? Also could you give some of the preventive steps i should be aware of, I know networking place vital role here - so primarily focusing on IPs, OR dependencies etc., ?


    Harsha

    The OR dependency has to configured by just configuring two IP's for Listener having different subnet. You can follow This blog

    The networking part has to be done by network team and I cannot add much on that.

    I cannot find blog for exact scenario but this blog has whitepapers which would help you


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by HarshaV Friday, January 13, 2017 7:34 PM
    Thursday, December 8, 2016 7:56 AM
    Moderator

All replies

  • Harsha,

    Your configuration seems fine and is supported. Node 1/2 is in SQL Cluster and node3 or 4 would ac as Secondary Sync/Async replica and vice versa.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, December 7, 2016 1:02 PM
    Moderator
  • Thanks Shashank!

    Any recommendations on articles or blogs for reference ? Also could you give some of the preventive steps i should be aware of, I know networking place vital role here - so primarily focusing on IPs, OR dependencies etc., ?


    Harsha

    Wednesday, December 7, 2016 6:41 PM
  • Thanks Shashank!

    Any recommendations on articles or blogs for reference ? Also could you give some of the preventive steps i should be aware of, I know networking place vital role here - so primarily focusing on IPs, OR dependencies etc., ?


    Harsha

    The OR dependency has to configured by just configuring two IP's for Listener having different subnet. You can follow This blog

    The networking part has to be done by network team and I cannot add much on that.

    I cannot find blog for exact scenario but this blog has whitepapers which would help you


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by HarshaV Friday, January 13, 2017 7:34 PM
    Thursday, December 8, 2016 7:56 AM
    Moderator
  •  I could also setup Readable Routing list (FCI-2) to make all reads load- balanced across two FCIs. Vice-versa applies to another set of databases from site2 to site1.

    Reads will NOT be load balanced across secondary servers. Additionally since one will be the primary and one the secondary there will only ever be 2 instances involved in the AG - it'll have 2 replicas, one primary and one secondary. This you would only have a single secondary for read offloading.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, December 9, 2016 10:06 PM
    Answerer
  • Seon,

    Yes, you are correct. In my test scenario it can't be load balanced as you have explained.

    thanks for pointing that.


    Harsha

    Friday, January 13, 2017 7:33 PM