November 23, 2021 at 5:11 pm
Installing SQL Server using different drives for data, tran logs, tempdb and backups has obvious advantages on physical servers. Is there any PERFORMANCE advantages on vmware. I am pretty sure whole server is on 1 data store.
November 23, 2021 at 8:33 pm
My understanding - even on a SAN the performance benefits may be lost. The idea is to have it on separate physical disks. once you start working with virtual disks, you can't know if the data and log are on the same physical disk even if they are on different virtual disks.
The performance benefit is in the I/O due to seek time and the concurrent read/write time for the most part. There is overhead from the OS that you should get a performance boost by having them on different virtual disks, but the boost would be very close to 0 that you may not even notice the benefit.
Now, for things like Backups, I would definitely put them on a different physical disk and virtual disk. If your data, logs and backups are all on the same physical disk and that disk dies, you lose your database AND your backup which is very bad. As for having it on a different virtual disk, my reasoning is more just for sanity purposes. When moving the data from disk to tape (or whatever your long term backup solution is), it is MUCH easier to just toss that whole drive content onto tape.
I also like having things on their own virtual disk for organization purposes as well. If I know my OS is on C, D is my page file disk, E is my data, F is my logs, and G is backups on any and all SQL instances I have, I can quickly and easily find my backups or logs or data. With failover that can get a bit more messy though.
At my workplace, we have one virtual host disk dedicated to backups so if you browse to \\backup\g$ (not real server name, just an example), ALL SQL backups reside on that one disk. That disk, since it is on a virtual host, has failover between 3 physical servers. Any server goes down and one of the others will pick it up and backups may only have a brief interruption (roughly 2-3 seconds for the failover to occur).
Having the whole server on 1 data store is fine, but if it is on 1 physical disk, then you may want to ask for a second disk to be added for your backups. Don't want all of your eggs in 1 basket...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 24, 2021 at 7:13 pm
Regardless of VM or physical - there is a performance advantage that can be realized when every data file for a database is on a separate drive. There is a trace flag that can be set that will improve DBCC CHECKDB performance when you can guarantee every file for the database is on its own drive.
Specifically for a VM - it all depends on how the volumes are presented to the server. If the volumes are allocated out of the VM pool - then the only thing that can happen is a degradation of performance because all other guests on that host are sharing the same disk pool. If the volumes are presented directly to the guest - those volumes will not be impacted by other guests on that host.
Using separate locations for system databases, tempdb, user databases, user tlogs and backups isn't about performance. It is about management of the system and *could* be useful for certain backup utilities. Some of those utilities can freeze the database - or the volumes to create snapshots at the storage level and if everything is on the same volume those utilities might not work (depends on the utility).
And just because you can put everything on the same SAN volume doesn't mean you shouldn't separate on the server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply