SQL Server 2008 backups for VMware databases

At a minimum, you’ll want to perform regular backups of your vCenter, Update Manager, and System databases. You don’t have to be a DBA to perform simple backups. You don’t need to know T-SQL or database programming to perform these steps. There’s an easy wizard that walks you through a standard Windows Next-Next-Finish set up.

There are a couple things to note in the walkthrough below. We’re using SQL Server 2008 Enterprise Edition 64-bit on a 64-bit Windows Server 2008 SP2 Enterprise Edition. The SQL server is also a virtual machine in a vSphere 4.1 environment.

Although not seen in the screenshots, there are two user databases – VCDB for vCenter and VUM for VMware Update Manager. The system databases, MSDB and Master, should be backed up, as well. Note that the Master database can only have full backups – not differential or incremental. Also, we chose to use a Full-Differential backup scheme. We’re not using any incremental. That’s not to say you can’t choose a Full-Incremental scheme or something else. It’s just personal preference.

There are good sites you can reference with regard to backing up your VMware databases. They include Bob Plankers’ VMware-centric The Lone Sysadmin blog post over at http://lonesysadmin.net/2010/10/21/how-to-install-sql-server-vmware-vcenter/ and a SQL DBA’s perspective on rebuilding versus reorganizing indexes at http://realworlddba.wordpress.com/2008/02/04/index-rebuild-versus-reorganize-whats-the-difference/. I’m thinking that one option could be reorganizing indexes before the differential backups and then rebuilding during the full backups. If reorganizing and rebuilding isn’t a daily task for such small databases (depending on the size of your virtual environment), perhaps separate maintenance plans for these tasks can be created with their own schedules – maybe every couple weeks or once a month, again depending on the frequency of changes in your databases.

Also on the Lone Sysadmin blog, there’s a download link for VMware’s own vCenter database sizing calculator. Here it is: http://www.vmware.com/support/vsphere4/doc/vsp_4x_db_calculator.xls.

Note that we’re backing up to a VM’s virtual disk. For better protection of your backups, you may want to get those backups off of the virtual disk and store them in a second place, whether virtual or not, for disaster recovery purposes.

In the end, I have several maintenance plans. The backups are staggered so compute resources used during the backup process are minimized. The backup times are late at night when minimal changes are taking place. Backups are saved in separate directories for clarity. Logs are saved in their own directory. All user databases are set to use a full recovery model, as well. This enables us to back up transaction logs several times a day which will enable a point in time recovery. We chose to backup transaction logs every 8 hours. If needed, you can change this to suit your needs. So with a full, differential, and transaction log replay, you can recover your VMware databases to within 8 hours of the loss. A look at my final maintenance plan strategy is below.

1. Log in with a user that has permissions to create maintenance plans for your databases
2. Expand your SQL Server > SQL Server Agent, right click Operators > New Operator…
   1. Fill in the Name: and E-mail name: sections and select OK

1. Expand your SQL Server > Management, right click Database Mail > Configure Database Mail
   1. Check box to skip this page in the future > Next
   2. Leave default, Set up Database Mail by performing the following tasks: > Next
   3. Enter profile name, select Add…
   4. Enter Account Name. Under Outgoing Mail Server (SMTP), enter a destination email address and display name, something like SystemsAdministrators@company.com and Systems Administrators. Also include the hostname of the Exchange server from which you want to send SQL Server emails. Ensure Anonymous Authentication is selected for SMTP Authentication and select OK.

   5. Select Next.
   6. Under Manage Profile Security, change the default public profile from No to Yes and select Next.

   7. Simply Next, Next, Finish all the way through. Hopefully all is green during the configuration. Close the window when done.
2. Create a database backup maintenance plan using the wizard.
   1. Expand your SQL Server > Management, right click Maintenance Plans and choose Maintenance Plan Wizard.
   2. Give the maintenance plan a name like, “VCDB Backup Plan – Full.” On the same window, choose to change the schedule of the maintenance plan. Leave all the defaults except change the time of the backup to happen at whatever time you chose. There are going to be several backups scheduled and it’s best to stagger them. For instance, make up a plan for backing up the vCenter, Update Manager, and System databases at different times throughout the week so you don’t have simultaneous backups running. They’ll probably be fairly small anyways, but you’ll have to adjust based on the size of your environment and frequency of database changes. Select OK when you’re done. Note that the system Master database can only have full backups – differential or incremental backups are not supported for the Master database. Because of this, you may need to separate your MSDB and Master Database full backups into different maintenance plans.

