|
|
Posted by Erland Sommarskog on 06/23/06 10:38
peterc (subscribe@cooperzone.net) writes:
> I have an existing database with approx 500,000 rows and accessed by a
> few hundred users per day creating approx 1,000 new records per day
> plus typical reporting - relatively low volume stuff for SQL Server.
>
> I'm about to add a process that will be importing data daily from
> legacy databases and summarizing it for reporting purposes, integrating
> it with the existing database. This volume of data will be considerably
> higher, perhaps 100,000+ rows per day, which will be deleted once it
> has been summarized and the results written to some intermediate
> tables.
>
> Is there any concern about mixing different levels of volume within one
> database? As I'll be creating lots of rows daily and then deleting them
> I was wondering about fragmentation, transaction logging etc. and
> whether having this processing in a separate database from the main
> application would be 'better'.
It's difficult to say, because the answer depends on many more things than
just the volumes. If you go with two databases, is there lookup data that
needs to be both databases and maintained separately? What about access
to the databases, would you need to maintain users in both databases?
If the other process is completely independent of the stuff which is
in the database now, there is all reason to keep it separate. But if
there is interaction between the two sets of data, I would hesitate to
make the split.
Of course, the database that gets these 100000 rows/day, needs more
attention for maintenance jobs, mainly defragmenting. As for the
transaction log, I get the impression from your description that you
could make it fine with simple recovery. But if you need full recovery,
you will need to back up the transaction log more often.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|