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 transactional 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 appropriate 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:
> 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 create a new snapshot.
> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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
> >|||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:
>> 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
>> create a new snapshot.
>> Will you modify both databases? If not, a snapshot of a mirror or
>> transactional 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
>> appropriate 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 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 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 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. Potential 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 through 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 replica 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 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 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:
>> 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 create a new snapshot.
>> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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,
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:
> 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 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 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. Potential 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 through 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 replica 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 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 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:
> >> 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 create a new snapshot.
> >>
> >> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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
> >> >
> >|||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:
>> 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 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
>> 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.
>> Potential 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 through 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
>> replica 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 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
>> 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:
>> >> 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
>> >> create a new snapshot.
>> >>
>> >> Will you modify both databases? If not, a snapshot of a mirror or
>> >> transactional 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
>> >> appropriate 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
>> >> >
>> >
>|||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 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...
> 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:
>> 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 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 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. Potential 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 through 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 replica 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 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 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:
>> >> 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 create a new snapshot.
>> >>
>> >> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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
>> >> >
>> >
>|||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:
> 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 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...
> > 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:
> >> 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 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 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. Potential 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 through 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 replica 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 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 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:
> >> >> 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 create a new snapshot.
> >> >>
> >> >> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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
> >> >> >
> >> >
> >|||> 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 specifically 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:
>> 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 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...
>> > 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:
>> >> 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 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 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. Potential 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 through 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 replica 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 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 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:
>> >> >> 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 create a new
>> >> >> snapshot.
>> >> >>
>> >> >> Will you modify both databases? If not, a snapshot of a mirror or transactional 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 appropriate 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
>> >> >> >
>> >> >
>> >
>|||On 21 Sep 2006 09:15:06 -0700, "Gary Lou" <garylou@.gmail.com> 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.
First you have to choose an interval.
Once it starts executing, time is totally dependent on your schema and
data volume, but it's generally much more expensive on the publisher
so you want to run it less often, leading to much larger latencies.
But you get more robust operation. There's no such thing as a free
lunch!
J.