52

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


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


10

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


9

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server. From MSDN: "Database mirroring is a primarily software solution for increasing database availability. Mirroring is ...


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.


8

Check out repmrg: repmgr is a set of open source tools that helps DBAs and System administrators manage a cluster of PostgreSQL databases.. By taking advantage of the Hot Standby capability introduced in PostgreSQL 9, repmgr greatly simplifies the process of setting up and managing database with high availability and scalability requirements. ...


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


6

For a failover-type situation, I use the handy 'sp_help_revlogin' stored procedure as published in http://support.microsoft.com/kb/918992 This procedure, when run on your primary server, will script out each login WITH the SID (this is the important part for making sure your logins are mapped to their respective database users) and hashed password (so the ...


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

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


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

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

If you are interested in synchronous DB Replication and Failover, I have a suggestion. You could look into using two tools: DRBD (Disk Replicated Block Device) provides Disk Level Replication (synchronous) between disks on two different servers. The disk changes are replicated over the network. It is best to use a crossover cable on eth2 for the NICs using ...


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


5

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


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


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