Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
- From: "Rex Gibson" <noamt1@xxxxxxxxx>
- Date: Fri, 7 Mar 2008 17:36:05 -0500
Refresh. Please ignore.
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:eud7jFoaIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
I'm going to open a ticket with MS I think. I see what you mean Ekrem.
However for the Install I don't see that the actual core db components
there isn't the choice of separating out
C:\PROGRA~1\MICROS~4\MSSQL$~1\binn\sqlservr.exe on the C drive and
master.mdf /masertlog.ldf on to a separate drive. You must do this post
install. I have tens and tens of servers to do. This is going to be a
pain.
Unless you can tell me different.
THanks again for you help. I really appreciate it.
"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:67411389-BCAD-4005-A8FC-1B315C84405B@xxxxxxxxxxxxxxxx
Some SQL Server 2005 binaries have to be installed to the "X:\Program
Files\Microsoft SQL Server..." path by design. You can't change it.
However, you have chance to change some other stuff's paths by clicking
the "Advanced..." button in the "Components to Install" window in SQL
Server 2005 Setup.
According to the database files, you can create your database files on
different disks: see the following example:
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'D:\Data Files\test.mdf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'E:\Log Files\test_log.ldf' , SIZE =
1024KB , FILEGROWTH = 10%)
GO
And you can change your existing databases' files' locations using ALTER
DATABASE
--
Ekrem Önsoy
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:%23PnRU8ZaIHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
#1) Makes sense.
#2) I am still not seeing it clearly. In 2005 I appear not to be given
the choice to install sql server binaries of the SQL Server on C drive,
it appears that I am not given the choice to install system
databases/log files on a separate drive, as I could in 2000. So I must
move them post installation if I am to gain the performance advantage of
having the binaries on the OS drive and System DBs (and log files -
excepting temp) on the data drive (in the example below D:) and User DBs
Data on D: and Logs on E:
Any thoughts Ekrem?
Anybody else have input?
"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:O0AnliRZIHA.5980@xxxxxxxxxxxxxxxxxxxxxxx
Hello Rex,
1) You'll not be using most of the system databases (the temp db is an
exceptional database in certain cases when you use it it extensively
for your application) intensively so you don't need to seperate them, I
mean locating them on different physical disks. As I told you, temp db
is an exceptional system database. If it's being used intensively in
your environment then you should locate it's log and data files on
different physical disks. But for the master, model and msdb you don't
have to seperate them as they are not going to be used intensively (at
least for most of the cases). However you should back up them when you
make a change in server level, this is one of the best practices.
2) For this question you must understand the reason why we should
seperate data and log files. We seperate data and log files to gain
write and read (shortly I\O)performance. The best practice is to
install the binaries of the OS and SQL Server on the C: drive (because
they are not going to be used intensively) and locate the data file of
the database on the D: drive and locate the Transaction Log file on the
E: drive. Of course these drives must be physically seperated so that
you'll gain performance benefits. Logically seperation does not mean
anything in terms of performance advantage.
--
Ekrem Önsoy
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:OUhBAPRZIHA.3964@xxxxxxxxxxxxxxxxxxxxxxx
Thank you Ekrem,
We are physically separating log and data files on separate disks. So
after thinking about it in need to rephase the question into 2 parts.
For SQL Server 2005:
1) In addition to separating data files and log files to separate
phyiscal for "user" databases, is it best also practice to separate
out the data and log files to different physical volumes for system
databases such as master, msdb, and tempdb? In which case this cannot
be done via the setup.exe but _must_ be done post installation using
the following techniques.
http://support.microsoft.com/kb/224071
Correct?
2) In a server configuration with 3 physical volumes, for example: C:
for system (local RAID), D: for data (SAN), E: for logs (SAN); is
there any reason to have the application files and folders on the C
drive and the data files on the D: drive and logs on the E: drive? By
application files I mean the binn folder which contains the sql server
executable among other things. Does it matter? Or can simply install
SQL Server on D drive, and do as advised in question 1 above with
regards to user and system db log files.
Thanks again for your kind attention.
-Rex
"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:E5D91BBB-241D-4709-871D-4F6A77385B40@xxxxxxxxxxxxxxxx
There was another post similar to yours by Saral6978 just 1 post
before yours.
Seperation of data and log files is a best practice. For example,
locate your SQL binary and Windows files on drive C: and put your
data file on drive D: and put the log file of your database on drive
E:
The important thing here is putting those files physically seperated
disks. A logical seperation would not gain you performance.
You can of course change your database files' folders using ALTER
DATABASE command in SQL Server 2005 as well. To learn more about this
command visit Books Online =
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
--
Ekrem Önsoy
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:e%23DcqK5YIHA.4896@xxxxxxxxxxxxxxxxxxxxxxx
Dear readers,
I am finally coming out of the dark ages and starting to use SQL
Server 2005. Due to the rather large nature of my organization, you
must forgive me for the rather late question.
I have operated in SQL 2000 under the assumption that a performace
boost is gained when the SQL Server Appliation files (such as the
server service executable, and agent service executable) were on
separate volumes/luns from the data files. Thus Application files
were installed on C. Data files on D and log files on E. Etc.
Now we are starting to move forward and use SQL2005 sp2 and during
installation, it appears we no longer have the same options (or
possibly we just don't understand). It appears that the SQL 2005
installation package does not allow for this type of configuration
in regards particularly to the application files being on a separate
location from the data files. We have tried a few ways now and done
several searches and are not finding any reference material on that
through we are seeing vague references that the same "best practice"
applies in 2005.
What I want is the binn folder on the c drive and the system db's on
the d drive. Obviously I can get the user dbs on the d drive. Is
this possible? Is it still a best practice? If not, why not? Anybody
have a link to a guide on how to do this? Proving me wrong in the
first place (This practice in SQL2000) is an acceptable solution
too.
Thank you for your kind attention!
-Rex Gibson
DBA -- in some very large, very slow moving orgainization.
.
- Prev by Date: Re: 2008 Question
- Next by Date: Remote Connection to SQL 2005
- Previous by thread: Re: Remove Old Backup Files in SQL 2005
- Next by thread: Remote Connection to SQL 2005
- Index(es):
Relevant Pages
|
