We are working for a client with an asp.net/SQL server application which has been using a SQL Server 2005 system till now. The client has decided to upgrade the database to SQL Server 2008 R2 and has procured a powerful server machine with 16 processors with enough RAM and installed Windows Server 2008 64 bit on the system. Now we are tasked with the installation of SQL Server 2008 on the system followed by migration of the existing DB from the SQL 2005 box. Now I am not a SQL Server DBA nor am I a System Administrator by role. Hence, in spite of reading up a lot of the literature on the net, I am not quite able to make sense of it all or put it in the right order of execution. Can somebody explain me the following items, preferably in terms of bullet lists:

What are the points of consideration during installation of SQL Server 2008, specifically in terms of

  • Configuring memory usage

  • Configuring the SQL Server to take advantage of the multiple processors available

  • Other factors to configure to enable SQL Server 2008 features for performance and scalability

  • Other points, if any, for configuring the Windows Server 2008 with respect to SQL Server 2008

Note: We will only use the Database Engine services in SQL Server. The applications does not use SSIS, SSRS, etc.

  • CPU: more CPU's/faster (not just mhz) is better! You get more speed so your database performs faster. I say, always get the best if you can afford it.

  • Memory is probably the most significant piece of hardware that affects SQL Server's performance. Ideally, your entire database should fit into RAM. Unfortunately, this is not often possible. At the very minimum, try to get enough RAM to hold the largest table you expect to have, and if you can afford it, get all the RAM your server can handle, which is often 2GB or more. There is no such thing as too much RAM.

  • These are features like dynamic caching and prefetching data to speed up your queries, the database will anticipate what you are doing.

  • Some other points: I/O is probably the one of the more important things as well. If you have slow I/O than this can become a very big bottleneck. Get some fast drives in there to make sure this doesn't happen.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.