This article help to
How SQL Server Always ON can help to achieve RPO and RTO? OR Achieve three in One Solution using SQL Server Always ON
How Always On in SQL Server works?
SQL Server Always On is one of the best high availability method started from SQL Server 2012. This can be used as high availability as well as Disaster recovery planning. From SQL 2014 you can add multiple replica in HA which can be used as read only secondary replica.
There are 2 modes available in Always ON.
- Synchronous Commit : In this mode wherever transaction occurs on primary it has to be committed on secondary first and then commit will be completed on primary database. This support automatic failover and minimize the data loss.
- Asynchronous Commit : In this mode transaction get committed on primary node without waiting response from secondary replica. This only support manual failover and there is chance of data loss.
Both of these mode have its own advantages and disadvantages.
How SQL Server Always ON can help to achieve RPO and RTO? OR Achieve three in One Solution using SQL Server Always ON
Unlike other SQL server HA and DR technique , Always ON has all the three advantages which can be achieved in one setup.
Four node Always ON setup is best HA and DR setup if you have all of three requirement. In this setup we will have
- Primary : Synchronous Commit(Automatic Failover)
- Secondary : Synchronous Commit(Automatic Failover)
- Secondary : Asynchronous Commit(Manual Failover) : This is for reporting queries.
- DR: Asynchronous Commit(Manual Failover) : Different Region
RPO and RTO are the most important parameter when we run application. Always ON support to achieve this with the help of synchronous commit with automatic failover mode.
Sometime there is also requirement to load balance the reporting query for better performance on primary database server. In that can we can setup another replica in same AG setup. This replica will have asynchronous commit mode. We can create SQL users and grant read only access on this server to run reporting queries. This will improve the performance on primary server.
For Disaster recovery , you can also use same setup where node will be in different region. This will help in case of any regional issue. You can periodically perform DR test to verify the connectivity.
If you setup Server like you can achieve all 3 requirement in one setup.