Unanswered: Replicating a database onto 2 other Servers
The current situation:
We have a 20gb database, running on AIX, derived fom a CICS/VSAM host system and intended to provide R/O access for queries and decision support.
This database is maintained by a home-grown production system, executed once per day, 7 days a week. The system comprises
1) stored procedures performing incremental adds, updates, deletes, based on records extracted from the host system;
2) fast BCP and "select into's" either performing full loads or servicing derived tables not carried on host system.
The option "Truncate log at checkpoint" is set at the database level.
After each production cycle the database is backed up via DTS.
Now we have been asked to:
1) increase the number of production cycles from 1 per day to 5;
2) after each of these 5 production cycles, copy the updated database onto 2 other SQL Servers running on separate AIX hosts.
This puts us into uncharted territory. Our prelininary thoughts on how to accomplish this:
1) After each post-production backup, use DTS recover to reproduce the updated databases onto the each of the other servers. This would be time-consuming and would interrupt access to those other servers.
2)Make use of the transaction log and then dump and recover from this log after each production cycle.
This would require extensive review and revison of a complex and lengthy set of production functions which currently have no need for, and do not use, the transaction log.
3) Acquire and learn Replication Server. We have no experience of this product and do not know whether the same kind of review and revision of the production system would have to be undertake.
Any and all suggestions, explanations, ideas would be much appreciated.
We use replication server here in a DSS environment. We do incremental adds,fast bcp and such and it works good. I don't think we use "select into" so you might have to verify how that gets replicated.
The latest version of Replication Server (12.6) has the capability to replicate multiple sites at the database level.
Create connections to all participating db's
Make a database replication definition for the primary
Make database subscriptions for each replcate
dump and load to the 2 replicates
set a few configuration parms and your done.
There are certain gotcha's of course. Especially for replicating DDL but overall the product performs well.