Solved

Setting up new SQL server

Posted on 2013-12-16
13
211 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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
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 250 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
 
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:
ScottPletcher earned 250 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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 19

Expert Comment

by:Patricksr1972
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:ScottPletcher
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

22 Experts available now in Live!

Get 1:1 Help Now