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
Showing posts with label off-site. Show all posts
Showing posts with label off-site. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
replication question
What is the thought on replicating over WAN links.
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
Wednesday, March 21, 2012
Replication Problems
We are thinking to replicate a 'READ ONLY' database to an
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so that it doesn't conflict with the replication. Why are you using a third-party replication tool?
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so that it doesn't conflict with the replication. Why are you using a third-party replication tool?
Subscribe to:
Posts (Atom)