Home > Articles > Operating Systems, Server

This chapter is from the book

Installing Microsoft SQL Server

Follow these steps to install Microsoft SQL Server:

  1. 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

    Figure 3.2. Run the Microsoft SQL Server 2008 R2 Setup.

  2. Select New Installation or Add Features to an Existing Installation, as shown in Figure 3.3.

    Figure 3.3

    Figure 3.3. Select New Installation.

  3. After the installer verifies that your server meets the requirements (see Figure 3.4), click OK.

    Figure 3.4

    Figure 3.4. The Installer verifies the prerequisites.

  4. 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

    Figure 3.5. License terms.

  5. 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

    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.

  6. 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

    Figure 3.7. Name the SQL Instance.

  7. 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

    Figure 3.8. Select Mixed Mode.

  8. 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

    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

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:

  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 3.11.

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

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.

Full Backup

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.

  1. Open the SQL Server Management Studio and connect to the SQL Server instance.
  2. Navigate to the Server\Databases folder.
  3. Right-click the database you want to back up.
  4. From the shortcut menu, select Tasks, Backup.
  5. 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.
  6. 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:

  1. Browse to c:\Program Files\Microsoft SQL Server\100\Tools\Binn.
  2. Run SQLCMD. The 1> prompt tells you that you are connected to SQL Server instance 1.
  3. Enter the backup command, as shown in Figure 3.13.

    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'
  4. To execute the command, type go and press Enter. The backup should process successfully, as indicated in Figure 3.14.

    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

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

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:

  1. Navigate to Administrative Tools\Local Security Policy.
  2. Expand the Security Settings\Local Policies\User Rights Assignment.
  3. 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.

  1. 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

    Figure 3.17. Create a Task.

  2. 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

    Figure 3.18. Configure a trigger (weekly).

  3. 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

    Figure 3.19. Determine the schedule and reoccurrence.

  4. 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

    Figure 3.20. Select Start a Program.

  5. Select the SQLCMD program and the argument as –i [Path to your SQL script], as shown in Figure 3.21.
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:

  1. Browse to the Task Scheduler Library and verify the reoccurring vCenter Database job appears in the right pane.
  2. Select the task, right-click, and select the properties of the newly created batch job, as shown in Figure 3.22.

    Figure 3.22

    Figure 3.22. Right-click properties.

  3. 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

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.

Differential

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

Figure 3.24. WITH DIFFERENTIAL command.

Log Backups

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020