Recommend PC Specs for SQL Server

Posted on 2013-05-28
Last Modified: 2013-05-29
I currently have a desktop application which records transactions on a SQL Server 2008 R2 Express database. There are about 100+ branches which uses this app.

Now I need to create a central server which will be used for centralized reporting. The central database will be uploaded once a day with an approximate size of 1GB per day.

I have never worked with database with that size so I need some recommendations:
1. The application for the branches is currently using SQL Server 2008 R2 Express. Should I use SQL Server 2012 for the central server or just keep using 2008 R2? And what edition?

2. The branches are currently using Windows 7. Is it ok to just use Windows 7 for the central server or is it better to use windows server?

3. Would a standard desktop PC be ok or is there a "server" PC? The computer would be in use 24/7.

4. Recommended processor and memory. Around 1GB of data per day.

Question by:Thomasian
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Accepted Solution

DcpKing earned 167 total points
ID: 39203378
1. SQL Server 2012 would be better - the tools that come with it and the additions - like column indices - can really benefit you as your tables enlarge. Certainly you'll need Standard Edition, but look at the others with an eye out for what you want to do with it. For example, judging by recent experience there may well be a SQL Server 2014, so you may not want to shell out for a very expensive edition until then, given that you may not be ready to use it until then (migrations always take longer than you expect!).

2. You could use Win7, but I'd rather suggest you go for the latest Windows Server version. It'll see you through this and the next version and will offer more facilities for supporting the database than will Win7.

3/4. Definitely not! Minimally get something with 4 cpus and 64 GB RAM. You You also need hard drive space to store that GB of new data daily, and you also have to plan for disaster - a second, almost-equally-capable machine or some plan from your hardware supplier - so that your business can survive something like a tornado, flood, or gas leak/explosion.
You don't say whether your GB of data is accumulating or getting replaced daily. Either way you're going to be doing a chunk of importing and, I presume, reporting.

As for the size, it won't be the GB that's the problem (at first). It'll be that at the end of the year you'll have a third of a TB !!  The way you work with tables with millions of rows is different to the way you do with just a few thousand!

One tip: decide how big you want your database(s) to get and allocate their sizes accordingly now. Autogrow is for emergencies. Similarly, make the space for your log files (on another drive!) pretty big too, and start them off large. That way you won't end up with fragmentation from the file system hitting your database system..



Assisted Solution

rlog earned 167 total points
ID: 39203686
I agree with Mike. RAM is cheap so while you're on it you might as well run 64gb. 4xcpu is minimum - I would probably go for 6 or 8. Use windows server.

Version - go with Standard edition SQL 2012. Latest I have seen is that we'll have SQL 2012 R2 in 2014 and SQL 2012 R3 2016. Have a look at the features on Std edition - is there something you need that's in the Enterprise Edition. We normally have Windows 2008 Enterprise (we hate the metro on servers). It's easy to upgrade SQL Server in case you need enterprise edition on a later stage.

Disk layout:
C: 256gb (mirrored)
E: 200gb (Mirrored) - SQL Instance installation
F: 1000gb (Raid 5 or mirror) - SQL mdf files (data files)
G: 500gb (Raid 5 or mirror) - SQL ldf files (transaction log)
H: 1000gb (single disk or mirrored depending on your) - backup file or use backup agents like backup exec and so on to take it directly to tape..

Take good notice of Mike's recommendation of pregrowing files... just my experience.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 166 total points
ID: 39204031
1-If you don't need high availability stick to 2008.  2012 will cost lots of money.
2-Forget using desktop OS for a server who's supposed to work 24 hours/day
3-Depends how critical i syour application and data.  Using a desktop raises security issues regarding data as well as physical access to your data.  If you value your data, host it in a secure datacenter on a server box.
4 processor sizing has nothing to do with data growth volume.  You need to size up according to usage.  how many people will be using the server ?
LVL 23

Expert Comment

ID: 39204038
<<There are about 100+ branches which uses this app.>>
I missed this comment.  Forget using a desktop for hosting this.  Buy a real server, host it in a secure location and stick to 2008 R2 if you don't need high availability.  Get some help into determining the performance profile of the application to size it up properly.
LVL 22

Author Closing Comment

ID: 39206553
Thanks for all the suggestions!

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question