Reporting solution

I was interested in knowing (my experience is limited) what might be the best
solution when using Reporting Services to return data that can be 15 minutes
old, tops. We are looking to have a separate, second data set dedicated to
this reporting. Is a type of replication best suited? Any other ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1I've been successful reporting from production tables with hundreds of
millions or rows of live data, hundreds of new rows per minute. Since the
tables have clustered indexes on either sequential entry OR time fields, the
reporting queries are virtually instantaneous.
What is the clustering index on the table?
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622a2e85990f8@.uwe...
>I was interested in knowing (my experience is limited) what might be the
>best
> solution when using Reporting Services to return data that can be 15
> minutes
> old, tops. We are looking to have a separate, second data set dedicated to
> this reporting. Is a type of replication best suited? Any other ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||This is ad-hoc reporting that will be instituted. We do not want ad-hoc
reporting on our production databases so we are looking at an alternative
solution.
Arnie Rowland wrote:
>I've been successful reporting from production tables with hundreds of
>millions or rows of live data, hundreds of new rows per minute. Since the
>tables have clustered indexes on either sequential entry OR time fields, the
>reporting queries are virtually instantaneous.
>What is the clustering index on the table?
>>I was interested in knowing (my experience is limited) what might be the
>>best
>> solution when using Reporting Services to return data that can be 15
>> minutes
>> old, tops. We are looking to have a separate, second data set dedicated to
>> this reporting. Is a type of replication best suited? Any other ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||In that case, some form of replication is in order -look into MERGE
Replication.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622a9e2ed3ce4@.uwe...
> This is ad-hoc reporting that will be instituted. We do not want ad-hoc
> reporting on our production databases so we are looking at an alternative
> solution.
> Arnie Rowland wrote:
>>I've been successful reporting from production tables with hundreds of
>>millions or rows of live data, hundreds of new rows per minute. Since the
>>tables have clustered indexes on either sequential entry OR time fields,
>>the
>>reporting queries are virtually instantaneous.
>>What is the clustering index on the table?
>>I was interested in knowing (my experience is limited) what might be the
>>best
>> solution when using Reporting Services to return data that can be 15
>> minutes
>> old, tops. We are looking to have a separate, second data set dedicated
>> to
>> this reporting. Is a type of replication best suited? Any other ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks Arnie. Can you verify something, please? I have been reading BOL on
the different types of replication: Merge, Transactional, and Snapshot. I am
trying to determine what is the best for our situation. It sounds like when
using Snapshot replication (if data on the subscriber could only be 15
minutes old at tops), that applying the Snapshot every 15 minutes, if a user
tried to generate a report while the Snapshot was being applied, then the
user might not have very good response time, depending on the size of the
Snapshot. Is that correct?
Arnie Rowland wrote:
>In that case, some form of replication is in order -look into MERGE
>Replication.
>> This is ad-hoc reporting that will be instituted. We do not want ad-hoc
>> reporting on our production databases so we are looking at an alternative
>[quoted text clipped - 15 lines]
>> to
>> this reporting. Is a type of replication best suited? Any other ideas?
--
Message posted via http://www.sqlmonster.com|||Snapshot Replication takes a 'snapshot' of the ENTIRE database and
replicates it to the subscriber.
Depending upon the size of the database, that could be a substaintial
performance issue. Imagine, 400 new rows of data, but 4 million rows (and
growing) transferred each time. Probably not a good plan -except for the
first time.
Transactional Replication is the best choice since the reporting server is
not changing any data and you are only concerned with the new data since the
last replcation update.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622b2eda71445@.uwe...
> Thanks Arnie. Can you verify something, please? I have been reading BOL on
> the different types of replication: Merge, Transactional, and Snapshot. I
> am
> trying to determine what is the best for our situation. It sounds like
> when
> using Snapshot replication (if data on the subscriber could only be 15
> minutes old at tops), that applying the Snapshot every 15 minutes, if a
> user
> tried to generate a report while the Snapshot was being applied, then the
> user might not have very good response time, depending on the size of the
> Snapshot. Is that correct?
> Arnie Rowland wrote:
>>In that case, some form of replication is in order -look into MERGE
>>Replication.
>> This is ad-hoc reporting that will be instituted. We do not want ad-hoc
>> reporting on our production databases so we are looking at an
>> alternative
>>[quoted text clipped - 15 lines]
>> to
>> this reporting. Is a type of replication best suited? Any other ideas?
> --
> Message posted via http://www.sqlmonster.com