September 27, 2007 at 8:22 am
We’re still relatively new to SQL Server 2005, with only a handful of low use installations. For any given installation, all components (SS2k5, SSIS, SSAS, …) are installed on a single server. In a more robust environment, what’s the recommendation for distributing these services to separate servers?
I appreciate your insights,
Bert
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
September 27, 2007 at 8:47 am
You will probably get a lot of conflicting information on this topic. MS does not seem to want to put anything into this argument other than to say that it depends on your situation.
Here are my thoughts - SQL and Analysis Services use memory in a very different manor and have very different usage patterns. That would lean me toward keeping them on different servers and since 64 bit processors are no longer significantly different prices, I would lean toward a dedicated 64 bit server for AS. SSIS is relatively unhappy on 64 bit, so that I would either leave on my SQL server or if I have very large databases, pt on it's own 32 bit server and go 64 bit for SQL.
Keep in mind that SSIS moves data, so being close to the data at one or both ends of the movement is good, but it is also multi-threaded and happy to be a resource hog, so having it's own CPU's can make up for added network traffic.
I think I was sufficiently vague and gave you no answers, but hopefully there is something to get you thinking.
September 27, 2007 at 8:59 am
Very helpful. These are the exact issues we'll be facing in the near future.
thank you,
Bert
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
September 28, 2007 at 3:06 am
I agree with Michael but would add I would also put Reporting Services on a different box. SSRS in 2000 and 2005 needs IIS in order to run, and having this lot competing for memory with SQL would only be OK under relatively small workloads. Keep the SSRS databases on your main SQL box, but do the rest of your SSRS work on another box.
If you have a large 64-bit box, then you can put more components on to it and still get good performance, but ultimately the poor workload management in Windows is going to mean you need to isolate workload to different OS instances to get optimum throughput.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 28, 2007 at 6:07 am
I always forget about reporting services. I agree - keeping that off of your SQL server is a good idea. All of these servers should have sufficient bandwidth between them. They should all be plugged into the same switch - preferrably Gigabit or fiber between them - to reduce network contention.
Keep in mind that these suggestions to put things on different servers means you have to license EVERY server. Putting SQL on one server and Reporting Services on another means you need a SQL license for each of the servers. This can get expensive quick.
Always look at the situation. Sometimes you can keep the cost the same by purchasing a really nice, very fast, 64 bit server to run everything for the same price as four servers and all of the appropriate licensing.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply