SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Fri, 01 Feb 2008 07:51:33


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.
 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Ekrem so » Fri, 01 Feb 2008 19:15:48

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://www.yqcomputer.com/

--
Ekrem soy

 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Sun, 03 Feb 2008 05:47:59

hank 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 soy" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Ekrem so » Sun, 03 Feb 2008 06:23:05

ello 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 soy


"Rex Gibson" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Sat, 09 Feb 2008 00:35:29

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 soy" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Ekrem so » Sat, 09 Feb 2008 02:07:37

ome 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 soy



"Rex Gibson" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...

 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Shan McArt » Sat, 09 Feb 2008 03:03:33

e careful with your SAN. I have had quite a number of clients that used a
SAN for a database server and suffered significant performance problems
because their underlying SAN infrastructure was a huge stripe across many
drives. When they thought that their database files were seperate because
they were on different volumes, they were actually using the same disk
spindles and were even shared with other servers. What most people
completely forget about is that moving the head on the disk is the slowest
operation and is most frequently the source of most I/O bottlenecks. Modern
disks can rattle off sustained transfer rates in excess of 100MB/s, but if
it takes 5 ms to move the head plus an additional rotation latency of 2ms,
that means that the disks are not transfering a single bit for 7ms. What
complicates this is that when the underlying SAN services an I/O request
that uses the disk that the SQL box is using, it moves the head, which also
has to be moved back, meaning that there is 14ms of no disk I/O.

Personally, I like to use direct-attached storage for SQL databases, but if
I had to use a SAN, I would insist on having independent spindles on the
drives that were being assigned to the SQL machine.

Think of it another way - having 2 volumes on the same underlying SAN disks
is no different than having 2 partitions on the same disk - it simply won't
improve your performance.

Shan McArthur - VP, Technology
ADXSTUDIO Inc. | 200 - 1445 Park Street | Regina, SK Canada | S4N 4C5
Tel: 306.569.6502 | Toll-Free: 800.508.7811 ext. 502 | Fax: 306.569.8518
"Rex Gibson" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Sun, 10 Feb 2008 03:27:57

e are on dedicated spindles.I checked when we did our initial migration
from local raid5. We actually saw a boost in peformance. Our san guy is
pretty sharp. Thank you though, I'm sure others will find this comment
usefull.


"Shan McArthur" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Sun, 10 Feb 2008 03:35:28

'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 soy" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files

Post by Rex Gibso » Sun, 09 Mar 2008 07:36:05

efresh. Please ignore.
"Rex Gibson" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...