• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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

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
6 Solutions
Marten RuneCommented:
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
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
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 ..."?
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Gerald ConnollyCommented:
Probably, i wrote it in the sense of, the security of not losing your data.
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.

Marten RuneCommented:
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!
Marten RuneCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now