Post reply

Does dropping pk,fks, and indexes required while data migration?

  • June 29, 2012 at 1:41 pm

    #277138

    I am doing migration data from stage database to target database using t-sql scripts.

    so, before start loaidng , I am dropping all the keys(primarykeys and foreign keys and indexes of the target tables.

    Then, after the load, I am recreating those all.

    Is my approach correct? Do i need to drop the keys? or only index dropping is enough?

    Could someone give me any advice?

    Thanks alot

    Disha

  • July 3, 2012 at 2:53 am

    #1508079

    Hi,

    Below is my Suggestion. Dont drop any Keys (PK,FK). For Example if you drop the keys and dump the data and while recreation if your data having problems in pk/fk it is a big head ache to find it.

    Please drop indexes while dumping else you can use reindex/reorgranize options according to the load of data.

    Thanks

    Pranesh.

  • July 3, 2012 at 3:27 am

    #1508094

    I think I'd agree with the above. Leaving the keys in place might make the migration a bit more of a pain because it'll thrw up any data integrity issues but that's a good thing because you're catching them early. If you drop the keys the import'll go smoothly but you'll still have to deal with any integrity issues when you go to recreate the keys - you won't escape the pain, you'll just defer it.

  • July 3, 2012 at 3:38 am

    #1508099

    Dropping the indexes and constraints will make the loading of the data faster. However, don't just wait for the implementation of the constraints afterwards to highlight any errors - you have no idea how many times you'd have to attempt and correct that before all of the data is valid.

    Why don't you validate the data beforehand? Write a series of queries against the data that is about to be imported, making sure that it is valid for the table it is going to. Make sure any field that will eventually become a foreign key is actually valid for that purpose. Check any dates/values etc to make sure they are within the acceptable range for your data.

    Once you know the data will be fine in the target table then you can import it, and when you re-implement any constraints it should all work first time.

    Knowing your data is valid beforehand is much easier than hoping you can fix any problems afterwards.

    Steve Hall
    Linkedin
    Blog Site

  • July 3, 2012 at 1:29 pm

    #1508456

    on some of our ETL loads we have a staging database with an initial table. we then run data validation and insert into a second staging table then insert from the second table to the production table with most of the constraints disabled since we all ready checked and fixed any data that was bad.

    after doing a bulk load depending on the size we then rebuild all indexes on the tables affected.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

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

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