A recent request from the SSWUG email newsletter asked how we as DBAs approach and handle application/service consolidation on your SQL Server servers. I have a pretty strong feeling and approach to this topic and emailed my experience to SSWUG…..and to my surprise (for the 2nd time I just heard from another DBA friend of mine), I was “published” in the daily SSWUG newsletter. Even further to my surprise, my entire email reply was included in its entirety. Thanks SSWUG!!
And just in case the link doesn’t work someday, here’s the text of the originally posed topic and my response.
Who Shares Your Database Server?
Many small businesses and often times large businesses find it difficult to have servers deployed for a single purpose. They may be constrained by hardware and software costs, or not have the necessary skills to configure servers according to best practices.
For example, Microsoft encourages the use of integrated security for Sql Server. Best practices teach us that your Domain Controller and Sql Server should reside on different instances of Windows Server. Already we have two servers.
IIS running on Windows, or even Apache, are often best when run on a different server. This is especially true if the web server faces the Internet. In that case, you will most likely have a firewall between your web server and all your internal hardware, and therefore can’t share windows with Sql Server. Even web servers that are for internal purposes only should not share resources with your database.
Other products also compete for resources that are often used in conjunction with Sql Server. SSIS, SSAS, and SSRS all may require significant memory, disk or CPU resources. Many businesses also have Mail servers, application servers, etc.
Let’s hear from you. You live in the real world and have to balance resources with business needs on a daily basis. Why not share things like what you are currently doing, what you like about it, what you would like to change, and the factors that caused you to choose your current configuration. Don’t worry if your system doesn’t follow best practices. The goal here is to share with each other the issues we all face. Send your experience to btaylor@sswug.org
Patrick Says…
We have a decent number of dedicated SQL Server instances (hundreds) to support all of the different business applications utilized in our environment. Server sprawl and SQL Server “Silos” as they’ve been labeled by the Systems Admins, have become more prevalent over the last few years, and to accommodate, most of our web/application/support service servers are run in VMs. We also run our SSRS and SSAS installations on dedicated servers because the required resources to do what they’re intended to do without causing performance grief.
I’ve always been a firm believer that you must hold your ground on providing the most appropriate environment for your SQL Server databases to perform within if you have any chance of meeting rigid SLAs (written or not, expressed or implied), and rarely, if ever, allow any other applications that consume even remotely moderate amounts of memory to be run on my SQL Servers. I’ve seen too many departmental SQL Servers struggle to breath with IIS\Exchange on them and just don’t allow it unless there’s no other way, and I document my stance early on.
SQL Server requires certain things. CPU, Memory, Storage. I rarely run into CPU issues with todays (and even yesterday’s) processors. I rarely spec a server with less than 4GB of RAM and usually much more, but there are a ton of configuration options/needs there. The I/O subsystem usually is where I have to compromise most, but that’s a whole different conversation. But right now we’re talking about role/application consolidation, and to me it comes down to how much RAM and CPU is available for SQL Server with at least 30% overheard (with a little flexibility). If there’s anything left in a departmental/dev/test server, we’ll talk about it. If it’s production, fuggeddaboutit!
-Patrick
