CSV design SQL Server 2014 hosted on HyperV cluster

Category: sql server dr

Question

Chuck Croasmun on Sat, 27 Jan 2018 16:27:52


I have a 2 node HyperV 2012 R2 cluster sitting on top of shared JBOD storage. I am virtualizing A standalone deployment of SQL Server 2014 Standard on this platform.

To isolate and provide separation of aspects like csv readcache from other workloads I am considering separate CSV's for: Data, logs, temp and possibly even putting the server on it's own segregated from the other VM's.These volumes would be implemented on the same pool as csv's for other workloads though so there is the possibility of redirection if ownership change for some reason. The pool needs to be accessible to both nodes, but I could only allow the VHD's to be owned by the server hosting the SQL server, I am limited in this aspect due to the other csv's on this pool needing to be able to change ownership.

First off, is it truly beneficial to create the volumes at this level? Theorectically it seems it would as technically these are separate LUN's.

Second, is there any major concerns especially with the redirection scenario, or to force avoiding redirection by only allowing the volumes to be owned by the host that SQL server is on?

eg:

- 4 TB Pool on JBODs managed by HyperV 2012 R2 cluster

- 80 GB CSV for SQL Server

- 25 GB CSV for Temp

- 125 GB CSV for Logs

- 350 GB CSV for Data

- Remaining pool space is a CSV for other workloads

Any input is greatly appreciated,

Thanks,

Chuck C

Replies

Chuck Croasmun on Sun, 28 Jan 2018 15:36:43


Posted on the storage forum also and wanted to add my response to someone on that forum for clarification:

Thanks for the response!! This is already a production HyperV cluster with Storage Spaces setup per best practice. This pool is new for expansion and I am trying to determine how best to support the SQL Server best practices sitting on top of a HyoerV deployment configured like this.

I created 1 large mirrored pool to optimize performance but SQL server is only going to require 20% of the space, thus my indicating the "Remaining space" bullet above being used for other workloads. Further I specified the "Stand Alone" specific deployment of SQL server due to concerns pool ownership among the hyperV host possibly changing. The other workloads will need this functionality, but I believe SQL Server can have issues with redirection.

Per SQL Server suggestions the different DB roles should be on separate LUN's, in Storage Spaces this equates to separate CSV's and further gives the versatility of managing them separately and the versatility of adjusting attributes such as read cache individually.

I get this versatility by pushing the drives down on to separate CSV's instead of 1 large CSV handling all workloads, but I have the ownership concerns al the pool layer. I can force the CSV's hosting SQL Server workloads to not change ownership, but I need to allow the pool to be allowed to change ownership.

It seems like this config might cause issues, but I am not sure of the implications, and I am not sure if the effort to push the drives down to separate CSV's (LUN's) is even worth the effort from a performance stand point.

I posted on the SQL Server forum also but will add this clarification on that post also.

Thanks again, any further comments are appreciated.

Teige Gao on Tue, 30 Jan 2018 08:27:59


Hi Chuck Croasmun,

 

>>Further I specified the "Stand Alone" specific deployment of SQL server due to concerns pool ownership among the Hyper-V host possibly changing.

 

It is suggested to use a standalone storage for SQL Server in case of disturbance or some false operation. Besides, it will make the disk continuous and reduce the fragmentation.

 

>>125 GB CSV for Logs - 350 GB CSV for Data

 

Which recovery mode will you choose for SQL Server? If it is full mode, from my point of view, it is not enough to give 125 GB for logs in your scenario, based on my experience, using about 500 GB for log is suitable in this scenario.

 

>>Per SQL Server suggestions the different DB roles should be on separate LUN's

 

I don't know how did you configure your CSV on LUNS and how these LUNs configured, if you configure all your CSVs on the same physical disk and using the simple recovery mode, it is meaningless to configure different roles on separate LUNs.

 

If these CSVs are configured on different disks, it will improve the I/O performance of SQL Server when SQL Server writing to log and data file. Another advantage is that when keeping log and data file on different CSVs, it will provide a point-in-time recovery when one CSV broken.

 

Best Regards,

Teige