Re: Design storage for SQL servers

rbauman79 · ‎2014-05-05

i have about 5-6 SQL servers running on my current vmware vsphere 5.1 infrastructure. 3 Host cluster, with a fas 2240HA storage running 15k 600gb drives, raid_dp, Raig group 24 disks, 1 aggregate, currently have 3 volumes and 3 luns: vmware_OS, vmware_Data, vmware_swap. i have VSC running and utilitze snapmirror to send vm data to DR site. i also use VSC and setup snapshots every 4 hours, along with nightly backups and month end. We have the licenses for snapmanager for SQL, Exchange, Virtual Infrastructure but are currently only using the SVI. Currently have about 30+vm's running on the datastores listed above. My SQL server vm's are laid out with C drive as OS and holding system DB's and D drive as user DB's, program files for applications. I have noticed that performance seems a little sluggish from time to time on a lot of my vm's. i have plenty of cpu/memory, nic (for each host i have 16cpu/32 logical cpu, 128gb memory, 8 1gb nics configured for multipathing and traffic separated as follows: iscsi path a and b, vmotion, and data) i am trying to determine if it is a disk i/o issue since all of vm's are running under the volumes/luns listed above. i am running perfmon on the sql vm's now but need to go through the data still. i have VCOM setup as well and am going through the data as well.

i have read several of the sql best practice guides but am looking for some guidance on how to set this up. besides performance and i also looking to use the SMSSQL but not sure where to start. should i create more volumes and luns and place all the sql servers in there and separate out system DB's from user DB's and put all logs on another lun?

radek_kubka · ‎2014-05-06

[I'd say this thread would be better placed in Microsoft Applications and Databases]

Have you read TR-4003 & SMSQL Installation and Administration Guide?

How many user DBs are we talking about? If not too many, then ideally you should have a dedicated pair of LUNs per each user DB for logs and tables. Then more LUNs per each SQL instance, one for Temp DB, one for system DBs and one for SnapInfo.

Regards,

Radek

rbauman79 · ‎2014-05-07

Hi have not read that one yet. So far i read TR3693, TR3779 & TR3821. i will read through the one you mentioned.

Right now i have 6 sql servers each running on the default instance. i have 28 User DB's total. How should i create the volume structure? one volume per lun? Since i am utilizing the default instance with multiple DB's under them. In TR3693 starting on page 26 it gives some examples of the structure which sounds like what you are describing, since i am only using one instance per server with multiple DB's does that change anything?

Just so i am understanding correctly, would i move the entire VM over to the new volume/luns and have that reside on say vol1/lun1 where the system DB's would be and move around existing vmdk's to the appropriate volume luns with user DB's on them already as well for each vm and/or create new vmdk on the vm to separate out userDB's, tempdb, snapinfo? or does this design mean leave vm on current volume/lun and then move system db, userdb, tempdb, to new volume/luns?

since i have roughly 28user db's should i separate out logs or is that overkill? i see by doing that you can be more granular with snapshot copies.

thanks for the help.

adamn · ‎2014-05-06

Hello rbauman79,

The Information Engineering team at NetApp is responsible for the delivery of the SnapManager for Microsoft SQL Server Installation and Administration Guide (that Radek mentioned).

If you have already consulted it and found that it did not provide answers, we'd like to understand what we can do to improve the document. The principal author of the guide is quite literally in the middle of making substantial improvements to the guide for its next release. We'd be more than happy to share an early draft for you to review.

Please let us know if that would be helpful and we'll get you the draft content as soon as possible.

Regards,

Adam Newton

Sr. Manager, Data Center and Cloud Information Engineering (DCC IE)

radek_kubka · ‎2014-05-07

Hi Adam,

It's actually quite handy you responded to this thread.

There are some areas of SMSQL which are still a bit mysterious to me:

- databases with more than one filegroup (https://communities.netapp.com/thread/35347)

- clustered SQL instances (https://communities.netapp.com/thread/32864)

If these topics can be addressed more thoroughly in the SMSQL IAG that would be awesome.

Thanks,
Radek