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.”