Sql-server – AlwaysOn Synchronous Mode Possibilty

availability-groupssql serversql server 2014

We have 2 SQL Servers at Production site and 1 SQL Server at DR site.

Please correct below things as I am not getting clear idea about these sync mode in AlwaysOn:

  • If I install SQL Server FCIs in Production environment (2 node) can I set one secondary to synchronous mode automatic failover and DR (Stand-Alone) server to Async mode?

  • Total 3 SQL Servers (Primary Sync, Secondary Sync and Secondary Async (Stand-Alone)) is it possible?

  • If SQL Server Cluster (2 Instances) is configured in Windows 2-node cluster, is synchronous mode possible?

  • Should all the SQL Servers be stand-alone to configure AlwaysOn synchronous mode and automatic failover?

  • Two SQL Servers in Production site, can I set secondary as Synchronous mode with automatic failover?

Best Answer

If I install SQL Server FCIs in Production environment(2 node) can i set one secondary to synchronous mode automatic failover and DR(Stand-Alone) server to Async mode?

If you use a SQL Server FCI with Availability Groups, you cannot have automatic failover of the AG but you still get automatic failover of the FCI. The FCI will be one instance and the DR secondary will be another (as FCI are logically a single instance).

Total 3 SQL Servers (Primary Sync,Secondary Sync and Secondary Async(Stand-Alone)) is it possible ?

This is possible, but you're going to use the FCI from your first question then there will only ever be 2 instances, so you'd have Primary Sync and secondary async.

If SQL Server Cluster(2 Instances) is configured in windows 2 node cluster,is synchronous mode possible ?

I'm not sure what you mean by SQL Server cluster and windows cluster. If you are talking FCIs, you wouldn't be able to AG between the FCIs because they'd share the same set of nodes.

Should all the SQL Servers should be stand-alone to configure Always-ON synchronous mode and automatic failover?

They don't have to be. It really depends on what you're trying to solve for. Remember that each secondary will have a copy of the database and will need to have the log blocks be transported to it. This may or may not be doable with the infrastructure available.

Two SQL Servers in Production site,can i set secondary as Synchronous mode with automatic failover ?

Assuming neither are in a SQL Server FCI, yes and you can do it on the fly (though it may take time to get in sync).

Related Question