December 26, 2011 at 8:17 pm
Not sure where to post this topic SQL Server 2008 Administration OR Design Ideas and Questions. Can someone direct me where to post this topic.
I have two drive arrays in a server (C and D both of same size) and also have an application(reporting app) running on that server. What's the best way to configure SQL Server? We do lot of inserts and update operations. Larger files will be uploaded through back-end when when interface fails to upload the data. Otherwise use web interface to updates & inserts.
Best practices say to keep your data and log files on separate drive arrays, but is it OK to put them on the same drive and put the application files on the other drive?
What's the appropriate way- keeping application files on one drive and data & log files on other drive OR keeping application files and sql data files on one drive and log files on other drive
December 26, 2011 at 11:27 pm
Can you provide us more information? What’s the disk sizes & available space on each disk? Is it RAID (type)? How big your database (data & log file) is? On which drive you placed tempdb files?
December 27, 2011 at 12:22 am
each drive is of 136Gb and its of RAID (C:72GB is free; app & sql server are installed on C)
and nothing is on drive D.
tempdb files are in C drive. I am planning to change default paths for data and log files. If i change it is that going to change for tempdb also?
each DB is of 4GB and some are above that. Few of DBs do more insert/update operation.
One part of the application has more read/write operations on particular databases and the databases those do more write operations have the application with only read activity.
can I do this:
application on C drive.
keeping data(C) & log(D) files on seperate drives for those databases with more write operations
keeping data(D) & log(D) files on same drives for those databases with less write operations
December 27, 2011 at 12:38 am
You are limited by hardware resources so whatever we would suggest it would be based on resource availability (and may not be best design).
Keep your C: drive reserved for App & OS so application won’t get hiccups.
Place the data file on D: drive so data read write operation would be independent of Application operations.
TempDB should go to D: driveTempDB may require huge space for unplanned sort operations.
Database Log Files (assuming you are backing up regularly in FULL Recovery mode) can be placed to C: drive. Once application is initialized, there would be less activity on C: drive so log writes would be smooth. Log files will be small in Simple Recovery model.
Backups: based on space availability (any disk).
You didn’t mention the RAID type? It should also be considered for decision making.
December 27, 2011 at 1:00 am
Thanks for your reply!
Its RAID 1
December 27, 2011 at 6:51 am
With just two drives you are not going to get much out of IO performance. RAID 1 is not great for performance but good for fault tolerance.
Keep in mind that the log files are sequential writes and data files are random writes. Therefore wherever you put the log file, you are not going to get high performance unless you have a dedicated drive for sequential writes.
In your case you don't have much choice other than putting all the data files in D-Drive and log on C- Drive (Risky since log files can grow if it is not being backed up regularly.)
-Roy
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply