We are looking for a M$ SQL expert to outline a best practice backup/redundancy strategy which does not require new hardware, or use of built in replication. We have two M$ SQL servers (2000 enterprise) and need to implement a better backup strategy than we have currently. Current setup: Sql1 ??" Production SQL server w/ ~85 gig of data in ~50+ databases Sql2- Non-production SQL server w/ 24 hour old data from SQL 1 Current Backup Process: SQL1 is backed up to a network share (staging server) nightly. Full backups are used as all databases are currently set to simple logging. Backup files from the staging server are written to tape nightly. SQL2 runs a script that restores the backups from the staging server nightly. This makes SQL2 an up to 24 hour old source of the data. Current disaster recovery, switch to using SQL2 should SQL1 fail, and accept up to 24 hours of data-loss. Goal: Due to the volume of data (currently >85gig) doing full backups nightly is less than ideal. Especially since 99% of that data is not new. We would like close to real-time syncing of data between sql1 and sql2. We can not use the built in replication, as it adds a large column to our normalized data tables. We would like to look into log shipping, or other options, between sql1 and sql2. We CAN turn on full transaction logging as needed. We would like to be able to have sql1 and sql2 sync’d as close to real-time as possible
1) Discuss and approve a backup/redundancy method to implement 2) Configure sql 1 and sql2 according to outlined plan 3) Test and debug the configuration Note: configuration will need to be done off-hours (9pm-12pm MST)
servers are windows 2000 advanded server, windows 2000 server, or windows server 2003. SQL is SQL server standard 2000 or SQL server 2000 enterprise.