Keeping tables online during loading with schema swapping using SSIS

  • shubhankarthatte

    Mr or Mrs. 500

    Points: 589

    Comments posted to this topic are about the item Keeping tables online during loading with schema swapping using SSIS

  • tonca001

    SSC Rookie

    Points: 30

    In my experience this sort of pattern has some sort of blocking issue to deal with.  What about using synonyms instead of a schema swap / lock?  We have had to remove this pattern from our DW load (introduced by a previous BI developer) as it was causing severe locking issues.  Yes our DW loads are certainly not best practice but I still think synonyms are a much better solution.

  • Jeff Moden

    SSC Guru

    Points: 997166

    I can't put my finger on it but, IIRC, I've seen the graphics for this article from another article here on SSC, particularly that first one and the article was on the same subject but a bit longer.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Lynn Pettis

    SSC Guru

    Points: 442360

    His first article perhaps?

  • Bobby Russell

    SSCrazy

    Points: 2685

    How is this any better than renaming the tables with sp_rename?

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Bobby Russell - Tuesday, December 5, 2017 9:32 AM

    How is this any better than renaming the tables with sp_rename?

    Job security?

  • Bobby Russell

    SSCrazy

    Points: 2685

    Lynn Pettis - Tuesday, December 5, 2017 9:41 AM

    Bobby Russell - Tuesday, December 5, 2017 9:32 AM

    How is this any better than renaming the tables with sp_rename?

    Job security?

    😀

  • andrea4618

    SSC Enthusiast

    Points: 117

    A couple of honest questions:
    1.  What happens to indexes, triggers, and other dependent objects  with schema swapping?  You may not want an index on the table in the shadow schema, but you probably will want that index in the active reporting schema.  Does the index in the dbo schema need to be rebuilt when the updated table is swapped in?
    2.  Is there any benefit to setting this up in a SSIS package rather than doing the schema swap as a Transact-SQL job step?
    3.  Technet specifies that "All permissions associated with the securable that is being transferred are dropped when it is moved."  So it sounds like you would have to set up steps in your ETL to restore any object specific permissions?

  • Jeff Moden

    SSC Guru

    Points: 997166

    Lynn Pettis - Tuesday, December 5, 2017 8:58 AM

    His first article perhaps?

    Yeah.... that's the one.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Lynn Pettis

    SSC Guru

    Points: 442360

    andrea4618 - Tuesday, December 5, 2017 11:25 AM

    A couple of honest questions:
    1.  What happens to indexes, triggers, and other dependent objects  with schema swapping?  You may not want an index on the table in the shadow schema, but you probably will want that index in the active reporting schema.  Does the index in the dbo schema need to be rebuilt when the updated table is swapped in?
    2.  Is there any benefit to setting this up in a SSIS package rather than doing the schema swap as a Transact-SQL job step?
    3.  Technet specifies that "All permissions associated with the securable that is being transferred are dropped when it is moved."  So it sounds like you would have to set up steps in your ETL to restore any object specific permissions?

    If it is anything like a partition swap you need to have the indexes.  Other things you would have to test.  I found with partition swapping I didn't need the default constraints, not sure about indexes as the ones on the tables I was working with we dropped the indexes before to the work to complete the data cut on the database. DRI may need to be there, but the two tables in this particular database with foreign keys weren't affected.

  • richardmgreen1

    SSCrazy Eights

    Points: 9951

    This is something we've been looking at as part of our load (just an idea at the minute).

     

    We have a multi-part process to load our tables as follows:-

    1. Load a "process list" table (this just gets any new/updated records and only contains basic information to identify the record affected)
    2. Load a working table with all the data used for that table (using the process list as a base)
    3. Merge the working table with the "proper" table
    4. Load/update all our DIM tables (kept in a separate database)
    5. Load/update all our FACT tables (kept in the same database as 4)
    6. Process cubes

     

    Questions:-

    1. Will this still work in our scenario?
    2. We have cubes attached to the FACT/DIM tables.  Can we use a similar methodology to have minimal downtime with our cubes (i.e. point the cubes to the shadow tables and the just rename the cubes after thay have processed)

    We want to have minimal downtime (don't we all?) when updating the tables and we currently only do a load once a day.  We are looking at trying several loads a day but need a method of keeping downtime to a minimum.

     

     

    Any help on this would be greatly appreciated.

    Richard

  • hakan_l_borg

    SSC Enthusiast

    Points: 117

    Hi, will this affect ongoing jobs on the table beeing refreshed? Ie should jobs running at swap time be killed or will they be able to read the old table until finished?

    Thanks for a great tip!

    Br Håkan B

Viewing 12 posts - 1 through 12 (of 12 total)

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