Disk sizes for Data Directories and necessity of installing File stream in SQL 2012

Hi  
 
Firstly:
During installing SQL 2012 on the Database Engine configuration window
For the Data directories: I am going to give 100GB for all the Drives. My software vendor is going to  install management information system on SQL server asked me to create a Drive with 300GB and it will be called as DocumentStorage.
Please let me know the drive I need to assign more space comparative to other Drives.I am not sure the drive that will need more space. Please let me know, which drive that will need more space.


Data Directories:
1)Data root directory:   D:
2)System database directory:D
3)User database directory: E
4)User database log directory:F
5)TempDB directory:G
6)TempDB log directory:L
7)Backup directory: B

Secondly: under File stream
On the SQL server we will be installing management information system . 70-90 Clients will be accessing the management information system which is installed on SQL 2012 server.Please let me know if I have to enable this option or not. Just trying to work out to minimise unnecessary services. If I enable File stream do I need to enable all the 3 which is below.


1)Enable File stream  for Transact-SQL access
2)Enable File stream  for File I/O access
3)Allow remote clients access to file stream Data

Thirdly: Can User database log directory and TempDB log directory on two different folders using the same drive such as L . Will it cause any performance issues.

 Please suggest any help much appreciated
lianne143Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The log directories needs To be the fastedt you've got. This means you want the most spindels for these directories. I e a raid 10 of 15k drives, or even ssd would be optimal.

one each for tempdb and user db log is the optimal configuration. Now it's rarely practically possible to set up a system that is configured this way.

but a rule of thumb, separate  fast drives for userdb log files and tempdb log files.

the file stream has not been suggested from your vendor, so they are expecting a separate drive with +300GB's. Now I gather this will be a combined application/SQL server. You'll wanna determine how much internal memory this server needs, AND configure a correct max value for the SQL server service. Leaving sufficient for windows OS (typically 4GB) plus memory consumption for the application service (vendor should know what's recommended) plus 1MB per estimated concurrent connection (I e, windows OS memory is used for connections to the SQL Server). Keep in mind that large filecopys to this share will also utilize the OS memory, this needs to be configured correctly).

lastly in a recovery scenario you'll need backups plus log files OR the userdatabase Data files in a DR recovery scenario. So don't put backups on the same physical drive as the user database Data files, or you might end up with data loss given that,the wrong drive failed.

a logical separation in different folders is never a problem, SQL will handle this correctly.

From my Windows phone, so ignore spelling errors and similar things in this posting.

Regards Marten
0
Gerald ConnollyCommented:
So are these "Drives" going to be real drives or partitions on the same LUN?

If separate LUNs, are these LUNs all from the same RAIDset?

For optimum security you need the DB and its logs to run on separate physical disks
0
Scott PletcherSenior DBACommented:
>> For optimum security you need the DB and its logs to run on separate physical disks <<

Did you mean for "recoverability you need the DB and ..."?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gerald ConnollyCommented:
Probably, i wrote it in the sense of, the security of not losing your data.
0
lianne143Author Commented:
I am not sure ie "Drives" going to be real drives or partitions on the same LUN?

We do have a SAN and a with 3 ESX servers and VMWARE. I have a support with VMware and as I request , they will be able to configure the Drives for me.

So please tell me as the  best way of configuring the disks and I will ask them to do for me.

 Also if you say for "optimum security you need the DB and its logs to run on separate physical disks"
the DB are you referring to
2) System database directory: D or

3)User database directory: E


I will be setting up a local 7)Backup directory: B drive and also backing the whole SQL to a NAS  .

If I don't enable file stream during installation , will I be able to configure the file stream at later stage if required.

I will be allotting 8Gb of RAM and if required I will be able to scale up.

thanks
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Quote "If I don't enable file stream during installation , will I be able to configure the file stream at later stage if required"?

yes it can be configured later!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Your drives will be on the ESX no matter how they are configured, but its still recommended to have one each for the log files, data, tempdb and so forth. Ithink the primary reason for this is that if you need better hardware this configuration helps when you change/add faster drives to the ESX servers.

how are the san mirrored across your sernvrhalls? Does the san take care of this, or is it up to the ESX or is it hostbased?

regards Marten
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.