Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope.
I understand and accept that this will require escalation to DTC.
My concern is that our sql instance houses database for distributed applications that, in some cases, require interaction through APIs only. If I enable MSDTC I believe it will apply across databases... this means that when we consume APIs or others consume our API on the same SQL instance we are potentially controller transactions between two theoretically separable applications. Perhaps if the sql actions fail in the methods employed by the API it may be none of my business, and they shouldn't know or care whether I'm invoking their method inside if a DB transaction.
So, in my specific case, if I could use MSDTC solely within my one database it would be appropriate. I am ok with an exception if I do an action across databases requiring escalation.