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 ...


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.


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

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, ...


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

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

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

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 ...


5

If you use a DBVIP, you could remove the DBVIP from the Master and bring it up on the Slave. EXAMPLE Let's make up a DBVIP, like 10.1.2.30. Put this up on the Master in the OS ip addr addr 10.1.2.30/24 dev eth1 Replace your IP in your application with 10.1.2.30, Let the application continue to run for a while, making sure the DBVIP is being used by the ...


5

A replica server (whether running as hot_standby or not) is running in what PostgreSQL calls "recovery mode". As the name suggests, this was originally developed to perform recovery from an unsafe stop: in this situation, it uses the recovery.conf file which defines how to find transaction logs (pg_xlog files) and keeps applying them until no more are ...


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. ...


4

Clustering works very differently in 2012, and using your knowledge from SQL Server 2008 could be confusing you. Try the following from PowerShell (as admin): Import-Module failoverclusters Get-ClusterGroup If you see something like: Name OwnerNode State ---- --------- ---...


4

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server) From the reference : Change Data Capture: Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes to the ...


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