19 votes
Accepted

Is "AlwaysOn" not always "Always On?"

You have a bunch of different questions in here. Q: What is the "Always On" thing? Microsoft uses that brand name (which was written without a space before 2016) to describe two different features: ...
Brent Ozar's user avatar
  • 42.9k
11 votes
Accepted

patching SQL Server with Availability Group and Secondary Replica

The scenario is called out and supported on the link you've provided. Availability Group with One Remote Secondary Replica If you have deployed an availability group only for disaster recovery, you ...
Sean Gallardy's user avatar
10 votes
Accepted

Why is there automatic failover for HA and manual failover for DR?

It is tricky with generalizations and simplifications. However, here's an attempt: My feeling is that HA generally is closer geographically. In such cases it is more realistic to use synchronous ...
Tibor Karaszi's user avatar
10 votes
Accepted

Availability Group - how much data was lost after forced failover

Go to SSMS Object Explorer Expand Always On High Availability Expand Availability Groups Right click your AG Select Show Dashboard In the dashboard, above the list of AG databases and their status, ...
Aleksey Vitsko's user avatar
9 votes

How do I configure AWS Aurora to separate write/read operations

I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google. https://aws.amazon.com/blogs/aws/new-reader-...
Tony's user avatar
  • 99
9 votes
Accepted

Always On AG is Down when service is stopped

It could be the issue described in INF: AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down by Arvindh Kalidasan - Support Engineer, ...
Christopher Silva's user avatar
9 votes

Availability Groups automatic failover is not so automatic

You have a quorum issue in this setup. There are only two participants in the cluster: SQL1 SQL2 If SQL1 goes down, there's no way for SQL2 to know that it should be the primary. For all SQL2 ...
Josh Darnell's user avatar
  • 29.2k
8 votes

Restart the active node services without failover

Use the Failover Cluster Manager application to manually stop the services. This tells the cluster you are intentionally shutting down the service, so it won't attempt to bring it back up on another ...
Hannah Vernon's user avatar
  • 70k
8 votes
Accepted

Does a replica set with only two machines make sense?

If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines). This is correct. ...
Stennie's user avatar
  • 10.2k
8 votes

Why is there automatic failover for HA and manual failover for DR?

This is going to change wildly based on each business's tolerance for data loss and recovery times. But, here are my thoughts on the matter. The SQL Servers do not live in a vacuum and our HA and DR ...
Jonathan Fite's user avatar
7 votes
Accepted

How long does a failover for SQL Server Availability Groups take?

Personal observation from experience with both FCI and AG failovers, with reasonable high volume transactional system (40k trx/sec). For each consider 6 databases ranging in size from 500MB to 4TB in ...
Nic's user avatar
  • 4,003
7 votes
Accepted

How is ‘tempdb’ getting used when Always On Failover happens?

Tempdb doesn't move when an AG failover occurs. Each replica is its own standalone instance. Each has its own tempdb. Queries will break during the failover. Once the application reconnects after the ...
Tara Kizer's user avatar
  • 5,946
7 votes

Always On Failover Clustering vs Always On Availability Groups

To start with there is nothing called as Always On or Always ON availability groups it is simply called as Availability groups. Always On Clustering and Always On Availability Groups are 2 ...
Shanky's user avatar
  • 19k
7 votes
Accepted

History of cluster failover

Is it possible to get a history of when a cluster failed over and which node became the active node? That depends on how your define "history". There will be, in the cluster log, system log, and ...
Sean Gallardy's user avatar
6 votes

Automatic failover support on SQL Server having only two servers

For automatic failover, you need a tie-breaker. Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting ...
Brent Ozar's user avatar
  • 42.9k
6 votes

Why is there automatic failover for HA and manual failover for DR?

Because in a DR scenario you need to check what really happened. E.g. your DR site loses contact with the primary. Should it activate? Well the primary might actually be fine and someone’s just put a ...
Gaius's user avatar
  • 11.2k
