Keeping tables online during loading with schema swapping using SSIS

Question

Keeping tables online during loading with schema swapping using SSIS

shubhankarthatte

Mr or Mrs. 500

Points: 589
More actions
December 5, 2017 at 12:00 am

#333885

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

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

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

TonyGaul SSC Rookie Points: 30 More actions · Answer 1

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: 1003851 More actions · Answer 2

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.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

December 5, 2017 at 8:58 am

#1970737

His first article perhaps?

Bobby Russell SSCrazy Points: 2801 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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: 2801 More actions · Answer 6

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 More actions · Answer 7

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: 1003851 More actions · Answer 8

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.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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 SSChampion Points: 10301 More actions · Answer 10

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:-

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

Questions:-

Will this still work in our scenario?
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: 132 More actions · Answer 11

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