sketched WSFC across counties

any idea on how to build a skethched WSFC across multiple subnet/Data center ? same way but just IP address diff and the depends on IP is OR instead of AND ? for the use of SQL server AOG.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all, contact your system administrators and security administrators to see if they even allows that. If they don't then you don't need to loose more time with this.
If they do, they ask their help to set the regional WSFC. Basically is the same way you set a local WSFC but with the particularity that domains need to be trusted and necessary firewall ports need to be allowed between the servers and also, the domain users need to have the necessary permissions in the another domain.
0
marrowyungSenior Technical architecture (Data)Author Commented:
did you try that before? is it good ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. All AOG that I've built had a node in a DR datacenter that's usually in another region.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

marrowyungSenior Technical architecture (Data)Author Commented:
the case you try is SQL 2016 ?

both DR and primary SQL server in the same AOG ?

so by this means your system and network administrator create that WSFC and add all SQL nodes to that WSFC for you ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL 2012, 2014, 2016, 2017. Since the beginning of AOG.
The solution that I like the most is having 3 nodes, where 2 nodes are in the Primary Site, where the Secondary Replica (sync) acts a HA (automatic failover) and the 3rd node in the DR site where the Secondary Replica (assync) acts as DR (manual failover).

so by this means your system and network administrator create that WSFC and add all SQL nodes to that WSFC for you ?
No, they just create the 3 nodes of the WSFC and we do the rest (install SQL Server instances and configure the AOG).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"The solution that I like the most is having 3 nodes, where 2 nodes are in the Primary Site, where the Secondary Replica (sync) acts a HA (automatic failover) and the 3rd node in the DR site where the Secondary Replica (assync) acts as DR (manual failover).
"

yeah. this is the minimum good number of node to start with.

"No, they just create the 3 nodes of the WSFC "

so they install 3 x nodes, join the domain, configure all network for multi site .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so they install 3 x nodes, join the domain, configure all network for multi site .
Right.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Anything needs to do to make SQL server able to failover based on diff IP subnet ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just be sure that Listener has 2 IP address, one to work on the Primary Replica subnet and another one to work on the other subnet.
0
marrowyungSenior Technical architecture (Data)Author Commented:
so you mean from cluster manger ? not from SQL AOG, right? I don't see that.

and here  ?


define more than one IP for SQL AOG
and relateionship is an OR, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
from here:

"You can create only one listener per availability group through SQL Server. Typically, each availability group requires only one listener. However, some customer scenarios require multiple listeners for one availability group. After creating a listener through SQL Server, you can use Windows PowerShell for failover clusters or the WSFC Failover Cluster Manager to create additional listeners. For more information, see To Create An Additional Listener for an Availability Group (Optional), later in this topic."

so this mean can't create ADDITIONAL listener by using SQL serve SSMS ?  

For more than one IP address for SQL listener, you are referring to :

MultiSubnetFailover Keyword and Associated Features
MultiSubnetFailover is a new connection string keyword used to enable faster failover with Always On Availability Groups and Always On Failover Cluster Instances in SQL Server 2012. The following three sub-features are enabled when MultiSubnetFailover=True is set in connection string:

Faster multi-subnet failover to a multi-subnet listener for an Always On Availability Group or Failover Cluster Instances.

Faster single subnet failover to a single subnet listener for an Always On Availability Group or Failover Cluster Instances.

This feature is used when connecting to a listener that has a single IP in a single subnet. This performs more aggressive TCP connection retries to speed up single subnet failovers.
Named instance resolution to a multi-subnet Always On Failover Cluster Instance.

This is to add named instance resolution support for an Always On Failover Cluster Instances with multiple subnet endpoints.
MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB

Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout.

Workarounds: We recommend that you do one of the following tasks.

If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).

Pros: If a cross-subnet failover occurs, client recovery time is short.

Cons: Half of the client connections will take more than 20 seconds

If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting” later in this section.

Pros: You do not need to increase your client-connection timeout value.

Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.

Open in new window


?
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so this mean can't create ADDITIONAL listener by using SQL serve SSMS ?  
First you'll need to create the Listener resource in the WFC and then you can add it as you added the first listener.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, WSFC anyway and that one is an additional network name resource?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it is. A Listener need to have a respective Network resource in the Cluster.
0
marrowyungSenior Technical architecture (Data)Author Commented:
and we can only create up to one more listener ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know if there's a limit for the number of listeners but I usually only need one and if some rare occasions, two listeners.
Can't really see the benefit to have a large number of listeners.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.