Post reply

Installation best practices

  • September 13, 2007 at 2:48 am

    #120482

    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

  • September 13, 2007 at 3:54 am

    #733407

    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

  • September 13, 2007 at 4:20 am

    #733410

    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

  • September 13, 2007 at 4:37 am

    #733416

    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

  • September 13, 2007 at 6:48 am

    #733457

    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.

  • September 14, 2007 at 12:12 am

    #733670

       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/

  • September 14, 2007 at 6:33 am

    #733743

    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,

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 14, 2007 at 12:53 pm

    #733867

    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.

  • September 14, 2007 at 6:08 pm

    #733932

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

  • September 17, 2007 at 10:31 am

    #734113

    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.

  • September 17, 2007 at 11:41 am

    #734127

    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

     

  • September 17, 2007 at 6:58 pm

    #734202

    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