sql server replication: Hello,

We are in the beginning stages of planning an infrastructure to
support a sports app that will allow customers to receive scores,
stats, in-game updates as real time as possible(e.g. espn online
updates)for multiple sports. We are using SQL server 2000 Enterprise
and Windows 2000 advanced server.

It has not been determined yet how the data will be fed to sql server.
This system will potentially need to support a very high volume of
small transactions/sec. Without going out and purchasing a Unisys or
superdome with the thought of scaling up, our current thought is to
try to scale out with multiple smaller systems which could potentially
allow us to plug systems in as needed. To do this I am considering
transactional replication.

Basically, we would have a Publishing database dedicated to receiving
the data feeds, this server would replicate out data to multiple
subscribers as the data comes in and the customer would be only
reading data from the subscribers. My understanding is that scaling
out with transactional replication is a good idea for mostly read-only
data. My concern is, while all of the subscribers in our scenario
would be read-only for the customer, they would constantly be
receiving inserts and updates from the Publisher while games are in
play and it seems that this could lead to some blocking issues on the
subscribers. We could look at using with no_lock(if approved) or
maybe disallowing page locks on the subscribers to help with
concurrency. I would just like to find out if transactional
replication is a feasible solution for this scenario. Are there any
known limitations scaling out with this method as far as the number of
subscribers that a publisher can support? Please let me know if more
details are needed.

Thanks,

Feasible yes. One very big thing is that you are going to have to plan for
a separate distribution server as well. Scaling out reads is one of the
primary things you can use transactional for. As far as how much processing
capacity you need, it really depends upon what kind of transaction volume
the publisher is going to see via the feeds. There is planned to be a very
good session at TechEd US which goes over a real world implementation for
some pretty impressive transaction volumes. You are also going to wind up
putting more processing power on the distributor than anywhere else in the
architecture since it is going to bear the majority of the workload.

As far as the blocking, I actually don't see any issue with using nolock and
allowing dirty reads. The reasoning behind it is this. All of your
transactions are being fed into the publisher. All of your customers are
reading from subscribers. That means the effect of a "dirty read" is simply
that they see the score as it already was and when they do a refresh it will
be a current score/stat/update. You don't have a real "dirty read", instead
you have a state of the data. They are either going to be reading data at
time X or time Y, because there won't be any case at the server where they
are reading where a transaction will get started and then rolled back.
Every transaction that starts on the read only servers will commit. That
removes all of your transaction lock contention from those servers.

--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Hi Greg,

For the scenario that you have mentioned where you require read only
subscription, Transactional replication would fit in the best.

There is no known limitations for scaling out the Subscription from a
publisher.



Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks guys. Just needed a sanity check on this to verify that it is
a solution.

Hey Mike, I attended your high availability course in San Francisco
which was I found very worthwhile and I highly recommend to anyone
that needs to get smart on this topic. I probably won't be able to
make it down to San Diego for TechEd but I'll be twisting my bosses
arm to get me out to one of your replication seminars. Any plans for
a San Francisco replication course?

Thanks,
Greg

On Thu, 29 Apr 2004 23:42:04 -0400, "Michael Hotek"
[quoted text, click to view]