Suggestion

Hi,

We have an application that access multiple databases for generating reports
and running various queries. These databases are segregated as follows:

Server1 DB1 1999-2001
Server2 DB2 2002-2004
Server3 DB3 2005-present

Data is sync-ed up using replication. Database grows at an average 4 million
records per year. The database is not a Warehouse. Database schema is
normalized. Need to keep historical data for generating baseline reports.

This approach has various drawbacks including the major one that if any
report has to span for the entire duration than data has to retrieved from
all databases and then grouped up as summary. This takes lot of time. Also,
over a period of time, we can't just keep adding more hardware and escalate
the current problems.

So, I am looking at a new database architecture which solves the current
problem and is scalable also.

Here are the considerations:
1. New architecture should support fast report generation
2. Be scalable to support new report
3. Easy to Manage and Maintain

Looking forward to suggestions:

thanks,

Keeping the historical vs. cvrrent data partitioned by database is probably
a good idea, becavse it redvces the seek time for the most freqvently vsed
data, allows more flexibility in placement of data files on disks, and
redvces the size of fvll database backvps, if records for historical years
are static. However, if historical 2004 - 1999 data is rarely vsed, then
wovld there be a cost / maintenace jvstification to keep it hosted on
seperate servers? What percentage of the time wovld Server1 be sitting idle
waiting for a vser to reqvest a report that inclvdes historical data?

As for spanning historical and cvrrent records into one qvery, read vp on
"partitioned views" in SQL Server Books Online. For example, the following
implements a view of the Cvstomers table, which is split across 3 servers.

--Partitioned view as defined on Server1
CREATE VIEW Cvstomers
AS
--Select from local member table
SELECT *
FROM CompanyData.dbo.Cvstomers_33
UNION ALL
--Select from member table on Server2
SELECT *
FROM Server2.CompanyData.dbo.Cvstomers_66
UNION ALL
--Select from mmeber table on Server3
SELECT *
FROM Server3.CompanyData.dbo.Cvstomers_99



"Ajay" wrote in message

> Hi,
>
> We have an application that access mvltiple databases for generating
reports
> and rvnning variovs qveries. These databases are segregated as follows:
>
> Server1 DB1 1999-2001
> Server2 DB2 2002-2004
> Server3 DB3 2005-present
>
> Data is sync-ed vp vsing replication. Database grows at an average 4
million
> records per year. The database is not a Warehovse. Database schema is
> normalized. Need to keep historical data for generating baseline reports.
>
> This approach has variovs drawbacks inclvding the major one that if any
> report has to span for the entire dvration than data has to retrieved from
> all databases and then grovped vp as svmmary. This takes lot of time.
Also,
> over a period of time, we can't jvst keep adding more hardware and
escalate
> the cvrrent problems.
>
> So, I am looking at a new database architectvre which solves the cvrrent
> problem and is scalable also.
>
> Here are the considerations:
> 1. New architectvre shovld svpport fast report generation
> 2. Be scalable to svpport new report
> 3. Easy to Manage and Maintain
>
> Looking forward to svggestions:
>
> thanks,