SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Separate ETL server


Separate ETL server

Author
Message
Tyberious Funk
Tyberious Funk
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 76
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Tyberious Funk
Tyberious Funk
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 76
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)?
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

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

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 11771
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.
Tyberious Funk
Tyberious Funk
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 76
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).
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 11771
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.
MMartin1
MMartin1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

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

----------------------------------------------------
How to post forum questions to get the best help
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7689 Visits: 9966
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

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