What is the best setup for files under this configuration?

[BlakeK]

Right now, our SQL server machine is setup as follows:

C: is 2 drives setup in a RAID 1 configuration, 33GB in size, 25 GB free
D: is 4 drives setup in a RAID 5 configuration, 203 GB in size, 49 GB free
E: is 6 drives setup in a RAID 5 configuration, 101 GB in size, 10 GB free

Currently, SQL is installed on C:, backups (BAKs and TRNs are stored on D:, and databases (MDFs) and transaction logs (LDFs) are stored on E:

I was told it is more efficient to have the LDFs on a different drive than the MDFs.
If this is true, which drive array would it be best to move the LFDs too?
Would it be OK to have them on the same array as the backups?
Or would it be optimal to have a 3rd array F: to move the LDFs to?

If I do decide to move the transaction logs, is the proper procedure:
1.) Detach the database
EXEC sp_detach_db 'eNCast'
2.) Move the LDF file to the new drive
3.) Reattach the database
EXEC sp_attach_db @dbname = N'eNCast',
@filename1 = N'F:\eNCast_Data.MDF',
@filename2 = N'X:\eNCast_log.LDF'
Where F: is the original location of the MDF and X: is whatever drive I moved the LDF to.

What does the "N" mean in the above statement? It is written like that in BOL.

 

[Catadmin]

If you're looking for optimal, regardless of cost, get a 3rd array with a new set of disks. This would be best.

However, if cost is an issue, consider this. Is your performance working fine? If you haven't noticed any problems, leave your configuration as is. I don't recommend moving the .LDF files from their current location to the backup drive location because your Transaction Log backups can interfere with your Transaction Log reads & writes. Especially if you frequently have backups and frequently have a lot of data going in and out of SQL.

If you do decide to follow through with moving the logs, your procedure for it as listed above is perfect with one minor exception. Actually, this is more my personal preference, but I like being paranoid about data. @=) I would recommend making a copy of the .LDF to store somewhere before moving the original. Once you reattach the DB and make sure it's working, you can delete the copy .LDF. But if something goes wrong and corrupts the files you are moving, you'll still have a good copy.

As far as the "N" goes, I have yet to find a reference which explains it satisfactorily. My W.A.G. is that it indicates the beginning of the database or file name. I never see it listed as part of the abstract explanation of the attach command, but I always see it as a part of the file name, regardless of what database they are demonstrating the command on.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[BlakeK]

Catadmin, thanks for the reply.
We have a new set of disks that are scheduled to added to the E: drive, our data partition, because as you see, we are down to 10GB free.
I am going to see if rather than adding it to the E: drive, we can create a new RAID array F: and then move all the LDFs to it. That will still accomplish the goal of having more free space on E: because the LDFs will be gone, and it will also have the added bonus of having the logs on a seperate partition.
I still have to talk to the hardware guy to find out if a new array is feasible. If this turns out to not be possible due to hardware or cost, then I will leave the log locations alone.
If I do end up moving them, I will use your suggestion about making copies of the LDFs somwehere else until the process is complete and verified.

One additional question. Is there anything special to move the LDFs for the master and msdb databases? What about the optimal locations for the tempdb MDF and LDF. And any special concerns moving them?

Thanks again.

 

[Catadmin]

Don't worry about the TempDB. It is literally a temporary database that empties itself out on a regular basis. It is only used during Insert, Update and Delete operations and then *poof* all data is gone.

As far as the Master, Model and MSDB database, they really shouldn't use Transaction Logs. The Master DB is the definitions of your SQL Server (collation, dictionary sort, etc.) which only changes once in a blue moon (or should, anyway). The Model contains all the user database objects and logins, so it will change more often, but you shouldn't waste your time with transaction log backups, just full backups either once a month or once every two weeks and a backup everytime you change or add something to your databases. The MSDB database contains all your jobs, replication and DTS information. Again, don't waste your time with transaction backups for this. Doing a complete database backup once a month or once every 2 weeks (depending on how often you add jobs & publications) should be plenty fine.

Additionally, I'm leary of doing anything with the moving of system dbs once they've been installed to a specific location.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[BlakeK]

