+972 52-548-6969
Remote Keys: Cross Database Foreign Keys
time to read 2 min | 264 words
Here is an interesting product (via Larkware). RemoteKeys Adds cross-database constraints to SQL Server, which is something that is really nice in many scenarios.
Specifically, I am a firm believer that database == application, and that separate applications should use separate databases. (Versioning, deployment, WhoBrokeMyQuery, etc). This product seems to give a nice answer to Referential integrity concerns.
I was interested to know how they did it, and it looks like a good UI on top of Linked Server + INSTEAD OF Triggers. I really like the idea, but a couple of things needs to be considered when using it are:
- Availability concerns - Does both databases have the same availability? If not, what happens when either database goes down? The way it works, both databases needs to talk to each other for CUD.
- Performance concerns - If we are talking about remote databases, then it is something that you really should consider carefully. The example on their site has US Orders and Europe Orders with remote FK to Customers Database. The problem is that this means that the Customers database needs to issue two remote calls (probably per statement). There may be some smarts there about consolidating checks, but I didn't check.
Cool idea nonetheless, although if I ever see the need, I will create the trigger via code gen and be done with it.
Comments
There's another important thing: if you're using something like this and doing cross-database transactions (usually distributed transactions), then you need to backup the databases using transaction marks so that both databases are consistent at the point they are backed up.
This is one of the reasons BizTalk uses its own (pretty complex) database backup jobs instead of the built-in backup mechanims in SQL Server, btw.
Ouch, didn't even consider that nest of worms
Well, to be fair, you'd only need that if you need to support "live backups" on a highly scalable, transactional system. If you can afford a backup window with full downtime, then you can still backup the regular way as you can guarantee there will be no live transactions during that time.
Oren,
This is what Base4 does too. It creates a view in the central database, and puts instead of insert/update/delete triggers over it to make it act just like a table. Works really well.
Alex
How do you handle the issues that I brought up? Or are they not really relevant in practice.
Oren,
I come from the ostrich school of programming... ;)
Just joking. No actually my theory is this, simply having access to information in a seperate database through a query like this:
context.Find<BugFile>(BugFile.Fields.Bug.AssignedTo == "alex@somewhere.com");
where BugFile is an entity stored in the core database, and Bug is a FK to a record stored in a seperate database (i.e. Fogbugz or something similar) is good enough.
Yes performance is not ideal, but it is much much better than the alternative (i.e. SOA style integration). And in Base4 most of the time the expectation is it is a different database on the same server so the uptime issue while still valid is less important.
I mean if you start going cross server you have to worry about things Collation etc.
Comment preview