We are using SQL Server 2000 Standard Edition on Windows 2000. If we want to set up a DR site, we have 2 options:
a. transactional replication
b. log shipping
However since we do not have Enterprise Edition, maybe the best option we have is transactional replication.
My problem with transactional replication is when we set it up, we do not seem to have control over initial snapshot. When the database is huge, we prefer to manually take the snapshot and restore it at DR site, is it possible ?
Originally posted by joejcheng
Yes, you have a choice between initializing the database to subscriber or not. If you say no, then sql would assume you already have the same schema and data in the subscriber.
Thank you. The manual does not seem to have step-by-step guidelines on how to manually send the snapshot over to subscriber server before transactional replication starts continous replication.
Btw, I come across a lot of people discouraging people using transactional replication as DR method.
Is it true that transactional replication is very taxing to the production server ?
Is custom log shipping worth trying (if we are using standard edition) ?
Is third-party software eg DoubleTake would be much better in our context ?
The other way around that is to disable the snapshot right after you setup the replication. You can disable it in the Replication Monitor or you can disable it as a scheduled task.
Transactional Replication can be very slow if you have large batches coming accross the network. But it provides you more concurrent processing than Log Shipping. Remember, Log Shipping is simply a backup/restore process, and every time you restore the database you would have disallow or kill all the user access. Just imagine this is done hourly, the users will be interrupted every hour. On the other hand, Log Shipping is easier to manage. And if you can restore only once a day in the evening, that could be your choice.