We have had some performance problems with a server that is running Terminal Services, SQL Server, and Access databases.

Question

We have had some performance problems with a server that is running Terminal Services, SQL Server, and is also the repository for multiple Access and Access databases.

I have had some advice from SQL Server gurus that running SQL Server and Terminal Services on the same server is a no-no. The Compaq SQL box has 1.7GB of RAM (boosted due to the problems) and quite a high general spec.

Do you have any suggestions for boosting performance?

Answer

The SQL Server gurus you have been talking to are correct. You do not want to run SQL Server and Terminal Services on the same physical server. Both are resource hogs, and they don't play nice together on the same server. While it is possible to play with some tuning options to make them play together a littler better than they may be now, it still is not a good idea to run them both on the same server.

For best performance, SQL Server should run on a dedicated server. The same is true for Terminal Services.

The next issue you should deal with is getting rid of your Access databases, moving that data to SQL Server instead. While moving the data, and upgrading the applications using the Access databases can be a pain, it is definitely worthwhile if you are interested in getting the best overall performance for all of your data management needs. In many ways, I regard Access more of a "virus" than I do a enterprise-quality database. As a SQL Server DBA, I consider it my obligation to seek out and destroy every Access database I run across.