Depending on the underlying storage, and the workload being driven by the application, typically yes.
The good news is apart from maybe 2 mins more to setup, there is no real significant downsides to more virtual disks so for a vBCA I generally see multiple VMDKs per PVSCSI controller.
Large Exchange deployments I typically deploy 4 or more VMDKs per controller (one per DB and one per logs).
As you already mentioned It can be a very sensitive topic for DBA.
Just to avoid everybody start shrinking their databases for no reasons 🙂 It is worth to mention that this is not a silver bullet for all SQL Server storage performance issues. Having said that it is absolute minimum to have SQL Data file and SQL Log file on a separate disks via separate SCSI controllers just simply because in most cases they both have to write at the exact same time.
There are some things to consider as well, which you may want to include into your post:
1. Always do your DB backup prior manipulating with database data or log files (I know it is obvious but just as a reminder).
2. Any Shrink operation (DB or File) cause index fragmentation and can slow the performance of queries. To avoid performance issues afterwards it is recommended to rebuilding the indexes on the file after shrinking (here is a good example http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/).
3. It is also better to use ALTER DATABASE instead of Deattach/Attach. Deattach/Attach operation will disable some of the functionality which may be in use by the DB relying application (Service Broker if was enabled, TRUSTWORTHY attribute). Also it may not be possible to attach/deattach at all due to replication, mirroring, snapshots. You will not have such problems if you do: ALTER DATABASE to select new file paths, take DB offline, copy the files to their new locations, and bring DB online. Relevant link: https://msdn.microsoft.com/en-us/library/ms190794.aspx?f=255&MSPPError=-2147217396
4. It is ok in your article, but it is worth to mention don’t muck around with filegroups of data files, the reason being the PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. New objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. If you are not sure keep everything in PRIMARY filegroup.
Those are alternatives but ones which were not chosen, I tend to list all available options as alternatives even when I do not recommend they are used. This at a minimum at least indicates the person (in this case myself) is aware of the option/s even if they are not recommended. e.g.: If the alternatives were not listed, a person reading a recommendation/decision doesn’t have enough context to understand why a decision was made.
SAP Hana, although I have 70+ Nutanix nodes, I’m about to buy an AFA for SAP Hana, because I need my storage to be certified by SAP.
Hi Josh,
Would there be any benefits in using more VMDKs per controller in this design? (say 3 or 4). Thanks.
Hi Jesse,
Depending on the underlying storage, and the workload being driven by the application, typically yes.
The good news is apart from maybe 2 mins more to setup, there is no real significant downsides to more virtual disks so for a vBCA I generally see multiple VMDKs per PVSCSI controller.
Large Exchange deployments I typically deploy 4 or more VMDKs per controller (one per DB and one per logs).
Hope that helps.
Hi Josh, that makes sense. Thanks.
Hey Josh.
As you already mentioned It can be a very sensitive topic for DBA.
Just to avoid everybody start shrinking their databases for no reasons 🙂 It is worth to mention that this is not a silver bullet for all SQL Server storage performance issues. Having said that it is absolute minimum to have SQL Data file and SQL Log file on a separate disks via separate SCSI controllers just simply because in most cases they both have to write at the exact same time.
There are some things to consider as well, which you may want to include into your post:
1. Always do your DB backup prior manipulating with database data or log files (I know it is obvious but just as a reminder).
2. Any Shrink operation (DB or File) cause index fragmentation and can slow the performance of queries. To avoid performance issues afterwards it is recommended to rebuilding the indexes on the file after shrinking (here is a good example http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/).
3. It is also better to use ALTER DATABASE instead of Deattach/Attach. Deattach/Attach operation will disable some of the functionality which may be in use by the DB relying application (Service Broker if was enabled, TRUSTWORTHY attribute). Also it may not be possible to attach/deattach at all due to replication, mirroring, snapshots. You will not have such problems if you do: ALTER DATABASE to select new file paths, take DB offline, copy the files to their new locations, and bring DB online. Relevant link: https://msdn.microsoft.com/en-us/library/ms190794.aspx?f=255&MSPPError=-2147217396
4. It is ok in your article, but it is worth to mention don’t muck around with filegroups of data files, the reason being the PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. New objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. If you are not sure keep everything in PRIMARY filegroup.
Hi Josh,
in the alternatives why we are using Set Host isolation response to “Leave Powered On” and cvm to shutdown.
can you please explain the reason for configuration mentioned in alternatives section?
thanks
ritchie james
Hi Ritchie,
Those are alternatives but ones which were not chosen, I tend to list all available options as alternatives even when I do not recommend they are used. This at a minimum at least indicates the person (in this case myself) is aware of the option/s even if they are not recommended. e.g.: If the alternatives were not listed, a person reading a recommendation/decision doesn’t have enough context to understand why a decision was made.
Hope that helps.