Installation best practices

Question

Installation best practices

athwaites

SSC Enthusiast

Points: 185
More actions
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

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

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

athwaites SSC Enthusiast Points: 185 More actions · Answer 2

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

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

athwaites SSC Enthusiast Points: 185 More actions · Answer 4

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.

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 5

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/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

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.

Bob Fazio SSChampion Points: 10794 More actions · Answer 8

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

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 9

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.

AndyGBrown SSC Veteran Points: 279 More actions · Answer 10

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

JDC-286127 Mr or Mrs. 500 Points: 503 More actions · Answer 11

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.