Simple SQL Server 2005 Replication - "D-1" server used for heavy queries/reports
- by Ricardo Pardini
Hello. 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!
