Using the latest version of NHibernate (and possibly some plugins), is that possible to map entities across multiple databases on different servers without DB link?
For the background, I'm looking to implement something loosely similar to what is described in this DBA.SE post.
After some googeling (although I haven't tried this yet), you can apparently at least map classes to tables in different databases on the same server, using
<class name="..." table="..." schema="database.schema">
...
</class>
in the mapping.
But IMHO this is a crappy solution, because it doesn't seem to be server-independent. Apparently, NHibernate just concatenates the schema value with the table value in the creation of the query - and for most servers, "just putting the database in there with the schema" results in a syntax, which HAPPENS work. BUT you don't cleanly tell NHibernate "this is the schema" and "this is the database" so that NHibernate can cleanly decide how to build the query - you mess with its query creation by injecting contextual data where it doesn't belong.
IMHO, NHibernate should suppress such dirty hacks and throw an exception if the "schema" value contains a .
Anyhow, there seems to be no technical reason why you shouldn't be able to go one step further and use schema="server.database.schema", where "server" is a name of a linked server (see for example http://technet.microsoft.com/de-de/library/ms190479.aspx )
Apart from that dirty hack, everyone seems to recommend using multiple session factories.
I've got an interesting answer on the nhusers mailing list. Excerpt follows:
From Jason Meckley
[...] if you are talking about storing a single entity across multiple databases, the answer is not cleanly. you might be able to do something with custom user types and/or event listeners to hydrate the object, but that would be a mess to maintain.
A different approach is to transfer the data from one db to another using an ETL process. similar to replication, but only transferring the data required by the other database. you end up with 1 writable database and many read databases. then you map the domain to a single database.
Another option is to determine why there are multiple databases. if they represent different types of data/objects then explicitly express this in the domain. for example CustomerWithAddress is a different entity than CustomerOrder. If you need information from both, then query each database individually and build up a projection in code.
var x = session1.get(id);
var y = session2.get(id);
return merge(x, y);