SQL Server Performance Forum – Threads Archive

Database and logs on same physical disk?

I am about to move my SQL 2000 DB to a new server. The old box is a dual PIII 600 with 512 Meg RAM. The new box (IBM X360-8686) is a Quad Capable Xeon MP 1.6 1 meg cache populated with 2 Processors currently and 4 Gig of RAM. But it has only 3 hard drives each 36 Gig with Hardware Raid. I had setup a Raid 1 array and let the 3 rd drive be a Hot Spare. After some thought I changed the Hot Spare into a standalone drive. The Raid 1 array is partitioned as 3 Volumes. System is 7 gig, Data is 22 Gig and Storage is 6 Gig. My databases are about 10 gig all combined. I am thinking I should use the standalone drives for the SQL Logs to help performance. Any opinions on this idea? My fear is I have no redundancy. I could create a maintenance plan to back them up every hour to the backup NAS on the network. I just want the best performance out of what we now own. The initial intention of this system was to connect to external storage and serve as a front end for a storage system but it has much more power than we need for that. We do it now with a dual PIII 1.13 with no issues whatsoever. Any suggestions based on experience are appreciated. Doug

What is the userbase on that application?
If you’re fairly looking for performance of application then ensure to seperate data and log files physically in order to gain the rAID levels. But on the part of backups, ensure to maintain regular intervals. I hope you’re comfortable about moving databases to the new server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also, if you can, is good idea to place data in one physicall and indexs in other.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

>> Also, if you can, is good idea to place data in one physicall and indexs in other. What does this mean. IE how do I do this? Sounds like I would have additional files that make up the database versus just an mdf and ldf that I have now.
>> What is the userbase on that application?
It is a Web App and there are rarely more than 10 simultaneous users hitting the database. >> f you’re fairly looking for performance of application then ensure to seperate data and log files physically in order to gain the rAID levels. What RAID levels? The Log drive would be a single drive only, not a Mirrored set as the data array is. If I had enough disk I would create a Raid 10 array for each but since this system was purchased for another purpose and retrofitted to the database I am wondering if the RISK of putting logs on a standalone disk is viable. IE what happens if I lose the drive the LOGS are running on. Does my database instantly crash?? Or will it create a new ldf on the same drive as the Mdf (boy that would be nice but I am dreaming here). That is my primary concern as I know performance will improve. My concern is what happens if the ldf drive dies in the middle of the day. DC

Supose you have 2 physicals drives and you put database in one drive. All I/O will on this drive.
But if you can split data in one drive and indexs in other drive, I/O will more balanced and you get more performance.
To do that, if neccesary, you can create a new filegroup in secondary drive. Using EM you can change indexs from Primary filegroup (first drive) to secondary filegroup (second drive).
Now you will have one mdf in one drive and one ndf in other drive. Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

On the part of recovery in case of failure, if you maintain the complete backups regularly, then its easy to recover them. You can think of mounting standby server and copy the backups for database & transaction log on daily basis. If the current setup is not having any issues in performance then leave them as it is or add more physical memory or disks for better performance to the current setup. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>