Database Design for a data extensive application

LVL 10
Who is Participating?
YveauConnect With a Mentor Commented:
Transactional replication would fit best here.
... ehm, not quite sure I understand the second Q. As you run a replication service here is what you do:
a. you define a publication (you point out hat data will be replicated)
b. you create a subscription (you tell which server is going to receive the published data)
c. you synchronize once in a while by means of the subscription to the publication (you run the job and all transactions that happened since the last contact between the publisher and the subscriber are copied to the subscriber, making it a perfect copy of the publisher again)

So when you dump the data from the publisher database into another database, that would not affect the replication mechanism. I'm thinking of a BCP action or backup, or select into or ... whatever, as long as you don't restore the publisher database, you'll be fine. after a restore of the publisher, you need to setup the replication again.

Try this site:

Hope this helps ...

1. Any comments/suggestion on above design.
Why not use SQL Server 2005 replication in stead of "a service that will fetch the data from transactional database, compile it, and dump into the reporting database" ? It's perfect for the job described and does all the error handling and other stuff for you. Note that you do NOT want mirroring, replication is the feature you need.

2. Any other better idea for load balancing for the above situation.
No, that is the it should be done ! I've set up this scenario many times, and it works like a charm.

Hope this helps ...
imrancsAuthor Commented:
Yveau, thanks for your input.

I have a few questions on replication:

1. What type of replication will fit for this scenario ?
2. Does replication supports operations on data prior to dump into another database ?


imrancsAuthor Commented:
I just got comments on what I was designing, no new ideas for which I was looking for. Anyhow, this was quite helpful.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.