Catadmin,
Thanks for the info on the system databases.
If I able able to have a 3rd array installed on the server, I will onlt move the LDFs for the user databases to that array.
Not sure how familiar anyone here is with the hardware aspect of this situation, but will the speed benefit of having a 3rd RAID array still be realized if that array is on the same controller as the 2nd array?
My hardware guy has told me that is we can't get another conrtoller for a 3rd array, we may still be able to have a 3rd array that is run off the saem controller as the 2nd array. Woudl that even be worth it, or if we can't get seperate controllers, should we just leave all the data on one array?

Thanks,
Blake

 

[Catadmin]

As far as RAID controllers go, I'm out of my depth on that one. If no one answers the question for you today, I'd recommend posting on one of the hardware forums and ask them if having 2 RAID 5 arrays on one controller is possible and/or causes any problems with accessing the data.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

Odds are that you are not even close to maxing out the through put on the RAID controller. You can use perfman to find out how much data is being pushed to the disks. You'll probably find that it is less than 10% of the max that can be pushed through the controller card.

Only once have I seen a server come close to maxing out it's RAID controller.

With reguard to the new array, I would have to recommend making a RAID 1 (or 0+1) not a RAID 5 for the transaction logs. The reason for this is that RAID 5 is slower for writting data to the array than raid 1 (or 0+1). This is because the raid controller has to calculate parity for each and every block of data written to the controller. With Transaction Logs being mostly write you will get better performance on a RAID 1 (or 0+1).

Your backup array will also give you getter performance if it is 1 or 0+1. You want to keep the array with the mdf files a raid 5 as that will give you more speed for reading than a RAID 0+1 with the same number of drives.

Moving the transaction logs of the system databases isn't needed at CatAdmin said, because the logs are rarly used. However don't bother trying to back up the transaction logs on the system databases. The system databases are in simply recovery mode so the logs can't be backed up.

As far as the "N" goes, I have yet to find a reference which explains it satisfactorily.

The N in the commands tells SQL Server that the path may contain unicode data in the string. It makes the text string being used an nvarchar instead of varchar.

With reguard to TempDB; in a perfect world TempDB should be on it's own RAID 1 array. TempDB is read from and written to constenly. Especially if you use temp tables or global temp tables. If your system is very busy, and you use tempdb a lot, I would look into this. If you don't use tempdb much then it can stay on the same drive as the other mdf files. It kind of depends on your situation.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

Thanks for the great detail Denny.
My only question is about TempDB. You mentioned that it is written to all the time, and that ideally it would be on it's on RAID array. Then you said if that's not possible, it can stay on the same array as the MDFs.
If I am able to get a 3rd array for the LDFs of the yser database, and set it up as RAID1, would it make since to move TempDB to that array rather than leave it with the MDFs, sicne you said writing would be faster to the RAID1 array the LDFs are going to, rather than the RAID5 array the MDFs will remain on?

Thanks

 

[Catadmin]

I would leave it with the other MDF files since the data that is being written to the TempDB usually comes from the MDF files. A shorter "hop" diskwise. But I don't know that moving it would necessarily cause any performance problems.

Another thought, if you really really have the money and the desire to optimize, would be to add a 2nd processor to your server so it can handle not only all the disk I/O requests that may not handled by the RAID controllers, but all the SQL requests back and forth. This is assuming you've got a SQL edition that supports multi-processing. It would also allow you to take advantage of hyper-threading which would theoretically speed up query performance.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[BlakeK]

Catadmin,
Our current setup is a Windows 2003 Server Standard, running SQL 2000 Standard SP3, on a machine with 2 Xeon 3.0GHz processors, and 4 GB of RAM (SQL Standard unfortunately only uses 2GB of the RAM, so I can't wait for SQL 2005 Standard which doesn't have RAM limits!)

 

[mrdenny]

I'd leave the tempdb where it is. You'll have less contention with it with the mdfs than the logs. I've just published a very long FAQ on disk configuration for SQL Server faq962-5747. I'm not sure if it will be available to you or not yet.

With reguard to a shorter hop from one file to another, I'm not sure where you are headed there. Either way the data has to come off the disk, through the controller, to the CPU and RAM, processed and sent back to through the controller to the disk.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)