|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 5:58 PM
Points: 2,
Visits: 38
|
|
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
|
|
|
|
|
SSC 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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 20,164,
Visits: 13,702
|
|
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".
"Data isn't the only thing that's supposed to have Integrity."
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 20,164,
Visits: 13,702
|
|
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".
"Data isn't the only thing that's supposed to have Integrity."
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, August 06, 2009 7:20 PM
Points: 126,
Visits: 134
|
|
We are using connection pool. There is no direct customer connections.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, August 06, 2009 7:20 PM
Points: 126,
Visits: 134
|
|
Detach attach is the problem because if process failed detach the database is not exists. If rename failed you have the old database working
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, August 06, 2009 7:20 PM
Points: 126,
Visits: 134
|
|
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.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 11:21 AM
Points: 9,104,
Visits: 8,528
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 20,164,
Visits: 13,702
|
|
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".
"Data isn't the only thing that's supposed to have Integrity."
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 18, 2010 8:42 AM
Points: 292,
Visits: 8,632
|
|
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/
|
|
|
|
