SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl

9/27/2013 7:33:06 PM


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:

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.

  •  SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing
  •  SQL Server 2012 : Storage Systems (part 6) - Measuring Performance - Sequential Disk Access, File Layout, Flash Storage
  •  SQL Server 2012 : Storage Systems (part 5) - Measuring Performance - Storage Performance Counters, Disk Drive Performance
  •  SQL Server 2012 : Storage Systems (part 4) - Storage Technology - Remote Data Replication, Windows Failover Clustering, SQL Server AlwaysOn Availability Groups
  •  SQL Server 2012 : Storage Systems (part 3) - Storage Technology - Storage Tiering, Data Replication
  •  SQL Server 2012 : Storage Systems (part 2) - Storage Technology - SQL Server and the Windows I/O Subsystem
  •  SQL Server 2012 : Storage Systems (part 1) - Storage Technology
  •  SQL Server 2008 : Policy-based management - Policies in action (part 2) - Creating a database properties policy
  •  SQL Server 2008 : Policy-based management - Policies in action (part 1) - Importing policies from file, Evaluating policies
  •  SQL Server 2008 : Policy-based management - Policy-based management terms
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

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

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone