Sample Document for Database Instance Migration Using Log Shipping by izw13825


Sample Document for Database Instance Migration Using Log Shipping document sample

More Info
									Hyper-V Cloud Practice Builder
Onboarding SQL Server Environments
The Hyper-V Cloud
Practice Builder from
Microsoft can help
                               This guide outlines the major considerations that must be taken into
Microsoft partners by:
                               account when onboarding Microsoft® SQL Server® environments into a
  Accelerating practice
   development by
                               private cloud infrastructure.
   recommended practices       There is a strong trend in IT to virtualize servers whenever possible, driven
   for planning and            by:
   delivering a Private
   Cloud solution based            1. Standardization
   on Microsoft                    2. Manageability
   Virtualization                  3. IT agility and efficiency
                                   4. Consolidating servers reduces hardware, energy, and datacenter
  Reducing training costs            space utilization costs
   by providing                    5. Virtualized environments allow new Disaster Recovery strategies
   methodologies for
   delivering Server           The Hyper-V™ role in Windows Server® 2008 R2 provides a robust and cost-
   Virtualization scenarios.   effective virtualization foundation to deliver these scenarios.
  Lowering risk by            However, there is significant risk in virtualizing SQL Server environments
   providing real-world
                               without giving careful consideration to the workloads being virtualized and
   examples of problems
   and solutions
                               the requirements of the server applications running on a Hyper-V
   encountered by              environment.
   Microsoft architects and
   consultants.                This Guide is part of the Hyper-V Cloud Practice Builder that is based on the
                               framework that Microsoft Consulting Services has utilized to deliver Server
The Hyper-V Cloud              Virtualization for several years in over 82 countries.
Practice Builder is for:
  Consulting Services
   management, sales,
   and delivery roles.

  Software and Hardware
   vendors looking to
   understand how a
   Private Cloud is

  Licensing and resellers
   interested in sizing and
   scoping Private Cloud

Onboarding SQL Server Environments                             4
Conducting a SQL Server Inventory with the MAP Toolkit         4
   Collecting Performance Data with the MAP Tool               6
Analyzing Performance Data and Determining the SQL Server
Onboarding Scope                                         7
   SQL Server Consolidation Planning                           8
   Support Policy for SQL Server in Virtualized Environments   8
   Storage Considerations                                      10
   Networking Considerations                                   12
Determining High Availability, Security and Disaster Recovery
Requirements                                                 13
   High Availability with Failover Clustering                  13
   High Availability with Guest Clustering Using iSCSI         14
   Combining High Availability Alternatives                    15
   Security and Isolation Strategies                           15
   Disaster Recovery                                           16
Determining SQL Server Versions and Editions                   17
Defining Private Cloud Onboarding Strategy                     17
   Physical-to-Virtual Conversions                             18
   New Virtualized SQL Server Instance                         19
Monitoring                                                     19
   Performance Health Metrics on SQL Server Guest Machines 19
   Performance Health Metrics on the Physical Host             20
Additional Resources                                           21

Onboarding SQL Server Environments
In general, the recommended approach to server consolidation will follow
the delivery model defined in the flowchart below, where steps 1 to 5 are
specific to SQL Server environments and steps 6 to 8 are Private Cloud
related tasks:


        Step 1:                                                  Step 3:
                                    Step 2:
  Conduct SQL Server                                         Determine High
                               Determine the SQL
    Inventory and                                          Availability, Security
                               Server Onboarding
     Performance                                          and Disaster Recovery
      Collection                                              Requirements

                                                                 Step 6:
        Step 4:                     Step 5:
                                                           Check Private Cloud
 Determine SQL Server         Define Private Could
 Versions and Editions        Onboarding Strategy

        Step 7:
   Provision Virtual                Step 8:
   Machines for SQL           Execute Onboarding             End
  Server Environment               Strategy

Conducting a SQL Server Inventory with the MAP
The existing SQL Server environment must be assessed to gain a
comprehensive understanding of the SQL Server computers, instances and
databases. The analysis of this information will determine the appropriate
scope for virtualization in a Private Cloud, as it presents opportunities, but
also constraints and restrictions. It is important to collect instance names,
computer names, the SQL Server components installed, the SQL Server
version and edition in the inventory for further analysis.

