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 Tuesday, March 6, 2007 9:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/2901.asp


Post #349442
Posted Sunday, March 18, 2007 11:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2007 8:59 AM
Points: 1, Visits: 4
Undoutedly one of the fine piece of work.  Great work Leo.  -  Debasish
Post #352288
Posted Monday, March 19, 2007 1:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
When you gave the problem description your solution was what was in my head - good to know that I at least thought of a real-world solution!


Post #352293
Posted Monday, March 19, 2007 1:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, March 6, 2008 6:06 PM
Points: 644, Visits: 2

I have an idea that with SQL Server 2005 you could accomplish something like this using a database snapshot.

Has anybody actually tried that?




Post #352303
Posted Monday, March 19, 2007 3:35 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Thanks you for sharing your experience Leo. It's a good and useful article. We use a data warehouse where the data from stage is loaded alternately into 2 sets of identical star schema (the difference is just the suffix at the end of table names) and there are a set of views which select from those sets. The downtime is minimal, just to drop and recreate the views to point to the just loaded set of tables. We have been using this for 6 years. Regards, Vincent
Post #352316
Posted Monday, March 19, 2007 3:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 622, Visits: 287

Good job, Leo!

Finnaly, I found an stored procedure which kills the db connections, KILL_DB_CONNECTIONS. But, not in the end, your logical schema was one of mine used in production at one of my customers. Thank you to confirm that what I thought was in good shape.



In Theory, theory and practice are the same...In practice, they are not.
Post #352317
Posted Monday, March 19, 2007 3:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 2,879, Visits: 3,229

What is the size of your DW, and how long does the backup and the restore take?  I would be concerned that as your DW size grows, you may run out of time to do the backup/restore cycle.

What are your thoughts on using a SQL 2005 database snapshot in place of the backup/restore.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #352320
Posted Monday, March 19, 2007 6:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
The size of the database is 5-10G. But remember that there is the whole day to backup and restore because after rename happened the LOAD databases required only for the next load in 24 hours.


Post #352343
Posted Monday, March 19, 2007 8:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 5, 2013 7:04 AM
Points: 1,197, Visits: 294
I was wondering what your customer thinks when you are killing their connections? I don't think that can be an acceptable solution to a 24/7 warehousing problem in all cases...


Post #352379
Posted Monday, March 19, 2007 8:17 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 622, Visits: 287

In fact, I am killing cache database connections made by other third party applications than the master application. So it is ok and my customer is happy 'cause the sql server instance isn't fullfill of unuseful connections



In Theory, theory and practice are the same...In practice, they are not.
Post #352385
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse