I am trying to figure out what would work better for our environment. We have a single transactional database which we are about to split into OLTP and OLAP. Reporting database will stay in the same data center. We also need a replica of the transactional database in the corporate office. It is very unlikely that either replica of the database would have more than few updates per hour to send back to the transactional database (might be actually 0 updates). Transactional database is Enterprise Edition 2000, runs on dual CPU system with RAID-1.
Reporting db in the data center will have to be synced with the transactional database every 5-10 minutes. Replica in the office - every several hours, up to 24 hours. Office is connected to the corporate office with high speed link (T-1 now, might turn into DSL at some point).
Our load is about 10 tx/sec at peak and is expected to double every 6-10 months in the next couple years.
So, here are my questions:
1. What are the pros and cons of log shipping vs replication?
2. Would it make sense to start out with the reporting database on the same physical server as transactional database (to save on extra hardware and license) until we can afford to add a new server? Would replication and log shipping work for the db on the same machine?
3. Can we implement same replication/log shipping technology for both reporting and office databases?
4. We planning on implementing clustering within a year. Which option would be better with clustering?