Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Separate ETL server Expand / Collapse
Author
Message
Posted Sunday, February 10, 2013 7:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:05 PM
Points: 7, Visits: 33
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
Posted Monday, February 11, 2013 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1418237
Posted Monday, February 11, 2013 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:05 PM
Points: 7, Visits: 33
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
Posted Monday, February 11, 2013 10:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:46 PM
Points: 1,051, Visits: 2,554
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
Post #1418544
Posted Monday, February 11, 2013 2:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1418628
Posted Monday, February 11, 2013 3:34 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
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
Posted Monday, February 11, 2013 5:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:05 PM
Points: 7, Visits: 33
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
Posted Monday, February 11, 2013 5:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
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
Posted Thursday, April 25, 2013 11:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 356, Visits: 871
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
Posted Thursday, April 25, 2013 1:03 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
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 »

Add to briefcase 12»»

Permissions Expand / Collapse