Hi,

I have an ASP.NET application that interacts with two database servers. What is the best practices way to configure this in NHibernate so that I can access persistant objects on both DB servers?

What I have come up with is using two session factories. Is there any way to use MS DTC to coordinate the transactions between the session factories? Any way to use a single session factory? I know that if these were multiple schemas on the same database server instance, I could use a single session factory, but this is definitely seperate databases on seperate physical devices (SQL Server).

Thanks,

JP

You can use a single session factory if you create a linked-server connection between the 2 servers. Then fully qualify the table names in the mapping files with the server and database names like this:

Thanks jnapier. That's a cool solution.

I am concerned though - how will this affect performance? I had not planned on doing joined queries between the two databases. So other than making this simpler for NHibernate, I guess I get distributed transaction, but at the cost of performance (since with the linked server connection there would be a lot more traffic between the two servers)?

Have you tried this? How much of a hit is it?

Thanks,

JP

I have only done this across databases on the same server but not across servers. I'm sure there will be a performance hit.

Im not sure what your needs are across the two databases so if the performance vs maintenance issue causes a problem then you would have to use 2 different session factories as you mentioned earlier.

If you are using .net 2.0 you could use TransactionScope and wrap your calls in the scope. Then the framework will use msdtc to coordinate the transactions across the servers for you. To get this to work you will have to disable aggressive connection release mode if you are using 1.2CR1. Also you will have to ensure that you start the transactionscope before your sessions open connections to the databases.

I have to talk to up to three databases on separate servers during a single page request. Here's the solution I came up with: http://www.codeproject.com/useritems/NH ... pleDBs.asp . I haven't found a way to wrap them in a single transaction...but then again, I haven't had the need as well.

Billy McCafferty