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 input. Show all posts
Showing posts with label input. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Replication scenario. Heavy usage and limited bandwidth
Hi all
I'm evaluating a potential replication scenario and I'd like some input from
the experts if possible.
I've got a 150GB database hosted at my headoffice that I need to replicate
to 4 regional offices. The regional offices are connected to the head office
by a WAN and I've been told that the available bandwidth is 512k
I would imagne that I would need to transfer the initial snapshot to the
subscribers by tape or the like, as I can't see that volume of data going
across the WAN in anything close to a reasonable amount of time.
I examined my transactional log backups to try ad get an idea of the amount
of data that would be replicated. The transaction log is backed up every 5
minutes and the log backups range from 5MB to 200MB
My concern is first, will the amount of replicated transactions flood the
512k line, and second, what kind of delay would I be looking at for
transactions to reach the regional servers.
Thanks very much
Gail Shaw (MCSD)
http://gail.rucus.net/
I think you will find that the 200Mg log backup probably occurred when you
were reindexing. I think you will find that your average log dumps will be
smaller, but this depends on load and frequency of dumps.
The replication commands should not consume all available bandwidth, but
again this depends on your load.
The delay is a function of availability, throughput and polling interval.
But just to through something out there, on some ISDN lines between NJ and
Guam we were hitting 1 minute.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GilaMonster" <gshaw [AT] sentechsa [DOT] com> wrote in message
news:EEA8A148-5685-43A8-9E38-A7237C845495@.microsoft.com...
> Hi all
> I'm evaluating a potential replication scenario and I'd like some input
from
> the experts if possible.
> I've got a 150GB database hosted at my headoffice that I need to replicate
> to 4 regional offices. The regional offices are connected to the head
office
> by a WAN and I've been told that the available bandwidth is 512k
> I would imagne that I would need to transfer the initial snapshot to the
> subscribers by tape or the like, as I can't see that volume of data going
> across the WAN in anything close to a reasonable amount of time.
> I examined my transactional log backups to try ad get an idea of the
amount
> of data that would be replicated. The transaction log is backed up every 5
> minutes and the log backups range from 5MB to 200MB
> My concern is first, will the amount of replicated transactions flood the
> 512k line, and second, what kind of delay would I be looking at for
> transactions to reach the regional servers.
> Thanks very much
> Gail Shaw (MCSD)
> http://gail.rucus.net/
|||"Hilary Cotter" wrote:
> I think you will find that the 200Mg log backup probably occurred when you
> were reindexing. I think you will find that your average log dumps will be
> smaller, but this depends on load and frequency of dumps.
The 200MB is after the completion of some of the nightly data importation
jobs. During the day the backups are around 5-10 MB, log backed up every 5
minutes. At night, during heavy data import, the logs range between 20MB and
200MB. Reindexing is only done on sundays, as it's the only downtime we have.
> The replication commands should not consume all available bandwidth, but
> again this depends on your load.
Any reliable way of measuring the load? I thought that the log size would
provide a good estimate. According to a monitoring tool I have access to, the
transactions/minute max out at 2000 during the afternoon, but that should be
mostly reads.
> The delay is a function of availability, throughput and polling interval.
> But just to through something out there, on some ISDN lines between NJ and
> Guam we were hitting 1 minute.
Doesn't sound too bad. What's the bandwidth on those lines and what kind of
load did you have?
One of my main worries is what happens if the subscribers get out of sync. I
can't just reinitialise the subscriptions and nosync initialisations look
like a fair bit of trouble
> --
> Hilary Cotter
sql
I'm evaluating a potential replication scenario and I'd like some input from
the experts if possible.
I've got a 150GB database hosted at my headoffice that I need to replicate
to 4 regional offices. The regional offices are connected to the head office
by a WAN and I've been told that the available bandwidth is 512k
I would imagne that I would need to transfer the initial snapshot to the
subscribers by tape or the like, as I can't see that volume of data going
across the WAN in anything close to a reasonable amount of time.
I examined my transactional log backups to try ad get an idea of the amount
of data that would be replicated. The transaction log is backed up every 5
minutes and the log backups range from 5MB to 200MB
My concern is first, will the amount of replicated transactions flood the
512k line, and second, what kind of delay would I be looking at for
transactions to reach the regional servers.
Thanks very much
Gail Shaw (MCSD)
http://gail.rucus.net/
I think you will find that the 200Mg log backup probably occurred when you
were reindexing. I think you will find that your average log dumps will be
smaller, but this depends on load and frequency of dumps.
The replication commands should not consume all available bandwidth, but
again this depends on your load.
The delay is a function of availability, throughput and polling interval.
But just to through something out there, on some ISDN lines between NJ and
Guam we were hitting 1 minute.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GilaMonster" <gshaw [AT] sentechsa [DOT] com> wrote in message
news:EEA8A148-5685-43A8-9E38-A7237C845495@.microsoft.com...
> Hi all
> I'm evaluating a potential replication scenario and I'd like some input
from
> the experts if possible.
> I've got a 150GB database hosted at my headoffice that I need to replicate
> to 4 regional offices. The regional offices are connected to the head
office
> by a WAN and I've been told that the available bandwidth is 512k
> I would imagne that I would need to transfer the initial snapshot to the
> subscribers by tape or the like, as I can't see that volume of data going
> across the WAN in anything close to a reasonable amount of time.
> I examined my transactional log backups to try ad get an idea of the
amount
> of data that would be replicated. The transaction log is backed up every 5
> minutes and the log backups range from 5MB to 200MB
> My concern is first, will the amount of replicated transactions flood the
> 512k line, and second, what kind of delay would I be looking at for
> transactions to reach the regional servers.
> Thanks very much
> Gail Shaw (MCSD)
> http://gail.rucus.net/
|||"Hilary Cotter" wrote:
> I think you will find that the 200Mg log backup probably occurred when you
> were reindexing. I think you will find that your average log dumps will be
> smaller, but this depends on load and frequency of dumps.
The 200MB is after the completion of some of the nightly data importation
jobs. During the day the backups are around 5-10 MB, log backed up every 5
minutes. At night, during heavy data import, the logs range between 20MB and
200MB. Reindexing is only done on sundays, as it's the only downtime we have.
> The replication commands should not consume all available bandwidth, but
> again this depends on your load.
Any reliable way of measuring the load? I thought that the log size would
provide a good estimate. According to a monitoring tool I have access to, the
transactions/minute max out at 2000 during the afternoon, but that should be
mostly reads.
> The delay is a function of availability, throughput and polling interval.
> But just to through something out there, on some ISDN lines between NJ and
> Guam we were hitting 1 minute.
Doesn't sound too bad. What's the bandwidth on those lines and what kind of
load did you have?
One of my main worries is what happens if the subscribers get out of sync. I
can't just reinitialise the subscriptions and nosync initialisations look
like a fair bit of trouble
> --
> Hilary Cotter
sql
Tuesday, February 21, 2012
Replication internals...
Hi,
I need to answer a question for management and I have little replication
knowledge. I'd appreciate any input here.
What happens to the dbs on either side i.e. dist and subscriber, when in the
middle of the operation, one of the systems crashes? vs. what happens when
you click the stop sync button? How do you recover both in the event of the
former assuming the stop synch does not separate, but controls, parent from
children records.
Thanks much!
replication stored the last recorded transaction in the meta data tables on
the subscriber and the publisher\distributor.
When the system comes back up the data will be replayed to the Subscriber
provided it has not being cleaned up on the publisher\distributor.
Transactions are applied on the subscriber within a transactional context.
If you are replicating data which is part of a pk-fk relationship these
commands will be replicated as past of a transaction - unless you are not
enforcing the relationship for replication.
So it is very much like the ACID properties you see when you apply the
transaction on the publisher.
If the distribution database is corrupted its all bets off, as it holds the
transactions to be applied on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JimMac" <JimMac@.discussions.microsoft.com> wrote in message
news:5BF14F6D-AB6E-4CB6-AC8E-125F43FFE652@.microsoft.com...
> Hi,
> I need to answer a question for management and I have little replication
> knowledge. I'd appreciate any input here.
> What happens to the dbs on either side i.e. dist and subscriber, when in
the
> middle of the operation, one of the systems crashes? vs. what happens
when
> you click the stop sync button? How do you recover both in the event of
the
> former assuming the stop synch does not separate, but controls, parent
from
> children records.
> Thanks much!
I need to answer a question for management and I have little replication
knowledge. I'd appreciate any input here.
What happens to the dbs on either side i.e. dist and subscriber, when in the
middle of the operation, one of the systems crashes? vs. what happens when
you click the stop sync button? How do you recover both in the event of the
former assuming the stop synch does not separate, but controls, parent from
children records.
Thanks much!
replication stored the last recorded transaction in the meta data tables on
the subscriber and the publisher\distributor.
When the system comes back up the data will be replayed to the Subscriber
provided it has not being cleaned up on the publisher\distributor.
Transactions are applied on the subscriber within a transactional context.
If you are replicating data which is part of a pk-fk relationship these
commands will be replicated as past of a transaction - unless you are not
enforcing the relationship for replication.
So it is very much like the ACID properties you see when you apply the
transaction on the publisher.
If the distribution database is corrupted its all bets off, as it holds the
transactions to be applied on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JimMac" <JimMac@.discussions.microsoft.com> wrote in message
news:5BF14F6D-AB6E-4CB6-AC8E-125F43FFE652@.microsoft.com...
> Hi,
> I need to answer a question for management and I have little replication
> knowledge. I'd appreciate any input here.
> What happens to the dbs on either side i.e. dist and subscriber, when in
the
> middle of the operation, one of the systems crashes? vs. what happens
when
> you click the stop sync button? How do you recover both in the event of
the
> former assuming the stop synch does not separate, but controls, parent
from
> children records.
> Thanks much!
Subscribe to:
Posts (Atom)