1. Select Next.
2. Select the following maintenance tasks:
   1. Check Database Integrity
   2. Shrink Database
   3. Rebuild Index
   4. Backup Database (Full) or whichever one you’re working on
   5. Maintenance Cleanup Task
   6. Click Next when done
3. Click Next on the Select Maintenance Task Order page
4. When asked to select a database, choose the appropriate one from the drop down.

5. When backing up your System databases, unless you need the Northwind sample database, don’t select the “System databases” option. Instead, choose “These databases:” and manually select ‘master’ and ‘msdb.’ This way, you won’t waste space, time, and compute resources backing up the sample database. An example is below.

6. Leave the defaults on the following pages when choosing your databases.
7. When asked to Define Back Up Database task, choose the appropriate database. The rest of the defaults are generally good, but I chose to place the backups in a user-defined location. My directory structure looks like this:
   1. D:\DatabaseBackups
      1. System
         1. Full
         2. Differential
         3. MaintenancePlanLogs
      2. VCDB
         1. Full
         2. Differential
         3. MaintenancePlanLogs
         4. TransactionLogs
      3. VUM
         1. Full
         2. Differential
         3. MaintenancePlanLogs
         4. TransactionLogs
   2. Note that you cannot backup the transaction logs on system databases (except the sample Northwinds database)
   3. When you create the System database backups, you can tell the task to create subfolders for each database. During the first backup, two folders will be created underneath D:\DatabaseBackups\System, something like D:\DatabaseBackups\System\master and D:\DatabaseBackups\System\msdb.
   4. Browse to the directory location you wish to save your backups, separating full from differential from transaction logs for each database.
   5. By default, the Verify backup integrity check box is not selected. Go ahead and check it so your backups will be verified.

8. When defining the maintenance cleanup task, browse to the same directory to which you chose to save your backups. Then choose to save 2 weeks’ worth of backups, or however much you feel comfortable with. Ensure the file extension stays populated when you’re finished browsing to a directory. If the ‘bak’ file extension has disappeared, simply type it in. Note that your transaction logs will have an extension ‘trn’ – not ‘bak’
9. On the reporting options window, browse to your MaintenancePlanLogs folder. Check the box next to E-mail report and select your Systems Administrators.

10. Choose Next, Finish, and Close when you see all green.

1. Test your maintenance plan backup by right clicking your maintenance plan and selecting Execute. If all’s well, you’ll get a Succeed message.
2. If you have to troubleshoot email messages from SQL Server, you can check a couple of things.
   1. To view that status of each sub-task within the Maintenance Plan, including the final email of the success or failure of the maintenance plan, expand SQL Server Logs, right click Database Mail and choose View Database Mail Log. By default, the Database Mail logs are selected. Simply deselect the top-most Database Mail check box and select Maintenance Plans. You should see a list of the maintenance plans that have been run. Expand some of these, or the latest one, if that’s what you’re troubleshooting, and look for a big red X, indicating a failure. An email problem would be one of the last steps in the maintenance plan. An example is below. The text of the error you could see if you’re having email problems is:

      “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”

      A quick google came up with the solution. The SQL Server Operator you specified was not configured as the default public profile. Right click Database Mail and choose Manage Profile Security. Ensure the Database Mail profile you want is set to the Default Profile.

      As you test your maintenance plans, see what’s populating in your backup directory structure. Look for the .bak files and log files. Look for emails to be sent, as well. Depending on how often you back up your databases, you may want to set up a rule in Outlook to send SQL Server emails to their own folder. I’d also suggest sending SQL Server emails to a Systems Administrator or DBA group mailbox instead of a distribution group, then setup the group mailbox with rules and folders specifically for SQL Server messages.

Let me know if you have any problems with this or if you find a better way to do things.

Google