C H A P T E R  16

SQL Server Private Cloud

Today, customers are heavily embracing virtualization technology. In the previous chapter, we discussed the various cloud deployments including Infrastructure as a Service (IaaS). IaaS gives IT the ability to quickly spin up an operating system environment in a fraction of the time it would normally take to procure new hardware and install an operating system and applications. Private cloud computing holds similar characteristics to public cloud computing. For one thing, both are elastic. By “elastic,” we are referring to the capability to quickly and easily spin up and down new operating system environments. The big difference between public and private clouds is that private clouds occur within a corporate firewall and sometimes leverage existing hardware. Private clouds are shielded from the theoretical insecurities of the Internet. For this reason, many companies are quicker to adopt a private cloud infrastructure. In fact, according to the online article “Sizing of the Datacenter” located at www.ctoedge.com/content/sizing-state-data-center?slide=13, the Association of Data Management Professionals (www.afcom.com) estimates that 70% of customers are planning on or are currently implementing a private cloud-based solution.

When creating a SQL Server Private Cloud there are four pillars that make up the benefits of this architecture. These pillars are as follows:

• Resource pooling
• Elasticity
• Self-service
• Control and customization

Building out a private cloud infrastructure is complex and beyond the scope of this book. In this chapter, we are going to explore conceptually what makes up a SQL Server private cloud and how you can prepare for building one.

ThE FIVE STAGES OF CLOUD COMPUTING

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. SQLIO is one such tool, and more information can be found at the following URL: http://technet.microsoft.com/en-us/library/cc966412.aspx. 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 16-1.

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

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

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

Figure 16-4. Summary tab in SQL Server Assessment report

Table 16-1 shows the information presented in the DatabaseInstance tab. Begin with the spreadsheet shown in Figure 16-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 16-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.

Upgrading SQL Server

One of the best practices when building out a consolidated SQL Server environment is to upgrade your database servers to the latest release. This will buy you a longer support cycle for the product. Versions like SQL Server 2000 are completely out of support, and dragging them into a new consolidated environment is not the best choice. However, there are circumstances that will prohibit you from upgrading. These include the vendor support for the applications that are the front end for databases not supporting a release more current than, say, SQL Server 2000. Also, internal business decisions to sunset the application could mean your choice is simply to migrate the physical SQL Server 2000 server to a virtual one and call it good enough.

Whenever you decide to upgrade the database servers, there are two important tools to be aware of in the upgrade process. These tools are the Microsoft SQL Server Upgrade Advisor (Advisor) and SQL Server Upgrade Assistant (Assistant).

The Advisor tool can be run from a desktop and points to the server instance you wish to upgrade. The tool goes through a check of potential issues you may encounter given the server you wish to upgrade. Examples of some of the potential issues include the use of deprecated statements like the *= and =* join syntax or extended stored procedures that were pulled from the product.

After the tool evaluates the server, it provides useful reports like the one shown in Figure 16-5.

Figure 16-5. SQL Server Upgrade Advisor’s instance report

If you sat for a moment and thought about situations where this tool may fail you could probably find quite a few. For example, if we were using encrypted stored procedures the tool could not obtain the text to parse to evaluate and so on. This tool is not meant to be the final check in whether or not you can upgrade; rather, it’s meant to give you a rough idea of the work involved in upgrading to the latest version. A more thorough tool used in application compatibility testing is the Assistant tool.

The SQL Server Upgrade Assistant is a joint development effort by Scalability Experts and Microsoft. The Assistant tool can be downloaded for free at www.scalabilityexperts.com/tools/downloads.html.

The Assistant tool more thoroughly tests for compatibility than the Advisor. This tool requires more resources, primarily time, to effectively use. The idea behind the tool is to capture a SQL trace of a live production workload (or a complete set of functional tests within a test environment) and replay this trace against a separate upgraded instance of SQL Server. Since the tool is capturing a SQL trace, it will capture every statement that goes against the source database. This allows the tool to trap errors, like using undocumented DBCC commands, which the Advisor tool will not capture. When the SQL trace is replayed against the new environment, a detailed line-by-line comparison will be presented, as shown in Figure 16-6.

