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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virus On motherboard 6 46
Sql Query Datatype 2 19
Executing SSIS with a Stored Procedure Permissions 12 26
Need SQL that flips  value 5 10
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now