peterc (su*******@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
