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-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 76
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.
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19963 Visits: 7660
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
prescientdba
prescientdba
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 713
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.
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16799 Visits: 4634
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)
Smile Smile Smile
Health & Nutrition
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

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

----------------------------------------------------
How to post forum questions to get the best help
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