Replication strategy

Hello,
I have a SQL 2000 database which stores details of consumers input via
our website.
I am currently trying to configure some off-site disaster recovery,
which includes synchronising our database (including tables, data and
stored procedures) to another server.
We are limited on bandwith at the moment due to our office locations,
so ideally I need to keep the data transfers to a minimum. I am happy
for the transfer to take place overnight.
My question is, which replication model should I choose. I first
thought that Snapshot would be best, but the only problem is that the
configuration seems to indicate that it would drop and re-create each
table on the desitination server. This obvisously isn't very scalable,
as more data in the database will mean that the transfer wil take
progressively longer.
Is there a better way to do it? I guess the ideal way would be to only
copy the changes made, each night. Can I do this with transactional
replication?
Cheers,
Andrew
Yes - transactional replication will have the least impact on your limited
bandwidth, so toher things considered I'd choose this. I have an article on
replication times on www.replicationanswers.com that you might find helpful.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
Will the storing of a whole day's transactions have any performance
implications on my SQL server?
|||Typically no, but if you find by doing some monitoring that the production
server is struggling, you can use a remote distributor. Before going to this
step, you can use pull subscriptions which'll offload some of the work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the advice. I currently have the distribution on the
production server and am using push subscriptions, in order to make it
easier to administer the whole thing.
I will try your suggestions out if we run into problems.
Many thanks