We have two SQL 2005 machines. One is used for production data, and the other is used for running queries/reports. Every night, the production machine dumps (backups) it's database to disk, and the other one restores it. This is called the D-1 process.

I think there must be a more efficient way of doing this, since SQL 2005 has many forms of replication. Some requirements:

1) No need for instant replication, there can be (some) delay

2) All changes (including schemas, data, constraints, indexes) need to be replicated without manual intervention

3) It is used for a single database only

4) There is a third server available if needed

5) There is high bandwidth (gigabit ethernet) available between the servers

6) There isn't a shared storage (SAN) available

What would be a good alternative to this daily backup/restore routine? Thanks!


The best alternative is log shipping. Log shipping is also based on backup/restore, but after an initial seed of the full database backup, the reporting site is maintained by applying the log backups from the main site. Log shipping is good because:

  • has no impact on the main site
  • has support in the SQL Management tool set for deploying, monitoring and administration
  • the data transferred is minimal, only the database log backup which contains only changes occurred since last shipment.
  • data delay is the relatively small: interval between logs + time to copy the log file and restore it.

The cons is that the reporting site is disrupted each time log is restored, all users are kicked out during the restore.

So if you have a typical 30 minute log backup interval then the reporting site is always up to 30 + X minutes (X being the time needed to copy the file and restore it, usually quite small), and users are disconnected every 30 minutes for a short time.

Another alternative is database mirroring. With DBM the reporting site is kept up to date constantly, but the downside is that the mirror database is offline. Reports must be run from a database snapshot that is updated periodically. Unlike log shipping, DBM also impacts the principal site. The big advantage of DBM solution for offsite reports is that once deployed it can serve as a high availability/disaster recoverability solution too.

Some use transactional replication too, but I'm not a big fan of that technology. While easy enough to deploy, it is slow on high load and it has a tendency to run into problems that are rather difficult to troubleshoot and diagnose. Besides, replication does not copy exactly a database, but instead maintains copies of published articles in the distribution database (ie. selected tables and indexes) and schema modifications require careful planning and deployment. With log shipping and mirroring database schema changes just get replicated w/o any problem.

  • Thanks for the answer. I'll look into log shipping, even though we have some heavy reports that run for longer than an hour and our main-DB transaction log backups are every 30-minutes. Users are already kicked out of the server in the manual D-1 process we run every night. Mar 27 '10 at 18:26
  • You can use database snapshots for long running reports. Create a snapshot, start the report on the snapshot, it can run as long as it wants to. When is done, cache the report then drop the snapshot. Log shipping can be applied in the meantime w/o interrupting the report. Mar 27 '10 at 20:43
  • Thanks Remus, but I think I can't use snapshots since I'm on SQL 2005 Standard. I think I'll actually try to implement roll-my-own "log shipping": just like the wizard does, but with extra intelligence, for example: before restoring a log backup, the secondary evaluates the number of users connected and what they're doing; if the secondary is being (heavily) used, it would delay log restore until a 'timeout' or until users go away. Maybe it's a little insane, but we have build more complicated solutions before. Thanks for all the help! Mar 28 '10 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.