October 21, 2010 at 4:38 pm
I have a New Server R710 16 gig RAM with 2 x 147 15K SAS RAID 1 for the OS 80 GIG partition set with the balance marked as a storage partition
Additionally 4 x 300 Gig SAS 15K. The question is whether we should create a 4 drive RAID 10 array and then create multiple partitions for the DB, temp DB and LOGS or
Create two RAID 1 arrays of 300 gig each and put the DB on 1 array and the Logs and Temb DB on the other in either one or two partitions.
This is W2K8 R2 and SQL SERVER 2008 R2. It will be a member of a Domain dedicated to the datacenter servers only.
While I have read best practices several times and the DBA and I have discussed this and went in circles.
We are just not sure how much disk we need to set aside for the logs as well as which array creation/partitioning is best.
We both feel after reading that creating 2 RAID 1 arrays is likely the best bet as it follows along with the practice of having the DB and Logs/temp db on separate disk. Maybe the Temp DB should go on the storage partition which is part of the 147 Gig OS Array???
Lastly we read that we should have no swap file on a machine dedicated to SQL SERVER 2008 R2. Is this accuracte?
TIA
DOUG
October 21, 2010 at 5:06 pm
You'll really want to get logs and tempdb off the same physical spindles if possible. There will be some heavy contention there if you don't.
By preference, data/logs are on separate physicals, as you've already discussed. TempDB, when possible, should also get shoved off on its own physical, when possible. If not, drop it to the data drives, or by preference, the local backup drives, since the tempdb is hopefully not heavily used while you're doing backups.
After reading your build, best practice isn't really an option. Too low a spindle count.
I would recommend (and I might get shot for this because if a drive goes you're SOL), but take the OS off the RAID. There's nothing there a proper ghosting of the drive won't allow you to rebuild, though you'll have more downtime than if you RAID 1 them.
Now you've got five disks. Two raid 1s for 300 gig a piece, and 1 simple drive that you stole from the OS for 147G. Take your 147G and give it to tempdb. Tempdb blows up, you just simply don't care (other than your SQL Server just went kaput). Swap drive, restart server, move on with life. This gives it room to move.
Now split your data and logs across the two 300Gig Raid1s. These are the important files that you DON'T want to lose in a single drive failure.
Make sure you're backing up to a non-local system, and you're good to go.
Is this the *best* scenario? Um... no. But it's what I'd probably recommend, depending on what SLA's I had and equipment available. A lot of this depends on how much drive traffic you're expecting, how much write vs. read (how active ARE those logs?), what your mandatory uptimes are, and a few other lesser considerations.
Oh, yeah... Swap File... you're going to need one, Windows doesn't behave without it. You should be able to share some space with the tempdb on the 'off drive'. Try to make sure the OS gets a couple of GIG of ram to breathe by setting your max SQL Server memory usage, too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 9:26 pm
Thank you
I cannot take the Raid off the OS. The server is in a data center so instant physical access is not happening.
We are coming from a SQL2005 server with 4 disks. 2 for OS Raid1 and 2 for everything else and logs are set to simple OFF altogether.
That does not mean this server should not be setup as best as it can be. That said I just cannot take the raid away.
Given that what is the next best choice for the TempDB. Split one of Raid 1's and share it with the Logs or use the 2nd partition off the system drive?
Is the TempDB more or less critical in performance compared to the LOGs.
Again thank you so much for the advice. It is really appreciated.
Doug
October 22, 2010 at 9:26 am
dcohn99 (10/21/2010)
I have a New Server R710 16 gig RAM with 2 x 147 15K SAS RAID 1 for the OS 80 GIG partition set with the balance marked as a storage partitionAdditionally 4 x 300 Gig SAS 15K. The question is whether we should create a 4 drive RAID 10 array and then create multiple partitions for the DB, temp DB and LOGS or
Create two RAID 1 arrays of 300 gig each and put the DB on 1 array and the Logs and Temb DB on the other in either one or two partitions.
This is W2K8 R2 and SQL SERVER 2008 R2. It will be a member of a Domain dedicated to the datacenter servers only.
While I have read best practices several times and the DBA and I have discussed this and went in circles.
We are just not sure how much disk we need to set aside for the logs as well as which array creation/partitioning is best.
We both feel after reading that creating 2 RAID 1 arrays is likely the best bet as it follows along with the practice of having the DB and Logs/temp db on separate disk. Maybe the Temp DB should go on the storage partition which is part of the 147 Gig OS Array???
Lastly we read that we should have no swap file on a machine dedicated to SQL SERVER 2008 R2. Is this accuracte?
TIA
DOUG
With only 6 disks in total you really have no option of following best practice. You are mentioning data and log files and tempdb and OS and pagefile, you haven't mentioned where you plan to store the SQL Server backup files!!
Personally I would do the following
➡ Inform management if they expect performance from the system they need better disk allocations and essentially more spindles!!
➡ RAID1 2 x 147GB OS and pagefile
➡ RAID10 4 x 300GB SQL Server data and log files (yes, inc TEMPDB). For ease of management you could create separate logical drives on the base array.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2010 at 12:09 pm
dcohn99 (10/21/2010)
Thank youI cannot take the Raid off the OS. The server is in a data center so instant physical access is not happening.
Well, that sucks.
We are coming from a SQL2005 server with 4 disks. 2 for OS Raid1 and 2 for everything else and logs are set to simple OFF altogether.
Well, point in time restorations are out the window. Logs still need to write, though, and sharing with the data can be a point of impact. If the server goes down, those logs are still *utterly necessary* for the reboot. Logs, even in simple mode, are incredibly important. They just take up less room.
That does not mean this server should not be setup as best as it can be. That said I just cannot take the raid away.
Given that what is the next best choice for the TempDB. Split one of Raid 1's and share it with the Logs or use the 2nd partition off the system drive?
Is the TempDB more or less critical in performance compared to the LOGs.
That's gonna depend on how much sorting and reading you do vs. writing to the data. Tempdb gets involved in a lot of things, one of which is re-sorting large data sets for reporting and the like. Logs are involved any time you change a piece of data. So, which is more important? Good question. Need to do a system analysis to answer that.
So, given that your OS is non-dividable and stuck in RAID 1, I'd take the 'leftover' space and stuff tempdb into that logical partition and put the pagefile into the OS logical drive. Not the best scenario but at least you're not hunting all over the world for it. I'd still keep logs/data on separate physical spindles, however, until you know a lot more about your read/write volume.
Again thank you so much for the advice. It is really appreciated.
Doug
No problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 22, 2010 at 1:32 pm
Craig Farrell (10/22/2010)
Logs, even in simple mode, are incredibly important. They just take up less room.
this is not necessarily true, it is possible to have huge log files even when the database is in SIMPLE recovery model.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2010 at 2:30 pm
You are correct on the HUGE Logs in simple mode. I have seen 100GB logs when the data file is far less in size. We don't write the code we just have to support it.
October 22, 2010 at 2:35 pm
Learn_something_new_everyday (10/22/2010)
We don't write the code we just have to support it.
😉 tell me about it!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2010 at 12:01 am
October 23, 2010 at 12:27 am
Posted Yesterday @ 9:30:05 PM
Valued Member
You are correct on the HUGE Logs in simple mode. I have seen 100GB logs when the data file is far less in size. We don't write the code we just have to support it.
------------------
Well, i am surprised to see that.. Why the log is been at 100 GB. Why checkpoint has not been occurred.
Or should i think like that.
in Simple recovery, the log file get increased due to long transactions, but they will not be shrinked back ( to reduce the size).
October 23, 2010 at 3:01 am
chetanr.jain (10/23/2010)
Well, i am surprised to see that.. Why the log is been at 100 GB. Why checkpoint has not been occurred.
When the database is set to truncate on checkpoint (SIMPLE recovery), the log is required to be at least 70% full for the checkpoint to take action. When the log gets to this stage and active transactions are present, the log will not truncate past this point. Any further transactions are logged onwards from this point as this is the only usable portion of the log.
Hence, The log can gow very quickly under certain circumstances!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2010 at 4:20 pm
>>>, the log is required to be at least 70% full <<<
What size is the log set to be to determine when it is 70% full? Can you set the size to be 1 gig for example and then once is have 700 meg it will start to truncate it?
I thought simple mode writes everything directly to the DB though I just learned it still passes through the log first. .
October 23, 2010 at 4:59 pm
I found this link that gives the information you were asking for.
October 23, 2010 at 7:04 pm
dcohn99 (10/23/2010)
>>>, the log is required to be at least 70% full <<<What size is the log set to be to determine when it is 70% full? Can you set the size to be 1 gig for example and then once is have 700 meg it will start to truncate it?
I thought simple mode writes everything directly to the DB though I just learned it still passes through the log first. .
as per the link below
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply