VMware datastores and SQL Server drives
-
Hi,
I am changing a standalone SQL Server 2008 R2 to a Windows Failover Cluster 2016 Server with SQL Always On Availability.
The current 2008R2 server has the following drive setup:
C: (OS), D: (SQL Server 2016 application program files), F: (SQL log files) and G: (SQL Backup files) are currently on one vmware datastore
and the E: drive (SQL data files) is on another separate vmware datastore
The planned 2016 Cluster servers will have the same drives.
Should each drive have it's own vmware virtual data store?
Or should there be separate vmware data stores for the SQL Data (E:) DatstoreA and SQL log files drives DatastoreB and the rest (C:, D: and G:) be on one data store DatastoreC? So three different datastores in total?
What is the recommended or best practice for a SQL AAG Cluster Server and vmware datastores?
-
My opinion (we run things on physical hardware with a SAN handling the physical disk, not VMs), and my understanding is that the datastore makes no difference; you want things on different physical disks wherever possible. If all of the data stores are on the same physical disk, you do not get the benefits of having things on separate drives.
If the datastores are on different physical disks, you want to keep yours data and log on separate disks for best I/O. And the fewer things that touch those disks, the better your I/O will be for SQL. For best disk I/O, your suggestion of having data on datastoreA, log on datastoreB and everything else on datastoreC would give the best disk I/O.
Now if Disk I/O isn't a bottleneck, doing that may not offer a performance boost. Or if all 3 data stores are stored on 5400 RPM disks, you are likely going to have disk I/O performance issues.
Now if you have a SAN on the back end, the 3 data stores may all end up being on the same physical disk, or you may end up having it spread across 100 disks. It depends on how you configured the SAN and how the SAN handles that in the back end. My understanding is that most modern SANs have the ability to watch for hot spots and do performance tricks in the background such as migrating stuff around from slow to fast disk or having one physical disk shared between multiple systems if the I/O is not high on the two and getting dedicated disk to ones that need it. Here, the disk I/O is handled on the back end and having different datastores just removes the OS overhead on reading and writing to the same logical disk.
If you have a SAN setup, Brent Ozar has a good writeup on where to put data and log files here:
https://www.brentozar.com/archive/2008/08/sql-server-on-a-san-dedicated-or-shared-drives/
but reaching out to the SAN vendor or reading their documentation related to their SAN and SQL Server - link 2:
https://www.brentozar.com/sql/sql-server-san-best-practices/
is likely going to be your best bet.
TL;DR - without knowing your disk backend (SAN, physical disk, etc) and if your datastores are on different physical disk, it is hard to give a good recommendation.
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. -
Where is tempdb going to be located? That is probably most critical in a VM environment.
Also, there are multiple types of SCSI adaptors in VM Ware. The default is an "LSI Logic SAS". For the data, logs, and tempdb, use the "VMWare Paravirtual".
You can create up to 4 different scsi adaptors. I suggest using separate paravirtual adapters for the data, logs, and tempdb, and the default for everything else.
Separate data stores may or may not make a difference. What is the physical configuration of the underlying disk subsystem?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thanks for your reply.
There are already about 70 volumes stored on the Dell Equallogic SAN using SAS 10,000 RPM disks with three SAS members (24 disks each) which are in one Storage Pool.
-
It doesn't matter then. The Equalogics are basically one big drive.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thanks Michael.
With SQL AlwaysOn AG Clustering, some have mentioned to use 64K unit allocation sizes for the SQL Data and SQL Logs, is this still necessary, or will it be fine with the default allocation on each cluster server?
If these changes are made on the same SAN volume will it matter? The C: drive on the same SAN volume was already setup as the default unit allocation size.
- Majik wrote:
Thanks Michael.
With SQL AlwaysOn AG Clustering, some have mentioned to use 64K unit allocation sizes for the SQL Data and SQL Logs, is this still necessary, or will it be fine with the default allocation on each cluster server?
If these changes are made on the same SAN volume will it matter? The C: drive on the same SAN volume was already setup as the default unit allocation size.
Yes. configure the data, log, and tempdb drives in 64k blocks.
Google David Klee. He has written quite a but about SQL Server on VMWare.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply