Re: SQL load balancing/replication - newbie question

Yes, you could do that. It isn't a free lunch. You do not need a SAN to
accomplish this, it can be done with local storage on both machines. You
will need a load balancer that will distribute the load between the servers.

Now, for the interesting part. If all you are doing is reading from the
databases, then you have very few issues since there is a master copy
somewhere. The only issue you deal with in this case is with data
visibility. Data will appear in one database before it makes it to the
second database, so if you are bouncing users back and forth, they can get
inconsistent results. Which brings up the other issue with doing any of
this. You want to configure your load balancing solution with "sticky"
connections. In otherwords, once a client has been connected to a SQL
Server, they remain on that SQL Server until the connection is terminated.

Now, if you are allowing writes in this environment, you are dealing with an
entirely different set of issues.
1. How do you handle two users modifying the same data on each server at the
same time?
2. How do you handle changes being made to both servers and being sent to
the other?
3. How do you manage the visibility of the data and the latency of the
change?
There aren't any cut and dried answers here. I've been doing replication
since before Microsoft SQL Server existed as a product. I've deployed a
load balanced solution where you use a hardware load balancer and still
allow writes to either SQL Server exactly once out of the hundreds of
implementations that I've done. If you don't know the replication engine
inside and out as well as having complete control over any application
connecting to your database server as well as understanding the data within
your database, I would not suggest trying to do this.

--
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


<foxj77@xxxxxxxxx> wrote in message
news:1155052797.439868.22900@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi there,

We have three live production SQL servers and one of them is nearing
capacity. I have been asked to investigate SQL replication and there
is an understanding that SQL replication is the hold grail and it will
solve all of our problems.

I have spoke to a few developers within the company and they have
talked about replicating databases to provide redundancy in case of a
hardware failure. What we could do with is load balancing the server
(having the same database running on two servers and share the load).
Sorry if im using the wrong terms for this as i am a bit new to this.

Would we need any specialist hardware to achieve the second option? I
know in the past the developers have talked about needing a hardware
load balancer and a SAN.

I have looked at SQL replication a little bit and i was looking at
transactional replication to mirror the databases. I'm not sure if we
would need anything to balance the requests out between the two
servers.

We are running SQL 2000 on windows 2003 enterprise (x2) and win 2003
standard R2.

Thanks for help and apologies if im sounding a bit dumb but im a bit of
a newbie to this.

Thanks


John



.

Relevant Pages