Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Machine Specs and Layout

Author  Topic 

etechpartner
Starting Member

3 Posts

Posted - 2012-12-23 : 11:55:29
I need to set up a VM ( or physical machine ) for a web application that looks a lot like Quicken Online ( Accounting System ). It will have approximately 100 concurrent users and 500 total users to start with.
Using either SQL 2008R3 or 2012, could you please recommend a machine config and DB settings that make the most sense. Is it a good idea to use SSD drives? Should tempdb have its own drive and number of files equal to cores used by the machine. For low user count like this, with low frequency of commits, should the db be set to READ_COMMITED_SNAPSHOT
Any best practice suggestions? This is a completely new setup so I am very open to recommendations from this forum.

ovc
Starting Member

35 Posts

Posted - 2012-12-24 : 13:35:29
Hi

in all these kind of question it always depends of several factor.

It is good always to keep in mind which is the target and how the system is going to evolve in 6 months or more regarding concurrent users and user load.

Regarding the number of concurrent number of users you should have a look at the CPU, max degree of parallelism and max worker threads options. For 100 concurrent users each 64-bit Machine should be enough.

Regarding SQL Server Version it should be your choice also regarding the application requirements (I assume you meant SQL 2008 R2). In SQL 2012 there are especially new high availability features and a better memory management.

Regarding disks: I would recommend at start to use normal hard drives. I had some issues regarding disk failures and data loss on SSD drives.

Regarding tempdb: It is necessary only if you have a lot of tempdb activity which can lead to tempdb contention.

I would use READ_COMMITED isolation level and activate READ_COMMITED_SNAPSHOT only if it is really necessary.

CHECKLIST to perform:
1. Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
2. Split mdfs(and ndfs) on different disks like the log files (ldf). (there should be different physical drives, not only disk partitions)
3. set max server memory setting
4. set max degree of parallelism setting. For maximal recommandation values use the info from the following article: http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/

Plan a good disastery recovery scenario if possible without single point of failures.
Go to Top of Page
   

- Advertisement -