sketched WSFC across counties

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.

did you try that before? is it good ?

Yes. All AOG that I've built had a node in a DR datacenter that's usually in another region.

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 ?

"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 .

Anything needs to do to make SQL server able to failover based on diff IP subnet ?

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.

so you mean from cluster manger ? not from SQL AOG, right? I don't see that.

and here  ?



and relateionship is an OR, right?

Follow this article to Create or Configure an Availability Group Listener.

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.

Select allOpen in new window

?

and this one:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server#MultiSubnetFailover

seems don't tell how to add one more IP address to the listener.

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.

ok, WSFC anyway and that one is an additional network name resource?

Yes, it is. A Listener need to have a respective Network resource in the Cluster.

and we can only create up to one more listener ?

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.

tks.