Yes AG support multiple subnets as described here. Also make sure that your data provider supports MultiSubnetFailover .. .NET Framework 4 supports it.
To answer your question ...
IF you use .NET framework 4 or 3.5 then the provider will support it as described here.
Also, a good reference to SQL Server Multi-Subnet Clustering is well documented.
With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.
If you want to enable automatic failover, you will need to ensure that you have an "automatic failover set", to include two replicas (in your case, that'd be your two nodes) both set with synchronous commit, as well as automatic failover.
When you setup the Availability Group, this can be accomplished through the GUI, but if you had this scripted out, it would appear in the CREATE AVAILABILITY GROUP
command. If you take a look at the syntax and possible parameters of CREATE AVAILABILITY GROUP
, you would simply see the opportunity to set AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
as well as set FAILOVER_MODE = AUTOMATIC
. This is essentially the steps to setup automatic failover.
Likewise, if this is already setup you can modify these replica settings with ALTER AVAILABILITY GROUP ... MODIFY REPLICA ON ...
and specify the same above parameters.
Before continuing on, though, with the above modifications it is definitely going to be worth verifying what you already have in place. A quick query that will tell you what your commit mode and failover mode is for your current configuration is below:
select
ar.replica_server_name,
availability_group_name = ag.name,
ar.availability_mode_desc,
ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;
Feel free to edit your question and put the output of the above query for review.
EDIT: In order to address your comment, it is worth pointing out that to have automatic failover you must be utilizing synchronous commit.
Best Answer
That's not what it means.
With a FCI, the protection is done at the service level. It means that if the primary node have an issue, the service will restart on the other node. As all node share the same storage, you end up with the exact same "instance" after a failover (same jobs, same logins, same DBs, etc..) but running from a different server.
In AG, the services is up and running on both node. The protection is set on at the database level (you can have some DB on the primary node that are in AG and some other that aren't for example). Each nodes also have their own storage.
If you are in sync mode and have the failover set to "automatic", then if the primary node of you AG fail, all DBs that are in your AG will failover to the other node.
P.s. The system databases are not part of the AG so you need to be carefull with everything releated to those (ex: Jobs, logins, etc) as they won't "failover".