Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loading a 24x7 Data Warehouse


Loading a 24x7 Data Warehouse

Author
Message
Erhan Hosca
Erhan Hosca
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 21
how do you know/verify that the connection(s) you are killing is "cached" ?
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 339
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.



Hans Lindgren
Hans Lindgren
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 366

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





noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
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
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 339
There is no way for our customers to run their own query. There are scheduled jobs and applications that using stored procs only.



Sorin Petcu
Sorin Petcu
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 345
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.
Olga Smith
Olga Smith
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 76

Leo,

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

thanks,

Olga


Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445

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.





Hans Lindgren
Hans Lindgren
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 366

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





Gary Nease
Gary Nease
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 423
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search