|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Thursday, August 06, 2009 7:20 PM
Points: 126,
Visits: 134
|
|
|
|
|
|
Forum 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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 7:42 AM
Points: 1,034,
Visits: 381
|
|
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! 
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Thursday, March 06, 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?
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 5:30 PM
Points: 80,
Visits: 157
|
|
| 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
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Monday, January 25, 2010 4:45 AM
Points: 620,
Visits: 259
|
|
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.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 7:29 AM
Points: 2,019,
Visits: 1,653
|
|
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.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, and 2008 R2. 24 February 2010: now over 9,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.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, August 06, 2009 7:20 PM
Points: 126,
Visits: 134
|
|
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.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 10:51 AM
Points: 1,175,
Visits: 212
|
|
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...
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Monday, January 25, 2010 4:45 AM
Points: 620,
Visits: 259
|
|
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.
|
|
|
|
