4 Replies Latest reply: Oct 19, 2014 10:41 PM by Mark Harrison RSS

    SQL Backups and VM Quiescing

    Bill Blair Wayfarer

      IHAC with a SQL database on vmdk's.  Their OS drive is now on the same datastore as their DB and logs.  I noticed when a snapshot is called it quiesces the VM as well as the DB.  If the OS is moved off the DB datastore as BP tells us to do, when the snapshot is called for the volume containing the datastore with the DB only, will it only quiesce the DB and the VM?

       

      Thanks,

      Bill

        • Re: SQL Backups and VM quiescing question
          Mitch Gram Adventurer

          Bill, It sounds like your OS, DB, and log volumes are all mapped to the same VMware datastore which is mapped to a single volume on the Nimble Storage Array.     Then on the Nimble Storage array, you have a volume collection that is selected to have scheduled VMware integrated snapshots.   If this is the case, when the snapshot occurs, the Nimble Storage array will ask VMwarefor permission to take a snapshot which in turn will use VMware tools installed on your OS to leverage VSS to quiesce the data for the snapshot.   If you create a new volume on your Nimble Storage array, map it to a VMware datastore, and move your OS there, one of two things can happen.   If this new volume is added to the same Nimble Storage Volume Collection, its snapshot schedule will be the same as the DB and logs volume, and the behavior will be the same as it is today.   If you create a new volume collection for this OS volume and assign it a schedule, the OS and DB snapshots will be independent.   

            • Re: SQL Backups and VM Quiescing
              Mark Harrison Adventurer

              If the O\S, logs and DB are on different Datastores (Nimble Volumes) and you are using VMware integrated snapshots then regardless of the volume collection the VMware tools will invoke the VSS writers and quiesce everything that is VSS aware. That's my understanding.

              For our larger SQL DB's I use in guest iSCSI volumes and disable the SQLServerWriter in VM tools and use the Nimble initiated VSS synchronization to quiesce and snap the SQL DB and Log volume pair.

              Basically the VMware tools will see the all the disks and try to quiesce all the files even if you are only scheduling a snapshot of say the volume containing the O\S. Naturally the snapshot will only occur for the volume(s) in the collection.

                • Re: SQL Backups and VM Quiescing
                  Mark Harrison Adventurer

                  Hi Bill,

                            You should consider using separate datastore(s) for logs with No Cache since there is little benefit in caching write intensive logs. The only time any serious reading of logs is required is during a restore or roll back after corruption. We are in the on-going process of migrating SQL servers from NetApp to Nimble and the DB's and logs are on the same logical drive and it has a negative impact on Cache utilization. Our DBA's are working to separate these fortunately.

                   

                  Regards,

                                 Mark.

                  • Re: SQL Backups and VM Quiescing
                    Mark Harrison Adventurer

                    Perhaps I'm not quite correct about VMtools/Vcentre quiescing all disks based upon my understanding of the following extract from the Nimble VMware integration guide:- "

                    "vCenter communicates with the VMware VSS Services Support in VMware Tools to quiesce application

                    writes to LUNs that correspond to volumes associated with the volume collection. Snapshot creation is

                    then triggered, a VM application-consistent snapshot is created, and writes are unquiesced."

                    Having said that I have seen the SQL Server Writer being invoked on a VM with it's SQL DB and Logs on separate volumes (Datastores) even though those volumes were not in the Volume Collection.

                    Perhaps someone can give me a sanity check on this topic?