May 31, 2011 at 11:05 am
Hi I have one SQL Server with about a dozen relatively small databases. I had the data and backup folders all on the same drive (C:\MSSQL10.MSSQLSERVER\MSSQL\Data & C:\MSSQL10.MSSQLSERVER\MSSQL\Backup) as the install for the server. All the Primary Data files and Transaction Log files were together in the Data folder.
We have recently migrated to a new NetApp configuration with VMWare and the files have been separated to different drives. Now the .bak files are on the C:\MSSQL10.MSSQLSERVER\MSSQL\Backup folder but the Primary Data files are on a virtual drive (E:\MSSQL10.MSSQLSERVER\MSSQL\Data) and the Transaction logs are on another virtual drive (X:\MSSQL10.MSSQLSERVER\MSSQL\Data). I'm told it is best industry practice to separate the files this way but I have never heard that so I would like some opinions from the experts that read these forums. Thanks for any thoughts on the matter.
May 31, 2011 at 11:34 am
Alan,
You are correct that the BAK, MDF and LDF's should all be separated at a minimum to provide better fault tolerance.
There are other complexities that make separating your files into different 'disks' to still be a problem. For example, if your drives are on a SAN, then your 'logical' disks may end up really landing on the same physical disks, or your 'separated' mdf/ldf/bak|trn ends up 'striped' across the same physical disks, then you loose the recoverability benefits in exchange for the SAN admin having an easier time managing your disks.
In your case, it sounds like you have a small operation, so your biggest issue is having those VM disks mapped to the same underlying physical disks, causing the same vulnerability as with the SAN scenario above. If they are, then you loose the recoverability that separating the files gives you. So ensure that E: and X: map to a different set of disks entirely from each other and your C:.
Your first goal is to ensure recoverability and data protection. THEN you can work on performance if you have the extra disks for that.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 31, 2011 at 1:09 pm
alan.fisher (5/31/2011)
Hi I have one SQL Server with about a dozen relatively small databases. I had the data and backup folders all on the same drive (C:\MSSQL10.MSSQLSERVER\MSSQL\Data & C:\MSSQL10.MSSQLSERVER\MSSQL\Backup) as the install for the server. All the Primary Data files and Transaction Log files were together in the Data folder.We have recently migrated to a new NetApp configuration with VMWare and the files have been separated to different drives. Now the .bak files are on the C:\MSSQL10.MSSQLSERVER\MSSQL\Backup folder but the Primary Data files are on a virtual drive (E:\MSSQL10.MSSQLSERVER\MSSQL\Data) and the Transaction logs are on another virtual drive (X:\MSSQL10.MSSQLSERVER\MSSQL\Data). I'm told it is best industry practice to separate the files this way but I have never heard that so I would like some opinions from the experts that read these forums. Thanks for any thoughts on the matter.
Is this SQL Server a virtual machine then
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2011 at 1:19 pm
You need to make sure you understand how the Netapp SAN has been setup and configured. If the LUNs presented for your data drive is from an aggregate with 64 spindles - you have protection from disk failure on the SAN itself and shouldn't worry too much about mixing data/log/backups across the spindles.
You should only be concerned with this if the aggregate is using a small number of spindles to provide the data. The fewer spindles - the more likely you are to suffer from contention or have issues if a spindle fails.
As for performance - on these types of SANs, performance is going to be based on the number of spindles and the amount of cache on the SAN. It will also be determined by how the filers are setup and whether or not your data and log LUNs are presented (pathed) through the same filer.
Using Netapp SAN - you need to also make sure your system databases are separated from your user databases. If not, then you will not get any benefits if you decide to move forward with SnapManager for your backups.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2011 at 8:28 pm
Another recommendation I would throw in is to have your backups on a different location. I've seen a number of clients run out of disk space and keeping backups on a different drive removes them as a factor. That and SnapManager stuff the only thing that having them on different logical drives makes a difference for. In a SAN situation just because they're on different logical drives doesn't mean anything about what physical drives they are on so you need to be familiar with that too.
June 1, 2011 at 10:47 am
Thanks All for your comments, gives me some place to start. I was not involved with the install of the NetAPP so I'll have to do some research on the data structure. We do have two NetApps located in two physical locations which sync every hour so I'm not so worried about either one crashing hard. I do backups through the SQL Server Agent and I see that the SNAP Manager (?) is also performing some type of backup as well. All our servers are installed on VMWare so I guess the answer to Perry is yes.
Seems like I have some studying to do on NetAPP and how it interacts with SQL Server, if anyone knows of some good resources get me going I would appreciatet. Thanks again for the input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply