Hello Professional.

I am working in Trading Firm and you can imagine how many transactions would
be in place per second, I was thinking to implement Peer To Peer Replication
b/w two of our production Servers and we can implement load balancing as
well but the only issue Peer to Peer replication takes almost 4 seconds to
replicate the data bi directional, we also increase the bandwidth but didn't
see improvements, we can't lose 1 second of data. I was more concern about
if client place a trade on one server and through some reason we redirect
him to another server and he can't see his transaction [becauase replication
takes 4 seconds to replicate].

I would appreicate if anyone guide me or provide me any link through which I
can fulfuil 100% load balancing configuration.We were also thinking to go
with Clustering but Clustering is a hardware solution and I think Database
Mirroring is better than clustering but we are looking for Active / Active
connection b/w two Servers so both the Sql Server should be Active Active.

Thanks for your help

Regards

Hello Noor,

First of all, there is no a "Load Balancing" like Oracle' s Real Application
Cluster (RAC) in SQL Server. There' s a pseudo Active\Active Clustering in
SQL Server.

Also, clustering is not a load balancing method actually because it's a High
Availibility solution. I just can be used as a part of a load balancing
solution. If you set up more than one active nodes in a cluster, you can
have more servers that you can move your SQL Server services in case of a
hardware problem. However, you can use the other nodes to balance the load
using Replication as I have done in my current project. For instance, in my
project there are two node-clustered servers in every location and the first
one is being used for OLTP purposes and the second one is being used as a
reporting server. However, in my situation 4-5 second delays are not problem
for my client.

Database Mirroring or Replication can be used to spread the workload between
nodes. However Database Mirroring may not be useful for your situation
because it won't let you to query the mirror database directly, you can
achieve this only using Database Snapshots (in case your SQL Server
instances are Enterprise Edition) but it won't give you a real time updated
database because a database snapshot is a static view of a database.

If you want your replication to work faster, you should ask a question about
this in the Replication newsgroup and I think Hilary or Paul may assist you
there.

--
Ekrem Önsoy



"Noor" <naissani (AT) hotmail (DOT) com> wrote

Thanks for your reply, really appreciated,

One quick question regarding Database Snapshot, I want to refresh my
database snapshot every morning at 6:00 AM but the problem is if someone
already using that database snapshot I can't drop and re-create it, can you
let me know which command I have to use first to kill all the sessions which
is currectly active and then drop the database snapshot and create again?
because sometimes people are already connected on.

Please advice.

Thanks

"Ekrem Önsoy" wrote:

hello Noor,

The best way to load balance database server in your scenario is to use
federal database approach. pick the tables which is center of usage, and
then just put those table in other database server and implement linked
server in between them. i know it is not easy but this is the only method to
achieve scalability. using distributed views u can make application thinks
that data is coming from same server.

also clustering is a good option (if u hardware is not a prob). originally
clustering is not a scale out solution it is actually for high availability.
but with little trick, we can achieve fair about of scalability. lets
suppose we have 100 gb database . first divide the database into two 500
database to make it federal like databse . then host part of database on each
cluster node and make sure that this configuraton will use only 30- 40 % of
node resources and configure them to fail over on one - other . in this way .
if one node get failed then it failover to other node then also total usages
will remain well under 80 % . the same thing can also be done with database
mirroring .

i am sure this will help u .

Manpreet Singh
MCITP(DBA, DB Dev)

manpreet singh
MCITP (DBA,DB Dev)

aso

"Noor" wrote:

Manpreet, I would appreciate if you can provide me your personnel email
address or you can email me one test email at naissani (AT) Hotmail (DOT) com,

Just want to touch base with you regarding MCITP.

Thanks in advance

"Manpreet Singh" wrote: