February 12, 2011 at 11:29 am
I would like to install sql server 2005 on a new server.
what usually is the best storage and arrangement of drives to put .mdf file, .ldf file, and back up file to?
I mean put mdf, ldf files on the same drive or different, and how about database backup files, to another drive?
Thanks
February 12, 2011 at 12:40 pm
If you want maximum performance, separate out .mdf, .ldf and backup files is the normal base setup.
Do not bother separating log and data files for your system dbs though.
If it's a small database(s) with low use you can get away without splitting them up.
for the perfect setup you need to know the usage characteristics of your database.
---------------------------------------------------------------------
February 12, 2011 at 12:48 pm
Thank you.
By the way, for the backup files, do you use native tool to backup or use third party tool to do it?
What is the better option?
Thanks
February 12, 2011 at 3:32 pm
unless i need compression or encryption native utilities all the way. Native backup works extremely well and is a no cost option, so only use a third party tool if it offers you something you need the native tools don't have.
I always backup to disk first and we use enterprise system backup tools to copy those off to tape (or virtual tape).
---------------------------------------------------------------------
February 12, 2011 at 9:22 pm
Good to know, thank you.
I like native tool also. It's fast to access and recover.
Our network guy suggested using Microft Data protection manager to direct do the back up and restore, For he thought we need to do duplicate backup, one is native, the other is DPM to back up the flat files, I didn't use it before, so not sure what is the benefit it is comparing with using native tools?
thanks
February 13, 2011 at 3:31 am
By all means use something like DPM (note: not personally familiar with this tool) to copy the .bak files off to tape once you have created them on disk using native utility, but thats all. Don't rely on it as a backup method by copying the data and log files, it possibly won't be able to do that anyway as the database files will be locked by SQL server.
Keep responsibility for database backups and ESPECIALLY restores in the hands of the DBA, network guys don't always understand that recovering a database is not like recovering a flat file and you just splat it back in place.
---------------------------------------------------------------------
February 13, 2011 at 8:13 am
I'd like to repeat exactly what George said...
Well, OK, I'll ad a little. If you can, seperate the OS from the mdf from the ldf. After that, it gets into a dance and you have to really start laying out what your needs are, bottlenecks, potential bottlenecks, hot spots,etc.
For backups, again, like George said, to local disks if you can. I'll add that, depending on the size of the database, it's best to run DBCC CHECKDB before the backup so that you know you're backing up a good database. Also, you might want to add a checksum to the backup so that you can use RESTORE VERIFYONLY to validate the backup (although this does slow down backups). But know that even with RESTORE VERIFYONLY, the only way to know for sure you have a good backup is to restore the database. So practice your restores as a double mechanism, validating your backups and improving your skill sets.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 13, 2011 at 2:34 pm
Grant Fritchey (2/13/2011)
I'd like to repeat exactly what George said...
😀
---------------------------------------------------------------------
February 14, 2011 at 1:45 pm
Found letting the system admin do OS drive system state backup then
if all your system databases are on 1 drive do a system state backup
using acronis.
If you do this, your system database recovery is simple as
stop sql server instance
mount backup as cd drive
drag and drop on top of old copy
restart sql server instance
this is a life saver if your system databases goes down.
as for normal db backup - i use sql server 08 r2 native with compression
it works great
February 14, 2011 at 3:54 pm
RogueBlackSheep (2/14/2011)
Found letting the system admin do OS drive system state backup thenif all your system databases are on 1 drive do a system state backup
using acronis.
If you do this, your system database recovery is simple as
stop sql server instance
mount backup as cd drive
drag and drop on top of old copy
restart sql server instance
this is a life saver if your system databases goes down.
as for normal db backup - i use sql server 08 r2 native with compression
it works great
Having flat file copies of the system database files definitely worthwhile as a DR procedure and belt and braces approach to recovering corrupted system databases but of course have native backups of the system databases as well.
---------------------------------------------------------------------
February 15, 2011 at 9:40 am
Note: When we're saying "separate" we mean "entirely different spindles" at a real, physical level. Drive letters on local disk or SAN often share spindles with each other or with something else, at which time you may well be hurting performance.
Before trying separation, trace your drive letters (and subdirectories if you're using junction/subdir mount points) down to actual, physical drives... and then find out (particularly on a SAN) whether those physical drives are used by anything else, perhaps outside of your control.
SSD's are a partial exception to the "don't share" rule, if you have them, since they don't have a head to move.
February 15, 2011 at 11:38 am
To add to what Nadrek just said, remember that just because the drive letter is different, doesn't mean the drive is different. Too many DBAs make the assumption that logical drives = physical drives, and it always gets them into trouble.
If the logical drives are all on the same physical drive (or SAN spindle, etc.), there's no reason to even bother with the separation as it'll be cosmetic only.
February 15, 2011 at 12:07 pm
Thank you, it's very helpful, I will definitely check into that.
February 15, 2011 at 12:13 pm
Brandie Tarvin (2/15/2011)
If the logical drives are all on the same physical drive (or SAN spindle, etc.), there's no reason to even bother with the separation as it'll be cosmetic only.
To add to what Brandie added to what I said:
It can be worse, actually; if we represent a spinning disk with a linear tape, i.e.
-----------------------------------------------------------------
And we have only SQL files (one MDF(M), one LDF(L) on it), for simplicity created once, at full size, and never grown:
-----------------------------------------------------------------
MMMMMMMMLLLLL---------------------------------------------
The head skips between the M and the L sections, which are close.
Now, if we have a D and an E logical drive on that same spindle or set of spindles:
D-----------------------------------------------E---------------
MMMMMMMM-----------------------------------LLLLL----------
You can see that the head(s) need(s) to move over a larger distance.
If you keep your drive defragged, not having different files on different drive letters is actually superior in this case.
If you don't keep your drive defragged... you have similar problems anyway.
Again, SSD's are a partial exception.
February 16, 2011 at 12:30 pm
What would be the best way for defragging a disk on witch database files are also stored.
backup database
defrag disk
and then restore database
that is what i think would be the best way but would require to stop production on my database.., witch i dont like if it is not really necessary
what would be the impact of defragging drive D:\ as your database file would be on D:\Sqldata\mydb.mdf
Anybody has experience with this.?
For the moment i dont have many defragmentation on the disk level but its growing a bit cause i have an import directorie on the same drive D:\imports\customers
on witch i receive XML files to import into the database
on 1 day this means serveral hundreds of xml files written and deleted afterwards (creates fragmentation on the disk)
Wkr,
Eddy
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply