Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Loading a 24x7 Data Warehouse Expand / Collapse
Author
Message
Posted Thursday, March 06, 2008 6:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 30, 2014 2:22 PM
Points: 4, Visits: 105
This is pretty well done.
I do have a suggestion for an alternative that uses only one db.
You could do the ETL into different tables instead of different databases. Then switch a set of views to point to the new tables from the old. This might let you run without killing any users.
Switching db's has other advantages though.

Regards,
Andy
Andrew Novick
http://www.NovickSoftware.com
Post #465061
Posted Saturday, March 08, 2008 10:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 26, 2008 12:03 PM
Points: 38, Visits: 21
Good solution for small Database but not for terrabyte databases. Ours takes 3 days to load from backup. Using partitioning and file groups or snapshot is a better choice
Post #466366
Posted Sunday, March 09, 2008 9:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 35,969, Visits: 30,260
I dunno... to me, killing everyone's connection seems to violate a 24x7 SLA. I've used the same method at the table level before without killing any connections with no ill effects. Takes about 65 milliseconds per table.

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #466389
Posted Sunday, March 09, 2008 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 35,969, Visits: 30,260
Gary E. Vernon (3/8/2008)
Good solution for small Database but not for terrabyte databases. Ours takes 3 days to load from backup. Using partitioning and file groups or snapshot is a better choice


Heh... wow, Gary... does the boss know that? 3 days is an awfully long time to be "out of business". I'm thinking you and yours need to figure out a way to do some parallel loads to cut the time down.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #466390
Posted Sunday, March 09, 2008 7:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
We are using connection pool. There is no direct customer connections.


Post #466442
Posted Sunday, March 09, 2008 7:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
Detach attach is the problem because if process failed detach the database is not exists. If rename failed you have the old database working


Post #466443
Posted Sunday, March 09, 2008 7:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
noeld (3/19/2007)
A 10GB data warehouse is small enough to follow the proposed procedure.

When your data warehouse reaches 200 to 500GB you will get into a TOTALLY different ball game.

Cheers,



When you have 500G you strategy has to be changed. You may check if schema level can be replaced or another set of objects, or some other solutions. It is not as scary as people thinking. We have 80-100G statistical databases but the strategy is different.



Post #466444
Posted Sunday, March 09, 2008 7:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Nice article.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #466445
Posted Monday, March 10, 2008 5:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 35,969, Visits: 30,260
Leo Peysakhovich (3/9/2008)
noeld (3/19/2007)
A 10GB data warehouse is small enough to follow the proposed procedure.

When your data warehouse reaches 200 to 500GB you will get into a TOTALLY different ball game.

Cheers,



When you have 500G you strategy has to be changed. You may check if schema level can be replaced or another set of objects, or some other solutions. It is not as scary as people thinking. We have 80-100G statistical databases but the strategy is different.


So, what strategy do you use?


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #466588
Posted Wednesday, March 26, 2008 1:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Picture links are broken; is there an alternative location/URL for these links? Can I PM and get them e-mailed to me?

Thanks,
James R.


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #475039
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse