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 6, 2008 6:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:30 PM
Points: 4, Visits: 113
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 8, 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 9, 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 @ 8:12 AM
Points: 35,770, Visits: 32,434
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."

(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 9, 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 @ 8:12 AM
Points: 35,770, Visits: 32,434
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."

(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 9, 2008 7:05 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
We are using connection pool. There is no direct customer connections.


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

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
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 9, 2008 7:15 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
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 9, 2008 7:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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 @ 8:12 AM
Points: 35,770, Visits: 32,434
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."

(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: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
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