DB Mirroring, Load Balancing, and Fault Tolerant

Hi All,
We are going to upgrade to SQL 2005 in the next couple months. One of
the the requirements is that we need to load balance the workload, and
to be fault tolerant. We have internal applications runing against the
SQL server, and public users running against the same database through
web browser.
We are thinking to implement DB mirroring and configure internal users
to run against SQLSERVER1(internal), and web users to run against
SQLSERVER2(external). Data update and insert will be in sync through
the mirroring. However, there will be no fault tolerant. If
SQLSERVER1(internal) offline, we will have to reconfigure the ODBC
connection with client stations. If SQLSERVER2(external) offline, we
will have to change the configartion file (web.config).
My question:
1. Is the above solution will work for load balancing?
2. What is the best practice for Load Balancing and Fault Tolerant
solution?
Thanks.
GaryLoad balancing <> fault tolerance
You cannot access a mirrored database. You can create a snapshot of it and *
read* off of that
snapshot. But the data in the snapshot gets older and older until you create
a new snapshot.
Will you modify both databases? If not, a snapshot of a mirror or transactio
nal replication can be
fore you. If you do, merge replication *can* be an option, but you have to a
nalyze what happens in
the case of a conflict and if the way merge replication handles it is approp
riate for you. "Update
anywhere" is not trivial.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158693382.824744.91400@.k70g2000cwa.googlegroups.com...
> Hi All,
> We are going to upgrade to SQL 2005 in the next couple months. One of
> the the requirements is that we need to load balance the workload, and
> to be fault tolerant. We have internal applications runing against the
> SQL server, and public users running against the same database through
> web browser.
> We are thinking to implement DB mirroring and configure internal users
> to run against SQLSERVER1(internal), and web users to run against
> SQLSERVER2(external). Data update and insert will be in sync through
> the mirroring. However, there will be no fault tolerant. If
> SQLSERVER1(internal) offline, we will have to reconfigure the ODBC
> connection with client stations. If SQLSERVER2(external) offline, we
> will have to change the configartion file (web.config).
> My question:
> 1. Is the above solution will work for load balancing?
> 2. What is the best practice for Load Balancing and Fault Tolerant
> solution?
> Thanks.
> Gary
>|||Hi Tibor,
Thanks for your reply.
I understand load balancing is not the same as fault tolerance. From
your reply, database mirroring is not a load balancing solution since
users won't be able to access to a mirrored database.
Yes, both internal and external users will modify both servers. We
probably don't want to go for a merge replication as we don't want to
deal with conflicts between two servers. Is bidirectional transactional
replication another solution? If so, does it have the same issue like
merge replication has?
In SQL 2005, is there any out of the box load balancing solution? We
can actually buy one single high end server for both internal and
external users to access to. The hardware cost is just more expensive
than buying two mid range server. Any comment will be appreciated.
Thanks again.
Gary
Tibor Karaszi wrote:[vbcol=seagreen]
> Load balancing <> fault tolerance
> You cannot access a mirrored database. You can create a snapshot of it and
*read* off of that
> snapshot. But the data in the snapshot gets older and older until you crea
te a new snapshot.
> Will you modify both databases? If not, a snapshot of a mirror or transact
ional replication can be
> fore you. If you do, merge replication *can* be an option, but you have to
analyze what happens in
> the case of a conflict and if the way merge replication handles it is appr
opriate for you. "Update
> anywhere" is not trivial.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1158693382.824744.91400@.k70g2000cwa.googlegroups.com...|||Gary
As I understood you have to go with transaction replication , because as
Tibor pointed that db mirroring is not for load balancing
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158708576.869780.214950@.i3g2000cwc.googlegroups.com...
> Hi Tibor,
> Thanks for your reply.
> I understand load balancing is not the same as fault tolerance. From
> your reply, database mirroring is not a load balancing solution since
> users won't be able to access to a mirrored database.
> Yes, both internal and external users will modify both servers. We
> probably don't want to go for a merge replication as we don't want to
> deal with conflicts between two servers. Is bidirectional transactional
> replication another solution? If so, does it have the same issue like
> merge replication has?
> In SQL 2005, is there any out of the box load balancing solution? We
> can actually buy one single high end server for both internal and
> external users to access to. The hardware cost is just more expensive
> than buying two mid range server. Any comment will be appreciated.
> Thanks again.
>
> Gary
>
> Tibor Karaszi wrote:
>|||Hi Gary,

> Yes, both internal and external users will modify both servers. We
> probably don't want to go for a merge replication as we don't want to
> deal with conflicts between two servers.
I'm sorry, but "not dealing" with conflicts is not an option. The analysis p
art I mentioned mean
that you have to ask yourself questions like:
Will two users on each site modify the same row at the same time? Ever?
If above happens, how do you handle it?
If above is supposed to not happen, how do you make sure it doesn't? If you
don't enforce it won't
happen, it will, sooner or later.
Merge replication has conflict handling so that one modification becomes the
winner and the state of
the database will be the same on both nodes after a merge.
Bi-directional transactional replication doesn't have conflict handling. Pot
ential conflicts will
render the databases in different states, which will lead to chaos over time
.
There is transactional replication with immediate updating subscribers, but
an update on a
subscriber result in a trigger being fired which will modify the publisher t
hrough a two-phase
commit operation, involving DTC. Something that doesn't scale - au contraire
.
Load balancing when you own data and also need to modify the different repli
ca of data is *not*
trivial. That is why you don't see any pre-packaged solutions for such. The
solutions you see
either:
* Has only one set of data (shared disk clusters)
* Partition the data over the nodes (shared nothing clusters).
* Has some internal synchronous replication of the modification, resulting i
n global locking and
synchronous duplicate modification effort, including network latency for a m
odification.
I haven't read the most recent theoretical literature for this topic, so I w
elcome pointers to where
I might err above.
How about using one box where you do the modifications and use transactional
replication to a
database where you don't modify, only do reporting and such?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158708576.869780.214950@.i3g2000cwc.googlegroups.com...
> Hi Tibor,
> Thanks for your reply.
> I understand load balancing is not the same as fault tolerance. From
> your reply, database mirroring is not a load balancing solution since
> users won't be able to access to a mirrored database.
> Yes, both internal and external users will modify both servers. We
> probably don't want to go for a merge replication as we don't want to
> deal with conflicts between two servers. Is bidirectional transactional
> replication another solution? If so, does it have the same issue like
> merge replication has?
> In SQL 2005, is there any out of the box load balancing solution? We
> can actually buy one single high end server for both internal and
> external users to access to. The hardware cost is just more expensive
> than buying two mid range server. Any comment will be appreciated.
> Thanks again.
>
> Gary
>
> Tibor Karaszi wrote:
>|||Hi Tibor,
I appreciate your comment on the topic.
How does Peer-to-Peer recplication work compare to merge replication?
In both Merge and Peer-to-Peer replication, do I need to configure
which server to point to in the application level or the client level?
Thanks.
Tibor Karaszi wrote:[vbcol=seagreen]
> Hi Gary,
>
> I'm sorry, but "not dealing" with conflicts is not an option. The analysis
part I mentioned mean
> that you have to ask yourself questions like:
> Will two users on each site modify the same row at the same time? Ever?
> If above happens, how do you handle it?
> If above is supposed to not happen, how do you make sure it doesn't? If yo
u don't enforce it won't
> happen, it will, sooner or later.
> Merge replication has conflict handling so that one modification becomes t
he winner and the state of
> the database will be the same on both nodes after a merge.
> Bi-directional transactional replication doesn't have conflict handling. P
otential conflicts will
> render the databases in different states, which will lead to chaos over ti
me.
> There is transactional replication with immediate updating subscribers, bu
t an update on a
> subscriber result in a trigger being fired which will modify the publisher
through a two-phase
> commit operation, involving DTC. Something that doesn't scale - au contrai
re.
> Load balancing when you own data and also need to modify the different rep
lica of data is *not*
> trivial. That is why you don't see any pre-packaged solutions for such. Th
e solutions you see
> either:
> * Has only one set of data (shared disk clusters)
> * Partition the data over the nodes (shared nothing clusters).
> * Has some internal synchronous replication of the modification, resulting
in global locking and
> synchronous duplicate modification effort, including network latency for a
modification.
> I haven't read the most recent theoretical literature for this topic, so I
welcome pointers to where
> I might err above.
> How about using one box where you do the modifications and use transaction
al replication to a
> database where you don't modify, only do reporting and such?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1158708576.869780.214950@.i3g2000cwc.googlegroups.com...|||Peer to peer replication is much higher performance than merge replication
but it gets this performance because peer to peer assumes there are no
update conflicts - it's up to the applications to ensure that there are no
conflicts. Merge has all the overhead of detecting and resolving conflicts
so it can't be as efficient as peer to peer.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158787805.355076.91130@.e3g2000cwe.googlegroups.com...
> Hi Tibor,
> I appreciate your comment on the topic.
> How does Peer-to-Peer recplication work compare to merge replication?
> In both Merge and Peer-to-Peer replication, do I need to configure
> which server to point to in the application level or the client level?
>
> Thanks.
>
> Tibor Karaszi wrote:
>|||Roger responded to your question about peer-to-peer transactional vs. merge.
Btw, there is plenty
more information in Books Online.

> In both Merge and Peer-to-Peer replication, do I need to configure
> which server to point to in the application level or the client level?
Replication wasn't designed for fail-over scenarios. There is no built-in su
pport for the client
re-direction, you'd have to handle this at the application level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158787805.355076.91130@.e3g2000cwe.googlegroups.com...
> Hi Tibor,
> I appreciate your comment on the topic.
> How does Peer-to-Peer recplication work compare to merge replication?
> In both Merge and Peer-to-Peer replication, do I need to configure
> which server to point to in the application level or the client level?
>
> Thanks.
>
> Tibor Karaszi wrote:
>|||Thanks for all the replies. I appreciate your thought and comments.
Merge Replication is slower. We have two servers that are connected in
a local LAN with 1 gbs speed. What is the estimate latency in second
for merge replication to replicate data between two servers? Many
thanks.
Tibor Karaszi wrote:[vbcol=seagreen]
> Roger responded to your question about peer-to-peer transactional vs. merg
e. Btw, there is plenty
> more information in Books Online.
>
> Replication wasn't designed for fail-over scenarios. There is no built-in
support for the client
> re-direction, you'd have to handle this at the application level.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1158787805.355076.91130@.e3g2000cwe.googlegroups.com...|||> What is the estimate latency in second
> for merge replication to replicate data between two servers?
May I suggest you try this in the .replication group? This group isn't speci
fically for replication,
and you have a better chance to get a good answer in that group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1158855306.405780.31990@.d34g2000cwd.googlegroups.com...
> Thanks for all the replies. I appreciate your thought and comments.
> Merge Replication is slower. We have two servers that are connected in
> a local LAN with 1 gbs speed. What is the estimate latency in second
> for merge replication to replicate data between two servers? Many
> thanks.
>
> Tibor Karaszi wrote:
>