Home / server / Questions / 126798
Accepted
Ricardo Pardini
Asked: 2010-03-27 15:44:29 +0800 CST

Simple SQL Server 2005 Replication - "D-1" server used for heavy queries/reports

  • 772

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!

sql-server sql-server-2005 replication query

1 Answers

  1. Best Answer

    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.

    • 1