 | |
Re: Archiving data
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Archiving data -
07-21-2004
, 09:30 AM
|
I have a database that I want to split. I will create 2 copies, one with current data and the second with archived data. Cascade delete are not |
table hierarchy. My questions are:
Quote:
|
1. What is the best method to completely move old data from the production database and load it into the archive? 2. How do I maintain the referential integrity of the data when loading it. Do I have to map the hierarchy of 300 tables? 3. Am I able to start enabling the cascade deletes without problems? Any comments or suggestions are appreciated. |
#2
| |||
| |||
|
|
Yes, I want to completely remove the rows from the live system. Is there more information you could offer? I like this idea but always prefer to |
Quote:
|
"Allan Mitchell" wrote: Hmmm archiving Are you going to be completely removing rows from the Live System and entering them into the Archive System? We have this requirement currently where I am working. we want 2 years in the live transactional system (and currently live data if "Open" for > 2 years) but the reporting DB requires 7 years worth of data We implemented transactional replication and then modified the stored procs created by replication. If our clean up routine deletes a row from the live system then that row is not removed from the reporting database. All "Live" rows and any row that has an "In Play" date of within two years is removed/updated etc There is no quick fix for this i do not think and a lot of testing and design will have to be built into the plans. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:B20FE5BA-6844-451F-B075-17E2DD658BF5 (AT) microsoft (DOT) com... I have a database that I want to split. I will create 2 copies, one with current data and the second with archived data. Cascade delete are not enabled. The criteria for archiving are based on a very high level in the table hierarchy. My questions are: 1. What is the best method to completely move old data from the production database and load it into the archive? 2. How do I maintain the referential integrity of the data when loading it. Do I have to map the hierarchy of 300 tables? 3. Am I able to start enabling the cascade deletes without problems? Any comments or suggestions are appreciated. |
#3
| |||
| |||
|
|
Who said I made mistakes <g My advice is to 1. Understand the business requirements 2. Look at different solutions (DTS, Replication, Homegrown) 3. Test 4. Test 5. Validate If you ever need to put data back then you will have to, providing you are using DRI, out it back in the cirrect order as well. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:3FFD44C6-75B5-4C60-A5D0-C83EB0BE8410 (AT) microsoft (DOT) com... Yes, I want to completely remove the rows from the live system. Is there more information you could offer? I like this idea but always prefer to learn from others mistakes. "Allan Mitchell" wrote: Hmmm archiving Are you going to be completely removing rows from the Live System and entering them into the Archive System? We have this requirement currently where I am working. we want 2 years in the live transactional system (and currently live data if "Open" for > 2 years) but the reporting DB requires 7 years worth of data We implemented transactional replication and then modified the stored procs created by replication. If our clean up routine deletes a row from the live system then that row is not removed from the reporting database. All "Live" rows and any row that has an "In Play" date of within two years is removed/updated etc There is no quick fix for this i do not think and a lot of testing and design will have to be built into the plans. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:B20FE5BA-6844-451F-B075-17E2DD658BF5 (AT) microsoft (DOT) com... I have a database that I want to split. I will create 2 copies, one with current data and the second with archived data. Cascade delete are not enabled. The criteria for archiving are based on a very high level in the table hierarchy. My questions are: 1. What is the best method to completely move old data from the production database and load it into the archive? 2. How do I maintain the referential integrity of the data when loading it. Do I have to map the hierarchy of 300 tables? 3. Am I able to start enabling the cascade deletes without problems? Any comments or suggestions are appreciated. |
#4
| |||
| |||
|
|
Who said I made mistakes <g My advice is to 1. Understand the business requirements 2. Look at different solutions (DTS, Replication, Homegrown) 3. Test 4. Test 5. Validate If you ever need to put data back then you will have to, providing you are using DRI, out it back in the cirrect order as well. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:3FFD44C6-75B5-4C60-A5D0-C83EB0BE8410 (AT) microsoft (DOT) com... Yes, I want to completely remove the rows from the live system. Is there more information you could offer? I like this idea but always prefer to learn from others mistakes. "Allan Mitchell" wrote: Hmmm archiving Are you going to be completely removing rows from the Live System and entering them into the Archive System? We have this requirement currently where I am working. we want 2 years in the live transactional system (and currently live data if "Open" for > 2 years) but the reporting DB requires 7 years worth of data We implemented transactional replication and then modified the stored procs created by replication. If our clean up routine deletes a row from the live system then that row is not removed from the reporting database. All "Live" rows and any row that has an "In Play" date of within two years is removed/updated etc There is no quick fix for this i do not think and a lot of testing and design will have to be built into the plans. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:B20FE5BA-6844-451F-B075-17E2DD658BF5 (AT) microsoft (DOT) com... I have a database that I want to split. I will create 2 copies, one with current data and the second with archived data. Cascade delete are not enabled. The criteria for archiving are based on a very high level in the table hierarchy. My questions are: 1. What is the best method to completely move old data from the production database and load it into the archive? 2. How do I maintain the referential integrity of the data when loading it. Do I have to map the hierarchy of 300 tables? 3. Am I able to start enabling the cascade deletes without problems? Any comments or suggestions are appreciated. |
#5
| |||
| |||
|
|
Is there a way to determine/record the table order for inserts/deletes to maintain referential integrity?? I have been looking but cannot seen to find |
Quote:
|
"Allan Mitchell" wrote: Who said I made mistakes <g My advice is to 1. Understand the business requirements 2. Look at different solutions (DTS, Replication, Homegrown) 3. Test 4. Test 5. Validate If you ever need to put data back then you will have to, providing you are using DRI, out it back in the cirrect order as well. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:3FFD44C6-75B5-4C60-A5D0-C83EB0BE8410 (AT) microsoft (DOT) com... Yes, I want to completely remove the rows from the live system. Is there more information you could offer? I like this idea but always prefer to learn from others mistakes. "Allan Mitchell" wrote: Hmmm archiving Are you going to be completely removing rows from the Live System and entering them into the Archive System? We have this requirement currently where I am working. we want 2 years in the live transactional system (and currently live data if "Open" for 2 years) but the reporting DB requires 7 years worth of data We implemented transactional replication and then modified the stored procs created by replication. If our clean up routine deletes a row from the live system then that row is not removed from the reporting database. All "Live" rows and any row that has an "In Play" date of within two years is removed/updated etc There is no quick fix for this i do not think and a lot of testing and design will have to be built into the plans. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Brian" <Brian (AT) discussions (DOT) microsoft.com> wrote in message news:B20FE5BA-6844-451F-B075-17E2DD658BF5 (AT) microsoft (DOT) com... I have a database that I want to split. I will create 2 copies, one with current data and the second with archived data. Cascade delete are not enabled. The criteria for archiving are based on a very high level in the table hierarchy. My questions are: 1. What is the best method to completely move old data from the production database and load it into the archive? 2. How do I maintain the referential integrity of the data when loading it. Do I have to map the hierarchy of 300 tables? 3. Am I able to start enabling the cascade deletes without problems? Any comments or suggestions are appreciated. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.