servers and distributed transactions question

In our production environment, we have three different SQL servers
that make up our application. One database sitting on each server.
The three servers are represented below with the corresponding
databases.
ServerPRD01: DB1
ServerPRD02: DB2
ServerPRD03: DB3
We created two network aliases through the SQL Server Client Network
Utility that we use to create linked server entries so that the
databases may talk to each other.
Alias-1: Alias to ServerPRD02
Aleas-2: Alias to ServerPRD03
Thus, DB1 can communicate to DB2 through a 3 part naming convention
after setting up linked server entries on ServerPRD01. A sample SQL
statement follows:
SELECT * FROM [Alias-1].DB2.dbo.ATable
Now, we don't have the resources to have three different SQL boxes in
our testing and development environments. Instead, we set up ONE SQL
server for each test and development environment. We then created the
network alias's that loop back to the same server.
ServerTST01: DB1, DB2, DB3
Alias-1: Alias to ServerTST01
Alias-2: Alias to ServerTST01
Thus, when we look at the SQL statement above, we are still using the
same alias, but we are going through the alias to communicate with a
database on the same server. We want to stick to using alias's so we
can maintain code integrity throughout our systems.
The problem is, the DTC doesn't seem to like running a distributed
transaction when the alias points back to the same server where the
transaction is started.
I'm not an expert in SQL server and I want to understand if this is
just the way it is, or is there a work-around. The error we get when
we try to run a transaction follows:
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
I've seen posts where people were having trouble with DTC, but those
instances always consisted of linked servers on different physical
boxes.
Thanks for your help in advance. Please reply to this newsgroup, I
don't check the email attached to this posting.The behavior you are seeing is normal and expected.
From BOL (topic: Loopback Linked Servers)
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
news:2c5e843.0402250800.51069a68@.posting.google.com...
> In our production environment, we have three different SQL servers
> that make up our application. One database sitting on each server.
> The three servers are represented below with the corresponding
> databases.
> ServerPRD01: DB1
> ServerPRD02: DB2
> ServerPRD03: DB3
> We created two network aliases through the SQL Server Client Network
> Utility that we use to create linked server entries so that the
> databases may talk to each other.
> Alias-1: Alias to ServerPRD02
> Aleas-2: Alias to ServerPRD03
> Thus, DB1 can communicate to DB2 through a 3 part naming convention
> after setting up linked server entries on ServerPRD01. A sample SQL
> statement follows:
> SELECT * FROM [Alias-1].DB2.dbo.ATable
> Now, we don't have the resources to have three different SQL boxes in
> our testing and development environments. Instead, we set up ONE SQL
> server for each test and development environment. We then created the
> network alias's that loop back to the same server.
> ServerTST01: DB1, DB2, DB3
> Alias-1: Alias to ServerTST01
> Alias-2: Alias to ServerTST01
> Thus, when we look at the SQL statement above, we are still using the
> same alias, but we are going through the alias to communicate with a
> database on the same server. We want to stick to using alias's so we
> can maintain code integrity throughout our systems.
> The problem is, the DTC doesn't seem to like running a distributed
> transaction when the alias points back to the same server where the
> transaction is started.
> I'm not an expert in SQL server and I want to understand if this is
> just the way it is, or is there a work-around. The error we get when
> we try to run a transaction follows:
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> I've seen posts where people were having trouble with DTC, but those
> instances always consisted of linked servers on different physical
> boxes.
> Thanks for your help in advance. Please reply to this newsgroup, I
> don't check the email attached to this posting.|||Geoff,
Thanks for the response. We are now looking to VMWare as a solution.
Kev
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OzBq457#DHA.3536@.tk2msftn
gp13.phx.gbl>...
> The behavior you are seeing is normal and expected.
> From BOL (topic: Loopback Linked Servers)
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from withi
n
> a distributed transaction causes an error:
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
> news:2c5e843.0402250800.51069a68@.posting.google.com...