Home / server / Questions / 361757
Accepted
user1034912
Asked: 2012-02-20 21:20:01 +0800 CST

How frequent can we do SQL transaction log backups?

  • 772

I'm installing a highly critical system. If the system fails, the users wants data up to the last minute.

To achieve this, I was thinking of running Transaction Log Backups + FTP every minute. Has anybody done this before? Is this the best way to achieve the 1-minute data loss tolerance?

Appreciate any advice,

Thanks!

backup logging sql

3 Answers

  1. Best Answer

    Well, you can do frequent tx log, but it still looses data.

    Best approach is:

    • 3 database servers, one small free edition as witness, using safe mirroring.

    2 copies of the database, kept in sync.

    Then use DPM (Data Protection Manager) to make near real time backups of that.

    Particularly target an ugprade to SQ LServer 2012 asap and mirror the database files on both servers (total: 4 copies of the database), preferably on separate casings / raid controllers.

    Yes, that gets expensive -but then "highly critical" means "pay what it takes".

    And you do not need FTP - SQL Server Log File Shipping can ship out the logs itself via file share.

    • 2

  2. At that frequency, you'd probably be better off using database mirroring.

    • 0

  3. For deploying High Availability solutions, I always recommend the use of HP Storage mirroring software. http://h18006.www1.hp.com/products/storage/software/sm/

    • 0