Data backup varies by the production scenario.

What you need to backup?

•SQL DataBase

•File system storage

•Full-text indexes

A different backup and restore procedure is required for each of above.

Considerations on the average disk write speed and the network throughput

•Local drive write speed: about 50-70 MB/sec.

•Disk Arrays: > 150 MB/sec

•SAN disk: > 300 MB/sec.

•Gbit throughput – theoretically about 120MB/sec, but in a real environment is limited by the disk s speed.

Considerations related to the system size

Small/Medium size: scenario 1 (not working 24/7)

In this scenario there is no production at night and there might be a no production days within a week.

•BACKUP:

1.SQL full backup runs every day and scheduled transaction log backups runs within production hours (hourly or more frequently).

2.Storage backup: the data on file system can be replicated with a script that does an incremental copy (only daily production) NOTE: the SQL transaction log backup and the storage incremental copy are ran at the same time in order to keep data syncronized.

3.Full-text indexes backup: the full-text engine can be configured to replicate realtime the indexes through Exalead master/slave capability.The index does a realtime replication while SQL and DATA uses a scheduled task. In case of failover the index will contains more recent data that the database. This will not create any problem in the full-text searches and the results will always be aligned to the database content.

•RESTORE

1.SQL: the restore is done by restoring the latest backup and applying all the transaction log backups.

2.Storage restore: for a quick restore the VOLUME can be changed to point to the back up server.Off peak production a full copy of the data can be performed from the backup server to the main server and the VOLUME can be restore to its original position.

3.Full-text indexes restore: See the Exalead DOC at http://tech.teradp.com/tech/download/exalead/doc/Exalead Guide.pdf

Very large database size and big storage: scenario 2/3

In this scenario there might be a production on 24/7.

Considering the disk and network speed we might have two different solutions.

•Backup a DB of 700GB

Run a backup on a network drive so that when the backup is over we don t have to transfer the file. The database size is 716,800 MB and the backup speed on the network may vary from 80MB/sec to 100MB/sec. This gives us a backup time of 8,960 sec through 7,168 sec (2 to 2,5 hours). Therefore, it is recommended to proceed with an SQL full backup every day and a scheduled transaction log backups to run hourly (or often) within production hours

•RESTORE

The restore produce is the same as the previous case

•Backup a DB of 2.5TB

The database size is 2,621,440 MB and it will require more than 7 hours to backup. This time might be too long to perform a full backup every day. In this case we do a full backup only once a week. Every day is performed an incremental backup with frequent scheduled transaction log backups within production hours. The advantage is that only a small portion of the database is backed up every day so that the daily backup time is very short.

Storage backup: even though the data on file system is big it can still be replicated with a script that does an incremental copy.Full-text indexes backup: the small/medium size procedure can be applied as well.

•RESTORE SQL: while the incremental backup is faster than the ordinary dump, the restore takes a bit longer. It s necessary to restore first the full backup, then all the incremental backup and last apply the tail of the transaction log. So while this method gives a faster backup time, it requires a longer downtime in case of failover. The restore of the storage and indexes is the same as in the Small/Medium size procedure.