Which mode should I select when performing an SQL failover cluster?

If we want to ensure high availability of SQL Server, we can adopt a Failover cluster. The simplest failover cluster is two servers, one active server and the other standby server. This is the cluster in AP mode. Another mode is the AA mode, that is, both servers are running SQL Server instances.

SQL Server does not have RAC like Oracle, so it is impossible to say that the two servers run the same instance at the same time. If you want to use both servers, then only one instance runs separately.

So when we want to create a cluster in the enterprise, should we do the AP mode or the AA mode?

In AP mode, a database instance is installed on two servers. Only one server is responsible for all operations and operations on the database instance, and the other server is idle. When the Active Server fails, the system automatically starts the instance of another server to implement failover. The biggest disadvantage of AP mode is that the resource utilization is low, and only one server is in use.

In AA mode, two database instances are installed on two servers, and each server runs one database instance separately. When a server fails, the system switches the database instance on the faulty server to another server, that is, the other server runs two instances simultaneously, after the server returns to normal, manually switch a database instance back to another server. The AA mode ensures that the resources of both servers are used.

The differences between two databases in one database instance and one Database in two database instances:

1. two databases in one database instance must run on the same server. Therefore, if both databases use a large amount of computing resources, the pressure on the database server may be high, the operation is slow, while one database in two instances can deploy each instance on different servers and use the resources of each server without affecting each other.

2. the access between two databases in one instance is relatively simple, and transaction operations can be performed on the two databases; the databases in the two instances need to access each other by connecting to the server or other methods. To perform transaction operations in the two instances, You need to enable the Distributed Transaction MSDTC, the use of distributed transactions will lead to a longer transaction time, and errors may be even greater, or even various unexpected errors. Therefore, distributed transactions are generally not recommended.

Therefore, if we have enough resources (that is, a lot of money) and want to make it simpler, we will adopt the AP mode. After all, the AA mode is more complex and difficult to manage than the AP mode. If we want to make full use of the server resources, the server is limited, and there are no distributed transactions between multiple databases, then using the AA mode is a good choice.

From previous experiences, it is best not to use distributed transactions, and you may encounter various flexibility problems.