Installation best practices

  • Hi all,

    After much research I have not found any information on the following subject...

    What are the best folders to install SQL Server in?

    Is it better to you the default C:\Program Files\MS SQL Server ?

    Would it be better on a different disk to Windows?

    I have an application on the D: disk and I was thinking of installing SQL Server here.

    Master, Tempdb are also installed in the default folder.

    Is this the best place for them? For this application I use very few temp tables.

    Thanks,

    AT

  • I do not think you will get performance improvements by installing SQL Server to a different location than the standard location. However, what does matter, is where you put your database and log files. These you can specify per database when you create them.

    Of course much depends on your requirements, but for basic scenarios placing the log on a different disk from the data files is a good start. There are plenty of articles to discuss how it is recommended to set up your file locations (e.g. http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx)

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thank you Andras.

    The question is specifically for SQL Server and NOT the database files (which are installed on separate disks as you specify).

    I do think it curious that nobody talks about the installation folder, certainly where the tempdb database is concerned, if it is used often (which I know many would consider bad pratice) there could be some serious performs hits.

    Regards,

    AT

  • Hi AT,

    you are right that initially the tempdb is in the SQL Server installation folder, but it is easily changed with two "alter database tempdb modify file" statements. The master database is usually less of a bottle neck, but it can also be moved relatively easily (you need to modify the startup parameters, shut SQL server down, move the files, start SQL Server. So this leaves us with accessing the binaries (but there are many system dlls that are used, and faster access to them is rarely a bottleneck) and the error log file (which can be moved too.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thank you again for your help.

    I am leaving everything on the system disk for simple installations.

    The tempdb database will be moved for applications using temp tables (with significant data volumes).

    I priviledge the other disks for separating data and log files.

  •    Its advisable to install your sql server in the default drive itself. For the data files your master and other databases,  Its based on the processor which you are using.  considering, if you are using core 2 processor your accessibility of datas is not a great deal becase your processor had the capability to work like a two processors and each one to take care of each drives, if you are using an ordinary processor then putting the data or databases in different drives will obviously affect the performance.

       

       

      Regards,

    Venkatesan Prabu. J

    HCL Technologies

    chennai

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • Andras,

    I read the article you lined to and I have a question about tempdb.  The article says, "For the TEMPDB database, create 1 data file per CPU, as described in #8 below".  Does it matter if the files are on the same array, or should they be on separate arrays?

    Thanks,

  • You should create 1 tempdb data file per actual CPU core (not counting hyperthreading), even if they are on the same drive.  If you have the luxury of using separate drives for each file then go ahead and use them, but the primary reason for this guideline is to maximize the throughput of allocating objects in tempdb by having a separate thread for each CPU available to process the allocations.

  • Personally I am not a fan of putting anything other than the OS on the C: Drive of a server.

  • Actually, there's sound reason to move your SQL Server install to another drive.  If you have multiple drives, put your OS on the C: drive and then the SQL Server install on another drive (D: for example).  If these two are actually different physical disks, you can get better performance out of doing this.

    Another reason to separate the install would be availability (or lack of) of server disk space.

    But other than those two reasons, there really isn't a "best practices" when choosing a drive to install SQL Server on.  It's really a thing that's left up to the DBAs & Server Admin to decide which suits their environment better.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • If you did decide to move the system databases then you should make sure you remember to remove the Resource database. Service pack installs can fail irreversibly if this is not done.

    http://msdn2.microsoft.com/en-us/library/ms345408.aspx

     

  • I have also read that moving your SQL install to another drive may cause you further pain down the road as upgrades and patches are deployed.  I would recommend leaving it in the default location unless you can truly see an advantage.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply