September 8, 2011 at 1:59 pm
I have a OLTP on a server1 and DW on a server2. ETL procedures populate DW on server2 using source data from server1.
Is it better to run ETL on server1 or server2 and why?
Thanks, Alexander.
September 8, 2011 at 2:03 pm
When is the process being run? Which one has more memory available? Are you transforming in the package or after drop to staging tables? Which one has more CPU available? Are you loading off a five nines system? Do you have downtime available on the warehouse?
etc etc etc.
This is a MAJOR "it depends" question. You have to tell us the whys, and then we can advise you. Dropping a question on us like it's a homework quiz with no real background to it makes it both difficult to answer... and unlikely to get answered.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2011 at 2:38 pm
Evil Kraig F (9/8/2011)
When is the process being run? Which one has more memory available? Are you transforming in the package or after drop to staging tables? Which one has more CPU available? Are you loading off a five nines system? Do you have downtime available on the warehouse?etc etc etc.
This is a MAJOR "it depends" question. You have to tell us the whys, and then we can advise you. Dropping a question on us like it's a homework quiz with no real background to it makes it both difficult to answer... and unlikely to get answered.
Run nightly.
Same memory.
Using stored procedures.
Same CPU.
Are you loading off a five nines system? - Not sure.
Do you have downtime available on the warehouse? - Yes.
September 8, 2011 at 5:42 pm
Five 9 means you are required to be up 99.999% of the time.
I'm not sure about my math on this but it would mean that you have roughly 26 seconds per month aloted for downtime.
September 8, 2011 at 11:58 pm
I've seen systems where regular DWH processing is being halted during the load window.
This way they free up resources on the dwh server and run ETL at that box to minimize impact on ongoing 24/7 oltp side.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2011 at 1:56 am
ALZDBA (9/8/2011)
I've seen systems where regular DWH processing is being halted during the load window.This way they free up resources on the dwh server and run ETL at that box to minimize impact on ongoing 24/7 oltp side.
Resource governor could help assigning the right priority to the workloads during the ETL processing.
If you want my two cents, I would let OLTP alone and run everything from DWH.
-- Gianluca Sartori
September 9, 2011 at 4:13 am
Gianluca Sartori (9/9/2011)
ALZDBA (9/8/2011)
I've seen systems where regular DWH processing is being halted during the load window.This way they free up resources on the dwh server and run ETL at that box to minimize impact on ongoing 24/7 oltp side.
Resource governor could help assigning the right priority to the workloads during the ETL processing.
If you want my two cents, I would let OLTP alone and run everything from DWH.
Me too. I would also pull everything to staging table(s) before doing any transformations to help minimise the impact on the OLTP system (I have seen systems get hopeless performance problems because the import to DWH was holding locks on the OLTP data while it did validations and transformations - found it difficult to believe anyone could be that stupid, but as I've seen it maybe it's worth saying "don't do that, stage separately first").
But you haven't told us enough about you system for our assorted 2 cents worths to be worth even that much.
Tom
September 9, 2011 at 5:48 am
Another consideration is also how you plan on capturing new or changed records.
This also applies to dimensions you may use in the warehouse.
Triggers in the background to capture the keys for later processing can be a good avenue.
I agree Staging is very important, along with error handling.
Plan for how to recover in case an error stops things half way through processing.
And a process for notification when a failure occurs.
Plan for expansion, both in data storage vokume and dimensionality.
Some of this can also be influenced by if you are building a cube off the DW, or it is just database.
Consider getting a book that covers the whole general topic of Data Warehousing.
These forums can be great for specific questions and troubleshooting.
With a topic so braod, you will likely get some good comments, but a book might cover gaps and be organized much better.
Greg E
September 9, 2011 at 6:15 am
Ninja's_RGR'us (9/8/2011)
Five 9 means you are required to be up 99.999% of the time.I'm not sure about my math on this but it would mean that you have roughly 26 seconds per month aloted for downtime.
It's a fun thing to play with.
Yes, 26 is about 26.29746, and there are 2,629,746 seconds on average in a Gregorian calendar month. Even for users of Easter Orthodox calendars (Julian, New or Revised Julian) it's still about 26.
But for some types of month "about 26" is too big; The shortest sort of lunar month is the draconic month (the period from when the moon crosses the earth's orbit to the next time but one it does so) and that's a fraction of a second less than 2,351,136 seconds, so results in five nines allowing average monthy downtime of only "about 24 seconds per month" (and average sidereal, anomalistic, and tropical lunar months are all slightly longer than the draconic month but still shorter than 2,400,000 seconds, so also result in "about 24"), and the longest kind of lunar month (synodic month - time between two successive new moons) at a few millisecond below 2551443 seconds brings us back to "about 26" (but almost a whole second less than the per calendar month figure).
Religion can affect the number too. In the Hijra calendar (which is neither a solar calendar, nor a calculated lunar calendar) when a month begins can depend on the weather (since it depends on observation, which can be affected by cloud cover) but I think the amount of that influence is not permitted to be greater than 1 day, and that all months have to be either 29 or 30 days long (each named month can be either length, depending on weather conditions at the end of the month, which of course may be different in different countries using the Hijra calendar). I think the rules for starting a month mean that the average (over a long enough period) number of seconds per month is the same as for synodic lunar months, but I'm not an Islamic jurist so if you want to know whether this is correct you should consult someone who is better qualified than I am. So I guess that in most Islamic countries five nines means "average downtime limited to a fraction over 25 and a half seconds per month" instead of "average downtime limited to a fraction over 26 and a quarter seconds per month" as expected by people used to any of the main Christian calendars.
I guess too that there are other sorts of calendars used around the world, by states or religions or tribes, but I don't know about them so can't say how much down time per month five nines would permit with those calendars.
Tom
September 9, 2011 at 6:21 am
Tom.Thomson (9/9/2011)
Ninja's_RGR'us (9/8/2011)
Five 9 means you are required to be up 99.999% of the time.I'm not sure about my math on this but it would mean that you have roughly 26 seconds per month aloted for downtime.
It's a fun thing to play with.
Yes, 26 is about 26.29746, and there are 2,629,746 seconds on average in a Gregorian calendar month. Even for users of Easter Orthodox calendars (Julian, New or Revised Julian) it's still about 26.
But for some types of month "about 26" is too big; The shortest sort of lunar month is the draconic month (the period from when the moon crosses the earth's orbit to the next time but one it does so) and that's a fraction of a second less than 2,351,136 seconds, so results in five nines allowing average monthy downtime of only "about 24 seconds per month" (and average sidereal, anomalistic, and tropical lunar months are all slightly longer than the draconic month but still shorter than 2,400,000 seconds, so also result in "about 24"), and the longest kind of lunar month (synodic month - time between two successive new moons) at a few millisecond below 2551443 seconds brings us back to "about 26" (but almost a whole second less than the per calendar month figure).
Religion can affect the number too. In the Hijra calendar (which is neither a solar calendar, nor a calculated lunar calendar) when a month begins can depend on the weather (since it depends on observation, which can be affected by cloud cover) but I think the amount of that influence is not permitted to be greater than 1 day, and that all months have to be either 29 or 30 days long (each named month can be either length, depending on weather conditions at the end of the month, which of course may be different in different countries using the Hijra calendar). I think the rules for starting a month mean that the average (over a long enough period) number of seconds per month is the same as for synodic lunar months, but I'm not an Islamic jurist so if you want to know whether this is correct you should consult someone who is better qualified than I am. So I guess that in most Islamic countries five nines means "average downtime limited to a fraction over 25 and a half seconds per month" instead of "average downtime limited to a fraction over 26 and a quarter seconds per month" as expected by people used to any of the main Christian calendars.
I guess too that there are other sorts of calendars used around the world, by states or religions or tribes, but I don't know about them so can't say how much down time per month five nines would permit with those calendars.
I wasn't expecting to get so much! Kind of like Paul's "comment / almost article" he made yesterday for the Right Kind of Join QOTD!
So bottom line, 25 sec gets you maybe 2-5 failovers on a clustered server. I've heard they take only a couple seconds. But with such a little window, that sounds almost impossible to maintain!
September 9, 2011 at 6:28 am
On the original question, I usually try to do most of the work on the DW server. Busy OLTP systems are usually more sensitive to load increases than OLAP systems are, since OLTP usually means lots of small locks, and code/settings/etc., are all optimized for a few rows locked at a time, where a DW load usually requires locking whole tables.
If they're on different servers, I usually try to ship the data over to the DW server in as raw a format as possible. This helps prevent MS DTC from locking the data for excessive periods of time. Once it's over there, then begin work on it.
As mentioned, it can sometimes help to stage the data separately on the OLTP server, but I find this less necessary these days. You can avoid most of the simple locking issues by using either a full-on snapshot (Enterprise Edition only), or snapshot isolation (not available cross server, but if you set the database to read_committed_snapshot, you get the same effect for default isolation - row versioning instead of locking/blocking). Sometimes it's helpful on really busy systems to use snapshot replication to send data over to the OLAP/DW server, and then digest it over there. Depends a lot on how active the OLTP server is and how heavy that makes the snapshots and the replication process.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 9, 2011 at 6:47 am
BTW this topic has been discussed at the first day of 24HOP [/url](24 hours of PASS free online training sessions)
Session 09 - Start time 20:00 GMT on Sept 7
title: So How Does the BI Workload Impact the DB Engine?
Presenter: Denny Cherry, Stacia Misner
Interesting considerations!
Sessions have been recorded and will get published later on.
It has been a marvelous 24HOP !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2011 at 7:10 am
Shurkadze (9/8/2011)
I have a OLTP on a server1 and DW on a server2. ETL procedures populate DW on server2 using source data from server1.Is it better to run ETL on server1 or server2 and why?
Short answer is: On server2 because of performance considerations.
Not so short answer is:
OLTP systems are in general very sensitive to performance changes - after all the company relies on OLTP systems to run its operation.
Having said that, you don't want to add any stress to the OLTP systems therefore ETL process should be designed to impact at less as possible such systems - move newly inserted/newly updated data from server1 to server2 into a staging area as fast as you can then proceed to massage data and load on server2.
In short. the E phase of ETL runs on server1 while the T & L phases run on server2.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2011 at 7:18 am
Ninja's_RGR'us (9/9/2011)
So bottom line, 25 sec gets you maybe 2-5 failovers on a clustered server. I've heard they take only a couple seconds. But with such a little window, that sounds almost impossible to maintain!
I generally prefer to think of it over longer windows than a month: five nines means 52 minutes and about 35 and two thirds seconds over 10 years, or 5 minutes 15.5 secs per year; and I want to understand what the shortest period "five nines" must apply over is - if it has to apply over each individual minute, it is clearly not possible (the system must never go dow for more than 0.6 milliseconds, and we don't have any clustering that will achieve that). Usually when a customer says he wants "five nines" suggesting some real figues (for example saying "I require 20 minutes per month down time for system maintenance - that precludes five nines") will lead to a discussion of real requirements that will change the requirement into something a bit less difficult to do (and a lot less expensive to procure and own) than five nines, and which meets the business need just as well.
In my view, anyone who wants "five nines" for a 24 hours per day 7 days per week OLTP system probably either has the wrong business model or hasn't turned "five nines" into numbers like that (or even the monthly 25 or 26 seconds) that actually mean something to him rather than just beiung a buzz phrase he's picked up from somewhere. And unless the system is to be completely behind closed doors with no internet access whatsoever and no-one allowed to bring in any non-empty media (ie all media must be unformatted when introduced to the system - and that includes all the end-user terminals in the OLTP system) from outside other than media provided by the software component suppliers and thoroughly tested before it gets anywhere near the real system anyone who offers to provide such a system has clearly forgotten slammer (or believes that the like will never happen again, which is optimistic to say the least) and is either a fool or a charlatan or (quite likely) both.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply