How to avoid MSDTC?

Question

Maybe this is just a stupid idea, that's why I toss it in here:

I am creating an application, using one database for base and current data (customers, articles, inventory, open invoices, ...) and one database per year for archive data (old invoices, inventory movement, ...).

There is only one nasty little problem: MSDTC. Most transactions only touch one database, but a few touch the current database and one archive year. Since all databases resign on the same server, this could be a simple transaction across two databases.

However, Linq to entities forces me to create a separate connection for each database, and here MSDTC kicks in, demanding a) to be installed and b) does slow down the whole thing.

Annoying!

Isn't there any way to get two ADO.Net entity framework models to use the same connection, both having their own current database?

I already tried to specify the database in the EF Schema, but EF does not like this and blocks it.

Very annoying. Maybe I just should move all tables into one database, but this is a pain later down the road when trying to separate the data for backup and speedup.

Answer 1

Try calling ChangeDatabase on your connection. MSDTC will always be involved if you use more than one connection. Even for one database. So may I suggest always keeping one connection around and change database via this method.

Hope this helps.