all groups > sql server replication > july 2006 >
You're in the

sql server replication

group:

Load balancing DB for global access



Load balancing DB for global access Lalitha
7/12/2006 5:48:02 AM
sql server replication: We have the below requirement:

We need high performing read/write capacity for users across globe
(concurrency appx 20K users). Users can work offline and later synchronize
data. Data synchronization frequency is medium to high (every hour or so).
Data size that has to be synchronized is also high (1 - 2 MB)
We also need failover.
The data cannot be horizontally partitioned based on region across
servers(what i mean here is user from AsiaPacific and NorthAmerica
can work on the same piece of data. User from any region will be equally
interested in reading/writing data of any other region). There is no other
good candidate for horizontal partitioning to suit our business needs.
This way DPV(s) do not fit this requirement as routing request to the SQL
Server that can provide most data is not logically viable
in the application layer. DPV(s) also make the solution more complex.

We cannot consider peer-to-peer transactional replication as we will need
out of box conflict resolution support.

So the option available is to have one server for each region and use Merge
replication (Users can receive data, make changes offline, and later
synchronize changes with the Publisher and other Subscribers.).
This way users can connect to the server which is geographically closest.

But my concern is replication will also have a negative impact on
performance when percentage of write operation, data conflicts increases.
This can be mitigated to some extent by scaling up the individual regional
servers.

Other than this is there any other viable option to suit this requirement
that i missed out? Or is there anything that I have to take care to give high
performance.

Thanks much,
Lalitha
Re: Load balancing DB for global access Paul Ibison
7/12/2006 3:59:31 PM
Lalitha - I agree with your conclusions re merge replication. I would
recommend looking at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/mergperf.mspx
for info on merge optimization and impact.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

AddThis Social Bookmark Button