SMSQL using VMDK? proposed storage infraestructure using federated backups

[miguel_maldonado]

Hello all.

Now that SMSQL has released version 5.2, a new feature is available: federated backups. With this new feature, now we can backup multiple instances/serves in a single job, thus reducing the number of schedule jobs and the number of SnapShots SS. Furthermore, It`s been a while since SMSQL supports VMDK disks allowing us to use a NFS structure with VMware.

I have an idea that would like to run with you to see if you might find it possible to organize the space in such a way that we can save time programming multiple jobs, managing multiple DataStores (DS) and saving space by using less SS.

For this proposal I have read TRs user guide to get an idea of bests practices for designing a storage infrastructure for SQL:

215-06101_B0_user guide.pdf

TR-3696 Microsoft SQL Server Relational Engine_ Storage Fundamentals for NetApp Storage.pdf

tr-3768 best practice guide.pdf

TR-3821_ Best Practice Guide for Microsoft SQL Server on NetApp Storage.pdf

TR-3941_ SnapManager for SQL Deployment Guide (OCT11).pdf

tr-4003 best practices.pdf

So, here is the idea.

Basic designs:

- All NFS DS for VMware

- Multiple SQL servers with multiple instances each.

- Follow best practice in isolating different kinds of archives:

- 1 VOL/DS for user DDBB (Data Base)

     VMDK for each DDBB

- 1 VOL/DS for logs DDBB

     VMDKs for each DDBB

- 1 VOL/DS for SnapInfo   

- 1 VOL/DS for system DDDBB (model, MSDB and master)

- 1 VOL/DS for temp files

With this scenario I would have 5 more DS for each SQL server in my VMware infrastructure, with 30 SQL servers, this model would need 30*5 =150 Datastores to manage, an awful lot amount for administration purposes.

Instead of this, I would suggest separating each DS by its use, grouping several VM disks on each DS.

- Follow best practice in isolating different kinds of archives:

- 1 VOL/DS for user DDBB (Data Base)

     VMDKs for each DDBB

- 1 VOL/DS for logs DDBB

     VMDKs for each DDBB

- 1 VOL/DS for SnapInfo

     VMDKs for each DDBB

- 1 VOL/DS for system DDDBB (model, MSDB and master)

     VMDKs for each DDBB

- 1 VOL/DS for temp files

     VMDKs for each DDBB

With this solution, and with Federated backups present in SMSQL, all MVs residing on the same DS, would have the same SS taken at the same time, kind of like what goes on when VSC launches a VMware SS.

What do you think about this approach?

[abhisek]

Hi Miguel ,

Please also have a look into TR 3785.

Regards,

Abhishek

[l_mitchell]

HI Miguel,

How has it gone, it would be interesting from a real world perspective how its working protecting / restoring db's inside vmdks.

Do you still set it out the same way in terms of using mount points, to keep it logical.

Any pros / cons in keeping it as vmdk in your opinion, as so far I've only ever personally used rdms?

If putting multiple sql server db vmdks into a single volume, and multipe server log vmdks into a different vol / nfs datastore, and using the federated backup feature it would be nice to hear what you think, and would you have done anything differently in hindsight.

Thanks

[miguel_maldonado]

Hi

We are still on the development phase of the project, and working around these ideas. When we get our hands down to it, I will inform about our experience.

Thank you

[JustinMedcraft]

I am keen to look into the option of using VMDKs as opposed to RDMs for SMSQL.  How does SnapDrive see the VMDKs?

[CASTROJSEC]

We use VMDK's on our SQL installs.  You need the VSC installed to do this.  set snapdrive to point to the VSC and the virtual center and you will see all your vmdk's and any RDM you use.  We break our disks down like this.

Each server has several DB disk and corresponding TLog disk.  each a separate LUN in its own volume.

DB01 and Db01-Tlogs

DB02 and Db02-Tlogs

SnapInfo

DefaultDB

TempDB

SystemDB

etc.

This works great (except when the powers that be decide to do some patching).

We also have a separate SQL server fpor verifiations.