> > So, the rules state that the data should be physically separated, but > handling them concurrently in the same application is not a problem? > Rule is data at rest and not data in memory. Long term goal is to encrypt data before it rests and store in a single silo, but we can't get there without really overhauling our old solution, which is where I am now.
> -Use a session factory per database and TransactionScope/MSDTC to handle > transactions spanning multiple databases. TransactionScope+NH is not > without its fair share of problems, but maybe you could limit its use. This isn't a bad thought, I used MSDTC TransactionScopes on a previous project and it worked out alright, but it wasn't using nhibernate or any ORM for that matter -(Assuming the silos look the same) Create views in one database, which > pulls data from the silos and merges it with UNION ALL, including its > identity in one column. If necessary, use this identity as part of the key. > Handle data manipulation using INSTEAD OF triggers, redirecting inserts etc > to the corresponding database. Such views and triggers should be possible > to create with a bit of "scripting" I really don't like triggers, they move business rules to the database and become a bitch to maintain with installations. -If possible use different setups (mapping, session factories, maybe even > separate code domains) for single vs. multi silo scenarios. This is *sort of *what we are doing now with our standard dbs having their own mappings to their fully qualified schema, but the whole system share a single session factory > -Maybe, just maybe, consider if the use of NHibernate, or any other OR > mapper for that matter, really will make you more productive. We had been manual scoping our sessions and transactions before, and now with our new infrastructure we are letting the dependency on the SessionFactory by the Services/Repositories drive scoping of the session (per request) and using Castle Proxies to intercept the requests to transacted methods and create a unit of work around them. This new setup really does work out well until we try and remove lazy="false" which is where I am now. What I had thought about doing which felt like a huge hack, but as an experiment was to create an interceptor and intercept the OnPrepareStatement and the OnLoad in the OnLoad use the ID to track the currently connected silo, and in the OnPrepareStatement look for the ID, if found prepend a use statement. I am willing to dig in to the guts here and get dirty, if for nothing else but to better understand nhibernate. I started browsing the source and was looking at DefaultDynamicLazyFieldInterceptor (mostly because the name sounded like something that could be useful to look at). Anyway thanks for giving me some ideas, if anything I mentioned above sparks other ideas do share :) -Patrick On Friday, August 23, 2013 5:27:42 PM UTC-4, Gunnar Liljas wrote: > > Ouch, that sounds painful! Not an ORM friendly setup. Hell, not friendy, > full stop. > > So, the rules state that the data should be physically separated, but > handling them concurrently in the same application is not a problem? > > Random things to test: > > -Use a session factory per database and TransactionScope/MSDTC to handle > transactions spanning multiple databases. TransactionScope+NH is not > without its fair share of problems, but maybe you could limit its use. > -(Assuming the silos look the same) Create views in one database, which > pulls data from the silos and merges it with UNION ALL, including its > identity in one column. If necessary, use this identity as part of the key. > Handle data manipulation using INSTEAD OF triggers, redirecting inserts etc > to the corresponding database. Such views and triggers should be possible > to create with a bit of "scripting" > -If possible use different setups (mapping, session factories, maybe even > separate code domains) for single vs. multi silo scenarios. > -Maybe, just maybe, consider if the use of NHibernate, or any other OR > mapper for that matter, really will make you more productive. > > /G > > > > > > 2013/8/23 Patrick Doran <[email protected] <javascript:>> > >> Gunnar absolutely. >> >> The dynamic databases represent data that must remain "siloed" (stored on >> separate discs) this is for compliance purposes and a restriction on our >> design (and also something I happened to inherit :( ). What this means is >> that for each new "silo" we create a new database, it is placed on to its >> new disc/partition and we store a master record of each silo in one of our >> Master databases. >> >> The need for transactions to span multiple databases is for a few reasons >> (I had considered a session factory per siloed database but the transaction >> limitation breaks that down) >> >> 1) We have certain objects that are stored in the silos however we store >> pointers to those objects in a master table in one of the shared databases. >> 2) We have some business requirements that ask that data in silo 1 can be >> related to something in silo 2. So this means we have to query Silo 1 for >> some guid, then point to silo 2 and save off a record that links some item >> back to a record, that happens >> to exist in silo 1. If any of those steps fail, transaction is rolled >> back, hence the need for a transaction >> 3) In certain scenarios We have to be able to loop over all of the silos >> aggregate the data and return the results >> >> Feel free to ask more questions, I know the design is rather unorthodox >> and I am really trying to make lazy loading work, but I am banging my head >> against my desk trying ;\ I appreciate your interest in helping! >> >> >> >> On Thursday, August 22, 2013 11:12:14 AM UTC-4, Gunnar Liljas wrote: >> >>> Could you describe the reasoning behind the design and how it is used? I >>> wont suggest that you change it, but I need to understand it in order to >>> propose something. >>> >>> >>> 2013/8/22 Patrick Doran <[email protected]> >>> >>>> The database setup we are using currently is 3 "standard" databases >>>> where their mapping schema is mapped to "database.schema", however we also >>>> have a set of mapped items where the mapping of the schema is to >>>> .schema. This database schema/architecture design may sound unorthodox but >>>> it something I can't change, so please don't suggest that. >>>> >>>> The current use for this DAL is in a MVC web application, we are using >>>> Autofac MVC dependency resolver to scope a session the full lifetime of a >>>> web request. We then use Castle Proxies to intercept requests to our >>>> Services that are marked with a transaction attribute or requests to >>>> Repositories, and sniff the httpcontext information to determine which >>>> database to point to. We create a unit of work with the interceptor, which >>>> based on the sniffed database value calls >>>> Session.Connection.**ChangeDatabase(database); >>>> This solution works great with lazy loading turned off. >>>> >>>> We had a different setup before that was not very flexible and have >>>> moved to this setup. Previously we had lazy loading turned off and we are >>>> trying to, with our new solution, turn it back on. In our integration >>>> tests >>>> we are able to lazy load successfully with the static schema objects, >>>> however the "dynamic schema" database lazy loading is problematic, because >>>> when it goes to lazy load it will encounter an ADOException "Invalid >>>> object >>>> name '.schema.table'", which makes sense. >>>> >>>> We also need to be able to switch databases during a transaction, so we >>>> can't tie the session directly to the database we want to connect to. >>>> >>>> I am curious if there is anyway to intercept the lazy loading requests >>>> and execute a Session.Connection.**ChangeDatabase command. This of >>>> course also has the requirement that I can somehow store which database a >>>> particular entity came from so that I can reapply when I intercept the >>>> request which database to target. My guess is I may need to implement an >>>> interceptor and wire it in differently IE config.SetInterceptor (in the >>>> code below) and perhaps during Instantiate I could set some property that >>>> is available to all "Dynamic Database" models IE Database, and then during >>>> lazy load intercept and issue the ChangeDatabase on the session. >>>> >>>> Here is what the Autofac registeration looks like that we use now: >>>> >>>> >>>> builder.Register(c => { >>>> string connect**ionString = >>>> String.Format(Conf**iguration.Settings.Infrastruct**ure.ConnectionString, >>>> DEFAULT_**DATABASE); >>>> var config = n**ew Configuration(); >>>> config.DataBas**eIntegration(db => { >>>> db.Driver<**SqlClientDriver>(); >>>> db.Dialect**<MsSql2008Dialect>(); >>>> db.Connect**ionString = connectionString; >>>> }); >>>> config.AddAsse**mbly(typeof(UnitOfWork).Assemb**ly); >>>> // maybe config.SetInterceptor(new SomeCustomInterceptor()) >>>> ISessionFactor**y factory = config.BuildSessio**nFactory(); >>>> return new Red**actedSessionFactory(DEFAULT_**DATABASE, >>>> factory); >>>> }).As<IRedactedSes**sionFactory>() >>>> .SingleInstance(**); >>>> >>>> >>>> // Repository **Interceptor. >>>> builder.Register((**c, p) => { >>>> IRedactedSessi**onFactory factory = >>>> c.Resolve<**IRedactedSessionFactory>(); >>>> >>>> >>>> string name = **GetDatabaseNameFromEnvironment**(c, p); >>>> name = name ??** DEFAULT_DATABASE; >>>> factory.Databa**se = name; >>>> return new Uni**tOfWorkInterceptor(factory, na**me); >>>> }).InstancePerLife**timeScope(); >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "nhusers" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to nhusers+u...@**googlegroups.com. >>>> To post to this group, send email to [email protected]. >>>> >>>> Visit this group at >>>> http://groups.google.com/**group/nhusers<http://groups.google.com/group/nhusers> >>>> . >>>> For more options, visit >>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>>> . >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> Visit this group at http://groups.google.com/group/nhusers. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/groups/opt_out.
