Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows Server

SQL Server 2012 : Running SQL Server in A Virtual Environment - IDENTIFYING CANDIDATES FOR VIRTUALIZATION

8/3/2013 11:28:52 AM

As virtualization’s popularity has grown, so has the debate about which server roles can be virtualized. Some of the very broad generalizations have contained a grain of truth. Certainly for a long time there was the view that production database servers could never be virtualized but virtualization technology has developed significantly to not normally make that a problem. In fact, VMware now suggests that virtualizing 100% of an enterprise server estate is now a possibility with the latest versions of their software.

Ultimately, the only way to determine how much of your server estate can be virtualized is to adopt some high-level guiding principles before performing a detailed review of relevant performance data to ensure your virtual environment can handle your anticipated workload.

Guiding Principles

When people ask me which servers they can and can’t virtualize, their question is often “what’s the biggest server you’d virtualize?” My answer is always that far more factors should influence the decision than the server’s size alone. This section outlines some general guiding principles that you can follow when considering virtualization. More detailed information is provided in the next section.

Server Workload

The first area is related to the server’s workload. Although you might assume that this is the same as the server’s size, a small server working consistently at its highest capacity may be harder to virtualize than a larger server that is often relatively idle.

Today, with a well-designed virtualization environment it’s safe to work to the following design assumptions:

  • Quiet server workloads can be and are routinely virtualized today. These might well be domain controllers; file servers; or the database servers for your anti-virus software, your expense tracking, or your HR system in a small to medium-size business. If the server is one whose performance doesn’t require monitoring or there is no question about it always meeting its business requirements, then you can consider this a small workload.
  • Active server workloads also can be and are often routinely virtualized; and as long as capacity checks for the required CPU, memory, and storage throughput are made first, these workloads usually virtualize well. This kind of server might well host your finance system’s database server, where for the majority of the time a constant number of data entry users execute low-cost queries, some utilization spikes occur with ad-hoc reports, while month-end processing creates an infrequent but demanding workload. You may well already be using some performance-monitoring tools to proactively look for slowdowns in the end user experience, as well as perhaps deploying a physical server to which you know you can add more CPU or memory as needed.
  • Busy server workloads need planning. The latest hypervisors claim to be able to accommodate them, but you need to design and tune your environment well first, in order to ensure the success promised by your virtualization vendor’s marketing. These workloads reflect those servers that you already have to proactively manage, even on relatively new server hardware. In the database world, these are likely to be transactional systems that have a high throughput of order or quotes being processed, or perhaps reporting servers that routinely perform CPU-intensive server-side aggregations. These are demanding workloads that require thorough planning and testing before deploying in a virtual environment.

Gathering Sizing Data

So far we’ve approached the identification of “good” virtualization candidates fairly unscientifically, whereas some virtualization specialists I’ve worked with would have immediately started collecting performance data and analyzing it. That step is still crucial for your virtualization planning, but working through the guiding principles just described should only take a couple of moments, and it will help you to quickly identify your potential risks and concerns, or even nonstarters, and save you some time.

More detail is provided in the next section on how to use collected configuration, utilization, or performance data to help design a virtual server running SQL Server successfully, but the following list describes some data you should collect and explains why it will be useful to your planning:

  • Memory utilization — This is one of the most important, if not the most important, piece of performance data to capture. How much memory does your database server currently have installed, how much does SQL Server currently actually use, and does SQL Server even have sufficient memory right now?
Some of the counters available in Performance Monitor you can use here are probably what you already use today for monitoring SQL Server. An instance with a very high (25,000+ seconds) Page Life Expectancy is likely to be able to be virtualized with the same or perhaps less memory than it has currently without significantly affecting performance. However, if there is a very low Page Life Expectancy value consistently being seen (<1,000 seconds) then it’s likely the server already has insufficient memory and I would expect the server when virtualized to have more memory than it has now. Finally, the Total and Target Server Memory counter values should also be used to determine what amount of memory is too much or too little based on the current Page Life Expectancy; it may be that not all of the server’s memory is being made available for SQL Server to use.
  • CPU utilization — This data will help you understand the server’s workload patterns and identify how easy it will be for your server to coexist with other virtual servers on the same host server once virtualized. As well as collecting the Average Total CPU utilization, you should also monitor how often periods of high activity occur and how long they last. For example, you might run a recalculation job every hour that takes 10 minutes to run. In addition to collecting CPU utilization data from within Performance Monitor, you should also understand how your instance of SQL Server uses parallelism. While your current physical server might have 16 cores, running a smaller number of parallelized queries on it requires different planning than if you run a much larger number of serial queries.
  • Storage utilization — This is an area often overlooked when designing virtual environments, yet it’s an easy area to capture data about. The regular logical disk counters from within Performance Monitor will help you size your host server requirements when you know the average and peak period IOPS and MB/s demands of your current server in order to deliver satisfactory query times. If available, also ensure that you capture the same data from your storage subsystem. Both sets of data should show the same trends even if they show slightly different values, and will be useful in your planning.
  • Network utilization — In the same way storage utilization is often overlooked, so is network utilization. The most useful Performance Monitor counter you capture here is the Mb/s throughput of the server’s Network Interface Cards during peak periods, perhaps during a ETL process, backup window, or busy business period.
Consideration must also be given if the physical server currently has, or if the virtual server will have, iSCSI storage presented to it. In these situations, the iSCSI traffic is likely to be far higher than any which SQL Server itself requires, and it needs to be accommodated.

Sizing Tools

Several tools are available for gathering the data you will need to understand the resourcing requirements of your future virtual servers. The Microsoft Assessment and Planning (MAPS) toolkit can scan a specific server or an entire network to report on the software and hardware resources currently being both deployed and utilized. It even has a built-in feature to specifically advise on physical-to-virtual (P2V) migrations. VMware has a similar tool called the Capacity Planner that also analyzes a workload over a period of time and advises on the best P2V approach.

Other than these tools designed to aid P2V migrations, you may be currently using others that are already storing the kind of information you need. SQL Server’s Management Data Warehouse and Utility Control Point features might be performing such roles in your environment.

Non-Performance Related Requirements

Having collected data about how hard your server may or may not need to work once it is virtualized, you should also collect information about when your server needs to work hard and, more important, be available.

For these requirements I suggest collecting the following information:

  • Peak workload periods — If your database server operates according to a structured and planned routine, then knowing when you need your server to be able to work especially hard is important. Being able to plan this in a calendar format will help you visualize all your different server workloads and prevent potentially negative conflicting workloads on the same host server.
  • Availability requirements — Currently, your existing server is likely deployed in a way that ensures it meets the business availability standards required of it. For example, it might be standalone or it might be clustered. Knowing what level of availability is expected of the server once it’s virtualized plays a large role in determining the virtualization technologies you adopt.
Other -----------------
- SQL Server 2012 : Running SQL Server in A Virtual Environment - MANAGING CONTENTION
- Microsoft Content Management Server Development : A Placeholder Control to Store All HTML Tags (part 2)
- Microsoft Content Management Server Development : A Placeholder Control to Store All HTML Tags (part 1)
- Sharepoint 2013 : Create a Team Site, Create an Enterprise Wiki Site in SharePoint Server, Create a Blog Site
- Sharepoint 2013 : Create a Subsite
- SQL server 2008 R2 : Reverting to a Database Snapshot for Recovery
- SQL server 2008 R2 : Setup and Breakdown of a Database Snapshot
- Windows Home Server 2011 : Maintaining Windows Home Server - Checking Free Disk Space on the System Drive
- Windows Home Server 2011 : Maintaining Windows Home Server - Checking Your Hard Disk for Errors
- Windows Home Server 2011 : Maintaining Windows Home Server - Checking System Uptime
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro