When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup.
The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data Quality Services, PolyBase, Integration Services, Analysis Services, and Reporting Services – it’s not like you have to put more coins in the front of the server. So what’s the drawback? Why not collect ’em all? What are the pros and cons?
The more you install, the harder performance troubleshooting becomes. They all drink from the same pool of available CPU, memory, storage, and network throughput. When one of them is performing slowly, you’ll be constantly asking questions like:
- “Is this slow because one of the other services are using all the power?”
- “Is this service’s power consumption affecting other critical apps?”
- “How can I monitor which one is using CPU/memory/etc at any given time?”
Patching and upgrades are harder, too. If your team actively uses these services, then at some point, they’re going to want to patch or upgrade just a portion of the services. Classic example: the BI team wants to move to a newer version of Analysis Services to support a cool new reporting tool, but the main app developers can’t certify their app on a newer version of the relational engine yet. If everything’s on the same box, this gets a lot harder. (Coordinating downtime is especially tricky.)
Uptime management is harder. If you want to make any of these services highly available, then you need to get clusters, load balancers, and things like Always On involved. Each of the features has its own unique ways of building high availability, and when you pile them into the same box, you’re not making your job easier.
However, Standard Edition licensing is easier. If you just bought enough licenses to cover this one VM or physical box, then you probably want to consolidate these services all onto that same box. You probably can’t afford to split them out across multiple boxes. (On the other hand, if you’re using Enterprise Edition and licensing per VM host, then you’re much better off splitting these into different VMs for the reasons we mentioned above – it’s not like it’ll cost you more for SQL Server licensing.)
Piling lots of services into the SQL Server does indeed have its costs – even when licensing is “free.”
23 Comments. Leave new
Even if they’re added, I thought you could always disable the services or simply remove them from the installation. I know, I know. But sometimes you’re handed lemons and you have to make lemonade.
Kevin – yeah, absolutely – I’m a fan of disabling them if they’re not being used (just to prevent folks from sneaking something in.)
I get SQL instances preconfigured with no user databases from a cloud vendor. Management here is letting them manage the licenses and we’re told if we create the installation template the licensing is our responsibility. Maybe true, maybe not. But given the nasty licensing surprises they’ve had here and how easy it is to fix the default install, it’s not a bad trade off. (No, it’s not Azure)
SSIS, SSAS and SSRS are pre-installed. so I just shut down the services at that point and build everything out following your guides.
Free plug: Brent’s classes are the biggest bang for the buck I’ve ever gotten from any training classes ever.
Kevin – awww, thanks sir!
I feel like SSIS is the one exception that “can” play well with SqlServer on same box without playground brawls…but that “can” is a really big caveat to kick around because it depends very much on how you use SSIS.
All ETL tools have an inherent weakness that’s just plain physics…if you attempt to join data in an ETL tool (like the newbie classes teach you) it has to bring back everything you’re interested in before the join can take place. That’s a REALLY bad idea unless you’re dealing with trivial data volumes. Yes its possible to cache the data on disk (bad idea) or in memory but the bottom line is 1) you’re doing WAY too much work just so your join looks graphical and 2) it puts a MASSIVE burden on the ETL server. And if that server is also your database server then I guarantee terrible things will happen…terrible things.
So what’s the solution? Rule of thumb….always do table joins in the database, not the ETL. Use views for the joins or write it in the dataflow source but do it on the server where it belongs. That applies to all ETL tools and whether you have the ETL tool on the db server or not.
Good advice. Since “big data”, the trend nowadays is to do more ELT and less ETL. Meaning, letting the database do the hard work and just use SSIS as an orchestrator (it’s really handy in letting stuff run in parallel).
I’ve been moving to that strategy as well. My position is technically sysadmin but got pushed into DBA work when the previous DBA position was eliminated and no other funding was available for consulting. I’m trying to use SSIS as minimally as possible and do most of the ETL in stored procedures, generally by selecting the minimal data I need into a temp table, then selecting it out of there and transforming it as its inserted into the permanent table. I mostly am only importing or exporting flat files with SSIS other than a few things that are just significantly easier to do in SSIS, such as string manipulation using the token function. I also find Visual studio frustratingly slow on my 4 year old work laptop and staying primarily in SSMS to modify a stored procedure is more agreeable to me.
I’ve also separated my app workloads into an AAG that only has the database engine role on it, then reporting databases, PowerBI, SSIS and SSAS are all co-installed in another AAG and use scaleout SSIS to keep one back package or query running on only half of the AAG. They definitely get hammered sometimes, but everything I have in them has lower expectations for performance. The freshest data is about an hour old and users generally have more tolerance for slowness in running reports than using an application.
The thing I am not excited about at all to enable is Python. All the larger python scripts I have seen use up an surprising amount of resources for relatively easy tasks and tend to need updating all the time for a million different module dependencies
Gary, that’s really good advice to do table joins in the database, not the ETL, but what if you have to do a join between two huge tables and they’re on different servers? Or if you have to do a join between a flat file and a huge table?
If you find that you need to do it on a regular basis, you can use techniques like log shipping, replication, or Always On Availability Groups to copy read-only versions of databases to different locations.
I’m also a fan of adding them later (if, and when, they’re needed). Why add a bunch of BLOAT to the Server that isn’t needed right up front? I totally agree with all of this! I feel that Microsoft has a sad reputation for putting WAY more bloatware into their products than is necessary.
If you’re using SQL Server as a data warehouse platform for a traditional BI stack (meaning no real-time requirements), it’s usually OK to put SQL Server, SSIS and SSAS on the same box. During the night, the ETL (or ELT) runs and SQL Server and SSIS are busy, but SSAS is doing nothing until it is refresh time. After SSAS is refreshed, only SSAS is busy handling queries, and SSIS and SQL Server are doing nothing. SSRS, that’s something you want to put on another box depending on how many users you have and what the availability and scaling requirements are.
That being said, you only need to install the SSIS service because you need some binaries to run SSIS packages. After the set-up, you can disable the SSIS service but you can still run SSIS packages. The service is dead weight.
You just described our setup. We do our dirty work in the dark hours. During office hours it’s just serving up reports and OLAP. I agree with Brent, and I’d love to separate them out, but we just don’t have the funding or resources for more. I guess that makes me one of the “little guys”. We just have to make the most of what we have.
For what it’s worth, in the emailed version of this blog entry, the “This week’s sponsor: SQLGrease” section does not have active links, only red underlined text.
try to leave all off my prod servers, don’t care if you make a bad cube, bad ssis package, easier to isolate less chance of developer killing my server. Of course I am licensed at the VM host so much easier to do
I’m facing this question now with 3 x 1Tb size dbs all with large cubes and whole stack ssis ssrs ssas on one 96gb ram 12 core sql2014. Wonder if i move SSRS and SSAS to different server won’t this leave the base staging/data mart server doing bulk of the work serving data to SSAS process task and SSRS Report requests as the underlying DB still on original box.
Greg – I deleted your duplicate comments.
Greg: Yes it will be, but it was doing that anyway. Once you move the others services it should have sole use of the server resources. So it’s not competing for RAM, CPU and priority.
Woops and thanks Brent. Peet – I see that it would help across all resources though i’m wondering. We’re on standard edition and RAM is by far the biggest challenge as we are running near real time integrations and analysis services processing. Would splitting SSAS/SSRS out be better or maybe just spread the large db’s across the two machines leaving all services on one of the machines. I feel the second approach seems messier (as brent mentions in the article harder to troubleshoot etc) says but might let us make full use of standard ed RAM limitations
Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed noow
Stevan – for questions, head over to https://dba.stackexchange.com or your favorite Q&A site.
What is the Microsoft best practice?
Should I separate SQL Server Engine and SQL Server integration Server on production environment?
Server 1.
–Install the SQL Server Engine, Agent, FullText, and Browser services.
Server 2.
–Install the SQL Server Integration Services
Questions:
Should I install the SQL Server Engine too on Server 2? Since the SSIS deployment are going
to use Project Deployment Model (uses ispac file)
Edwin – go ahead and read the post. Thanks.
There’s a wrinkle for SSRS. We want to install various non-prod instances on a single VM using SQL 2019. It appears that SSRS does not support this and is limited to one instance per VM. (The SSRS install is separate and manual as well.) So, it appears we have to install each SSRS instance on a separate VM even if it’s not needed. Yuck. If this was production, it would be worse because of the added cost. I’m surprised that there’s not more about this. How have folks adapted to this? Is the answer to use stay with SQL 2016?