The use of the Microsoft Assessment and Planning (MAP) Toolkit, a free
Solution Accelerator, provides a powerful foundation for conducting
inventories, assessments, and includes reports which can expedite the SQL
Server onboarding process. The MAP tool securely runs within your
network infrastructure without requiring the installation of agent software
on any SQL Server computer. Once the inventory is completed, the MAP
toolkit generates a series of reports and graphs summarizing the results of
the assessment. The MAP Solution Accelerator Toolkit can be downloaded
from Microsoft's TechNet website:

MAP 5.0 supports SQL Server 2008 R2, 2008, 2005 and 2000 with the SQL
Server Consolidation Report feature.

The database inventory engine in MAP 5.0 discovers SQL Server instances in
the network. It gathers and pulls database information together and
presents a SQL consolidation report of your current SQL Server

After selecting the SQL Server scenario, you can specify SQL Server names
manually or rely on the automatic discovery mechanism of MAP to discover
existing SQL Server instances. With the SQL Server instances discovered, the
MAP SQL Collector then collects SQL Server database information. The SQL
Server database information is collected through two mechanisms:
    1. Establishing database connection via the SQL Server ADO.NET
    2. Through the SQL Windows® Management Instrumentation (WMI)
         provider. To establish a database connection, you can specify SQL
         or/and Windows authentication. The same Windows authentication
         can be applied for retrieving data from the SQL WMI provider or a
         different authentication can be provided separately.

The SQL Server database information collected through database
connection includes information collected through SQL Server system
stored procedures and system catalogs, for example, product version,
database name, database users, database role, server-wide configurations,
and last backup date. The information collected through SQL WMI provider

includes performance counter information, for example, data file and log
file size and log file used size. The SQL Consolidation report provides a
combined view of the data collected.

Collecting Performance Data with the MAP Tool
For the purposes of this analysis, use the following reports to help identify
the opportunities for onboarding SQL Server workloads into a Private Cloud:
     SQL Server Consolidation Report
     Performance Metrics Analysis
     Server Virtualization Recommendations

To begin the assessment, from the actions pane of the application, click
Select a Database. This will prompt for the name of a new database
repository where the analysis results will be kept. Next, create a text file
that contains the machine names that will be a part of the analysis. The file
should be a plain text file with each machine on a separate line. When
completed, select Capture performance metrics from the Actions pane. The
wizard will prompt the user to open a file that contains the listing of
machine names, created in the previous step. Open the file, and continue
with the dialog prompts. It is recommended that duration of the analysis be
for at least 24 hours to capture a representative sample of the workload
being performed on the targeted machine. It is important to take into
consideration when the analysis is performed as workloads may be
significantly different during certain times of the week, month or year
based on business requirements.

The final collection step requires the engineer or administrator to identify
the target hardware that will serve as host for the new guest SQL Server
machines. From the center pane, select the link titled “Prepare
recommendations for server consolidation using Windows Server 2008
Hyper-V or Virtual Server 2005 R2”. The subsequent pages of the wizard will
prompt for base configuration (CPU, network speed, disk space and speed)
of the targeted host machine. Based on the information provided, the MAP
tool will develop recommendations based upon the performance baselines
taken in the previous step.

To generate the reports and proposals, based upon the data that has been
collected, from the Actions pane, select Prepare new reports and proposals.
In the wizard, verify the following reports and proposals are checked:

       SQL Server Consolidation Report
       Performance Metrics Report
       Server Consolidation and Virtualization Recommendation Report

Depending on the number of servers analyzed, the generation of the
reports may take several minutes to complete. When finished, the reports
will be located in %userprofile%\documents\map\Repository where
Repository is the name of the database specified when the analysis was

Analyzing Performance Data and Determining the
SQL Server Onboarding Scope
When the reports have completed generating, the analysis phase can begin.
The first report will provide insight into the SQL Server servers / instances
that were analyzed. This report provides a summary level of information
regarding the versions and editions of SQL Server that were found during
the scan. In addition, it provides insight into the different features of SQL
Server that are installed, such as Reporting Services or Analysis Services.

                 Figure 1 – SQL Server Consolidation Report

