<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Data Warehousing
»
Integration Services
»
Separate ETL server
15 posts, Page 1 of 2
1
2
»»
Separate ETL server
Rate Topic
Display Mode
Topic Options
Author
Message
Tyberious Funk
Tyberious Funk
Posted Sunday, February 10, 2013 7:39 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:35 PM
Points: 7,
Visits: 28
I have recently inherited responsibility for managing a small data warehousing team, using SQL Server 2008 R2. My predecessor, on the advice of our enterprise architecture team, has set up the environment to have separate servers for ETL and the warehouse DBMS. This was on the grounds that it is considered "best practice" to separate the application layer from the database layer, and also that it would result in "improved performance".
Performance improvements are unlikely. Our ETL jobs all run overnight, when no other work is going on, and rarely take more than 1-2 hours. The Production database is actually more powerful than the ETL server, so if they were in a combined environment would probably run
faster
.
In terms of separating the application layer... I completely understand this is considered good practice. But our warehouse is a very small and generally not considered business critical. Important, yes. But not business critical. DBA / system admin resources in the organisation are limited, so it seems like overkill to maintain a separate server just for ETL.
To put things in perspective...
We have no Dev/Test environment, just Prod. Development and testing work gets done directly in the production environment, and changes are frequently made on the fly. This is on the grounds that maintaining an additional environment would be too resource intensive. Understandably, our Production environment is a massive mess and it has become extremely difficult to distinguish work-in-progress from completed tasks.
Personally I think I would prefer a separate Development and Production environment (both with ETL and database) rather than a single Production environment.
Post #1418186
Phil Parkin
Phil Parkin
Posted Monday, February 11, 2013 12:57 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242,
Visits: 9,494
Is this just a rant (it's a good one!), or are you hoping for comments?
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1418237
Tyberious Funk
Tyberious Funk
Posted Monday, February 11, 2013 3:01 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:35 PM
Points: 7,
Visits: 28
Sorry if it came off sounding a bit "ranty". That wasn't my intention... I was wanting some opinions on an optimal architecture in a constrained environment.
My personal experience has tended to vary from two extremes -- either very small, single server environments (mostly doing pure development work) or very large enterprise environments running business critical reports. I'm not used to the "in between" situation. Personally I would have thought in terms of making compromises, separating application and database layers would have been less important than having separate development and production environments.
I guess I was hoping for some practical experiences... what are some of the reasons for having a separate ETL server (beyond performance considerations)?
Post #1418274
wolfkillj
wolfkillj
Posted Monday, February 11, 2013 10:17 AM
Right there with Babe
Group: General Forum Members
Last Login: Monday, May 20, 2013 6:54 PM
Points: 721,
Visits: 1,375
Tyberious Funk (2/10/2013)
<snipped>
We have no Dev/Test environment, just Prod. Development and testing work gets done directly in the production environment, and changes are frequently made on the fly. This is on the grounds that maintaining an additional environment would be too resource intensive. Understandably, our Production environment is a massive mess and it has become extremely difficult to distinguish work-in-progress from completed tasks.
Personally I think I would prefer a separate Development and Production environment (both with ETL and database) rather than a single Production environment.
OK, as I read your predecessor's opinion, having the separate ETL server is important to take load off a production server that is already all mucked up with development and testing work? Yikes!
If the production server can handle the overnight ETL processes without hindering production processes and you can only afford two servers, I would absolutely push for separate development and production environments. The additional effort to manage a development environment (that in almost all respects will be configured exactly the same as the production environment, permissions being the most obvious exception I can think of) should be manageable, even with a small team. You say that the data warehouse is important but not business-critical, but I expect that the users of that data warehouse would be calling for your head if it is unavailable for two or three days while your team rebuilds it (you mentioned that your DBA resources are limited) because someone ran some ill-conceived, un-reviewed, and untested code that brought it crashing down in flames.
Post #1418544
Phil Parkin
Phil Parkin
Posted Monday, February 11, 2013 2:01 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242,
Visits: 9,494
I agree with the previous comment. Even if performance is degraded slightly (and it's not clear that it will be), the fact that you can maintain a separate dev environment is worth it on its own.
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1418628
Michael Valentine Jones
Michael Valentine Jones
Posted Monday, February 11, 2013 3:34 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 2,945,
Visits: 10,514
I prefer to have the ETL (SSIS) installed on a different server because it is quite common to need to install or update additional software, controls, DLLS, etc. that might need a reboot, or that might experience a problem that will require a reboot.
Also, you might need to have the ETL (SSIS) on a different version of SQL Server than the main database server.
Post #1418666
Tyberious Funk
Tyberious Funk
Posted Monday, February 11, 2013 5:17 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:35 PM
Points: 7,
Visits: 28
Michael Valentine Jones (2/11/2013)
I prefer to have the ETL (SSIS) installed on a different server because it is quite common to need to install or update additional software, controls, DLLS, etc. that might need a reboot, or that might experience a problem that will require a reboot.
Also, you might need to have the ETL (SSIS) on a different version of SQL Server than the main database server.
This is an interesting point. At the moment we are running 2008 R2 across all the environments, but I'm wanting to investigate upgrading to 2012. Is it possible to run your ETL on 2008 R2 and your DBMS and Reporting Services environment on 2012? Well... I'm sure it is
possible
. But is it
practical
?
Being able to update systems progressively would certainly make the process less painful (if we decide to go ahead).
Post #1418691
Michael Valentine Jones
Michael Valentine Jones
Posted Monday, February 11, 2013 5:57 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 2,945,
Visits: 10,514
Tyberious Funk (2/11/2013)
Michael Valentine Jones (2/11/2013)
I prefer to have the ETL (SSIS) installed on a different server because it is quite common to need to install or update additional software, controls, DLLS, etc. that might need a reboot, or that might experience a problem that will require a reboot.
Also, you might need to have the ETL (SSIS) on a different version of SQL Server than the main database server.
This is an interesting point. At the moment we are running 2008 R2 across all the environments, but I'm wanting to investigate upgrading to 2012. Is it possible to run your ETL on 2008 R2 and your DBMS and Reporting Services environment on 2012? Well... I'm sure it is
possible
. But is it
practical
?
Being able to update systems progressively would certainly make the process less painful (if we decide to go ahead).
I have setup a number of systems with ETL on dedicated servers, and they run fine with different versions of SQL Server on the ETL, source, and target servers.
Post #1418706
mmartin1
mmartin1
Posted Thursday, April 25, 2013 11:09 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 193,
Visits: 642
I've seen it where many developers create ETLs and thus a dedicated server makes sure they are all stored in production in one location, regardless of the servers that are the actual subjects of the dataflow.. Of course you are putting all your eggs in one basket should something happen to the server and there is no failover.
Post #1446629
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Thursday, April 25, 2013 1:03 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317,
Visits: 9,216
Let's not forget that installing SSIS on its own server requires purchasing separate licenses for SQL Server on that server. If you are running SSIS on an ETL server and do not have the licenses required - it could end up costing you a lot more than you realize.
For that reason alone, I normally setup a reporting system/data warehouse and include the ETL processes (SSIS) on that server. This leaves my production system without the requirement for additional components. Since the reporting/data warehouse server is just that - it is much easier to schedule a restart as needed.
For a constrained environment like this - I would definitely recommend the above scenario. However, keep in mind that wherever you install SSIS you need to account for the additional memory it requires. How much you leave for SSIS will be determined by your ETL processes and how they are written and configured.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #1446683
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
