Post reply

Strategy to Move incremental data from Migration Server to Target System in migration

  • November 14, 2016 at 2:16 am

    #314889

    Hi All,

    We want to migrate data from Migration SQL server database from final layer to Target(Sql Server) server into respective table . There are two type of data that will be migrated first one will be full load and another part will be incremental load , we are in process to draw strategy how data will be migrated in case of incremental i.e strategy to migrate changed/incremental data from Migration server/final layer to target system tables . What strategy/process should follow to load incremental data.

    It will be very helpful if any one suggest suitable approach to design migration strategy from their prior experience .

    Thanks

    Alok

  • November 14, 2016 at 3:06 pm

    #1912366

    getalok_akg (11/14/2016)

    Hi All,

    We want to migrate data from Migration SQL server database from final layer to Target(Sql Server) server into respective table . There are two type of data that will be migrated first one will be full load and another part will be incremental load , we are in process to draw strategy how data will be migrated in case of incremental i.e strategy to migrate changed/incremental data from Migration server/final layer to target system tables . What strategy/process should follow to load incremental data.

    It will be very helpful if any one suggest suitable approach to design migration strategy from their prior experience .

    Thanks

    Alok

    If you're asking for help by simply stating that you have "full load" data and "incremental load" data, and not defining exactly what those terms mean, then you're not really giving anyone anything to go on. Those terms can vary significantly in what they actually represent. We'll need a LOT more detail than that. For example, what kind of data quantity is represented in each of those? What kind of time-window is the migration expected to take place in? Will there be updates to the data during the migration time-frame? I could probably come up with several others, but those answers would at least be a good start down the road.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 14, 2016 at 6:00 pm

    #1912377

    Why not do a full backup and point in time transaction log file backups? When you're ready, do a final full backup and continue the transaction log backups. Do all of the restores to the new database with NO RECOVERY. When you're ready, do a TAIL LOG backup of the source and that will take the source offline. Then do a restore of the TAIL LOG on the new database using WITH RECOVERY and you're all done with the data migration. This will take comparatively seconds to do compared to the rest of the restores. It's how we did our migration and production was down just long enough to do the TAIL LOG restore and some smoke testing. Of course, we had tested the hell out of all that and more on our Dev and Staging boxes first.

    Change the compatibility mode to what the new server is and test. If it goes well, you're all done.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 15, 2016 at 4:11 am

    #1912436

    Hi Thanks for reply .

    Let me elaborate more on this . We are in process to migrate data from one platform to another .We will be moving data in two steps :

    1. Full Load : from Current month +last 24 month data (i.e let's say we are running our ETL package on 10th November then in Full load we will move data for 10th November +last 24 month )

    2: Incremental Load : Let's say we run our SSIS package on 15th November then data between 15th Nov to 10th November as well as changed data during this period will be migrated .

    we are in process to define strategy for incremental load like where we should store data in between , key column we should use, if we can have specific parameter to categorise full/incremental load . we have created date , modified date column in respective tables .Kindly guide/suggest solution .

    Thanks

    Alok

  • November 15, 2016 at 4:13 am

    #1912437

    Hi Jeff ,

    Thanks for reply . Our client is not agree for backup approach due to business constraint . .

    Thanks

    Alok

  • November 15, 2016 at 5:04 am

    #1912447

    Hi Thanks for reply .

    Let me elaborate more on this . We are in process to migrate data from one platform to another .We will be moving data in two steps :

    1. Full Load : from Current month +last 24 month data (i.e let's say we are running our ETL package on 10th November then in Full load we will move data for 10th November +last 24 month )

    2: Incremental Load : Let's say we run our SSIS package on 15th November then data between 15th Nov to 10th November as well as changed data during this period will be migrated .

    we are in process to define strategy for incremental load like where we should store data in between , key column we should use, if we can have specific parameter to categorise full/incremental load . we have created date , modified date column in respective tables .Kindly guide/suggest solution .

    Thanks

    Alok

  • November 15, 2016 at 9:36 am

    #1912587

    getalok_akg (11/15/2016)

    Hi Thanks for reply .

    Let me elaborate more on this . We are in process to migrate data from one platform to another .We will be moving data in two steps :

    1. Full Load : from Current month +last 24 month data (i.e let's say we are running our ETL package on 10th November then in Full load we will move data for 10th November +last 24 month )

    2: Incremental Load : Let's say we run our SSIS package on 15th November then data between 15th Nov to 10th November as well as changed data during this period will be migrated .

    we are in process to define strategy for incremental load like where we should store data in between , key column we should use, if we can have specific parameter to categorise full/incremental load . we have created date , modified date column in respective tables .Kindly guide/suggest solution .

    Thanks

    Alok

    Well,

    That tells us that Full Load vs Incremental Load is calendar-based, but doesn't say much else. Your response to Jeff Moden indicates you can't use backup and restore, and you've not indicated what kind of data volume you're talking about either, nor made any mention on how you intend to transport the data from the source to the destination. Are the servers involved even connected to the same network? You haven't really helped describe the situation in anywhere near enough detail to get a useful answer. We have no idea what other constraints might exist for your environment, so anything we suggest would be mere guesswork at this point.

    However, at least from the point of view of the calendar basis for your data migration, if you have any tables that do not have an existing field that serves as a basis for including a particular record in a particular migration, then you'll need to figure out how to handle that scenario, and a lack of network connectivity between the two systems could be a serious problem.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 18, 2016 at 12:16 am

    #1913299

    getalok_akg (11/15/2016)

    Hi Jeff ,

    Thanks for reply . Our client is not agree for backup approach due to business constraint . .

    Thanks

    Alok

    It would be really helpful if you told us what that business constraint actually is. 😉 It might help us solve the problem and it might help us understand your client a bit better so that we can avoid violating such business constraints (which, in this case, sounds a bit odd and rather a matter of fact decision that anything practical).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 18, 2016 at 12:19 am

    #1913300

    getalok_akg (11/15/2016)

    Hi Jeff ,

    Thanks for reply . Our client is not agree for backup approach due to business constraint . .

    Thanks

    Alok

    It would be really helpful if you told us what that business constraint actually is. 😉 It might help us solve the problem and it might help us understand your client a bit better so that we can avoid violating such business constraints which, in this case, sounds a bit odd and rather a matter of fact decision that anything practical especially since the term "migration" usually indicates a "one-off" problem rather than a daily occurrence.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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