The second report that is generated is the Performance Metric Results
report. This report details the Processor, Network and Disk utilization
recorded over the duration of the analysis. These key pieces of data will
enable the engineer to verify that when the workload is virtualized, it is

placed on a host that supports the demands of the application.
The final report that is generated as a part of the analysis is the Server
Virtualization Proposal and Recommendations Report. The proposal
document first details the targeted host information. This is extremely
helpful as hardware may change and it is always best to detail the
assumptions used at the time of analysis. The next section of the proposal
details each proposed virtualization candidate and aggregates each of the
performance characteristics. This shows how each virtual guest will
consume the resources of the VM host.

An important item to take into consideration is the analysis is a static
snapshot of the environment. As workloads run within the virtualized
environment, it is critical to monitor both the guest systems and host
machine to verify adequate end user performance is achieved.

SQL Server Consolidation Planning
The SQL Server Consolidation Guidance is an MSDN® article that provides a
framework for choosing among virtualization, multi-database, and multi-
instance consolidation strategies for SQL Server Database Engine OLTP
applications by highlighting some of the key decision points based on
technical analysis.

The Microsoft Consolidation Planning Tool for SQL Server (CPT) is a
Microsoft Office Excel® Add-in that can help customers identify which
source servers can be consolidated onto a given set of destination servers
based on major resource consumption such as CPU and memory. The Excel
spreadsheet information is obtained from the performance data collected
by the MAP tool.

Support Policy for SQL Server in Virtualized
The official support policy for SQL Server products that are running in a
hardware virtualization environment can be referenced at

Microsoft provides technical support for SQL Server 2005, SQL Server 2008,
and SQL Server 2008 R2 (this includes all components that that are included

with SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2) for the
following supported hardware virtualization environments:
      Windows Server 2008 and Windows Server 2008 R2 with Hyper-V
      Microsoft Hyper-V Server 2008 and Hyper-V Server 2008 R2

The following restrictions and constraints may affect the support policy of
the above supported configurations:
     Guest Failover Clustering is supported for SQL Server 2005, SQL
        Server 2008, and SQL Server 2008 R2 in virtual machines when the
        shared storage is provided by an iSCSI interface and provided all of
        the following requirements are met:
            o The Operating System running in the virtual machine (the
                 “Guest Operating System”) is Windows Server 2008 or
            o The virtualization environment meets the requirements of
                 Windows 2008 Failover Clustering, as documented in this
                 Microsoft Knowledge Base article:
        (The Microsoft
                 Support Policy for Windows Server 2008 Failover Clusters)
     The SQL Server product must be a supported version under its
        current Microsoft Support Lifecycle policy according to
     Virtualization Snapshots for Hyper-V are not supported to use with
        SQL Server in a virtual machine. It is possible that you may not
        encounter any problems when using snapshots and SQL Server, but
        Microsoft will not provide technical support to SQL Server
        customers for a virtual machine that was restored from a snapshot.
     Live Migration is supported for SQL Server 2005, SQL Server 2008,
        and SQL Server 2008 R2 when using Windows Server 2008 R2 with
        Hyper-V or Hyper-V Server 2008 R2

When planning the Private Cloud onboarding strategy for SQL Server
workloads, it is beneficial to understand the maximum configurations with
Windows Server 2008 R2 Hyper-V.

Each virtual machine, also known as a guest operating system, can support
up to 4 virtual processors and 64 GB of memory.

The physical servers running Hyper-V in Windows Server 2008 R2 can have
the following maximums: 64 logical processors, 8 virtual processors per
logical processor, 512 virtual processors per server, 384 operating virtual

machines on a server, and up to 1 TB of memory. Therefore, when placing
multiple SQL Server virtual machines within a single Hyper-V host, you must
verify the total SQL Server workload fits within these boundaries.

Storage Considerations
Storage is a critical component to the SQL Server architecture. Database
servers could be heavily I/O bound because of database read and write
activity and transaction log processing. Additional SQL Server services can
also impact host I/O and CPU utilization.

The Hyper-V supported storage devices are Direct Attached Storage (DAS),
Fibre-Channel Storage Area Network (SAN) and iSCSI SAN.

