Re: 365 databases a year

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 04/18/04 

Date: Sun, 18 Apr 2004 08:22:16 -0400

Paul,
I agree with Andrew... It sounds as if you need a real-time piece and a
historical reporting piece. It is generally good to separate the two. But
splitting the things up into multiple databases one per day will end up
biting you in the ***... Imagine your boss asking you to compare some
information on a monthly basis. You'd had to get information from many
databases to put in a single report.... It would get problematic quickly...
Since separation is still a good idea, start with separating the current
data from the historical data... On the history tables put an effective date
column... When a new row is added to the employee table, add it to the
history table as well with a current date of today... You may even consider
putting ALL of the history into a separate database from the current info
.....

Good luck.... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <a@b.com> wrote in message
news:ua0%23uyAJEHA.3704@TK2MSFTNGP11.phx.gbl...
> Okay, please be patient with me while I digest.  I am an application
> developer, not a DBA, and so I may not have all the right angles here
(which
> is why I am writing here).  Can you clarify "storing a timestamp in a
single
> database"?  I assume you mean storing a timestamp in every applicable
table?
>
> Just about every row of data in my database needs to be timestamped.  So
> when a datum is changed, it needs to be done with an insert of a new row
> with the current date (intra-day changes overwrite).  So, if I add an
> employee record with the name column as "Paul" on 1/1/04 (id=1), and
another
> with a name of "Fred" on 2/1/04 (id=2), and two months later we want to
> change Paul's name to "Pablo", I would insert a new row with the name
column
> as "Pablo" on 3/1/04 (id=1 still).  Now, my boss asks me to get the names
of
> all the employees on 2/1/04.  I SELECT id, Max(date) as max_date FROM
> employees where date <= 2/1/04 and then re-select where the id and
max_date
> match.
>
> This is not _terrible_ in the simple case, above, but now my boss wants me
> to join on the positions table, and perhaps a few other tables that are
time
> stamped as well.  It is starting to get ugly.
>
> So, I figure, hey, I can do away with the timestamp altogether if I just
> back up the database every time it changes.  If I am careful about using
> stored procedures to access data, and source control my application with
> corresponding database changes, then all my selects and joins are just the
> simple case, which is 99.9% of my needs (I will rarely need to go back to
a
> view of the database, but when I do, I want to do it quickly, adn not have
> to have someone call me up to do a restore for a particular date on a
> separate system).  I was figuring that we were doing a backup of the
> database everyday anyway, why not mount it as a new database with the name
> as a date.
>
> If, however, there is a simpler way to do this that I am not aware of,
> please, feel free to let me know!
>
> Thanks,
>
> -Paul
>
>
> "Adam Machanic" <amachanic@air-worldwide.nospamallowed.com> wrote in
message
> news:egfxIk$IEHA.3308@TK2MSFTNGP10.phx.gbl...
> > Why?
> >
> > What's wrong with just storing a timestamp in a single database?
> >
> > This scheme will give you an incredible maintenance and development
> > nightmare, especially should you choose to change a column definition
> (want
> > to do that across 500 databases?  I don't)...  Or if you ever wish to
pull
> > historical aggregate data, please get a book because you'll need some
> > entertainment while waiting for your query to pull everything together
> from
> > 500 tables across 500 databases.  And I don't know who's going to write
> that
> > query :)
> >
> > Anyway, the point is, unless you have a REALLY good reason, you should
not
> > do this... And a million rows of historical data will not affect your
> > performance in the slightest bit if you tune your indexes properly.
> >
> >
> >
> > "Paul" <a@b.com> wrote in message
> > news:ueJzsB$IEHA.1388@TK2MSFTNGP09.phx.gbl...
> > > Would it be possible to make a daily database version to avoid adding
a
> > data
> > > date to every piece of data in the system.  For example, if I have a
> > > database named MyDatabase, at the end of processing for the day of
> > 4/16/04,
> > > I make a backup, and restore the database as MyDatabase-2004-16-04.
> This
> > > process would continue every day we run processing.  We would probably
> > only
> > > need real time access to 18 months of data, and would delete the
oldest
> > > ones.  Our database is probably not going to get very large, one or
two
> > > dozen tables, a million or so rows at most.
> > >
> > > What things do you think I would need to worry about in doing
something
> > like
> > > this?  The "old" databases would not need to be referenced all that
> often,
> > > and could even be stored on a second server so they don't interfere
with
> > the
> > > "current" database's performance.  I am concerned that SQL Server
might
> > have
> > > a problem with having 550 databases defined.
> > >
> > > Thanks,
> > >
> > > -Paul
> > >
> > >
> >
> >
>
>