There exists a stabilized SQL Server 2008 database designed using generic tables. So, rather than getting another table for every entity (e.g. Items, Orders, OrderItems, etc), we've generic tables (Organizations, Instances, Associations, Characteristics, etc).

We've made the decision to possess a separate denormalized database for convenient retrieval of information. Would you please advise me of numerous technologies available to synchronize these 2 databases, presuming they've different schemas?

Cheers, Mosh

When two databases have so significantly different schemas you ought to be searching at approaches for data migration or replication, not synchronization. SQL Server provides two technologies with this, SSIS and Replication, or write your personal script to get this done.

Replication will require new or modified data from the source database and copy it to some target database. It offers systems for arranging, packaging and disbursing changes and may handle both real-time in addition to batch updates. To operate it must add enough info both in databases to trace modifications and matching rows. Inside your situation it might be difficult to identify which "Items" have transformed as you would need to identify all relevant modified rows in 4 or even more different tables. It is possible but it may need some effort. Regardless, you would need to create sights that match the prospective schema, as replication does not allow any transformation from the source data.

SSIS will pull data in one source, change it and push it to some target. It's no built-in systems for monitoring changes so you'll have to add fields for your tables to trace changes. It's strictly a load procedure that can run based on an agenda. The primary benefit is you can perform a multitude of changes while replication enables almost none (aside from drawing the information from the view). You can create dataflows that modify just the relevant Product area whenever a Product related Attribute record changes, or just reconstitute a whole Product record and overwrite the prospective record.

Finally, you may create your personal triggers or saved methods which will run once the data changes and copy it in one database towards the other.

I ought to also explain you have most likely over-stabilized your database. In most three cases you'll have some performance penalty whenever you join all tables to reconstitute an entity, producing a bigger quantity of securing that's necessary and inefficient utilization of indexes. You're compromising performance and scalability with regard to easy change.

Possibly you need to have a look in the Sparse Column feature of SQL Server 2008 for the way to aid flexible schemas while keeping performance and scalability.