A maximum of 4 Virtual IDE disks are supported on each machine. The boot
disk, commonly referred to as the startup disk must be attached to an IDE
device and a virtual machine. Either a virtual hard disk or a physical hard
disk can be used. A maximum of 256 SCSI disks are supported on each
virtual machine. This is possible because each virtual machine can support a
maximum of 4 virtual SCSI controllers and each controller can support a
total of 64 disks. Each Virtual hard disk can support up to 2040 GB. Virtual
SCSI disks are recommended over virtual IDE disks when implementing SQL
Server virtual machines. However, for SQL Server workloads, pass-through
disks provide the best performance from a disk perspective. Each SQL
Server workload placed in a virtual machine should comply with the

For a virtualized SQL server, these are the recommendations for the storage
     Separate LUNs or spindles for the Host Operating System, guest OS
        VHDs and SQL data, as shown Figure 2 below.

                      Figure 2 - Separate LUNs

   LUNs must provide data protection using RAID 1, RAID 5 or RAID 10.
   The guest OS must reside on a fixed size VHD.
        o Due to Hyper-V requirements, even if Hyper-V host failover
            cluster failover will not be used, a save state file (.vsv) must
            be provisioned. Therefore, the minimum size must be at
            least 15GB+ the VM memory size.
   SQL storage must be configured as Fixed VHD, SCSI pass-through or
        o SCSI pass-though is preferred for storing databases and log
                  On SQL servers, it is not recommended to use VHDs
                     near the 2040 GB maximum VHD size because of
                     the management overhead for managing large files.
                     Pass-through disks must be used in this scenario.
                  If Fixed VHDs are chosen for SQL storage, 250 GB is
                     the maximum recommended VHD size. A practical
                     and manageable size for a VHD is 100 GB.
        o Databases and logs must be on separate LUNs and separate
        o Fibre channel/SCSI HBAs require access to the hardware
            layer on the operating system and the LUNs must be
            configured on the host OS. The LUNs will be presented to

                the guest OS as VHDs or pass-through disks.
            o   When using iSCSI, the iSCSI initiator can be configured to
                the host or to the guest machines.
                     The best iSCSI performance occurs when the
                        initiator is configured to the Host Partition and
                        disks are presented to the guest Operating System
                        as pass-through disks. However, the pass-through
                        disks limit the portability of Virtual Machines to
                        other hosts, because the VM will be tied to the host
                        storage configuration.
                     If the iSCSI initiator is configured to the guest
                        Operating System, there will be a significant
                        performance impact but this configuration allows
                        more flexibility because the Virtual Machines can
                        be more easily moved to another host.
                     A separate iSCSI Gigabit network is recommended
                        when using iSCSI storage. The best performance is
                        achieved with a dedicated NIC with jumbo frame
                        configuration and no Virtual Network Switch.

Networking Considerations
It is recommended for each host server to have a dedicated management
NIC for each Hyper-V host.

When using database mirroring, it is recommended that the principal and
mirror virtual nodes have one additional dedicated link used for the
transport of the synchronization data.

For the configuration of virtual NICs, the Synthetic network adapters must
be used instead of the Legacy network adapters, due to overall better
performance (Legacy NICs are emulated devices).

Assuming that the host can handle the aggregate bandwidth of all guest
machines, the networking design must be as simple as possible, as shown in
Figure 3 below:

            Figure 3 – Network diagram for Virtualized SQL Server

Determining High Availability, Security and
Disaster Recovery Requirements
Determining your high availability and security requirements are very
important as they will influence the underlying Private Cloud infrastructure.

High Availability with Failover Clustering
A common SQL Server high availability strategy employed by many
organizations is Hyper-V Failover Clustering. A Failover Cluster is a group of
independent servers that work together to increase the availability of
applications and services. The clustered servers (called nodes) are
connected by physical cables and by software. If one of the cluster nodes
fails, another node begins to provide service (a process known as failover).
The SQL Server virtual machines are redistributed within the cluster in case
of a physical host failure.

Failover Clusters also provide Live Migration for planned migrations of
virtual machines within a cluster. With Live Migration, running SQL Server
virtual machines can be moved from one Hyper-V physical host to another
Hyper-V physical host without service disruption or downtime. This is
displayed in figure 4. Live migration is integrated with Windows Server 2008
R2 Hyper-V and Microsoft Hyper-V Server 2008 R2 and requires the failover

