Correct and proper configuration of SQL server with SMSQL

KKChan · ‎2017-07-14

HI all,

I have little experience and knowledge on SnapManager and need your advice for a correct and proper SQL configuration prepare and implement for my user. Current their SQL is running on a Windows 2012 VM. DB & log locate on different VMDK volumes. All system, DB & logs are under same DataStore.

From what I study on SnapManager for SQL installation and setup Guide, follow is the storage layout requirement that need to fullfil:

1. Individual LUN or VMDK for database files, logs, and SnapInfo directory

2. If use VMDK, DB files, logs and SnapInfo directory should locate on different DataStores

3. VSC backup cannot apply to VMDK hold DB, logs and SnapInfo directory

According to the requirement above, follow will be the storage layout to meet user environment (ESX with SAN connection to storage, VM SQL server)

1. 4 DataStore will be require for the environment, 1 use for SQL server VM contain windows server OS & SQL program. The other 3 DataStore will use for SQL DB, log & SnapInfo directory.

2. Current VSC can only use for System volume backup, while the DB & log volume will backup by SMSQL

Is my understanding correct? One question is can these DataStore locate in same volume? Can I create 1 aggregate have 1 big volume, then create 4 different LUNs for those 4 DataStore?

The second phase of the planning is SnapVault will be apply in the environment, backup will use SnapVault to another FAS storage. But when I study the installation & setup guide (page 27), it state that "Note: SnapVault support is not available for database residing on VMDK", is it means that if I plan to go for SnapVault protect, I can't use VMDK for DB & log? I can only use LUN in the configuration?

So the final configuration will be 1 DataStore hold the VMDK for system & SQL application. Three other LUNs will be create to use for DB, log & SnapInfo directory? The VM will schedule to have daily backup by VSC while DB, log and SnapInfo directory will backup by SMSQL?

Any advice will be welcome!

So if my understanding is correct, if go ahead for the SMSQL implement, I need to do the followin tasks:

1. create new LUNs (RAW not VMDK) for DB, logs & SnapInfo

2. migrate all the DB & log to new LUNs (downtime require)

3. setup SnapInfo directory on new LUN

4. create new LUNs in the destination FAS

5. create SnapVault relieatonship between source & destination LUN

6. configure SMSQL for backup setting

7. Test and confirm the backup and SnapVault image in destination storage

spinks · ‎2017-07-17

Not a direct answer for your question, but if you are looking at running Microsoft SQL in a VMware environment then you should really take a look at SnapCenter.

SnapCenter works much better with VMware environments and it is much new code that scales and performs better compared to SnapManager.

Hopefully someone else can chime in on the best practice for SQL in a VMware environment. I forwarded the thread to a couple people.

Thanks,

John

skneo · ‎2017-07-19

1. create new LUNs (RAW not VMDK) for DB, logs & SnapInfo

2. migrate all the DB & log to new LUNs (downtime require)

3. setup SnapInfo directory on new LUN

4. create new LUNs in the destination FAS

5. create SnapVault relieatonship between source & destination LUN

6. configure SMSQL for backup setting

7. Test and confirm the backup and SnapVault image in destination storage

As a simple way for snapvault we use only 2 Volumes for sql server

We have one volume for Datastore for SQL Server and Splitt System Windows, SQL Server Programm, SystemDB, TEMPDB, SnapInfo as seperate vmdk diks on this datastore.

User DBs an User Logs on extra Volume as RAW Lun.

Configure an SnapVault with the right policy and snapmirror labels for datastore volume and raw lun volume

Configure SnapManager

as result: UserDB ist snapvault as RAW LUN an can esay mount to any other server.

The snapinfo snapshot on volume level will automaticly transfer the rest of the sql server volume without use of VSC to secondary site and include all othe vmdks.

But your task list is right when you use 1 volume vmdk server 1 raw lun database and logs 1 raw lun for snapinfo as you has written.

KKChan · ‎2017-07-20

Hi Skeno,

Thanks for your reply. It really help to clear my mind. But I just have one more question, can I create the RAW volume for DB & log under the same aggregate volume which contain the system VMDK? Or I need to create another volume under the another aggregate that only use for RAW DB & Log? Current aggregate contain other LUN for another datastore and have been configure SnapVault (to another FAS) already.

Regards,

KK

skneo · ‎2017-07-21

I need to create another volume under the another aggregate that only use for RAW DB & Log?

Current aggregate contain other LUN for another datastore and have been configure SnapVault (to another FAS) already.

The SnapManager SQL Snapshots is on Volume Level.

The Aggregate has no matter except disk type and performance.

You should use one seperate Volume with LUN for User DB & LOG, for heavy load you can mount the user db raw lun with a seperate SCSI controller on vmware.

Another Singel Volume with vmfs Datastore for the Server if you will use snapinfo as vmdk drive, the snapinfo snapshot will create a volume snapshot from the whole datastore on every snapmanager backup.

Or you seperate the SnapInfo Disk as seperate Volume RAW LUN and must manual Backup the VM Datastore from the SQL Server.

if you have larger environments you can seperate more things as singel raw lun volume as example every user db / log or Temp db / log.

But if you fine with sql on vmdk now, i think it is no reason for make it complex for only backup and snapvault an db server.