November 20, 2003 at 10:25 am
If I currently have servers that the program files and data files were installed on the D drive - is this okay? Should I leave it or take the time to get another partion created and have the data files moved to yet another different drive?
If I should have it moved then how do I do this without disrupting the server? Can it be done?
What is the best practice? What do you do? Do any of you just install program files on the C drive and data on the D drive?
If I currently have analysis server also on one server along with sql server installed on the D drive - is this okay?
Please I need some advice.
November 20, 2003 at 10:32 am
It's really up to you. The tools will always be on the C drive unless you hack the registry. It doesn't really matter where the program files are stored since they aren't accessed much once the server starts.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 20, 2003 at 11:05 am
I frequently do this as my secondary drive is a SAN. Having the Program files on the same drive is really not an issue. The only issues you need to be aware of are: Size and Disk Contention. Size is a no brainer. And disk contention will really be an issue if you do lots of read/writes and especially with AS how frequently you are reprocessing cubes. Seperating out your log files and/or having the AS repository on seperate disks could improve performance if there are issues.
November 20, 2003 at 11:12 am
One suggestion for future installs was to install program files on c drive, data on a seperate drive, log on another seperate drive and tempdb on yet another. What do you think of this has future practice for installs?
November 20, 2003 at 11:16 am
also please forgive me for my stupid questions but I am very new to db's and sql server.
if I seperate my log and data on seperate drives - how do I do that?
November 20, 2003 at 11:22 am
quote:
also please forgive me for my stupid questions but I am very new to db's and sql server.if I seperate my log and data on seperate drives - how do I do that?
I'm sorry this is not what I had meant please disregard. What I meant to say was should I do this for all logs on all db's and when I switch the drive paths is there any impact on the dbs and the users accessing it etc.....
November 20, 2003 at 11:57 am
we typically set up our data seperate from our log.
Start Enterprise Manager-> Server -> right click properties->database settings ->Default Data directory: D:\SQL\DATA
Default Log Directory: E:\SQL\DATA
everytime you create a new database it will created in the respected folder you have assign as default.
mom
November 20, 2003 at 12:22 pm
If I need to change existing configuration so that log and data are on seperate drives then what impact will that have on the server and the users?
Does tempdb need it's own drive as well?
November 20, 2003 at 12:27 pm
tempdb only needs it's own drive if there is contention. Slowness and lots of tempdb activity. You can use perfmon to look at transacitons/sec, etc. for the databases.
To separate the files now, the easiest way is to detach and reattach the db, moving the log files in between. Requires downtime for the db being detached.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 20, 2003 at 12:34 pm
how does detaching and retaching the db's vs. just going into the db and clicking properties then changing the drive path for data then log, make it better practice?
I really want to make sure that I do it the correct way. If I detach and reattach does it address log and data seperatly?
November 20, 2003 at 12:55 pm
you can not change the directory of an existing database. You would have to detach and re-attach those database to get move it to different folder. Because you detach a database you also take this database away from user, the effect to users are downtime. the database is down until you re-attacth it back. Also the database will reflect your login as dbo thus you have to run
exec sp_changedbowner 'sa'
exec sp_changedbowner 'owner of database'
mom
November 20, 2003 at 12:57 pm
I forgot to address the log and data problem.
Once you detach the database you have to go to the database file for .mdf and .ldf and move it to the folder that you want it to stay.
at the reattach statement you also list out where your mdf and ldf file are locate in order for it to work.
mom
November 20, 2003 at 1:23 pm
How can I do this all through enterprise manager?
And do you think if My current set up is program files and data/log on D drive - that I need to go through this hassle to make it correct or is it okay to leave as is and just do the seperate drives going forward?
What were your thoughts of seperate drive for tempdb?
November 20, 2003 at 2:05 pm
You could leave your current one the way they are and set up the correct path for the future databases. We did have a server that has different drive for tempdb and I have not seen any performance change from the old set up. Let's just say it depends on how heavily you are using your tempdb and how big the size of your databases are. 99.9% of my servers don't have tempdb on their own drive.
mom
November 20, 2003 at 3:04 pm
what type of problems do you see I would run into having the program and data files on the d drive?
Would it be better practice to stay consistant and keep doing this or with new servers seperate the two?
Wouldn't it be inconsistant to put new db's in the new drive?
Thanks for all the replies.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply