- What are adverse affects to using replication
- What are some examples of when replication is beneficial
To add some more about transactional replication:
Just a few thoughts. You might also checkout the whitepaper on mirroring + repl I wrote last year at http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/ReplicationAndDBM.docx
Edit: ok - it's lunchtime and I have some more to add:
When choosing any kind of HA solution (good timing as I'm teaching an HA class for internal Microsoft DBAs today), you need to start with requirements analysis before evaluating technologies. It's a bit hard to give recommendations without knowing all your requirements.
I blogged about questions to ask yourself when coming up with an HA strategy: see http://www.sqlskills.com/BLOGS/PAUL/post/HA-Where-do-you-start-when-choosing-a-high-availability-solution.aspx
ok - no more edits! :-)
Replication is quite a diverse technology and can be used to cater for a number of different scenarios, the choice of which will determine the specific type of replication that is implemented.
For example, merge replication can be used to support distributed processing by spreading the workload of an application across several servers, i.e. distributed processing architectures.
Merge replication often requires an application that is relatively aware of its environment. Techniques such as conflict resolution also have to be taken into consideration in order to ensure data consistency across the entire integrated environment.
Transactional Replication can be used in a similar fashion to log shipping however you can limit the specific objects that are replicated to the subscriber. This can be useful if only a subset of tables is required for reporting purposes.
For a full listing of the available architectures see the following Microsoft Replication reference.
The flavour of replication you use will determine the type of issues you may encounter and need to consider. For example, Merge Replication requires that Schema changes be made to your database.
There are also security considerations to be taken into account, for example if you data is to be replicated over the public internet or whether or will need to encrypt communications etc.
Replication is a big topic but I hope that this information sets you off in the right direction.
If you have to shut off replication for any reason and restart it (or, just starting the first time) it tends to cause BOTH servers to become nearly non-responsive while in process. Once up and running (and left alone), it's not noticeable though (at least not for us).