Post reply

Perfect way for transfering data

  • September 25, 2004 at 9:51 pm

    #65687

    Hi everybody,

    I will describe in few words the situation I need help with:

    We have a production server, with SQL2000. The application that is running on that server is inserting data into the MS SQL Database. Since there are more than 500 000 records per day inserted in to the DB, the application is deleting data that is older than one month. Shortly, the DB is holding data just for one month.

    Our purpose is to have the other server, a powerful dedicated SQL Server to hold a copy of this data. We want to bring the whole data from the production server to the dedicated server, so that if you search for data older than one month to find it.

    If I move with DTS the data from the production server to the dedicated server, the next DTS will overwrite or append data. But I need to add just the newly inserted records. So this is not going to work. If I run the SQL backup, I have the same options, append to media or overwrite existing media.

    All I need is to bring initially the current data and then just the new inserted ones.

    What is the best way to do this?

    Things that are not suitable for my solution:

    1. changing the application, so that data will not be deleted after one month

    2. changing the DB to Oracle

    I hope I was clear enough.

    Thanks a lot

  • September 26, 2004 at 10:12 pm

    #524444

    Changing the db to Oracle??? You won't find that suggestion here!

    If you are working on a month-by-month basis, can't you just run a DTS package at 23:59 on the last day of every month to perform the transfer/delete for the previous month?

    Eg DTS runs on 31 Oct 2004, transfers and then deletes any records where transaction date < 1 Oct 2004 etc etc.

    Or, for more-frequent updates, could it run every day and transfer/delete records where transaction date < dateadd(m, -1, getdate())?

    Regards

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 27, 2004 at 2:11 am

    #524454

    I'm not sure I understand the problem.  As Phil says, at the end of the month before the cleardown for the next months data simply run the DTS to copy across the records to the dedicated box.

    You don't have to delete the existing data on your new dedicated box.

    If it is a rolling month then again, as Phil says do your scheduled transfer.

    LinkedIn Profile

  • September 27, 2004 at 10:53 am

    #524548

    Couldn't you send the transaction logs to the other server and apply them there. 

  • September 28, 2004 at 9:47 am

    #524658

    I think you guys are right. I will run the DTS every month with append option on. Since every second are at least 10 records added, how can I make sure that I don't miss any data during the transfer?

    There are at least 20 tables and unfortunately there is no Primary Key in the main table. Don't blame it on me, blame the company who wrote the app.

    Thanks a lot  

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply