SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).
can I expect consistent results to be returned from both replicas, every time?
Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different meaning from the distributed (CAP) consistency. You are not guaranteed to read the most recent consistent state. Particularly, you are not guaranteed to read your own committed writes. And the reads from each replica, while each being consistent, may not match.
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).
Best Answer
No. If the secondary drops, the primary node will continue operation. However, you need to be aware of how much disk space the queued transactions will consume. If you lose the second node for an extended period of time, it may make sense to evict it from the cluster so that the primary node won't queue the transactions.