You ever have one of those conversations that makes you say, huh thats a strange coincidence? I figured I would blog this conversation I had about what the differences are between mirrored databases and an ODS. This might seem like, come on whats this guy talking about its obvious, yep I thought the same thing until a client asked me to explain further and we got into it with a surprise.
So lets define each:
- Mirror – Exact duplicate copy of your transactional (OLTP) source databases. Not to be confused with SQL Server Mirroring technology.
- ODS (Operational Data Store) – Referencing Ralph Kimball here, an interim area between the transactional (OLTP) source systems and the data mart or warehouse that has one or more data sources and combines subject areas to avoid duplication. The ODS is transactional data and does not store history and is traditionally more up to date, meaning the data latency from the source systems is smaller and very close to the transactional (OLTP) source databases.
My client said we have five different databases that do not use the same data. But, they can all be tied together as one database captures transactions that feed another database for a different portion of our business, then that feeds another, and so on and so forth. If we don’t combine the sources is it really truly an ODS or is it just a mirror? I had a good long pause on this one and then smiled and said yes. My client said what do you mean yes, which one is it? I said pick one, your database systems are inherently designed in a way that if mirrored you essentially have an ODS. The databases don’t have any duplicate data elements in different structures, and are all contained in one location (server in this case). They can be queried with the data combined easily so you have an ODS but really all it took to get you one was to build a mirror of your five databases. I also explained that they have a rare case as usually data is duplicated in different systems with a twist on it (more or different attributes, and data), and some transformation or cleansing is usually required.
So, my point is that because you mirror your transactional (OLTP) source systems doesn’t mean you have an ODS, but in rare instances like this one you may. Jen Stirrup has a really good blog post about SQL Server Denali and the place of the ODS and gets into a lot of ODS detail.