Solved

Database Design for a data extensive application

Posted on 2007-11-19
4
158 Views
Last Modified: 2013-11-13
0
Comment
Question by:imrancs
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20311288
Imran,

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 ...
0
 
LVL 10

Author Comment

by:imrancs
ID: 20326445
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 ?

Imran



0
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20330397
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:
http://msdn2.microsoft.com/library/ms151198.aspx

Hope this helps ...
0
 
LVL 10

Author Closing Comment

by:imrancs
ID: 31409901
I just got comments on what I was designing, no new ideas for which I was looking for. Anyhow, this was quite helpful.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Homework Help 5 69
Need help with a query 6 66
Selection from table2 where criteria for table1 10 31
Can Unique column have more than one Null? 8 43
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now