February 15, 2012 at 5:21 pm
To all the seasoned guys and gals out there. What would be the ideal backup strategy? Fail-over clustering or nightly backups with log file backups every five minutes, for a business with 4 locations wanting to achieve minimal downtime. What are your thoughts and opinions on both methods.
February 15, 2012 at 9:08 pm
It all depends on business needs. Normally 24/7 environment will have HA solutions as well as DR planned.
Fail over clustering gives you High availablity of your database. It does not gurantee the Disaster recovery.
For disaster Recovery shortly DR you can try any of the following options.
1) Log shipping.
2) Database mirroring.
3) Replication.
Each option has its own merit and demerits.
Let us know if you need to more about the options availabe.
--- Babu
February 15, 2012 at 9:34 pm
February 16, 2012 at 1:59 am
newkidd (2/15/2012)
What would be the ideal backup strategy? Fail-over clustering or nightly backups with log file backups every five minutes, for a business with 4 locations wanting to achieve minimal downtime.
Clustering is not a backup strategy. It's a high-availability offering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2012 at 6:05 am
Just remember that backups don't help down time. If you want to improve the recovery time in the event of an outage, in addition to the backups you have, you might want to toss in a differential backup so that you're applying fewer log backups.
And, piling on with Gail, failover is not a backup.
If you want additional high availability options, look to mirroring the important databases. This gives you a good (better in my opinion) option for HA without all the shared resources required for a cluster.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2012 at 12:10 am
Any reason why you chose the interval of 5 minutes for transaction log backups? You may not need to back it up this often depending on the number of transactions you have.
Whatever strategy you choose, test it regularly. Perform regular failovers of your cluster and always test your backups. You should also test your point-in-time restores. This is where you'll find out why having too many tx log backups can be a bit of a pain.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply