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).
The more common and accepted way is that once a job lands on a server in an AG you start editing it:
- Add a first job step that checks whether this is the primary.
- Exit on success if it isn't.
- Otherwise continue onto the normal job step.
This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find them):
The second way is to create a watchdog job using similar methods which will toggle the jobs on and off. This is probably more acceptable seeing as you're fiddling with SQL Server internal jobs. But it's up to you.
I wasn't quite happy with the samples above hardcoding AG names and such. I prefer to just have the job step operate within the database in question, and this way it can work out for itself if it even has a group and is part of it (as your jobs should continue running even if you temporarily remove them from an AG).
Set Nocount On
If Exists (
-- Database in an AG
Select Top 1 0
From sys.availability_databases_cluster adc
Join sys.availability_groups ag
On adc.group_id = ag.group_id
Join sys.dm_hadr_availability_group_states dhags
On ag.group_id = dhags.group_id
Where adc.database_name = Db_Name()
)
And Not Exists (
-- Database in an AG which is Primary on this instance
Select Top 1 0
From sys.availability_databases_cluster adc
Join sys.availability_groups ag
On adc.group_id = ag.group_id
Join sys.dm_hadr_availability_group_states dhags
On ag.group_id = dhags.group_id
Where adc.database_name = Db_Name()
And Upper(dhags.primary_replica) = Upper(@@Servername)
)
Begin
Raiserror('This is not the primary replica.', 16, 1) With Nowait
Return
End
-- Check if the database isn't accessible
If Not Exists (
Select Top 1 0
From sys.databases
Where databases.name = Db_Name()
And state_desc = 'ONLINE'
)
Begin
Raiserror('The database doesn''t exist or isn''t ONLINE on this node.', 16, 1) With Nowait
Return
End
This kind of thing is also useful for SSIS jobs and other bits that pop up over time.
Going one step further you probably want to create a PowerShell script which will go through and check your servers from time to time for AGs which have CDC jobs and which don't have a watchdog, so that you will be notified to create one.
Best Answer
Latency on the asynchronous replica will be dependent on lots of factors, such as size of the transactions (index rebuilds could cause a huge amount of backlog), I/O, network, etc. If you want zero latency, then you'll need to use synchronous and not asynchronous, but be aware that this adds to the transaction time and should be within the same data center as the primary replica (in general).
When you force failover, the AG replicas will need to be rebuilt with a full backup and log chain to get back in sync and rejoined to the AG.