April 27, 2009 at 11:05 am
We're trying to determine the best architecture for our transactional databases. They are currently SQL 2000, but we are upgrading to 2008, adding replication and cdc.
We are considering two options:
1)Enabling CDC on the Transactional database and using ETL to move data out of CDC tables to a single Reporting Databases server. Real-time reports run against both Transactional and Reporting database servers.
2)Replicating the Transactional databases via 2 Distributor Database Servers to 3 Reporting Database Servers with CDC enabled on the Reporting Database Servers. Real-time reports run off of the Reporting database.
Currently, the reporting against the transactional databases does not impact us all that much(except for year-end of course :-D).
Like most transactional db's, selects outnumber inserts, updates, and deletes, so it would seem that enabling CDC on the replicated server would make sense. Comments?
As for replication, we are thinking that transactional replication using the pull method would lighten the load on the live Db servers, and give us better 'recoverability' when we bring servers down for patching. In other words, we're thinking pull replication is better suited than push to pick up where it left off after a SQL restart. Am I off-base with that?
Finally, I see articles that mention putting the distribution database on the publisher or a third server, separate from the subscriber. Can the distribution database reside on the same server as the subscribers?
Thanks in advance for your comments.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply