DTCTransaction Vs. SQLTransaction (Performance)

Can anyone pinpoint the differences between DTCTransaction and
SQLTransaction. I am specifically interested in terms of performance. I am
not sure if there is any performance penalties that you have to pay using
DTCTransaction versus SQLTransaction. If so how much e.g. is there something
like, DTCTransaction will be 2 times slower than SqlTransaction ?
Thanks in Advance. ArifI can't give specific benchmarks, but if you think about a dtc trans
versus a sql trans, you have to realize that a dtc trans has to be
coordinated by the app servers dtc, the sql servers dtc and sql server.
The added layers of complexity would make be tend to believe that the
dtc trans would have more overhead.
Not to mention there are 3 points of failure as opposed to one with sql
server.
Just my 2 cents...sorry I don't have a specific benchmarks.
John|||Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> Can anyone pinpoint the differences between DTCTransaction and
> SQLTransaction. I am specifically interested in terms of performance. I
> am not sure if there is any performance penalties that you have to pay
> using DTCTransaction versus SQLTransaction. If so how much e.g. is there
> something like, DTCTransaction will be 2 times slower than
> SqlTransaction ?
A DTCtransaction is a distributed transaction that you need to use, if
you connect to more than one server, and want the transaction to encompass
all servers. You should not use DTCtransaction if you are connecting to
a single server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, In one of our applications we are debating over using Service
Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
which basically gives you the leverage to have multiple resource managers
controlled by an external transaction manager e.g. on windows 2000
distributed transaction coordinator (DTC).
From a logical persepctives if we are dealing with only one resource
manager, it's better to use local transaction and use resource manager as
transaction manager too e.g. using ADO.NET transaction etc.
What I am trying to understanding is how much extra penalty you have to pay
by using DTCTransaction Vs. SQLTransaction. I am basically looking for some
statistics or research done by anyone in comparing these two.
"Erland Sommarskog" wrote:

> Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> A DTCtransaction is a distributed transaction that you need to use, if
> you connect to more than one server, and want the transaction to encompass
> all servers. You should not use DTCtransaction if you are connecting to
> a single server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> Erland, In one of our applications we are debating over using Service
> Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
> which basically gives you the leverage to have multiple resource managers
> controlled by an external transaction manager e.g. on windows 2000
> distributed transaction coordinator (DTC).
> From a logical persepctives if we are dealing with only one resource
> manager, it's better to use local transaction and use resource manager as
> transaction manager too e.g. using ADO.NET transaction etc.
> What I am trying to understanding is how much extra penalty you have to
> pay by using DTCTransaction Vs. SQLTransaction. I am basically looking
> for some statistics or research done by anyone in comparing these two.
I will have to admit that I don't have numbers to present, only a gut
feeling.
Since I'm an SQL Server person, I would probably prefer to handle
transactions by sending down BEGIN TRANSACTION myself, and not involve
a component that I don't really know what it's doing. But that's me.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you use DTC with a single resource manager DTC won't do a two phase
commit so the extra overhead is not all that great unless network roundtrips
to the database are very expensive but as Erland says, controlling your own
transactions in SQL Server is usually a better idea and if you complete
eliminate DTC, you don't have to worry about what happens if it's not
running - one few moving part is generally an advantage. On the other hand,
if you want COM+ to manage transactions for you so you can freely compose
transactional and non-transactional components and nest component calls
without worrying about transaction management, there might be a reason to
use DTC.
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
"Arif Khan" <ArifKhan@.discussions.microsoft.com> wrote in message
news:02C8BD8D-757C-48EB-8D49-95E566DDA5C2@.microsoft.com...
> Erland, In one of our applications we are debating over using Service
> Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
> which basically gives you the leverage to have multiple resource managers
> controlled by an external transaction manager e.g. on windows 2000
> distributed transaction coordinator (DTC).
> From a logical persepctives if we are dealing with only one resource
> manager, it's better to use local transaction and use resource manager as
> transaction manager too e.g. using ADO.NET transaction etc.
> What I am trying to understanding is how much extra penalty you have to
> pay
> by using DTCTransaction Vs. SQLTransaction. I am basically looking for
> some
> statistics or research done by anyone in comparing these two.
>
> "Erland Sommarskog" wrote:
>

Dtabases over multiple instances

Hi all,
For performance reason, I want to devide all databases over multiple
instances.
Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
In this case, the application would need to know on which
VirtualServer/Instance the database resides.
If there is no way to tell the application, is there a solution within
SQL(2000) for it to do some kind of redirect to the rightServer/Instance
where the database does reside ?
I am looking for something similar like Exchange, where all servers in the
Organisation know where(which server) your mailbox resides.
So you could connect to any mail server and get directed to the one with
your mailbox.
Thx,
Erwin
Clustering doesn't work like that. Each instance owns a defined set of
databases. Each database can be owned by only one instance. The entire
instance fails as a unit to another host node should the first host node go
down. Clustering is not a scale-out technology.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> For performance reason, I want to devide all databases over multiple
> instances.
> Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
> In this case, the application would need to know on which
> VirtualServer/Instance the database resides.
> If there is no way to tell the application, is there a solution within
> SQL(2000) for it to do some kind of redirect to the rightServer/Instance
> where the database does reside ?
> I am looking for something similar like Exchange, where all servers in the
> Organisation know where(which server) your mailbox resides.
> So you could connect to any mail server and get directed to the one with
> your mailbox.
> Thx,
> Erwin
>
|||Thx,
I understand that.
I am looking for some kind of solution where you could connect to A database
without having to know which server(or instance) the database physically
resides(clustered or non-clustered).
As in the Exchange example, connecting to An Exhange server which will
redirect you to the server actually holding your mailbox.
It would be a good option for SQL to be able to do this too.
Thx,
Erwin
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> Clustering doesn't work like that. Each instance owns a defined set of
> databases. Each database can be owned by only one instance. The entire
> instance fails as a unit to another host node should the first host node
go[vbcol=seagreen]
> down. Clustering is not a scale-out technology.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
the
>
|||Hi Eppie,
You can puiblish the database to AD and let you're application search for
the database name in AD ; find the server/instance name and connect. This
solution however has nothing to do with clustering but counts for all SQL
sever installations.
The SQL server magazine had an article on this in their April number
providing explanation and code to get it working. (see link below)
http://www.winnetmag.com/Windows/Art...41/pg/1/1.html
(members only)
"Eppie" wrote:

> Thx,
> I understand that.
> I am looking for some kind of solution where you could connect to A database
> without having to know which server(or instance) the database physically
> resides(clustered or non-clustered).
> As in the Exchange example, connecting to An Exhange server which will
> redirect you to the server actually holding your mailbox.
> It would be a good option for SQL to be able to do this too.
> Thx,
> Erwin
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> go
> the
>
>