snippetsqlMinor
How can I minimize SQL Server data loss in AWS in the event of failure?
Viewed 0 times
minimizecanlossthesqlawshowserverdatafailure
Problem
If I am running a SQL Server in AWS. What is a low cost solution to minimize my risk of data loss in the event of failure at the database level?
Lets assume I am performing backups every 6 hours so worst case I can have my servers back up in a couple hours but depending of the time of the failure I could lose 6 hours of data.
Lets assume I am ok with the 2 hours to bring the servers back up but I am not ok with 6 hours of data loss.
Is there a way to narrow my dataloss gap to around 15 mins or less without getting into replication etc.
I am open to any way to host SQL Server in AWS
Lets assume I am performing backups every 6 hours so worst case I can have my servers back up in a couple hours but depending of the time of the failure I could lose 6 hours of data.
Lets assume I am ok with the 2 hours to bring the servers back up but I am not ok with 6 hours of data loss.
Is there a way to narrow my dataloss gap to around 15 mins or less without getting into replication etc.
I am open to any way to host SQL Server in AWS
Solution
Additional to your Full backups you can take transaction log backups every 15 minutes. A transaction log backup contains a list of the changes that happened. During the restore those changes get re-applied.
In the case of a disaster you would restore the newest full backup and then all of the log backups that were taken since that full backup in chronological order. With that you would have a maximum of 15 minutes of data loss. The additional storage is dependent on the amount of changes in your system. Usually it is significantly smaller than another full backup.
You could also add differential backups into the mix: Full backup every 6 hours, differential every 2 hours, log backup every 15 minutes. Most likely that wont safe storage, but it could make the restore a lot faster, depending on your situation of course.
No matter which solution you end up taking, make sure you practice the restore regularly, as it is not completely trivial and a disaster is not the right time to try to figure out how it works.
In the case of a disaster you would restore the newest full backup and then all of the log backups that were taken since that full backup in chronological order. With that you would have a maximum of 15 minutes of data loss. The additional storage is dependent on the amount of changes in your system. Usually it is significantly smaller than another full backup.
You could also add differential backups into the mix: Full backup every 6 hours, differential every 2 hours, log backup every 15 minutes. Most likely that wont safe storage, but it could make the restore a lot faster, depending on your situation of course.
No matter which solution you end up taking, make sure you practice the restore regularly, as it is not completely trivial and a disaster is not the right time to try to figure out how it works.
Context
StackExchange Database Administrators Q#41778, answer score: 3
Revisions (0)
No revisions yet.