Recommend PC Specs for SQL Server

Posted on 2013-05-28
Medium Priority
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
LVL 16

Accepted Solution

DcpKing earned 668 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 668 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 664 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When the first reports of the initial sales of Nintendo Switch in the Land of the Rising Sun appeared. In Japan, only 330,637 consoles were sold for the first day. But many large retail chains have already sold out the entire edition of the console …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 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