Sql-server – SQL Server 2008 Installation pointers for optimizing Performance and Scalability

sql server

We are working for a client with an asp.net/SQL server application which has been using a SQL Server 2005 system until 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.

Best Answer

It is SQL Server 2008 or SQL Server 2008 R2 ?? Because in your question you mention that client want to upgrade from 2005 to 2008 R2 and you're asking about migration from 2005 to 2008. So for sake of answer I am assuming it is 2008 R2

Since you will not be using other services like SSRS,SSAS and SSIS it should make you like a little easy. Some suggestions are,

  1. Use separate physical drives for data and log files for all user DBs
  2. Separate physical drive for temp db, backups,system dbs
  3. Aligned drive partitions (however it should be taken care by itself since you are using Win 2008 R2 but better check 'em)
  4. Setup proper auto increment based on your expected growth
  5. Proper recovery mode (Full recovery mode is recommended)

Actually you can find loads of tips for SQL Server database server configuration. Following links can give you some help. For SQL server 2005/2008 (but can be used for 2008 R2) Or General MSDN help for SQL Server 2008 R2 Or google for "SQL Server best practices", which can be of different categories like installation, dev, security configuration, administration, migration etc. But I really doubt that you will find all-in-one type of solution somewhere because in most of the cases it really depends on your requirements.

Related Question