SQLServerCentral / Discuss Content Posted by Leo Peysakhovich / Article Discussions / Article Discussions by Author / Loading a 24x7 Data Warehouse / Latest Posts

RE: Loading a 24x7 Data Warehouse

James_DBA — Wed, 26 Mar 2008 13:10:53 GMT

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.

RE: Loading a 24x7 Data Warehouse

Jeff Moden — Mon, 10 Mar 2008 05:53:25 GMT

[quote][b]Leo Peysakhovich (3/9/2008)[/b][hr][quote][b]noeld (3/19/2007)[/b][hr]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,[/quote]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.[/quote]So, what strategy do you use?

RE: Loading a 24x7 Data Warehouse

RBarryYoung — Sun, 09 Mar 2008 19:19:24 GMT

Nice article.

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Sun, 09 Mar 2008 19:15:43 GMT

[quote][b]noeld (3/19/2007)[/b][hr]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,[/quote]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.

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Sun, 09 Mar 2008 19:09:01 GMT

Detach attach is the problem because if process failed detach the database is not exists. If rename failed you have the old database working

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Sun, 09 Mar 2008 19:05:25 GMT

We are using connection pool. There is no direct customer connections.

RE: Loading a 24x7 Data Warehouse

Jeff Moden — Sun, 09 Mar 2008 09:13:01 GMT

[quote][b]Gary E. Vernon (3/8/2008)[/b][hr]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[/quote]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.

RE: Loading a 24x7 Data Warehouse

Jeff Moden — Sun, 09 Mar 2008 09:10:56 GMT

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.

RE: Loading a 24x7 Data Warehouse

Gary E. Vernon — Sat, 08 Mar 2008 22:09:52 GMT

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

RE: Loading a 24x7 Data Warehouse

Andrew Novick — Thu, 06 Mar 2008 18:03:44 GMT

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,AndyAndrew Novickhttp://www.NovickSoftware.com

RE: Loading a 24x7 Data Warehouse

Gary Nease — Thu, 06 Mar 2008 12:37:17 GMT

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.

RE: Loading a 24x7 Data Warehouse

Hans Lindgren — Wed, 21 Mar 2007 01:11:00 GMT

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

RE: Loading a 24x7 Data Warehouse

Ian Yates — Tue, 20 Mar 2007 17:55:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

Olga Smith — Tue, 20 Mar 2007 17:12:00 GMT

Leo,

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

thanks,

Olga

RE: Loading a 24x7 Data Warehouse

Sorin Petcu — Tue, 20 Mar 2007 03:53:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Mon, 19 Mar 2007 16:08:00 GMT

There is no way for our customers to run their own query. There are scheduled jobs and applications that using stored procs only.

RE: Loading a 24x7 Data Warehouse

noeld — Mon, 19 Mar 2007 14:11:00 GMT

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,

RE: Loading a 24x7 Data Warehouse

Hans Lindgren — Mon, 19 Mar 2007 09:09:00 GMT

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

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Mon, 19 Mar 2007 08:50:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

Erhan Hosca — Mon, 19 Mar 2007 08:25:00 GMT

how do you know/verify that the connection(s) you are killing is "cached" ?

RE: Loading a 24x7 Data Warehouse

Sorin Petcu — Mon, 19 Mar 2007 08:17:00 GMT

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

RE: Loading a 24x7 Data Warehouse

Hans Lindgren — Mon, 19 Mar 2007 08:03:00 GMT

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

RE: Loading a 24x7 Data Warehouse

Leo Peysakhovich — Mon, 19 Mar 2007 06:21:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

EdVassie — Mon, 19 Mar 2007 03:52:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

Sorin Petcu — Mon, 19 Mar 2007 03:39:00 GMT

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.

RE: Loading a 24x7 Data Warehouse

VincentRainardi — Mon, 19 Mar 2007 03:35:00 GMT

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

RE: Loading a 24x7 Data Warehouse

pshotts — Mon, 19 Mar 2007 01:53:00 GMT

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

Has anybody actually tried that?

RE: Loading a 24x7 Data Warehouse

Ian Yates — Mon, 19 Mar 2007 01:13:00 GMT

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!

RE: Loading a 24x7 Data Warehouse

Deb Chat — Sun, 18 Mar 2007 23:23:00 GMT

Undoutedly one of the fine piece of work. Great work Leo. - Debasish

Loading a 24x7 Data Warehouse

Leo Peysakhovich — Tue, 06 Mar 2007 09:43:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/2901.asp