Installing Microsoft SQL Server
Follow these steps to install Microsoft SQL Server:
Launch the installation. Click OK to have the SQL Server 2008 R2 setup enable the Microsoft .NET Framework, as shown in Figure 3.2.
Figure 3.2. Run the Microsoft SQL Server 2008 R2 Setup.
Select New Installation or Add Features to an Existing Installation, as shown in Figure 3.3.
Figure 3.3. Select New Installation.
After the installer verifies that your server meets the requirements (see Figure 3.4), click OK.
Figure 3.4. The Installer verifies the prerequisites.
Accept the licensing terms, as shown in Figure 3.5, and click Next. Click the check box if you want to help Microsoft further develop SQL by sending usage data. In most production environments, this option is not selected.
Figure 3.5. License terms.
Select the SQL features. The only features you need are the Database Engine Services and the Management Tools, as shown in Figure 3.6. After selecting the features, click Next.
Figure 3.6. Select Database Engine Services and Management Tools.
It is quite common to run into a deployment in which the SQL Server instance is already up and running, but the management tool has not been installed. Because the 2008 Management Tools are no longer available as a separate download, it is possible to use SQL Express Management Studio 2005. An even better solution is to have a ThinApp version of SQL Express Management Studio 2005 as part of your toolkit.
Set the SQL named instance (see Figure 3.7). Although using the default instance is fine, it is better if you provide a specific instance name and then click Next.
Figure 3.7. Name the SQL Instance.
Specify the SQL administrators (see Figure 3.8). After adding the appropriate SQL administrators, select Data Directories. Select Mixed Mode (SQL Server authentication and Windows authentication) if you intend to run all databases from one location. Although the vCenter database uses Windows authentication, the Event Database does not.
Figure 3.8. Select Mixed Mode.
Update the default locations for the databases and logs, as shown in Figure 3.9. Even if you are running the Windows Database Server as a VM, it is a good idea to separate the database and the logs on separate partitions. Separating the database and logs on separate partitions ensures that you can still manage the SQL Server in the event you run out of capacity on the volumes. If the SQL Server is virtual, you can separate different Virtual Machine Disks (VMDKs) on different storage tiers to more finely control IO.
Figure 3.9. Separate the database logs from the OS partition.
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. We discuss View Composer more in Chapter 6, “View Operations and Management.” 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 3.10.
Figure 3.10. 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:
- Connect to the SQL database instance on the SQL Server.
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.
- 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 3.11.
Figure 3.11. 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 3.12 shows the three databases mapped to the db_owner role.
Figure 3.12. 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. The steps provided in this book are not meant to supersede established backup practices and policies, but instead serve as a reference in case an option is needed or if additional understanding is required on SQL backups.
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.
A full backup copies all the information in the database. Full backups also include the transaction logs and any data that has not been written to the database. In a small virtualization environment, it is possible to run full backups for the vCenter database. When the environment grows beyond 20 ESXi hosts, the database can grow to 10–15 GB. In this case, a combination of full or differential backups might be necessary.
- Open the SQL Server Management Studio and connect to the SQL Server instance.
- Navigate to the Server\Databases folder.
- Right-click the database you want to back up.
- From the shortcut menu, select Tasks, Backup.
- In the Database Backup dialog box, select the type of backup you want the server to perform, the backup destination path, and the backup options.
- Click OK to back up the database or click the Script button if you want to generate a script to run the backup with the selected options.
You can also run backups from the SQL command line by performing the following:
- Browse to c:\Program Files\Microsoft SQL Server\100\Tools\Binn.
- Run SQLCMD. The 1> prompt tells you that you are connected to SQL Server instance 1.
Enter the backup command, as shown in Figure 3.13.
Figure 3.13. The BACKUP DATABASE command.
The command to do a full backup is BACKUP DATABASE [Name of database] TO DISK = N’[PATH]’. In this example, we typed
BACKUP DATABASE vCenter TO DISK = N'S:\Backup\vCenter_12282011.bak'
To execute the command, type go and press Enter. The backup should process successfully, as indicated in Figure 3.14.
Figure 3.14. A successful backup.
To set up reoccurring backups, you need to set up a maintenance plan under SQL and ensure that SQL Agent is started. If you are running a SQL Express Edition, you need to look at scheduling a SQLCMD command because maintenance plans are not available in the Express Edition.
After the SQL Agent starts, you can set the backups to happen according to a schedule. If you are not using a SQL Express Edition, you should see the Maintenance Plans module under Management, as shown in Figure 3.15.
Figure 3.15. Maintenance Plans module.
Create a Back Up Database task and set it up according to a reoccurring schedule, as shown in Figure 3.16.
Figure 3.16. Set a reoccurring schedule.
If you are using SQL Express, you can use the following process to automate the SQLCMD Backup command. First, you need to create a SQL script using the command you ran from the command line:
BACKUP DATABASE vCenter TO DISK = N'S:\Backup\vCenter.bak'
The file extension does not matter, but in this case save the database with a .bak extension so that it is easy to identify. Now you need to create a scheduled task to initiate the SQLCMD command and execute the SQL script. You also need to create a local ID under which the scheduled task can run with suitable privileges including the logon as batch job privilege. You can add a policy through the Active Directory (AD) by separating out your vCenter Server in a separate OU. You should do this through Active Directory policy, but you can configure this locally by doing the following:
- Navigate to Administrative Tools\Local Security Policy.
- Expand the Security Settings\Local Policies\User Rights Assignment.
- Add the account that will run the scheduled job to the Logon as Batch Job Properties and click OK.
When you are done, you can open the scheduler to create a basic task.
Open the scheduler on the SQL Express Server and create a basic task. Provide a descriptive name such as vCenter Backup job and a description of when the job occurs, as shown in Figure 3.17. Then click Next.
Figure 3.17. Create a Task.
Configure the trigger; in this case, set up the backup job to be triggered weekly (see Figure 3.18). Then click Next.
Figure 3.18. Configure a trigger (weekly).
Set the frequency you would like the backup to occur at (see Figure 3.19) and click Next. If you would like the backup to happen every two weeks, you can adjust the Recur setting from 1 to 2.
Figure 3.19. Determine the schedule and reoccurrence.
Set it to start the SQLCMD command with arguments. To do so, select Start a Program (see Figure 3.20). Then click Next.
Figure 3.20. Select Start a Program.
- Select the SQLCMD program and the argument as –i [Path to your SQL script], as shown in Figure 3.21.
Figure 3.21. Select SQLCMD as the program and your script as the arguments.
After you complete these steps, you need to adjust the properties a little for the job:
- Browse to the Task Scheduler Library and verify the reoccurring vCenter Database job appears in the right pane.
Select the task, right-click, and select the properties of the newly created batch job, as shown in Figure 3.22.
Figure 3.22. Right-click properties.
- Ensure Run Whether the User Is Logged On or Not is selected, as shown in Figure 3.23. Then select Change User or Group... and ensure the job is running under the proper credentials.
Figure 3.23. Select the user under which to run the task.
The preceding description is just a sample of how you can ensure you have regular full backups running if you have opted to run SQL Express. You might want to fine-tune your settings to keep several weeks’ worth of full backups and also to move them to a separate location.
If your database is getting too big for a full backup, you can perform a differential backup. A differential backup copies any changes made since the last full backup job. It is designed to reduce the time needed to perform a full backup. You can make your backup job a differential job by adding the WITH DIFFERENTIAL statement, as shown in Figure 3.24. In this case, your final backup strategy adds a combination of full and differential backups, so you must ensure you have access to all the backup files.
Figure 3.24. WITH DIFFERENTIAL command.
The third type of backup does not copy the changes; it copies only the transactional logs of the database. After the logs are copied, the portions of the log files not needed for active transactions are truncated. For regular maintenance, it is a good practice to back up your log files daily.
When you are happy with your scheduled job, you can quickly apply it to the remaining databases because the jobs are exportable to XML files from the Task Scheduler console. Simply export the job as an XML file, make some edits so that it can be applied to the other databases, and reimport it. In general, the VMware Update Manager View Composer or Event databases do not require the same frequency of backups as the vCenter database.