Figure 16-6. SQL Server Upgrade Assistant’s Report Viewer

When developing a private cloud environment for SQL Server, as someone in the DBA role, your tasks will heavily center on the database inventory and consolidation piece. The magic of a private cloud has almost everything to do with the infrastructure—the flexibility of the operating system, the hypervisor, and disk subsystems. One of the other major pieces of resource pooling is taking the physical servers and virtualizing them. Products like Microsoft System Center Virtual Machine Manager (SCVMM) have this capability. SCVMM adds a ton more value and features on top of the Hyper-V platform and is a major component in architecting a private cloud infrastructure.

Elasticity

Elasticity is, perhaps, the most important component of a private cloud. Imagine if someone in your organization comes to you and asks for a new database server to be provisioned. If you don’t have the server room, you would have to buy new hardware, install the operating system, install SQL Server, and patch and maintain this new server. When the users are finished with the database, you would repurpose the server. In a private cloud environment, we would simply spin up a new virtual machine with SQL Server running. When the customer was finished with the database, we could easily delete the virtual machine image. In this section, we will look into specific features that will support an elastic environment of SQL Server database servers.

Making creating new virtual machines easy may lead to virtual machine sprawl. Even worse, each one of these virtual machines could be configured differently, causing a management nightmare. One way to mitigate this nightmare is to use virtual machine templates. A virtual machine created from a template could contain all the applications and settings needed for a specific function. For a database, we could have a template that installs SQL Server in a certain directory with certain defaults that are common in our organization. SQL Server supports virtualization templates by allowing a SQL Server installation to be Sysprepped. “Sysprep” is short for “System Preparation Utility.” Essentially, when you Sysprep a SQL Server instance, you install all the bits on the operating system but don’t complete the install. By having a Sysprep image of an operating system with a Sysprep install of SQL Server, we can deploy this same image many times, and from this image, we can make an installation legitimate by completing the install. By using Sysprep, we avoid having to install the SQL Server bits after every new operating system created. There are some restrictions in using Sysprep with SQL Server. For more information read the article, “Considerations for Installing SQL Server Using Sysprep” at the following URL: http://msdn.microsoft.com/en-us/library/ee210754(v=SQL.110).aspx.

Even without a Sysprep image of SQL Server, you could tell the virtual machine host to run an unattended install of SQL Server after a new virtual machine is created. The end experience is the same, but with Sysprep except your new virtual machine of SQL Server will take a bit longer to get ready, because SQL Server has to install the bits on the operating system.

As mentioned in the previous section, Microsoft System Center Virtual Machine Manager (SCVMM) is the primary tool used within Windows Server to create a private cloud environment. SCVMM allows you to create virtual machine templates. Assume now that you have set up SCVMM and have deployed a few Hyper-V host machines.

In a traditional SQL Server deployment, we often see failover clustering used as a high-availability solution. These requirements don’t change when a database lives within a virtual machine. Fortunately, you can define guest failover clusters for the virtual machines as well as cluster the virtualization hosts. If you are interested in setting up a shared disk resource within a Hyper-V virtual machine, read the following blog post: http://blogs.technet.com/b/mghazai/archive/2009/12/12/hyper-v-guest-clustering-step-by-step-guide.aspx. The article “Hyper-V: Using Hyper-V and Failover Cluster” located at http://technet.microsoft.com/en-us/library/cc732181(WS.10).aspx describes the requirements for using Hyper-V and failover clustering.

In a virtualized environment, you are sharing the same physical server (virtualization host) for multiple operating systems and database instances. If something bad happens on the virtualization host or if you need to upgrade a piece of hardware, taking the host offline could affect a lot of users and applications. For the unexpected downtime of a virtualization host, you can cluster the virtualization host as mentioned earlier in this section. For planned downtime, you can leverage a feature within Hyper-V called Live Migration. Live Migration moves a running virtual machine to another virtualization host with no downtime. This means your database users will be unaffected by this operation.

