Advice on which replication method to use for our requirements/hopes
Posted on 2008-06-17
- 2 instances of SQL 2005 Standard connected to each other over a secure 1Mb WAN link
- Instance "A" is production now and has been for a few months
- Instance "B" is fresh and has no databases on it yet
- Current (full and incremental) backups of A already exist on the server for Instance "B" (roughtly 7GB) and are being kept current by the hour
- DB in question has 2 schemas which are tightly related, but still distinctly different and for seperate apps
- Identity columns are used heavily in both Schemas
- High volume (potentially BULK), but small (< 1k per row) inserts and updates WILL frequently occur at instance "A" for schema "1". Low volume (and never at the same time) updates should be possible at instance "B" for schema "1".
- Moderate volume inserts/updates should be possible at instance "B" for schema "2" and will rarely occur at instance "A", but should be possible.
With the "given" facts, and stated "goals/variables", which replication method makes the most sense? Any additional factors, steps, considerations, directions provided would be greately appriciated. I feel capable of setting up any replication scenario, but I'm not confident in my ability to determine which would be the most appropriate.
Also, if our requiremets don't appear feasable for SQL 2005, but would be with SQL 2008, feel free to enlighten me. Upgrading isn't out of the question and this replication scenario can be put on hold if there's just cause.