SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keeping tables online during loading with schema swapping using SSIS


Keeping tables online during loading with schema swapping using SSIS

Author
Message
shubhankarthatte
shubhankarthatte
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 86
Comments posted to this topic are about the item Keeping tables online during loading with schema swapping using SSIS
tonca001
tonca001
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 258
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
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463756 Visits: 43803
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204042 Visits: 40038

His first article perhaps?



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Bobby Russell
Bobby Russell
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1630 Visits: 534
How is this any better than renaming the tables with sp_rename?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204042 Visits: 40038
Bobby Russell - Tuesday, December 5, 2017 9:32 AM
How is this any better than renaming the tables with sp_rename?


Job security?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Bobby Russell
Bobby Russell
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1630 Visits: 534
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?


BigGrin
andrea4618
andrea4618
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 69
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
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463756 Visits: 43803
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204042 Visits: 40038
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search