Virtualization
Virtualization of the operating system
plays a key part in architecting a private cloud solution. SQL Server
is one particular workload that has unique characteristics, which need
to be taken into account when virtualizing SQL Server. For one thing,
the biggest contention within a database is disk I/O. When we
virtualize a database, we need to make sure the I/O we obtain from
within the operating system environment is pretty much what we had on a
physical server. There are tools you can use to obtain I/O metrics
based on a typical SQL Server workload.
One of the limitations of running inside a virtual machine host is that
your virtual machines are limited in using multiple host bus adapters.
This could also cause your disk I/O to suffer. Provided your SAN
engineers have convinced you that all will be well in the virtual
world, there is one other thing to note when virtualizing a database.
If your existing SQL Server implementation currently leverages more
CPUs than your hypervisor can give per virtual machine, it is probably
not a good idea to virtualize this workload.
Resource Pooling
In a SQL Server private cloud environment,
consolidating databases is the prime objective and a benefit of
resource pooling. This pillar maps to Gartner’s Stage 1, server
virtualization. By simply virtualizing your SQL Server instances, you
achieve a reduction in operational expenses, reduction in energy costs,
and a reduction in physical space required for hardware. When it comes
to databases, we don’t really want to perform a physical to virtual
machine action on them without proper planning.
For one thing, before we set out on an effort
to consolidate databases, we need to know which databases we want to
consolidate. Even though we may have a master list, there could be
other SQL Servers in our environment that popped up without our
knowing. This concept, known as “SQL Server sprawl” comes from a
variety of factors such as users getting frustrated at the time it
takes to acquire new databases to power users ignoring corporate
policies and installing software on their own. Whichever the case, it
is important to know of all of our SQL Server installations. Microsoft
has a free tool called the Microsoft Assessment and Planning (MAP)
toolkit to make this inventory task really easy.
Discovering SQL Server Sprawl
The MAP tool is not meant to be an auditing
tool. It doesn’t secretly send information back to Microsoft, so you
don’t need to worry about Steve Ballmer knocking on your door after you
use it demanding more money. The tool’s original design was to scan
your network and tell you which PCs were upgradable to the latest
versions of Office and Windows. From a business standpoint, it would
make sense that Microsoft would invest in this free software for you to
see which PCs are able to be upgrade.
The plumbing of the tool though was seen as a
great value to other products like SQL Server. In fact, in the latest
version of MAP, the tool can inventory Windows and Linux environments
as well as Oracle, MySQL, and Sybase. The MAP tool can be downloaded
from the following URL: www.microsoft.com/map.
When you launch the MAP tool, its default tab is “Discovery and Readiness,” and this tab is shown in Figure 1.
Figure 1. Discover and Readiness tab of the MAP tool
From this tab, you can click Inventory and
Assessment Wizard to launch a wizard that will guide you through the
process of scanning your environment. The wizard will first ask you
which scenario you are targeting. This is shown in Figure 2.
Figure 2. Inventory and Assessment Wizard Inventory Scenarios page
Depending on the scenario you select, the
wizard will leverage many different technologies to use to obtain
information. The most common is the Windows Management Instrumentation
(WMI). Once we select the scenario, the next page asks you which
methods to use in discovering your environment. This is shown in Figure 3.
Figure 3. Inventory and Assessment Wizard Discovery Methods page
From this page, you can see that Active
Directory is an option. This would be the best option if you have
Active Directory deployed in your organization. Alternatively, you can
specify an IP range, specify names from a text file and a few other
options. Depending on which methods we chose, the wizard will either
ask us for Active Directory credentials or standard username and
passwords for those computers we selected to query. These credentials
are not stored anywhere other than memory for the duration of the scan.
After completing this information, the wizard will start scanning.
Depending on which options you selected, this could take hours or days.
The result though is worth the effort. For SQL Server scenarios, there
are three reports that are generated. The first two are Excel files,
and the third is a summary document written in Word that basically
summarizes at a high level the information from the first two Excel
files. Excel file number one is called SQL Server Assessment, and it
contains three tabs: Summary, DatabaseInstance, and Component. The
Summary tab displays a summary of the count of instances and components
found in the scan. Information from the Summary tab is shown in Figure 4.
Figure 4. Summary tab in SQL Server Assessment report
Table 1 shows the information presented in the DatabaseInstance tab. Begin with the spreadsheet shown in Figure 4. Look at the bottom of the window. You should see a tab named DatabaseInstance.
The Component tab lists similar information to
Database Instances, except it lists the components (e.g., Reporting
Services, Analysis Services) that are installed for each server as well
as the server specifications, like the number of processors and RAM.
The second Excel file, SQL Server Database
Details, goes into depth on the databases within each instance. It
contains six tabs: Overview, SQLServerSummary, DatabaseSummary,
DBInstanceSummary, DBInstanceProperties, and DBUserDetails. This
spreadsheet contains a plethora of information and a summary of each
tab is in Table 2.
Before any consolidation work is
performed, we need to know the exact environment we are dealing with.
The MAP tool is a fantastic and free way to get a grip on what SQL
Server instances are running in your environment.
