People who code: we want your input. Take the Survey
54

The way I always like to visualize high availability solutions is the following: SQL Server Failover Cluster Instance (FCI) What is highly available? The entire instance. That includes all server-objects (logins, SQL Server Agent jobs, etc.). This also includes databases and their containing entities. It's a great solution for highly available SQL ...


23

two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance What Kind of workload? "It depends" - but seriously, this is useful for an online application where you need to have local in data center High Availability. You are protected against a failure of one machine, or of one operating system. The logins, jobs, new databases, ...


19

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well. However just to give more overview of what a database mirroring Endpoint is: From BOL, Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables ...


19

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: Failover Clustered Instances (FCIs) - what your grandpa used to call an active/passive cluster Availability Groups (AGs) - like database mirroring, but works ...


12

Failover - automatic or otherwise - isn't provided directly by PostgreSQL. You'll need external tools like repmgr. The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer. If you do choose to use autofailover, you ...


11

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 may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure: ...


10

First, there's no SQL Server 2013, but the good news is that the version number of SQL Server doesn't matter. It's more based on Windows failover cluster networking, which is explained here in a 3-part series by the Microsoft folks: http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx And then ...


10

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to ...


9

It is also important to consider what is shared. Failover Clustering uses two or more server nodes sharing one disk array. If the disk array goes down then you lose service, regardless of how many server nodes there are. If the server room where that disk array is located catches fire or floods then you lose service. AlwaysOn Availability Groups and ...


9

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-endpoint-for-amazon-aurora-load-balancing-higher-availability/


9

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, Microsoft GTSC. In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary ...


9

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 knows, the network has gone down and SQL1 is still operating as the primary. Thus, SQL2 goes to the RESOLVING state (it's not sure what to do). This is to prevent ...


9

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 methods, like sync Availability Groups. In such cases, it is reasonable to failover automatically. But when we talk DR, it is reasonable to imagine longer distances, ...


9

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, there is Add/Remove Columns button Pick Estimated Data Loss (time) and add this column to dashboard You will be able to see the estimated data loss time for each ...


8

No it will not. The databases are failed over independently from each other. There's no harm is failing over the test database, the production database will stay where it is.


8

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 plans are part of a concerted whole that includes app servers, BI and other resources. Add in a healthy mix of geographic dispersal and synchronization across ...


7

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 node. The Failover Cluster service will wait until you manually bring the service back online using Failover Cluster Manager, where you can decide which node ...


7

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 size. Times listed for failover are what it takes for the database to up and in a writeable state on the new node. Your mileage can, and will vary, but this is ...


7

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 failover is complete and crash recovery is also complete, the queries can then run on the other instance where it has its own tempdb. A Failover Cluster ...


6

I found the problem. SQL Native Client and the .NET SQL Data Provider should seamlessly connect to a failover partner even if the primary is down; however, the default configuration of both providers is to try both TCP/IP and Named Pipes. This configuration is fine, and works appropriately, when the servers are still both available but the primary has ...


6

Just for completeness, there is the option of using plain old mirroring. The advantages here include having two copies of the database without the complexity of using Availability Groups, and without needing shared storage for Failover Clustering. Disadvantage, although slight, is mirroring is deprecated. Failover times with mirroring are on the order of ...


6

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. In order to elect (and maintain) a primary, a majority of voting members need to be available. It is impossible to read the data from the database if the ...


6

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 themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end ...


6

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 potentially sprinkled in other logs depending on a few things, the events that show a failover has occurred. For example, in the event logs you might search for ...


6

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 backhoe through the fibre, this happens all the time. Or the primary might have been totally destroyed by a natural disaster or a terrorist atrocity. There is ...


5

Or two of the NICs in each machine have to have some private network scheme which will allow them to communicate directly? You need 2 NICs as One NIC is connected to the public network --> This is where your client applications will connect using clustered IP address and clustered SQL Server name. A recommended practice is to have 2 teamed NICs for public ...


5

After a forced failover testing with async mode, do I need to rebuild my old primary database? I'm not sure exactly what you mean by "rebuild" the database, but provided the databases are still in a working condition then you shouldn't need to take any actions like that. What you're seeing, by performing a forced failover, is by design. If you do ...


5

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 resources/services to a particular node and then carry on with your needed activity. The database engine and SQL Agent are cluster aware by default. Other SQL ...


5

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 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering? Yes you are correct. ...


5

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 server reboots or failovers of an FCI, particularly one with a lot of memory, I like to run a CHECKPOINT on every database. This minimizes the time spend on a clean ...


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