Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

661 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