clustering feature to be added and configured on the servers running
Hyper-V. By utilizing Live Migration, architects and database administrators
can continue to verify service continuity and maintain their SQL Server
virtualization high availability objectives even during planned maintenance.

          Figure 4 – Achieving High Availability with Live Migration

High Availability with Guest Clustering Using iSCSI
Another SQL Server high availability option involves guest clustering using
iSCSI. Guest failover clustering, as illustrated in Figure 5, makes the
provisioning of clusters possible in a virtual environment. A guest failover
cluster is simply SQL Server failover clusters inside virtual machines with
nodes operating as virtual machines. SQL Server 2005, SQL Server 2008 and
SQL Server 2008 R2 support guest failover clustering. The shared storage
required to build a cluster is connected to each virtual machine by means of
iSCSI. SQL Server guest cluster nodes can all run on the same physical host
machine or on different physical host machines. If SQL Server guest
clustering is running on the same host, business operations and high-
availability will be compromised if the host becomes unavailable. Therefore,
consider running the active node and standby node of a SQL Server guest
cluster on different physical host machines to maintain high availability and
reduce interruptions in business operations with your SQL Server virtual


        Figure 5 – Achieving High Availability with Guest Clustering

Combining High Availability Alternatives
Database Mirroring, Log Shipping or Peer-to-Peer replication can be utilized
in conjunction with Live Migration or guest clustering using iSCSI. By
combining the high availability alternatives you can maximize availability,
business continuity, disaster recovery, and manageability of your SQL
Server virtualized environment. For example, if you implemented a SQL
Server virtual machine on a guest cluster in a primary Datacenter, and
another SQL Server virtual machine on a guest cluster in a redundant site,
you can implement Database Mirroring or Log Shipping as a disaster
recovery mechanism to verify business continuity between the SQL Server
virtual machines in the two different locations. Moreover, you may
combine high availability with virtualization by implementing a worldwide
peer-to-peer replication topology where every single SQL Server instance
partaking in the replication topology is virtualized.

Security and Isolation Strategies
Understanding the security and isolation requirements can not only
influence where you store your virtual machines, but can influence whether

or not SQL Server computers, instances, and databases can be consolidated.
For example, strict security requirements may be in place for the Human
Resources department to verify their databases are isolated from other
production databases. These types of databases may require isolation as
they are governed by a specific regulatory compliance. Finally, the TempDB
database may be a performance bottleneck if you consolidate too many
databases onto a single virtual instance of SQL Server.

To achieve complete application and operating system isolation when
virtualizing SQL Server, you can maintain different versions and editions of
SQL Server within the same physical Hyper-V host. For example, you can
virtualize SQL Server 2008 R2, SQL Server 2008, SQL Server 2005 and SQL
Server 2000 on separate virtual machines running on the same Hyper-V
host. If needed, you can also maintain different editions such as 32-bit
editions and 64-bit editions of the application and operating system.

Disaster Recovery
As with any physical implementation of SQL Server, disaster recovery must
be a component of the design from the beginning. Based on the application
requirements, various means of recovering from a disaster can be
accomplished, with various levels of administrative actions required during
the failover process.

Based on the availability requirements for the system, log shipping,
replication, and mirroring are all options that may be employed when
designing a disaster recovery solution. In addition, it is critical to verify that
the SQL Server has maintenance plans defined to back up both system and
user databases on a frequent basis.

The Hyper-V Volume Shadow Copy Service (VSS) Writer allows for the
backup of Virtual Machines and their state information but it is not
integrated with the SQL Server VSS Writer under all supported storage
scenarios. Therefore, it is not recommended to back up the Virtual
Machines VHD files, but to perform a SQL Server aware snapshot from the
guest OS. Any backup software that supports a SQL Server-aware software-
based VSS solution (for example, Microsoft System Center Data Protection
Manager) can be used.

