sql server programming: Reporting -- sql server programming

Reporting

Yan
12/19/2006 11:40:16 PM

sql server programming: Using sql server 2005.

We are in the stage of redesign to an existing web application. In this
process we intend to break the current application into many small
applications so when we talk about database we will have 20 databases
instead of 1, where every database may be phisicaly located on a different
server.

My concern is regarding quering data(Reporting). At the moment I see 2
options available; distributed queries and having a unified database for
queries using an ETL process.

My question is, first is there any other options apart from the 2 options
mentioned above and what is the advantage /disadvantage of each options.

Additional information:

--we need data to be updated up to the hour (run the ETL every one hour).

--the size of the data is around 8GB

Thanks,

Yan

------------------

Re: Reporting

AlterEgo
12/20/2006 9:03:16 AM

Yan,

I am assuming you are capturing Web transactions in one form or another.
Distributed queries against OLTP tables will kill you. You may want to
consider replication. There are many combinations of configurations to
choose from., and one might fit your needs.

If ETL is better, one method that you may want to consider is to partition
these transactions in hourly bucket tables. Trans00, Trans01 ... Trans24.
That way, you avoid the contention of reading the data while transactions
are being slammed into the same table. Also, you can truncate the table
after the ETL task is complete - way less expensive than deletes.

-- Bill

[quoted text, click to view]