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 Thursday, April 25, 2013 5:20 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
Jeffrey Williams 3188 (4/25/2013)
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.


We already have a license for our ETL server... which is what bemused me. The organisation I'm working for is relatively small and budget-conscious. It seemed like a luxury to have a dedicated ETL machine that pretty much sits idle for around 20-22 hours each day. Both in terms of under-utilised hardware and in terms of licencing costs.

Post #1446752
Posted Thursday, April 25, 2013 5:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 6,158, Visits: 7,221
Heya Ty,

First, let me add another voice to lament the situation you find yourself in. I'm also a major proponent of a separate ETL server, although the size of your organization really doesn't warrant it... because the reasons it's important aren't important to you... yet.

First and foremost, a dedicated ETL server allows for more effecient security and organization. It means that individual servers have their SQL agent/schedules/etc dedicated to simple maintenance to their hosted items. Index rebuilds, backups, etc. All ETL work and the like are also in a single place, so there's no hide and seek for packages that pull instead of push when you do ETL changes, etc.

Next, you don't end up in memory wars with your existing applications when you're running large processed in mid-day against your critical systems.

Thirdly, as mentioned, reboots and installs and the like on a separate ETL server allow for rebooting the SSIS components without requiring a critical system to be taken down because someone wants a new DDL library for some random off the wall process they're building.

None of these apply to your environment. You're wasting a perfectly good and purchased license to... do practically nothing with it. You could have a dev environment on that weaker box! You could... well, a lot of things.

I would seriously consider moving the SSIS/ETL components over to the warehouse server for now, until your organization actually grows large enough to be important. Use the old ETL server as a dev box, and get your developers used to playing in the sandbox without breaking production.

That's... a mess. You need to decide if the politics are ripe to start fixing things, or if you want to duck and just collect your paycheck. Either way, I'd write up the email explaining the logic behind the changes and pass it along to those who matter, so at least you have said paperwork to cover your arse when someone tries to bring the hammer down on your head.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1446754
Posted Friday, April 26, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 27, Visits: 662
We have a separate ETL server in our environment. It gives us the separation of layers as was stated earlier, but more importantly for us, it gives us one central server to manage all of the interfaces we having running in our environment rather than running them on either the source servers or the target servers. We have many nightly ETL jobs running on this server where only one of them is for data loads to our data warehouse. The remainder are for data integration/synchronization purposes.
Post #1446993
Posted Friday, April 26, 2013 8:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:55 AM
Points: 1,615, Visits: 2,118
I think the priority needs to be on separating testing and development from production, and you can worry about mxiing ETL into production at a later date. Just getting production cleaned up will be a large effort, no matter how small the organization or enterprise. It's amazing what the passage of time can accumulate. Just documenting what's already there is task one, and that will quite likely take far more time than you might be willing to believe. You may even find yourself with code for which there's no source in your .NET side of the house, or, make the revolting discovery that the deployed code is actually significantly different from what you thought was the documented source, and thus that said allgedly correct source, isn't correct; just after deploying an update that used the allegedly correct source, only to discover that you have no good backup of the previously deployed, properly working, code. That leads to task two: Don't change ANYTHING until EVERYTHING is documented, and most importantly, VERIFIED. It's always amazing just how many dependencies you discover AFTER something changes. At the very least, be 100% sure you can go back to the previous state of affairs before making changes.

Hope you find it all.... and good luck with your efforts!


Steve
(aka sgmunson)

Internet ATM Machine
Post #1447021
Posted Friday, April 26, 2013 10:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 317, Visits: 823
Craig makes a good point above. In a small organisation you don't have as many developers and you are likely to be familiar with all ETL processing. There is little to no chance a developer (like in another department if a big company) can deploy his ETL somewhere and others not know about it, as can be the case in a larger company. You likely don't have that many servers to hide things in. Thus those larger business, that can also afford extra licenses, will want all developers' ETL's centrally located for easier administering such quality checks.
Post #1447099
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse