I am hoping that you may be able to assist me in evaluating the needs of our school district that is purchasing a software package that uses SQL Server as the backend database.

Question

I am hoping that you may be able to assist me in evaluating the needs of our school district that is purchasing a software package that uses SQL Server as the backend database. Currently, we have one server per school that acts as the authentication server, printer server, file server, and application server. None of these servers have SQL Server currently installed on them.

Firstly, I am concerned about putting such a resource intensive application on each site's main server. With the limited amount of funding, I believe this is the preferred strategy of the administration. However, I believe we will see a performance issue. What would you suggest in this situation?

Secondly, I am concerned about the amount of resources needed to serve data to 20-50 users per server. I searched the Internet high and low to find a "general" idea of what kind of system resources that I will need based on a set amount of users that will be accessing the server. Would you be able to make any recommendations?

Any suggestions would be helpful. I am VERY green to SQL Server and I would like to provide the best solution to our school district.

Answer

Estimating SQL Server usage is very difficult, almost impossible unless you perform tests on your own under realistic circumstances, which is often impractical. That's why you haven't found much advice on the Internet on how to proceed.

My first advice would be to ask the vendor if they have any recommendations based on their experience, or perhaps you can call other schools who use this software to find out their experience.

Generally, SQL Server should not be run a domain controller, and better yet, it should be located on a dedicated server. If you try to run SQL Server on the current servers you have described, performance most likely will suffer big time. What you can do now is to find out how busy your current servers are, and if you find they are very busy, then you have easy proof that adding SQL Server to them is not a good idea. But if you find the servers are not used much, then perhaps SQL Server will run, although not to its optimum performance. If your current servers have less than 512MB of free RAM available, I would definitely not run SQL Server on them, or if you have no choice, upgrade the RAM.

Is each server connected to a WAN? Is it possible to invest on a single SQL Server and put in in a central location, then have the application access it over a WAN? This may or not be practical, based on how well written the application is. Another option would be to run the application under Terminal Server (and SQL Server on its own box), and allow users to connect to a Terminal Server session over the WAN. Both the Terminal Server and the SQL Server should both be located together in a central location. Again, your application will determine if this is possible or not.

I wish I could give you better advice, but it is hard to do. Where I work, we always use dedicated servers, and always purchase large (4 CPUs, 4GB RAM) because we also can't always predict load. It is cheaper in the long run to get better equipment up front, than it is to fix hardware-related performance problems later.