One of the biggest limitations with virtualization is memory use. If each virtual machine takes 4 GB and you only have 32 GB on the virtualization host, you can only run six to seven virtual machines. We know that SQL Server loves to use memory and that it will free memory that is not used over time. Earlier versions of SQL Server introduced the ability to add hot memory and CPUs. These features, combined with Hyper-V Dynamic Memory found in Windows Server 2008 R2 SP1, allow you to specify a default minimum and maximum RAM size for a virtual machine. When you launch a virtual machine, the minimum RAM will be reserved, and as the activities within that virtual machine require more memory, more will be allocated up to the maximum value. As the virtual machine releases the memory, the memory will be freed to the virtualization host. Dynamic Memory allows you to stuff in more virtual machines per virtualization host—about 40% more virtual machines per virtualization host. For more information on Hyper-V Dynamic Memory, check out the following URL: http://technet.microsoft.com/en-us/library/ff817651(WS.10).aspx.

Self-Service

If you consolidate your databases and virtualize them, you are moving one step in the cloud direction. By leveraging virtual machine templates, we make management a lot easier. At this point, if you configured your environment with SCVMM and a few Hyper-V hosts, you could call it a private cloud. However, we can optimize that cloud a bit more. As of now, if a user asks us for a virtual machine of SQL Server, we would do the heavy lifting of finding out specifically the size they are looking for, finding the VM host with enough space, and creating the virtual machine. However, SCVMM 2007 introduced a self-service portal (www.microsoft.com/en-us/server-cloud/system-center/virtual-machine-manager-self-service-portal.aspx). This portal contains a web page that allows the end user to request IT resources such as a database server with 2GB RAM and 500GB storage. The portal runs this request by the IT administrator to confirm it. Upon confirmation, a new virtual machine with the requested configuration is automatically provisioned on a host that has enough space for the requested virtual machine.

Control and Customization

If popping up another virtual machine is easy and doesn’t require buying new hardware, internal customers may kept requesting resources. Perhaps their requests are without merit. To keep the virtual machine farms from getting out of control, you can assign costs per virtual machine template. Usage and charge reporting are available out of the box with SCVMM. As a DBA, you may be on the receiving end of this charge-back model by requesting the resources from IT.

Implementation Options

So far in this chapter, we’ve explored the key concepts and features within SQL Server, Microsoft’s Hyper-V, and Microsoft System Center Virtual Machine Manager that enable you to build a SQL Server private cloud environment. If you went ahead and installed and configured these products on your own, you would be self-building the solution. To build out your own private cloud, start with reading the Hyper-V deployment guides located at www.microsoft.com/en-us/server-cloud/private-cloud/hyperv-cloud-deployment.aspx. Note that, to get to the guides, click the Build Your Own menu on the left-hand side of the page, and click the Step 2 tab. If you do not want to do this on your own, you can hire a Microsoft partner as well; credible partners are listed on the web site.

There are two additional ways to get a private cloud up and running. Certain hardware manufactures provide fast track architectures that modify the guidance suggested by the Hyper-V cloud deployment based on their hardware architectures For example, if you have an HP shop, check out HP’s “Cloud Foundation for Hyper-V” at the following URL:
http://h71028.www7.hp.com/enterprise/us/en/partners/microsoft-cloud-foundation.html?jumpid=ex_r2858_us/en/large/tsg/microsoft_cloud. Dell’s Hyper-V Cloud fast track is located at http://content.dell.com/us/en/enterprise/d/cloud-computing/microsoft-hyper-v-cloud-fast-track.aspx.

If you want the “Easy Button” approach to SQL Server in a private cloud, you can take a look at an appliance. A few hardware vendors like HP are coming out with database consolidation appliances that are essentially all the components we have discussed in this chapter preconfigured in a box. The HP Database Consolidation Appliance is available in half- or full-rack configurations. The idea is if you need more space, you can add more racks. For more information on the HP Enterprise Database Consolidation Appliance, check out the following URL: www.microsoft.com/sqlserver/en/us/solutions-technologies/Appliances/HP-dca.aspx.

Summary

This chapter presented a conceptual view of the applications, components, and capabilities of a SQL Server private cloud environment. Unless your organization is extremely small, chances are you will probably have another IT person helping you with or primarily responsible for a SQL Server private cloud architecture. The content in this chapter will help you work alongside that person or team, enabling you to play a part in bringing the benefits of cloud computing into your own environment and under your control.