Loading a 24x7 Data Warehouse

Question

Loading a 24x7 Data Warehouse

Viewing 15 posts - 16 through 29 (of 29 total)

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

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 1

how do you know/verify that the connection(s) you are killing is "cached" ?

it's simple, I am killing the "cached" connections from third party application administration, e.g. - when a customer request an report made with Cold Fusion technology, this application give you an administration tool where you could set how much time "cached" connections would live.

In Theory, theory and practice are the same...In practice, they are not.

Olga Smith Old Hand Points: 323 More actions · Answer 2

Leo,

Did you try using detach/attach method instead of backup/restore?

thanks,

Olga

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Hmm - 500GB database That'd be scary!

Certainly the backup of a DB of 5-10GB in size doesn't take very long at all. A detach, file copy and then reattach could be a feasible solution as well, although I haven't really thought about it too far so there could something I've missed. For that matter, the detach & attach could take longer!? Benchmarking will tell I suppose.

I am curious though - what do you do when you have a 500GB database? I imagine some people might do something so horribly horribly crude as physically swapping drives haha

The idea about keeping the DBs the same and merely updating views to point to the correct DBs or, if a single DB with table suffixes, the correct tables seems logical, although maintenance of the views must be rememebered if and when a new table is added.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

I can't remember when I last worked with a DWH of 5-10 GB My last was 1 TB..

CDR data are major league space consumers...

Gary Nease SSC-Addicted Points: 424 More actions · Answer 5

Embedded images http://www.sqlservercentral.com/articles/2901/DataWa1.jpg and http://www.sqlservercentral.com/articles/2901/DataWa2.jpg are 404 when I look at this article, or if I try to hit them directly.

Regards,

Gary

Andrew Novick SSC Enthusiast Points: 102 More actions · Answer 6

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

Gary E. Vernon SSC Enthusiast Points: 128 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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.

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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.

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

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 10

We are using connection pool. There is no direct customer connections.

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 11

Detach attach is the problem because if process failed detach the database is not exists. If rename failed you have the old database working

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 12

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Nice article.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

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.

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

James_DBA Default port Points: 1422 More actions · Answer 15

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/