5 votes
Accepted

Failed to create, join or add replica to availability group in SQL Server 2014

Msg 19405, Level 16, State 17, Line 3 Failed to create, join or add replica to availability group 'AGName', because node 'Node3' is a possible owner for both replica 'Node3\ReadOnly' and '...
Sean Gallardy's user avatar
5 votes

Reaching SQL Server AlwaysOn HA listener from the Internet

It's generally considered a really bad idea to make your database accessible from the internet. Making the database accessible from the public internet is a HUGE security risk. It's easy to port-...
AMtwo's user avatar
  • 16.1k
5 votes

Stopping SQL Services on Active Node

When configured as a Cluster in Windows, you need to control the SQL Server Services from the Failover Manager, not SQL Server Configuration Manager. In Windows Failover Manager, you can fail the ...
SQL_Hacker's user avatar
5 votes
Accepted

In 2-node cluster using failover with Dynamic Quorum, is Witness needed?

Dynamic quorum basically dynamically adjusts votes depending on available servers. Each time when one of nodes goes down, dynamic quorum will remove the vote from that node. In your scenario you have ...
S4V1N's user avatar
  • 1,064
5 votes
Accepted

Longer time to bring SQL online after node failover

Most likely your problem is caused by the databases going through recovery to redo or undo transactions that haven't been hardened to data files. Avoid recovery all together Before doing planned ...
AMtwo's user avatar
  • 16.1k
5 votes
Accepted

Why automatic failover does not occur when I turn off SQL Service in SQL Server Failover Cluster Instance?

The Failover Cluster Manager is THE tool for managing the services protected by the cluster, so I believe it's not supposed to fail over the service if the administrator chose to stop the service ...
Ronaldo's user avatar
  • 5,069
5 votes

Failover time after SQL Server patch

After patching the passive node and rebooting it, we've tried to failover to that node. But surprisingly it took ~ 10 minutes to start the SQL Server service which hanged on change pending state. ...
Sean Gallardy's user avatar
4 votes

Force a Member to Become Primary in mongodb

The reason nms02 becomes Primary when it joins the replicaset is due to having the higher priority setting. According to the documentation: Specify higher values to make a member more eligible to ...
Derek Downey's user avatar
  • 23.4k
4 votes

Is MSDTC required for SQL Server 2012 Fail Over Cluster?

Microsoft Distributed Transaction Coordinator (MSDTC) provides a mechanism for ensuring atomicity and consistency across multiple data sources. It is required if you plan on performing explicitly ...
Hannah Vernon's user avatar
  • 70k
4 votes
Accepted

Understanding of SQL Server active/active cluster

You've essentially got it right. Active/Active is really just a multi-instance SQL cluster. When you install the Failover Cluster Instance (FCI) I1 on N1 of the Windows Server Failover Cluster (WSFC)...
Chad Mattox's user avatar
  • 2,156
4 votes
Accepted

Is an automatic failover to the primary replica of a readonly replica in an availability group possible?

This is covered in the documentation: Configure Read-Only Routing for an Availability Group (SQL Server), but for this approach to work, you will need to configure your read only routing URL on both ...
TheGameiswar's user avatar
  • 2,989
4 votes

Adding secondary replica from different subnet for AlwaysON

IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites
Robert L Davis's user avatar
4 votes
Accepted

MySQL H.A with automatic failover

Galera == PXC == MariaDB w/Galera -- These are close to the same. For HA, you need 1 (or more) node in each of 3 datacenters. (Think earthquakes, tornados, floods, even datacenter outages -- which do ...
Rick James's user avatar
  • 77.7k
4 votes

Who or what initiated the failover

What we would like to also see in the email is WHY and/or WHO. Then you have quite a good bit of development work ahead of you. Seriously. Everyone asks this same question, but it is not an easy one ...
Sean Gallardy's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible