Close to real time sync for data in relational and flat schemas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VT - 09 Mar 2008 22:43 GMT

Hi all,

Can someone point me to the right direction?

I need to synchronize data updates in relational schema with data in it's
flat (denormalized) equivalent in near real time mode.

What is the best way to achieve this if using triggers is not an option ?

Are there any proven approaches or tools (incl. 3rd party) which do that ?

Is there more appropriate microsoft.public.sqlserver.server sub-group for
this topic ?

Any good idea or suggestion is greatly appreciated,

Thank you,
VT

Reply to this Message

David Portas - 09 Mar 2008 23:15 GMT

> Hi all,
>
[quoted text clipped - 14 lines]
> Thank you,
> VT

A denomalized schema is simply the result of joins in a normalized schema.
That's easily achieved just by using views.

I suspect you want something more than a denormalized schema though. Are you
talking about data warehousing? If so there is a whole industry of products
to support you but I hesitate to suggest anything without knowing more about
what you are trying to achieve.

Have you seen the new Change Data Capture feature in SQL Server 2008?

Signature

David Portas

Reply to this Message

Tibor Karaszi - 10 Mar 2008 08:56 GMT

> A denomalized schema is simply the result of joins in a normalized schema. That's easily achieved
> just by using views.

Which can be created in the source database. You can then use transaction replication to replicate
the source database and query off of the destination database (if you want to off-load reporting
from the production db, that is).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Hi all,
>>
[quoted text clipped - 23 lines]
>
> Have you seen the new Change Data Capture feature in SQL Server 2008?

Reply to this Message

Mike C# - 10 Mar 2008 03:03 GMT

> Hi all,
>
[quoted text clipped - 14 lines]
> Thank you,
> VT

Like David pointed out, you can use views, indexed views, and several other
possible alternatives including third-party tools. Can you be more specific
about your situation and ultimate goal?

Reply to this Message

VT - 10 Mar 2008 23:22 GMT

Hi all,

Thank you for your responces.

What I am trying to achieve is to search against flat schema which as it was
rightfully pointed out - same as a join.
Profiling of the search against relational schema shows bulk of the time
with each query goes into joining of the tables.

I think of flat table as a substitute for that join.

Datawarehousing solutions may not be applicable for this as they are not
designed to work in realtime. They are more like batch type processes which
happen periodically.

Using view is great, but problem is that join has outer joins and such view
has nulls and therefore cannot be indexed.

Thanks,
VT

> > Hi all,
> >
[quoted text clipped - 18 lines]
> possible alternatives including third-party tools. Can you be more specific
> about your situation and ultimate goal?

Reply to this Message