April 5, 2010 at 9:35 am
I receive records on a daily basis that are inserted into a SQL table on Server1. The records are processed by a translator which looks at data in one column and changes a second column from "queued" to "done". Once this is completed the process is complete and the records just build up and I have little or no control over how long I keep them. What I would like to do is keep only those records for one week in SERVER1.DB1.table1 and move all the older records to SERVER2.DB2.table2.
I'm looking for the best way to do this on a daily basis automatically. Now I do this manually by exporting from SERVER1.DB1.table1 to SERVER2.DB2.table2 and delete the older records from SERVER1.DB1.table1.
FYI I have total control of SERVER1.DB1.table1 but only SELECT and MODIFY on SERVER2.DB2.table2
I'm sure there are several ways to do this I looking for the best.
April 5, 2010 at 9:40 am
Partitioning may be the answer for this archive and purge scenario.
Have you considered a sliding window on SERVER1?
You can send last partition rows to SERVER2 then truncate partition on SERVER1.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 5, 2010 at 9:46 am
I see what you are saying and that might work but I'm not sure how to do it.
April 5, 2010 at 10:59 am
Database partitioning is only available on the Enterprise version of SQL Server -- is that what you're running?
Rob Schripsema
Propack, Inc.
April 5, 2010 at 11:08 am
allenb-717661 (4/5/2010)
I see what you are saying and that might work but I'm not sure how to do it.
Please start by searching for "sliding window" in BOL; there is an example - including needed t-sql code - on the AdventureWorks sample database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 5, 2010 at 2:15 pm
Server1 is currently 2005 Express and Server2 is SQL 2000. I will probably be moving from Express to Enterprise.
I also have other servers running SQL 2005 Enterprise and SQL 2008 Enterprise.
April 5, 2010 at 2:18 pm
Thanks, I will tonight when I have some time.
AB
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply