Best Practices for SnapManager for SQL and SnapDrive 6.3 with VMDKs

[#1]

Hi all,

With the introduction of SnapDrive 6.3 and VMDK support, how does this affect the way Ideploy SQL in virtual environments?

I am familiar with SQL data layout using RDMs and LUNs through the Microsoft ISCSI initiator in the VM but how does this affect SQL data layout using VMDKs? Do I just substitute LUNs for VMDKs on NFS volumes using the same logs/database separation? Is the sizing process the same? Is throughout on a VMDK in a NFS volume the same as a LUN?

Any help in this area would be useful, as we will be deploying more and more of these environments using SnapDrive 6.3 and this would really simplify things.

Thanks,

David Brown Senior Virtualisation and Storage Consultant for EACS Limited - NCDA, NCIE

[amritad]

You could file a support case for this. In parallel we’ll try to check with engineering.

Regards

Amrita

[murnal]

I have verified the SDW 6.3p1 behavior with VMDK’s on NFS datastores and they seem to be working on the QA setups. The setup we have used in our QA labs :-

Virtual Center 4.0

ESX 4.0

VM version : win2k8EEx64

VSC : 2.0.1 x86

SDW 6.3

SDW 6.3p1

Test steps followed :

1. Installed VSC 2.0.1 on the same system where the Virtual Center is installed. This will be a plug in to Virtual Center.
   
2. Registered the VSC 2.0.1 to the VC IP,user name and password.
   
3. Configure the VSC 2.0.1 ,select the Backup and Recovery->Setup and “Add” the storage system on which the NFS datastore is created.
   
4. Add the VMDK to the NFS datastore.
   
5. Install SDW 6.3
   
6. After the VC/ESX settings wizard, a SMVI wizard pops up. Click on the checkbox  for the user to enter the IP address of the SMVI. Click OK.
   
7. After the installation is completed, From the disk management , format the disk and assign the drive letter.
   
8. SDW 6.3 now shows the VMDK created on the NFS datastore.
   

Upgrade Scenario

1. Upgrade SDW 6.3 to SDW 6.3p1.
   
2. SDW 6.3p1 identifies the VMDK disk created on the NFS datastore.
   

QA team would like to know more on the customer configurations which will help us to analyze the problem quicker.

[watan]

Hi Radek,

Archana was able to confirm that its working in the lab, please see comments below.

Thanks Archana for the quick response!!

[radek_kubka]

Guys,

Many thanks for all your responses & sincere apologies, because the actual issue turned out to be caused be me not checking VSC versions carefully enough!

I had upgraded from VCS 2.0 to 2.0.1, but later on I restored an earlier version of vCenter virtual machine, so it popped back to 2.0. You can see in this thread that I actually did look into the version information & the build info said 2.0.10194.1146. This in fact though was 2.0, not 2.0.1 as I assumed looking at the beginning of the build number. So this perfectly explain why SDW beta recognised NFS VMDKs & non-beta refused to do it - the former doesn't check whether VSC is 2.0.1, whilst the latte does.

Now I upgraded (again ) VSC to 2.0.1 and all works fine!

Kind regards,

Radek

[watan]

Glad to hear that it worked for you and thanks for your honesty!! 

[watan]

Hi Radek,

I'm confirming with Dev/Qa to see if anybody else is having any issues.  Will post a response once I get it.

[davieb1969]

Excellent, that would be most handy.

Thanks

[jjbitservices]

Watan,

Is there already a best practice regarding SQL / VMDK / NFS?

Currently i'm with a customer who has 33 SQL servers. If I use the traditional LUN layout i''ll get 132 NFS exports only for SQL.

vSphere 4.1 accepts only 64mounts on an ESX host.

Currently the database vmdk's are mixed with other VM's  in a Datastore.

From my point of view vmdk=lun isn't needed because snapshotting on NFS is way more efficient. A few extra snapshots on a volume shouldn't be a big problem i'll guess.

Cheers,

JJ

[watan]

Hi JJ,

Unfortunately we don't have anything available now but the next SMSQL BPG is in the works and should cover this.  Its not the SMSQL BPG but definitely worth taking a look

Accelerating Development of Microsoft SQL Applications in Heterogeneous Environments

[illumitltd]

Hi Watan,

Is there any update on the BPG?

We're using SMSQL with .vmdk's on NFS (and will shortly be using SMSP, too). I'm specifically interested in:

- Layout in an NFS environment (.vmdk's sharing volumes; location of C:\ drive vs data vs logs .vmdk)

- .vmdk type (should data disks be independent .vmdk's? - VSC/SMVI will also snapshot the parent volume otherwise; is this OK?)

- Storage vMotion implications (if any)

- General best practice guidance

The technology is great; we just need some BPG advice / guidance, please!

Thanks,

Barney.

[watan]

Hi Barney,

I'll check with my team to see if they can chime in for your questions and see how the progress is going on the bpg.

Thanks,

Allan

[watan]

Here's another thread http://communities.netapp.com/message/55895#55895

Abhishek is our resident expert for SMSQL and will be updating the BPG. 

[illumitltd]

Thanks Watan; all assistance / guidance is much appreciated.

I guess the BPG will still hold the answers I need though, as the link above mainly refers to "traditional" LUN-based storage rather than .vmdk's. If we apply the same rationale to .vmdk's/datastores as to LUNs/volumes, then Best Practice would be:

Sysdb in it's own volume

Data in it's own volume

Logs in their own volume

SnapInfo in own volume

Since each volume is an NFS export to ESX, we are therefore consuming 4 NFS mounts per SQL server. Given that there is a limitation of 64 NFS mounts on ESX, it would be good to reduce this.

- Is it therefore possible to place sysdb and data in a single volume?

- What about placing Logs and SnapInfo in a single volume?

- Can we sensibly place vmdk's from multiple SQL servers in the same volumes (as long as SMSQL jobs don't run concurrently?).

- If we accept the risk of SQL system loss in the event of losing a volume, can we place all four vmdk's in one datastore? (we have snapmirror as a recovery point in this scenario).

It seems that the major benefits of vmdk's over block storage for SMSQL are space savings and flexibility/mobility of storage. It would be great to have some advice on what's good/sensible practice here.

Please keep us posted on BPG progress.

Thanks again,

Barney.

[abhisek]

Hi Barney ,

We are currently in process of refresh of the BPG.However we are also going to publish a SMSQL activation guide

which will contain more about configuring the SQL over vmdk environment information.

You need to architect your environment keeping VM files and SQL db files on separate datastores. You would require separate vmdk’s for system db’s, user db’s, log files, temp db and log files.

Keep in mind that you have 8 datastores by default and can reach 64 datastores as maximum. 

Regards,

Abhishek

[illumitltd]

Hi Abhishek, and thanks for the reply. Please do keep us posted on the BPG.

Kind Regards,

Barney.

[abhisek]

Hi Barney ,

Please look for TR 4003 , TR 3941 and TR 3785.

Regards,

Abhishek

[jhubert]

Any updates?  The latest SMSQL guide I saw mentions VMDK support, but does not address restore scenarios or best practices.  Ideally, I'd like to see sample configurations and the restore implications of different layouts (same datastores, separate VMDKs,), (RDMs vs VMDKs, etc..)

[abhisek]

Please have a look into TR 3941 and TR 3785.Also the new BPG to be released in this month will have contents in respect to VMDK implementation.

[awells]

Hello.  Is the new BGP available now?  My first attempts to locate it have not been successful.  Thank you.

[spence]

http://media.netapp.com/documents/tr-4003.pdf

[keitha]

Although there is no BPG yet I did want to highlight a couple things for you that I discovered with a client. Regardless of how you configure SQL in VMware (RDMs, VMDK, direct to VM) the backup operates very similarly. That is, you can do SQL backups in a few seconds.

The restores however are rather different based on the method you use. In a RDM or Direct to VM model restores are usually Volume level snaprestores which are very fast. In a VMDK model however a volme level SnapRestore is obviously not possible so with NFS a File level SnapRestore is used which is much slower. Important for admins to know.

In a LUN environment or if you place more than one DB in a VMDK then a file copy occurs. Again this is much slower than the Volume level SnapsRestore but you now also have to watch your snapshot growth and the restore will grow your snapshot size significantly.

Don't get me wrong, it's still a great option to have but you really have to discuss the different restore scenarios and implications with the customer and see which model is the best fit for them.

Choice is a good thing!

keith