Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Recommend PC Specs for SQL Server

Posted on 2013-05-28
5
Medium Priority
?
516 Views
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.

Thanks
0
Comment
Question by:Thomasian
5 Comments
 
LVL 16

Accepted Solution

by:
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..

hth

Mike
0
 
LVL 4

Assisted Solution

by:rlog
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.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
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 ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
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.
0
 
LVL 22

Author Closing Comment

by:Thomasian
ID: 39206553
Thanks for all the suggestions!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

876 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