amazon web services - AWS RDS SQL Server failover really slow

Question

We recently resized one of our Multi-AZ RDS SQL Server instances. With the goal of aligning our resource needs with instance capabilities... In summary we were cruising along on a bigger instance then we needed and saw the obvious cost savings. Our business is seasonal and we expect this could need to be up-sized later in the year.

According to AWS the failover process typically takes around 60-120 seconds. In this window we knew our application would not be able to communicate with the database. Realistically we were expecting less than 5 minutes, however the actual time was around 20 minutes before our applications were able to connect and query the databases. This is in-line with the time AWS said the failover had completed.

I am interested to know if anyone else has had similar experiences? We you able to change anything in your setup to improve the failover time? Are the factors under our control which can reduce the failover time.

Further details: We have approximately 25 databases hosted on this instance. We had been on an db.m4.2xlarge instance

question from:https://stackoverflow.com/questions/65941486/aws-rds-sql-server-failover-really-slow

Answer 1

Not an expert on SQL-Server at all, so take this with a grain of salt.

RDS for SQL Server works different from the other RDS database engines and the article you linked to is very generic. The SQL Server uses either Always-On-Availability groups or Database Mirroring depending on your setup, so this could be a factor that influences the failover time. The documentation for RDS SQL Server Multi-AZ setups is quite extensive, so you might want to review that.

The only hint mentioned there is:

• Failover times are affected by the time it takes to complete the recovery process. Large transactions increase the failover time.

If you have AWS support in your account, I'd ask them for the details, they have more insight into the underlying issues and should be able to tell you more - it's a managed service after all and if it doesn't deliver on the typically 60-120 second failover times, I'd ask the service provider what went wrong - doesn't mean it's necessarily their fault, but they can at least point you to the root cause :-)