Loading a 24x7 Data Warehouse

  • 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.
  • Leo,

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

    thanks,

    Olga

  • 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.

  • 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...

  • 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

  • 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

  • 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

  • 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)

  • 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)

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

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

  • 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.

  • 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]

  • 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)

  • 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/

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

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