Ayende @ Rahien

It's a girl

RavenDB: Replicating to a relational database

I just finished implementing a very cool feature for RavenDB, the Index Replication bundle allows you to replicate an index to a relational database.

What does this mean? Well, consider the following document:

var q = new Question
 {
     Title = "How to replicate to SQL Server?",
     Votes = new[]
     {
         new Vote{ Up = true, Comment = "Good!"}, 
         new Vote{ Up = false, Comment = "Nah!"}, 
         new Vote{ Up = true, Comment = "Nice..."}, 
     }
 };

And this index:

from q in docs.Questions
select new 
{
         Title = q.Title, 
         VoteCount = q.Votes.Count
}

With the aid of the Index Replication bundle, that index will be replicated to a relational database, giving us:

image

You can find full documentation for this feature here and the bundle itself is part of RavenDB’s unstable as of build 159.

Comments

configurator
09/22/2010 08:44 AM by
configurator

Very cool feature!

You have a type in the documentation:

"You can directly replicate between a document and a RDBMS table, because the format of a document simply cannot be made to work on a relational database."

Should probably be "You can't ..."

Jeff Handley
09/22/2010 10:35 AM by
Jeff Handley

Nice! I'm getting closer and closer to wanting to use RavenDB. :-)

Khalida Abuhakmeh
09/22/2010 11:46 AM by
Khalida Abuhakmeh

When does the replication happen?

  • is it triggered with every new document that is added to that index ?

  • is it on demand (press this button here) ?

  • is it a timed job ?

When I first heard about this i thought "Why would I want my index definition stored in a database?" Now i understand you meant the index result set is replicated into the database.

This is a great bundle for teams who have members more comfortable doing reporting with SQL.

Thanks.

tobi
09/22/2010 12:08 PM by
tobi

This is very very useful as it makes it very easy to do mixed data storage with sql server still in use. This will make migration a lot easier too. I think you should advertise this feature a bit to see if it might be a killer one.

You could also try to replicate json trees as xml for which sql server has great support.

Ayende Rahien
09/22/2010 12:17 PM by
Ayende Rahien

Configurator, thanks, I fixed that.

Ayende Rahien
09/22/2010 12:20 PM by
Ayende Rahien

Tobi,

Xml fields in SQL Server are... workable, but they don't really work nice with reporting tools.

tobi
09/22/2010 12:40 PM by
tobi

Another idea would be to replicate from sql server to ravendb as a means of having stacked materialized views (which sql server does not support yet). You can implement this in a number of different (complex) ways: CDC, replication infrastructure, triggers, change notification/sqldependency class.

I was proposing xml fields as a means of making sql server behave more like a document storage. I am sure there are cases where the performance benefit justifies the complexity.

Ayende Rahien
09/22/2010 01:05 PM by
Ayende Rahien

Tobi,

I would be pretty trivial to do the reverse using triggers, but I am not sure what it would give you.

What do you mean, stacked materialized views?

tobi
09/22/2010 01:33 PM by
tobi

Materialized views can only reference tables, not other materialized views. So you can only get one level deep. It could be very convenient to have nested materialized views to build up more complex structures. I have encountered the need multiple times (I wanted to do two level aggregation and the other time I wanted to express exists queries through joins and groupings over multiple levels).

nested materialized views = stacked materialized views.

research.microsoft.com/.../Views-on-Views.pdf

Also I would have liked to pre-materialize a xml/json document that describes every piece of data needed for a particular url. This is not possible using sql server indexed views but would be quit a killer. Image you could stitch all information required for a whole forum thread (thread-info, posts, users, their reputation and badges, ...) up into one json document by creating a tree of raven indexes that ultimately result in a table with pk (threadid) and a json document containing all required data. this would allow you to write normalized data in sql server (convenient and immune to data anomalies) and read in a document-oriented way from raven (also convenient and very fast). not sure if this is possible today as I believe raven indexes cannot join or build tree structures but I hope you see the use case. Low dev costs and blinding performance. certainly a complex feature set to implement.

Ayende Rahien
09/22/2010 01:39 PM by
Ayende Rahien

Tobi,

You can certainly do it today using Raven.

Comments have been closed on this topic.