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
Showing posts with label limited. Show all posts
Showing posts with label limited. Show all posts
Monday, March 26, 2012
Replication scenario question...Merge or Transactional?
The situation I am faced with is we have a web application supported by a SQL
2000(sp4) database that resides on a limited bandwidth network. Our
distributed users are constantly complaining of "slow" response times. Our
local users have no such complaints. Some of our leadership has suggested
sending a SQL server/IIS server to the remote location and using some type of
replication to synchronize the data between these boxes. The requirements
are for minimal latency and concurrent updating of data. The leadership also
want this solution to be completely automated (little or no supervision of
the replication process) and as with everything we do they want it right away
(we're talking days, not weeks). I am very new to replication and have read
through the BOL section and am in the process of reading Hillary Cotter's
book. I am leaning toward an implementation of Merge Replication but I am
unsure if this is the right solution. Any advice or informed opinions would
be greatly appreciated.
There is no concurrent replication option ie each solution will have a degree
of latency. If you use merge then you can select from a variety of conflict
resolvers and easily work offline. This might be your best option. There are
alternatives - queued updating subscribers, immediate updating subscribers
and bidirectional transactional replication. Do you have BLOBS in the table?
Are the subscribers always connected? Should they be able to continue if not
connected? These questions will clarify and narrow down the options a bit.
Whichever option you select, don't rush - you'll need time to configure it in
a test environment to establish a set of protocols (change management, error
handling...) and to simply verify that it all works for your situation.
HTH,
Paul Ibison
"Dave Stokes" wrote:
> The situation I am faced with is we have a web application supported by a SQL
> 2000(sp4) database that resides on a limited bandwidth network. Our
> distributed users are constantly complaining of "slow" response times. Our
> local users have no such complaints. Some of our leadership has suggested
> sending a SQL server/IIS server to the remote location and using some type of
> replication to synchronize the data between these boxes. The requirements
> are for minimal latency and concurrent updating of data. The leadership also
> want this solution to be completely automated (little or no supervision of
> the replication process) and as with everything we do they want it right away
> (we're talking days, not weeks). I am very new to replication and have read
> through the BOL section and am in the process of reading Hillary Cotter's
> book. I am leaning toward an implementation of Merge Replication but I am
> unsure if this is the right solution. Any advice or informed opinions would
> be greatly appreciated.
2000(sp4) database that resides on a limited bandwidth network. Our
distributed users are constantly complaining of "slow" response times. Our
local users have no such complaints. Some of our leadership has suggested
sending a SQL server/IIS server to the remote location and using some type of
replication to synchronize the data between these boxes. The requirements
are for minimal latency and concurrent updating of data. The leadership also
want this solution to be completely automated (little or no supervision of
the replication process) and as with everything we do they want it right away
(we're talking days, not weeks). I am very new to replication and have read
through the BOL section and am in the process of reading Hillary Cotter's
book. I am leaning toward an implementation of Merge Replication but I am
unsure if this is the right solution. Any advice or informed opinions would
be greatly appreciated.
There is no concurrent replication option ie each solution will have a degree
of latency. If you use merge then you can select from a variety of conflict
resolvers and easily work offline. This might be your best option. There are
alternatives - queued updating subscribers, immediate updating subscribers
and bidirectional transactional replication. Do you have BLOBS in the table?
Are the subscribers always connected? Should they be able to continue if not
connected? These questions will clarify and narrow down the options a bit.
Whichever option you select, don't rush - you'll need time to configure it in
a test environment to establish a set of protocols (change management, error
handling...) and to simply verify that it all works for your situation.
HTH,
Paul Ibison
"Dave Stokes" wrote:
> The situation I am faced with is we have a web application supported by a SQL
> 2000(sp4) database that resides on a limited bandwidth network. Our
> distributed users are constantly complaining of "slow" response times. Our
> local users have no such complaints. Some of our leadership has suggested
> sending a SQL server/IIS server to the remote location and using some type of
> replication to synchronize the data between these boxes. The requirements
> are for minimal latency and concurrent updating of data. The leadership also
> want this solution to be completely automated (little or no supervision of
> the replication process) and as with everything we do they want it right away
> (we're talking days, not weeks). I am very new to replication and have read
> through the BOL section and am in the process of reading Hillary Cotter's
> book. I am leaning toward an implementation of Merge Replication but I am
> unsure if this is the right solution. Any advice or informed opinions would
> be greatly appreciated.
Labels:
application,
bandwidth,
database,
faced,
limited,
microsoft,
mysql,
network,
oracle,
questionmerge,
replication,
resides,
scenario,
server,
situation,
sp4,
sql,
sql2000,
transactional,
web
Subscribe to:
Posts (Atom)