The current implementation of VSS in Hyper-V does not support host-based
backups for pass-through disks or iSCSI disks that are connected to an iSCSI
initiator inside the guest virtual machine. As a result, VSS backups of a SQL
Server guest virtual machine that are performed from within the host
machine are not supported in these storage scenarios. Therefore, hardware
VSS or VDS solution are not able to back up SQL Server in a Hyper-V


Apart from the limitations described above, recommended SQL Server
disaster recovery procedures for physical environments should be enforced.

Determining SQL Server Versions and Editions
The SQL Server version or edition you choose for your SQL Server virtual
machines is based on numerous conditions including whether or not you
plan to use the Physical-to-Virtual (P2V) tool, guest clustering or
consolidate. For example, if your plans include the use of the P2V
conversion tool then you will migrate your physical servers to virtual servers
"as is". As such, the version and edition of your SQL Server after conversion
remains the same. On-the-other-hand, if you plan on deploying a brand
new SQL Server virtual machine from scratch, you will have to determine
the SQL Server version and Operating System you will use.

Your decision can also be impacted by the SQL Server features or
components you plan on deploying. If your plan involves Guest Clustering
then the Enterprise Edition of Windows Server 2008 R2 is required.
Likewise, if you plan on managing more than 25 instances of SQL Server
with a SQL Server Utility Control Point then the Datacenter edition of SQL
Server 2008 R2 is required.

It is beneficial to review the following links to fully understand which
version and edition of SQL Server you require based on the features you
plan on implementing:
       Overview of Editions (SQL Server 2008 R2 Editions and Components)
       Features Supported by the Editions of SQL Server 2008 R2
       Editions and Components of SQL Server 2008 R2
       Features Supported by the Editions of SQL Server 2008 R2

Defining Private Cloud Onboarding Strategy
Three strategies are available when migrating your SQL Server environment
from physical servers to virtual servers.

The first option is using a conversion tool to execute operations "as is”.

The second option is deploying a new virtualized SQL Server instance and
conducting a database migration from the legacy physical server. During a
SQL Server virtualization strategy, many organizations take the opportunity
to upgrade their databases to the latest database platform or consolidate
their SQL Server instances and databases onto fewer virtualized systems
reducing costs and simplifying management.

As a result, consolidation through virtualization is the third option.

Physical-to-Virtual Conversions
Microsoft System Center Virtual Machine Manager 2008 R2 can be used to
convert existing physical SQL Server computers into virtual machines
through a process commonly referred to as physical-to-virtual (P2V)
conversion. Virtual Machine Manager simplifies P2V conversions with the
use of a task-based wizard that automates much of the conversion process.
Since the P2V process is completely scriptable, you have the ability to
initiate large-scale P2V conversions of SQL Server through the Windows
PowerShell™ command line. It is worth mentioning that the conversion
from SQL Server physical servers to virtual servers in this strategy is
accomplished through a one-to-one mapping process. As a result, while you
will see a decrease in the number of physical servers in your infrastructure,
you will still have the same number of SQL Server instances to manage.

During a P2V conversion, disk images of the physical hard disks of the target
computer are created and formatted as virtual hard disks (.vhd files) for use
in the new, virtual machine. The new, virtual machine will have the same
identity as the original, physical machine upon which it is based.

System Center Virtual Machine Manager can perform either an offline or an
online P2V migration on all supported operating systems (only offline is
available for Microsoft Windows 2000). Online conversions rely upon the
Volume Shadow Copy Service (VSS), so the source machine does not have
to be rebooted during the process. In an offline conversion, the source
machine is rebooted into the Windows Preinstallation Environment
(Windows PE) to image the physical disks.

The following table lists the online and offline support for P2V migrations
using System Center Virtual Machine Manager 2008 R2:

     Operating system           P2V       P2V online       V2V
 Windows Server 2008 /           No           No           No
 Windows Server 2008 R2
 with Hyper-V role enabled
 Windows Server 2008 /           Yes          Yes          Yes
 Windows Server 2008 R2
 without Hyper-V role
 Windows Server 2003 SP1         Yes          Yes          Yes
 or later
 Windows Server 2003 x64         Yes          Yes          Yes
 Windows 2000 Server SP4         Yes          No           Yes

