[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 R2 Configuration Help

Posted on 2012-09-19
7
Medium Priority
?
499 Views
Last Modified: 2012-09-20
Hello Experts,

We’re hoping you can give us some insight to some general SQL configuration questions we have before we begin our SQL installation.  We’ve looked for answer to these questions but couldn’t find much solid information, or we couldn’t understand certain aspects of SQL in the articles we did find.  Hopefully, you can help us in completing our task.  Thank you, in advance, for any help you could give us.

We’re building a new SQL Server:
1.      Windows Server 2008 R2 SP1
2.      SQL Server 2008 R2 SP2  

The server storage consists of eight (8) 15k SAS Hard Drives split into 3 RAIDs:
1.      Volume #1 for the OS - 600GB in a RAID 1 (2 Drives)
2.      Volume #2 for SQL Databases – 1200GB in a RAID 10 (4 Drives)
3.      Volume #3 for SQL Logs – 600GB in a RAID 1 (2 Drives)

There are five (5) databases, roughly:
1.      Database #1: 300GB
2.      Database #2: 25GB
3.      Database #3: 25GB
4.      Database #4: 5GB
5.      Database #5: 500MB

These five databases are used in one (1) of our Enterprise Applications (a Document Management Solution) and consist mainly of PDFs, Word Documents, and Excel Documents.  Our Software vendor has not been able to provide much support or Best Practice for setting up the SQL Server for this application.
 
Your help and advice is appreciated for the following questions (Please keep in mind we are not SQL experts or DBAs of any sort):
1.      Is it better to install SQL on the System Drive (Volume #1), the databases on the Data Drive (Volume #2), and the SQL Logs on their own drive (Volume #3)?  
2.      Is there even a separation of SQL Server and the databases to do this or should SQL and the databases should all be on the SQL Data Drive (Volume #2) and the logs on their own drive (Volume #3)?
3.      On Volume #3 where we want to put the SQL Logs, can this also be used as the storage area for Shadow Copies?  Or is this bad practice?
4.      Is it general practice to install an application on the same drive as the databases, or normally would this be done on a separate volume as well?
Thank you for your time and help.
0
Comment
Question by:2011Volt
  • 4
  • 2
7 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 38414831
I would leave 600 gb for the first drive, can be smaller 100-200 gig

1.      Is it better to install SQL on the System Drive (Volume #1), the databases on the Data Drive (Volume #2), and the SQL Logs on their own drive (Volume #3)?  
correct
2.      Is there even a separation of SQL Server and the databases to do this or should SQL and the databases should all be on the SQL Data Drive (Volume #2) and the logs on their own drive (Volume #3)?
Its always better to have OS and pagefile reside on one drive, another one for data, one for log and a separate one for the tempdb
3.      On Volume #3 where we want to put the SQL Logs, can this also be used as the storage area for Shadow Copies?  Or is this bad practice?
I will never store the backups on the same server, should be on a separate machine or tape
4.      Is it general practice to install an application on the same drive as the databases, or normally would this be done on a separate volume as well?
Application and OS can reside on the same drive
0
 
LVL 1

Author Comment

by:2011Volt
ID: 38414960
Thank you aneeshattingal for yourquick response!  

To clarify a bit:

3.      On Volume #3 where we want to put the SQL Logs, can this also be used as the storage area for Shadow Copies?  Or is this bad practice?
I will never store the backups on the same server, should be on a separate machine or tape

We use Backup Exec 2010 R3 for our SQL Backups to a Virtual Tape Library on a separate server.  My question was pertaining to the Shadow Copies that are made on the SQL Server.  I'm told that when the Microsoft Volume Shadow Copy Services (VSS) is configured to write the shadow copies of a volume to a storage location on the volume itself, you can have very high I/O or low disk space problems.  Symantec recommends making the shadow copy location on a drive that separate of the page file and is not being backed up in the daily backup.  I guess that kind of answer the question that I shouldn't put it on the volume with theSQL Logs since we will want to back those up, but would it hurt if I split up the drive into multiple volumes?  I'm not sure how much space I need for the logs, and how much space is requried by the shadow copies...

Your thought?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38414996
I wont split the last drive for the log file at all, you can split the first drive and use that for VSS; Remember, with sql server backup, you are not actually backing up the entire drive, when you run a backup statement in sql server, it actually backup the data and log in a way that, it can recreate the database from that backup.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38415003
I never used Symantec Backup tools
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 38418354
1.  
In general, yes.  If you don't have other drives available, also place tempdb on drive #1.  
You can mix data and log files if you want (to help balance total i/o), BUT **NEVER** HAVE THE DATA AND LOGS **FOR THE SAME DB** ON THE SAME DRIVE.
For example, data files for db1 could go on drive2, logs for db1 on drive3; logs for db2 on drive2, data for db2 on drive1.

2.
Everyone says that, but you can mix and match data and logs as long as you follow the restriction above.

3.
NO.  Logs writes are THE most critical thing for SQL Server.  SQL waits for the write to be confirmed.  And if they can't get space to write the log, the db(s) involved will STOP.  Don't put undue strain on a SQL log drive.

4.
No.  Typically apps are installed on separate drives, although they could be installed on drive #1, if it contains no data or log files (other than tempdb).
0
 
LVL 1

Author Closing Comment

by:2011Volt
ID: 38418410
Thanks for your input, it has really helped to steer us in the direction we"ll be going.  We'll see how it goes this wekeend.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38418507
Good luck, Let me know how it went

Aneesh
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

834 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