Using replication for redundancy

  • We have stepped up the criticality level on one of our

    production db's (actually it is 4 db's) so I now have to

    setup replication and maintain a backup server.

    What would be the best type of replication to use?

    Are there any good replication resources (books etc..)

    available that might help?



  • I'd recommend transactional for this type set up, though you may want to look at log shipping as an alternative. I like this book, although there is not a huge amount of replication coverage:

    Definitely put up two test machines and practice before you go live!


  • Thanks Andy.

    2 more questions.

    Does SQL Server 7 support log shipping? If so, does it work?

    In a nutshell what are the pros and cons?

    I guess that's 3 questions...sorry.

  • How much latency are you willing to accept? Will the replicated databases be serving as backup only (no reporting or anything else)?

  • Sean - 1/2 hour latency is the spec right now.

    The primary purpose is failover but I wouldn't want to rule out reporting or other read only access.

    Thanks again.

  • Without knowing many more details, Transactional is probably your best bet, easiest with low-headache. How much data will you be moving within a 1/2 hour?

  • not much... normally under 10 meg. There are data feeds throughout the day that may be a little larger.

  • If you've got a stable connection to your backup server, then transactional should be your best alternative. BOL and the MSDN site have some great white papers and other resources to get you up to speed on replication quickly.

    Good luck!


  • Log shipping is supported using a set of sp's in the BORK. The theory is pretty simple. Every x minutes run a log backup, copy to the other server, restore it. Log shipping is just automating that. You can do it in SQL2K only with the Enterprise Edition (using built in features) but I bet the code from the 7.0 BORK would still work.

    Its not that hard to manage. Downside is that the backup server has to stay in read only mode.

    I'd suggest if you have time you try both, then decide. Both have merits. I use transactional to move stuff to a reporting server, works well - cant you log shipping because they do writes to the reporting server AND we filter what gets replicated.


  • Thanks for the help guys... I will try both.

  • My 2 cents:

    Use Log shipping. Both methods will do the job, but when you have a failure and have to move back to the primary, replication will be more problematic.

    Log shipping is easier to reverse or turn off.

    Not sure if you can do it with system sprocs in v7, but it could easily be scripted using DTS.

    Steve Jones

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply