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,
-PaulAll that just to avoid adding a DATETIME column? Boy you have more time on
your hands than anyone I know<g>.
Andrew J. Kelly SQL MVP
"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
>|||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
>|||Actually, time-wise this is a lay-up. Every night the backup/restore
process runs, and now I have a fixed view of the universe at that moment.
We can go back and see it without any fear of manipulation of the data. The
hard work I was trying to avoid was that to look at the data for a given, I
would have to do a Max(date) on all the data I was interested in prior to a
date, and then select based on that value (some data elements won't have
data for any given day). This would have made joins pretty ugly.
So in essence, yes, all that just to avoid adding a DATETIME column. The
application code is quite simple to write and maintain at that point. And
disk space is cheap now. Frankly, I think it is quite an elegant solution.
But the question still remains. What problems is SQL server going to give
me?
Thanks,
-Paul
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23OYNMY$IEHA.3216@.tk2msftngp13.phx.gbl...
> All that just to avoid adding a DATETIME column? Boy you have more time
on
> your hands than anyone I know<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <a@.b.com> wrote in message
> news:ueJzsB$IEHA.1388@.TK2MSFTNGP09.phx.gbl...
> data
> 4/16/04,
This[vbcol=seagreen]
> only
> like
often,[vbcol=seagreen]
> the
> have
>|||Will SQL Server handle it OK, probably. Will you regret it later, probably.
Andrew J. Kelly SQL MVP
"Paul" <a@.b.com> wrote in message
news:uTlfjk$IEHA.3576@.tk2msftngp13.phx.gbl...
> Actually, time-wise this is a lay-up. Every night the backup/restore
> process runs, and now I have a fixed view of the universe at that moment.
> We can go back and see it without any fear of manipulation of the data.
The
> hard work I was trying to avoid was that to look at the data for a given,
I
> would have to do a Max(date) on all the data I was interested in prior to
a
> date, and then select based on that value (some data elements won't have
> data for any given day). This would have made joins pretty ugly.
> So in essence, yes, all that just to avoid adding a DATETIME column. The
> application code is quite simple to write and maintain at that point. And
> disk space is cheap now. Frankly, I think it is quite an elegant
solution.
> But the question still remains. What problems is SQL server going to give
> me?
> Thanks,
> -Paul
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23OYNMY$IEHA.3216@.tk2msftngp13.phx.gbl...
> on
a[vbcol=seagreen]
> This
oldest[vbcol=seagreen]
two[vbcol=seagreen]
something[vbcol=seagreen]
> often,
with[vbcol=seagreen]
might[vbcol=seagreen]
>|||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...
> data
> 4/16/04,
This[vbcol=seagreen]
> only
> like
often,[vbcol=seagreen]
> the
> have
>|||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).
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
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OlCq$OAJEHA.3216@.tk2msftngp13.phx.gbl...
> Will SQL Server handle it OK, probably. Will you regret it later,
probably.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <a@.b.com> wrote in message
> news:uTlfjk$IEHA.3576@.tk2msftngp13.phx.gbl...
moment.[vbcol=seagreen]
> The
given,[vbcol=seagreen]
> I
to[vbcol=seagreen]
> a
The[vbcol=seagreen]
And[vbcol=seagreen]
> solution.
give[vbcol=seagreen]
time[vbcol=seagreen]
adding[vbcol=seagreen]
> a
probably[vbcol=seagreen]
> oldest
> two
> something
> with
> might
>|||Paul,
It's pretty much imposable to design a database in a newsgroup post due
mainly to lack of all the details but it sounds like you need archive
tables. Essentially each table that needs this "versioning" will have to
have an almost identical table that will server as a history table. It will
have all the same columns but not the same constraints such as PK
constraint. Then you add triggers to each of the main tables that will
insert a copy of the current row before any change is made with the datetime
of the event. Then your app will address the main tables as normal and can
address the history tables when needed. You would have to filter the
queries on the datetime range that your after.
Andrew J. Kelly SQL MVP
"Paul" <a@.b.com> wrote in message
news:ORe90ECJEHA.232@.TK2MSFTNGP12.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).
> 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
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OlCq$OAJEHA.3216@.tk2msftngp13.phx.gbl...
> probably.
> moment.
data.[vbcol=seagreen]
> given,
> to
have[vbcol=seagreen]
> The
> And
> give
> time
> adding
a[vbcol=seagreen]
MyDatabase-2004-16-04.[vbcol=seagreen]
> probably
or[vbcol=seagreen]
that[vbcol=seagreen]
interfere[vbcol=seagreen]
>|||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 butt... 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...
> (want
pull[vbcol=seagreen]
> from
> that
not[vbcol=seagreen]
a[vbcol=seagreen]
> This
oldest[vbcol=seagreen]
two[vbcol=seagreen]
something[vbcol=seagreen]
> often,
with[vbcol=seagreen]
might[vbcol=seagreen]
>
No comments:
Post a Comment