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

Posted on 2014-07-16
Last Modified: 2014-07-28
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
Question by:lianne143
    LVL 20

    Assisted Solution

    by:Marten Rune
    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
    LVL 16

    Assisted Solution

    by:Gerald Connolly
    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
    LVL 68

    Assisted Solution

    >> 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 ..."?
    LVL 16

    Assisted Solution

    by:Gerald Connolly
    Probably, i wrote it in the sense of, the security of not losing your data.

    Author Comment

    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.

    LVL 20

    Accepted Solution

    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!
    LVL 20

    Assisted Solution

    by:Marten Rune
    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now