?
Solved

Setting up new SQL server

Posted on 2013-12-16
13
Medium Priority
?
226 Views
Last Modified: 2016-11-23
Hi guys,

I've purchased my new server for my SQL server project and now I'm getting ready to install the OS and SQL 2008. I need advice on how best to configure the machine. I have a Dell Powerwedge 420, 2TB, RAID 5 server 24GB Ram. I need answers to questions like how many partions, etc as well as general "how everyone else does it" information. I understand there are 3 partitions needed 1 for the OS, 1 for the data or SQL db, 1 for the SQL logs? I could have the latter completely wrong but thats my general understanding. Also I need to know what size to make each of the partitions, etc. For my OS I'm going with Windows 12 since I already have a license and SQL 2008. Any advice you guys could give would be great. I will be running two applications off of this server. One is a payroll server with about 3 to 5 users and maybe another instance in the future like Sharepoint. Overall, both of them will probably have about 10 to 20 people in the applications at any given moment. I don't anticipate a heavy load on the machine. With 24GB of ram it should handle almost anythign thrown at it.  Basically, how would you set this up if it were you? Thanks,
0
Comment
Question by:js_sts
[X]
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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39722105
Hi

I believe you have a good understanding on best practices.
You have 2TB hdd space, i would give about 200GB (or more if you are planning to install more heavy software in the future)  for the OS and divide the rest amongst the database partition and log/backup partition.

All other specs sounds like you have some perfect iron setup.

Cheers
0
 
LVL 23

Assisted Solution

by:Thomas Grassi
Thomas Grassi earned 1000 total points
ID: 39722114
If it was me I would install a SSD drive in the server for the OS and use the 2 TB raid drive for all data

You could use a SATA drive also.

I would keep the OS of the Raid drives and would not partition anything.
0
 

Author Comment

by:js_sts
ID: 39722244
I personally don't partition trgr. Usually I get raid 1, and setup the OS with no partion. But I was just trying to follow best practices. If I were to go with a partion approach Patrick, for the logs, how big should it be? 200GB too?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 23

Expert Comment

by:Thomas Grassi
ID: 39722255
Yes you can raid the OS drive using raid one

But again I would get a separate drives for the OS
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 39722273
Best practice is to put a given database's data files and log files on separate physical drives.  Just partitioning one drive won't help if (when) a drive goes bad.

Note that you can put data and logs on the same drive, as long as no individual db has any data file(s) on the same drive set as its log file (there should only be one log file, except temporarily if necessary while recovering from a specific log issue).

You don't necessarily need a separate partition for the O/S.  You'd almost certainly help performance much more by putting tempdb on a separate partition rather than the OS.

24G is not a lot of RAM if you attempt to run SQL and other software, especially SharePoint, on the same box.  You'll need to set SQL's max memory careful and adjust it until you get it to the best fit for your specific situation.
0
 

Author Comment

by:js_sts
ID: 39722338
thanks Scott this will only be a SQL server. Nothing else. I reckon will have 2 instances at most, maybe 3. Nothing else will run on the machine. So from what I'm hearing from you you guys is the best thing to do is to do the 3 partion approach (OS, SQL db, logs). Any other helpful hints?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39722380
Again, just partitioning will NOT help if you lose the drive set, and everything is on one drive set.

Be sure to include backup space in one of the partitions.  You will need to backup the dbs, obviously; you may or may not need to backup log file(s), depending on your specific requirements.  The ultimate physical location of the backups can be slower storage (or even tape), since hopefully you will rarely need to restore a backup.
0
 

Author Comment

by:js_sts
ID: 39722500
Thanks Scott, we will have regular backups of the db probably onto another machine we've designated for backups. The goal for this box really is to use the best practices when setting up the SQL server so that we get the best speed and performance out of the machine. I understand about losign a drive and that happens. I just want to make sure its setup right and this coupled with nightly backups should ensure a long life. Thanks! Any other suggestions before I start up?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39722512
If the previous full backup is OK, with NO current data, then you may be all right.  

My main suggestion is as it was before: you need at least two separate physical drives for a proper SQL setup, period.
0
 

Author Comment

by:js_sts
ID: 39722603
Do you mean two phyiscal drives as in one drive for the db and one drive for the backup?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39722706
No, one for data file(s), one for log file, at least for a given db.

If the backups need to be on the same drives, they should be on the log drive, until they can be moved to another drive/media location.

If you have a single set of drives, and the drives fail, then you've lost everything: the data files, the log file, AND the backups.  What on earth do you do then????
0
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39723262
Hi again,

You are going to serve 25 people and make regular backups and keep your transactional logs on a raid 5 volume. I seriously do not see the need to break up that raid 5 set nor see i need for a extra drive or even raid 1 set for the OS.

My production database runs on a slight thicker machine that you have (2x Xeon and 32GB ram) and has a raid 1 for the OS and raid 5 (6 discs) devided for data partition and log partition.
We currently service about 15000 users and handle around 250000 transactions per second and it is working well.

Hope this example helps.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39724146
@Patricksr1972:

Yes, you've been lucky so far.  Sooner or later that RAID will get damaged somehow.  Then you lose give or take a day, or perhaps almost 2 days, of transactions.  Business executives SHOULD want some type of serious explanation then of why the IT group let that happen!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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