After the SQL instance is installed,
it is important to ensure your SQL databases are backed up properly.
Microsoft SQL 2008 makes this process easy to configure. Of course,
there are other third-party solutions that back up not only your
database instances but also everything else in your environment. SQL
supports a Simple or Full recovery model. A Simple recovery model does
not back up the logs, so recovery is limited to the last backup. A Full
recovery model includes the logs, so it allows you to recover the
database to a certain point in time, assuming the log is not damaged.
For a VMware vCenter environment, you have a
vCenter database, an Update Manager database (which is optional but
highly recommended), and also with VMware View, a View Composer and
Events database. Make sure that you create the database and also provide the permissions
necessary for connecting to the SQL database. The account requires
db_owner permissions to the vCenter and Update Manager database for the
installation. In addition, the account requires temporary db_owner
permissions to the MSDB System database for both vCenter and Update
Manager. The purpose is to ensure the installation can create SQL Agent
jobs for the vCenter statistic rollups, for example. The vCenter
statistic rollup jobs allow vCenter to purge data it is collecting to
populate the performance data within vCenter. The tables used to store
this data are as follows:
• VPX_HIST_STAT1—Stores integral values at the lowest level of granularity (daily level)
• VPX_HIST_STAT2—Weekly Stats Rollup Job, which repeats every 30 minutes, performing rollups at a weekly level.
• VPX_HIST_STAT3—Monthly Stats Rollup Job, which repeats once every two hours, performing rollups at a monthly level
• VPX_HIST_STAT4—Yearly Stats Rollup Job, which repeats twice a day, performing rollups at a yearly level.
It is best to install vCenter and configure
the VMware Update Manager before revoking the db_owner access to the
System databases.
The default installation of SQL assigns a
Simple recovery model. A Simple recovery model means that a
point-in-time backup is the only one supported. Data added or changed
between backups may be lost with a Simple recovery model. Changing the
type to Full recovery allows you to restore data up to the point of
recovery.
You can change the recovery model by
selecting the properties of the database and, on the Options, changing
the recovery model from Simple to Full, as shown in Figure 9.
Figure 9. Change the recovery model to Full.
Let’s step through the process required to
create the database and assign the appropriate permissions; then we
will review how to ensure the database is properly backed up. Create each database by opening the Microsoft SQL Management Studio and taking the following steps:
1. Connect to the SQL database instance on the SQL Server.
2. Right-click the Database Module and select a new database.
Ensure your database names are
indicative of what they will be used for—that is, vCenter, VMware
Update Manager (VUM), vComposer, and vEvents.
3. Expand the Security Module and add a new login.
The account should be the one that you
created so that you can connect and perform the installation. In this
case, we created a svc_SQL Account, as shown in Figure 10.
Figure 10. Choose the account that will be the db_owner.
Ensure the account is mapped to the
appropriate database and has the db_owner permission. To ensure the SQL
Agent jobs are created properly, db_owner permission is also required
for the MSDB database. After the installation is complete, this
permission should be revoked.
Figure 11 shows the three databases mapped to the db_owner role.
Figure 11. User mapping.
After you create the databases and have the
appropriate permissions, you should schedule the database backups if an
enterprise backup solution is not in place. Although most server
virtualization environments do have enterprise backup solutions in
place, due to the requirement of needing a second virtual server, this
is not always the case in virtual desktop environments. It is
recommended that you have a specific backup solution in place, but at a
minimum, you should set up backups. In most cases, a dedicated SQL
support team exists and has a defined backup process.
When you are looking at a backup strategy for
your vCenter and your virtual desktops, you should consider how
valuable the data is, how much the data is changing, the overall size
of the database, and how much the data is used. With vCenter, the
database is a configuration database to store metadata. As your
environment grows, however, the availability of the data and overall
service becomes increasingly critical.
When using SQL Server 2008, you have three primary backup types: full, differential, and log backups.
