I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.
The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.
I figured I'd add the needed code to the stored procedure that's doing the update for the record.
When the stored procedure is called to do the update, the PK and parameters are sent.
The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:
Name, DateTime, Changes
FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204
How does the above approach sound?
Is there a better way you'd suggest?
Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?
Thanks,
PeterHave you considered using a trigger? You can use the inserted and deleted tables to compare your data without having to save it manually. Then insert your data into the history table as you suggested.
We often just save the before image to the history table along with the type of operation performed, the id that was used to perform it and a time stamp. Inserted records are not recorded (because all their data is already recorded on the live table) but deleted ones are. The differences for updated records can be determined at any time by comparing the before image to the next or previous stored image or the current actual record. We rarely actually look at this sort of history however unless data disappears or the customer tells us that there is something else wrong with the data and we need to trace what happened to it.|||ejustuss - thanks for the thoughts.
Good idea about simply saving the before image prior to the actual save of the new one.
Our users are used to systems where they can click a button and see essentially the change history of the record. This particular system is a Work Order system. The status of the WO changes over time, etc.
In other non-SQL Server systems I've developed I have a routine that prior to actual save:
1. saves "before save" copy of record
2. updates the record with new values into the DB
3. peels off an "After save" copy of record
4. runs a routine that compares side by side each field. Any changes are noted in a text variable (field name, new value).
5. Once all the fields are spun thru (compared), if there is any information in the text variable, a "change table" record is created with FK to the parent record, Who Changed it, When changed, and a single text field describing all the changes.
Weeks later when a user is viewing the particular record, they can press a button and have a query run against the change table to bring up a simple list of what changed when.
One wrinkle is that a Work Order has a huge text area. Once a WO is accepted by a lab, this text are becomes "frozen". So if we simply peel off a before save copy each time a user specifies an update - I wouldn't want to needlessly include this particular field due to space considerations.
Bottom line - I was assuming someone might have a canned routine for spinning thru a record comparing all field values against an identical layed out record. I figured there might be a system function or 2 to:
1. Identify how many fields are in a table
2. identify the content of a field - something like @.@.Field(i)
where i=1 to number of fields in the table.
Peter|||I don't know of any function like that although I am sure you can write one.
In triggers you can use IF UPDATE(column) to test if a column is updated but you still have to use the column names which means writing a different trigger for each case . The data in text fields will probably not be accessible within the trigger.
You don't have to save all the fields if you do a before image either just the fields you want to compare in case they are updated.
Showing posts with label approach. Show all posts
Showing posts with label approach. Show all posts
Tuesday, March 27, 2012
History/archive database
Anybody have any favorite approach to recommend regarding archiving?
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
Josh
Josh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>
|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basis
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier as
>the size increases in general. One thing you may want to look at is SQL2005
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change your
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
Josh
Josh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>
|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basis
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier as
>the size increases in general. One thing you may want to look at is SQL2005
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change your
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
History/archive database
Anybody have any favorite approach to recommend regarding archiving?
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
JoshJosh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.
4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basi
s
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier a
s
>the size increases in general. One thing you may want to look at is SQL200
5
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change you
r
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
JoshJosh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.
4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basi
s
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier a
s
>the size increases in general. One thing you may want to look at is SQL200
5
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change you
r
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
History/archive database
Anybody have any favorite approach to recommend regarding archiving?
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
JoshJosh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basis
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier as
>the size increases in general. One thing you may want to look at is SQL2005
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change your
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
We have an OLTP database that we'd like to keep small in the interest
of speed, and on the other hand need to keep virtually all records
around for seven years for auditing and tax purposes. So, the obvious
thing is to have a process that removes closed or otherwise obsolete
logical entities from the OLTP database, and some other process that
moves them to the archive, before or after being removed from OLTP.
(I have done this before and understand the many problems involved in
trying to declare something "closed or otherwise obsolete", but am
looking for discussion, latest ideas, best practices, etc)
We've had suggestions on one side to keep only the hottest records on
the OLTP side, and then try to make it invisible when a query needs to
access something "historical" (like yesterday). I'm more accustomed
to end-of-year processes to clean stuff out. But, we may also want to
duplicate (or replicate) stuff out on a daily or intra-day basis, in
order to run as many reports as possible on the archive instead of the
OLTP, potentially even current YTD reports.
So, many questions, like should the archive database share the OLTP
schema, or be a DW-style star, or something else? Thinking seems to
be that for now, yes we'll keep the archive on the same schema, and
sometime in the next year or three we'll make the transition to a
star. ETL should be custom-written DTS/SQL code rather than, say,
merge replication. Latency should be about 24 hours from OLTP to
archive for duplication, and about once a year for removal from OLTP.
Sound reasonable, or does anything here sound like a mistake?
Thanks.
JoshJosh,
It's pretty tough to give enough info in a newsgroup post to warrant the
best advise for something like this. There are many things to consider and
knowing more about how your environment is set up and works on a daily basis
will play a big role in how to do this. Some things to keep in mind though.
Smaller tables do not necessarily mean faster. With proper indexing size
does not matter as much for a true OLTP system. Maintenance gets trickier as
the size increases in general. One thing you may want to look at is SQL2005
and the partitioning features. You don't say how the rows are determined to
be "Closed" but partitioning may give you some options that will change your
mind.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:hgf7o1pasmbsi3um88a6cmq6511d447dd6@.4ax.com...
> Anybody have any favorite approach to recommend regarding archiving?
> We have an OLTP database that we'd like to keep small in the interest
> of speed, and on the other hand need to keep virtually all records
> around for seven years for auditing and tax purposes. So, the obvious
> thing is to have a process that removes closed or otherwise obsolete
> logical entities from the OLTP database, and some other process that
> moves them to the archive, before or after being removed from OLTP.
> (I have done this before and understand the many problems involved in
> trying to declare something "closed or otherwise obsolete", but am
> looking for discussion, latest ideas, best practices, etc)
> We've had suggestions on one side to keep only the hottest records on
> the OLTP side, and then try to make it invisible when a query needs to
> access something "historical" (like yesterday). I'm more accustomed
> to end-of-year processes to clean stuff out. But, we may also want to
> duplicate (or replicate) stuff out on a daily or intra-day basis, in
> order to run as many reports as possible on the archive instead of the
> OLTP, potentially even current YTD reports.
> So, many questions, like should the archive database share the OLTP
> schema, or be a DW-style star, or something else? Thinking seems to
> be that for now, yes we'll keep the archive on the same schema, and
> sometime in the next year or three we'll make the transition to a
> star. ETL should be custom-written DTS/SQL code rather than, say,
> merge replication. Latency should be about 24 hours from OLTP to
> archive for duplication, and about once a year for removal from OLTP.
> Sound reasonable, or does anything here sound like a mistake?
> Thanks.
> Josh
>|||On Wed, 23 Nov 2005 09:56:07 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>It's pretty tough to give enough info in a newsgroup post to warrant the
>best advise for something like this. There are many things to consider and
>knowing more about how your environment is set up and works on a daily basis
>will play a big role in how to do this. Some things to keep in mind though.
>Smaller tables do not necessarily mean faster. With proper indexing size
>does not matter as much for a true OLTP system. Maintenance gets trickier as
>the size increases in general. One thing you may want to look at is SQL2005
>and the partitioning features. You don't say how the rows are determined to
>be "Closed" but partitioning may give you some options that will change your
>mind.
Andrew,
Thanks for the comments.
I'm busy trying to identify all the info that I can in our
environment, so couldn't even give it all yet in any case.
On size, I agree that a well-designed database should be relatively
insensitive to size, and I sure hope that's true, because in this our
new application we've been running it against about a 3gb database,
but as we're ramping up production it should start growing about
1gb/month now, and 5gb/month soon, so if we're going to be able to
even store one year's data in the main OLTP database, it better scale
gracefully from 3gb to about 60gb! My history concerns are in this
context, that we need to start planning now on how to hold multi-60gb
years of data. Certainly partitioning is going to be called for,
views right now in SQL2K, the new partitioned tables (I suppose) when
we get to SQL2005.
Should we keep it all in something as similar as possible to the OLTP
schema, or in a DW schema, or in a special archive schema? Questions
like that.
Just looking for odd hints and war stories.
Happy Thanksgiving to all!
Josh
Subscribe to:
Posts (Atom)