SQL Server 2014 Setup and Configuration

I am setting up a new SQL Server 2014 running on Windows Server 2012 R2.  I have two LUNs, one that is RAID 1 which has the OS installed on and the second LUN is RAID 10.  I was wondering for best performance how I should setup the data directories?  I currently have it set to install these directories in the following location:

Instance root directory:              C:\SQL\Microsoft SQL Server\
Shared feature directory:           C:\SQL\Microsoft SQL Server\
Shared feature directory (x86): C:\SQL\Microsoft SQL Server (x86)\

Where should I place these?

Data root directory:
User database directory:
User database log directory:
Temp DB directory:
Temp DB log directory:
Backup directory:

Analysis Services
Data directory:
Log file directory:
Temp directory:
Backup directory:

Also, should any of the service accounts be changed for any reason?

Thanks for your input.
ollybubaAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should separate your database files (data and transaction log) from the rest. If possible separate Data files from Transaction Log files also.
TempDB can stay in a local drive since usually they are faster than a SAN and also because tempdb is recreated every time SQL Server engine is started.
Backups should me moved (or created directly) to a network share so space can be released as soon as possible.

About the accounts I wrote an article about SQL Server security: The service account and I recommend you to read it.
0
 
David Johnson, CD, MVPOwnerCommented:
Don't use the system drive for the database files if at all possible  putting the log and database files on separate spindles (physical drives) .. in your case put the data files on the 2nd LUN
0
 
ollybubaAuthor Commented:
I have read your article about SQL server security.  I am running at a domain functional level of Windows Server 2008 which from what I've read you have to be at 2008 R2 to use an MSA or VSA.  I have one domain controller that is still on Server 2008.  Should I then just use a domain account or use a built in one?
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.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. With those versions unfortunaly you'll need to go for domain account. Isn't a bad option tough but requires some management that MSA or VSA don't. Just don't use Local System on Network since those are worst options.
0
 
ollybubaAuthor Commented:
Use the Domain Admins OU or a specific domain admin?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be the domain where the machine is.
0
 
ollybubaAuthor Commented:
I mean when I type the account name under the Services Accounts tab should I use the OU Domain Admins or my own credentials user.domain.com?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not user what you're asking here. Did you create a user account in AD and configured it as a service account?
If so you just need to change the SQL Server service account to the new user you've created. You can do this with a local administrator account.
0
 
ollybubaAuthor Commented:
No I did not create a service account because I'm in a 2008 AD functional level.  When I put what account name I want to use under the Account Name column on the Server Configuration page when setting up SQL should I put my own credentials user.domain.com or should I use an OU like Domain Admins so any domain admin can use their credentials or does it not work that way?  Also could I just use a local Administrators credentials on the SQL server for those service account names vs. domain credentials?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Now I understand what you said before.
You should request for create a new AD user configured to be used as a service account since it will have other kind of permissions.
You shouldn't use the local administrator credential since it will give access to all machine and won't have access through the network. At worst scenario you should use Network Service but just in the case that you can't get the service account to be created in AD.
0
 
ollybubaAuthor Commented:
Does this drive configuration look okay?

Data root directory:            B:\SQL
User database directory:      B:\SQL
User database log directory:      B:\SQL
Temp DB directory:            C:\SQL
Temp DB log directory:            C:\SQL
Backup directory:            \\Other Server

-Analysis Services-
Data directory:                  B:\SQL
Log file directory:            C:\SQL
Temp directory:                  C:\SQL
Backup directory:            \\Other Server
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You really have a drive called B: ?
What is intended to be in Data root directory?
If you can't add more disks, then you won't have much ways to organize your files so I would say isn't bad configuration.
0
 
ollybubaAuthor Commented:
I wasn't sure if the Data root directory was okay to leave on the C: drive.  I only have two luns for this server.  C: obviously has the OS.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
By Data Root do you mean System databases? If so, you can let them in C:\SQL.
With 2 luns only you don't have many options.
0
 
ollybubaAuthor Commented:
Can the user database directory and user database log directory reside on the same lun?
0
 
Anthony PerkinsCommented:
Can the user database directory and user database log directory reside on the same lun?
Can they? Yes.  Should they? No.  You can even place everything on C: drive.  Of course, when performance tanks you will have to hire a DBA to straighten it all out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.