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 Monday, March 19, 2007 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 10:55 AM
Points: 8, Visits: 21
how do you know/verify that the connection(s) you are killing is "cached" ?
Post #352391
Posted Monday, March 19, 2007 8:50 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
Customers do not see it because connections are coming fro the connection pool and pool reistablish it right after. Worst case scenario that customer must hit refresh button. As I pointed in the article, the traffic is very low at night time and for customer to see the connection being disconnected must be exact time request and kill command comes at exact the same time. Then refresh button hit is required. All the other cases are taking care of by the connection pool.


Post #352403
Posted Monday, March 19, 2007 9:09 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 guess that is good if you can be sure about that just a refresh would do it.

I've seen quite a few DWHs where some query runtime gets into the hour range (most time because of the AdHoc attack and under educated users). They schedule their queries somehow (like with a WAITFOR TIME/DELAY) and are pretty angry when they get back in the morning seeing their query being killed.. But then again. They would know and expect this behaviour with a well documented DB switching procedure.

Good article Leo!

Regards, Hanslindgren




Post #352414
Posted Monday, March 19, 2007 2:11 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
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,




* Noel
Post #352498
Posted Monday, March 19, 2007 4:08 PM
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
There is no way for our customers to run their own query. There are scheduled jobs and applications that using stored procs only.


Post #352534
Posted Tuesday, March 20, 2007 3:53 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:29 AM
Points: 622, Visits: 287
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.
Post #352579
Posted Tuesday, March 20, 2007 5:12 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 7, 2011 10:03 AM
Points: 149, Visits: 76

Leo,

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

thanks,

Olga

Post #352842
Posted Tuesday, March 20, 2007 5:55 PM
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

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.




Post #352843
Posted Wednesday, March 21, 2007 1:11 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 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...




Post #352872
Posted Thursday, March 6, 2008 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:12 PM
Points: 23, Visits: 285
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
Post #465433
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse