Is it normal to lose 30 minutes of data on a SQL Server crash?

Question

We have worked with Oracle for a number of years and we now need to develop a new application using SQL Server in AWS for the first time.

What surprised us, is that the new SQL Server DBA told us off the bat that SQL Server uses some kind of replication every 30 minutes (or with 30 minutes delay, I don't remember): in short, he said that on an AWS SQL Server crash, when the secondary server comes up "we will lose only 30 minutes of data in Production".

EDIT: By "crash", we mean the primary server is dead/unrecoverable.

We never expected this as a normal behavior and we haven't seen something like this in Oracle... ever.

Is it normal to expect to lose 30 minutes of commits on a SQL Server crash? This would include payments, invoices, and other transactions that we would consider quite important.

Should I push back about this, or this is considered normal in SQL Server?

Answer 1

Yes, if that's you pay for or have setup.

For Oracle, I'm pretty sure that that you did not ask. Clients always assume RTO and RPO are 0. When you explain what RTO and RPO, they request 0 for RTO and PRO. Then when you explain the complexity and cost, the client is likely okay okay with 24 hours while we do our best for our default 15 minutes. Take a look at

https://www.brentozar.com/archive/2017/10/sql-server-architecture-review/

I love Brent's response for one of the questions:

"For Infrastructure-as-a-Service, there are no changes. For Platform-as-a-Service, there’s no chart – just put in your credit card and turn the knob to the level of protection that you want. ?"

So, what's in your wallet? ?

Here is one definition of RTO and RPO, from the AWS Well-Architected documentation:

Recovery Time Objective (RTO) is ...the maximum acceptable delay between the interruption of service and restoration of service. This determines what is considered an acceptable time window when service is unavailable.

Recovery Point Objective (RPO) is ... the maximum acceptable amount of time since the last data recovery point. This determines what is considered an acceptable loss of data between the last recovery point and the interruption of service.

The architecture or features determine what RPO and RTO are possible. For a platform as a service, you do not define the implementation. If you pay for near zero RTO and RPO, then I'd expect something more than a single instance for HA and backup/restore for DR.

The following link might help explain some of this. It's a complex subject. The complexity can be a risk for RTO and RPO. For example, our first SQL cluster did not failover correctly because some resources where not configured correctly as cluster resources...we lost a 9 for that. I like this link because of the image that shows the feature relation to RTO and RPO - copied here.

https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/an-overview-of-high-availability-and-disaster-recovery-solutions/ba-p/370479

The following link indicates the Azure instance or database has a 12h and 1h RTO and RPO, respectively, using geo restore. You can use auto failover to improve this to 1h and 5s. It's all very vendor specific.

https://docs.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview?view=azuresql

This link shows that paying more allows the use of features and resources that provide a better RTO and RPO. I would expect "Hyperscale service tier zone redundant availability" to drain the wallet.

https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla?view=azuresql&tabs=azure-powershell

If doing it yourself, the feature selection is not enough. Having an old SAN go down for 3 days (yes) can be a real show stopper, no matter what features are used.