New Virtualized SQL Server Instance
The other Virtualization Migration strategy consists of implementing a new
virtualized SQL Server instance and then migrating the data from the source
physical server. By implementing a new virtual machine with SQL Server
2008 R2 and then conducting a migration, you have the capability to
upgrade the legacy database platform to the latest version of SQL Server.
Alternatively, you can conduct a P2V migration of an existing legacy SQL
Server instance and then conduct an in-place upgrade after it has become

For more information on SQL Server consolidation strategies and
recommended practices, review the following white paper: Consolidation
Using SQL Server 2008, at

Performance Health Metrics on SQL Server Guest
In the resulting consolidated architecture, the SQL Server guest machines
should be monitored as any other SQL Server. The same performance
metrics are valid. They can be monitored using System Center Operations
Manager with the SQL Server Management Pack.

The most important guest performance metrics are:
     On a SQL server:
           o Process Object
                     % Processor Time: On average, this should run
                        under 40% with occasional spikes as individual
                        queries run.
           o System Object
                     Processor Queue Length: Less than 2 x the number
                        of CPUs in the system. If the counter is growing and
                        % Processor Time is continually over 80% you will
                        need to allocate more virtual processors to the
                        guest machine or adjust the virtual processors
           o Memory Object
                     Pages / Sec: Monitor to verify the SQL Server is fully
                        taking advantage without starving the underlying
                        operating system.
           o Physical Disk Object
                     Avg. Disk Queue Length: Less than 2 x the number
                        of spindles on the disk being monitored.
                     Average Disk Sec/Read: 11 – 15 ms or lower
                     Average Disk Sec/Write: 12 ms or lower
           o Network Interface Object
                     Total Bytes / Sec: Average less than 60% of
                        theoretical maximum of NIC. However,` verify it
                        occasionally can peak over this to avoid a limit on
                        the network traffic due to a downstream problem.
     On any SQL Server host, if the average CPU utilization on a server is
       greater than 70% there will be little gain in consolidating that
       particular server into a physical host with guests in other roles.

Performance Health Metrics on the Physical Host
The physical host machine must be monitored to verify it is capable of
handling the resulting CPU, I/O and network for all the guest machines.
The most important performance metrics on the physical host machine are:
     Processor, % Total Processor Time.
     Hyper-V Hypervisor Root Virtual Processor (% Total Run Time must
        be less than 80%).
     The storage metrics depend on the device type:

            o    iSCSI: Network parameters.
            o    Fibre Channel: HBA.
            o    DAS: Hyper-V Virtual Storage Device (Read Bytes/sec and
                 Write Bytes/sec)
       On Network Interface, Bytes Total/sec must be lower than the
        nominal NIC throughput to verify it is not exhausted.
       If there is any indication that Virtual NIC packets are being dropped,
        the physical NIC probably does not have enough throughput for the
        aggregate bandwidth.

Additional Resources
Support policy for Microsoft SQL Server products that are running in a
hardware virtualization environment

Hardware and Software Requirements for Installing SQL Server 2008 R2

Microsoft Consolidation Planning Tool for SQL Server

SQL Server Consolidation Guidance

Running SQL Server 2008 in a Hyper-V Environment – Best Practices and
Performance Considerations

Planning, Implementing and Supporting SQL Server Virtualization with
Windows Server 2008 R2 Hyper-V and Live Migration

High Performance SQL Server Workloads on Hyper-V

Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices

Description of disaster recovery options for Microsoft SQL Server

Disaster Recovery Planning (Database Engine)

In addition to the resources above, please visit to find resources for delivering
Microsoft Virtualization technologies.

The information contained in this document represents the current view of Microsoft Corporation on the
issues discussed as of the date of publication and is subject to change at any time without notice to you.
This document and its contents are provided AS IS without warranty of any kind, and should not be
interpreted as an offer or commitment on the part of Microsoft, and Microsoft cannot guarantee the
accuracy of any information presented. The information in this document represents the current view of

The descriptions of other companies’ products in this document, if any, are provided only as a convenience
to you. Any such references should not be considered an endorsement or support by Microsoft. Microsoft
cannot guarantee their accuracy, and the products may change over time. Also, the descriptions are
intended as brief highlights to aid understanding, rather than as thorough coverage. For authoritative
descriptions of these products, please consult their respective manufacturers.


To top