A Word on Server Consolidation - SQL Server 2008

Currently, many of us are working in companies where the latest cost-saving initiative is that of serverconsolidation. This new buzzword is getting a lot of attention lately. You may be happy to learn that SQL Server 2008 has some features that will help if your organization decides to consolidate. If we look at why companies consolidate, we find two key motivations behind the effort:

Reduce costs: Consolidation reduces costs through reductions in software licensing fees, technical support costs, and, ultimately, hardware expenses. By using fewer servers, we will generate less heat and consume less electricity, and in the end, we are a bit friendlier to the environment.

Reduce server management complexity: Since SQL Server is a low-cost, enterprise-ready database platform, a lot of application vendors have included SQL Server as part of their solution. This trend, in addition to the proliferation of SQL Server in the enterprise as the organization’s critical database, has led to SQL Server sprawl. When the sprawl gets out of hand, administrators do not know how many SQL Server implementations are actually deployed or where a specific database actually resides. Sprawl is much more difficult to administer, secure, and update. Server consolidation can reduce this sprawl.

Now that you are sold on server consolidation, realize that you should not implement server consolidation just for the sake of doing so. If done wrong, consolidation can make things worse. Some applications will not work well in a consolidated environment, and you must ensure that proper testing has been performed before moving consolidated servers into production.

When we talk about server consolidation and SQL Server, it is important to separate the physical hardware and operating system from the software aspect of a SQL Server implementation. From a physical aspect, depending on the hardware vendor, the server can be set up to host different operating systems by physically separating resources like memory and CPU utilization. Certain operating systems have native virtualization capabilities, like Windows Server 2008 and its Hyper-V technology. Hyper-V allows you to create separate virtual machines (VMs) running within a single physical machine. With Hyper-V, you can also efficiently run multiple different operating systems—Windows, Linux, and others—in parallel, on a single server. This exciting new technology in Windows Server 2008 does give VMWare a run for the money in the virtualization market. If you are interested in consolidating via server virtualization on Windows.

From a database application perspective, consolidation to SQL Server means either consolidating multiple databases on a single server instance or consolidating multiple server instances across multiple physical servers, but fewer than the number of servers the organization currently has.

Generally, in consolidation, we are reducing the number of physical servers and/or software licenses needed for our organization. What an organization actually ends up with as far as how many databases live within each instance and on how many instances live on each physical server is totally dependent on the organization’s architecture and the requirements and restrictions for each database.

Consolidation may have some unwanted side effects. Consider the case where we are consolidating two server instances into one. If we have different users for each of these instances, we may have inadvertently given an elevation of privilege to some user accounts with this merger. Consider the case where the sysadmin of server instance 1 has no access to server instance 2, and these two instances are merged. The sysadmin of server instance 1 now has access to the contents that were contained on server instance 2. In security lingo, this is a prime example of an elevation of privilege. Given this example, it is important to emphasize that consolidation will not only put a strain on performance with respect to disk I/O, but it also may create some security issues if not properly designed.

Realize that the final architecture and design of consolidation is only one part of the overall consolidation effort. There are a few key issues that you may need to address, depending on where you fall in terms of responsibility in your organization. Perhaps one of the most important issues is that consolidation needs a buy-in from all parts of the organization. It needs an executive sponsor to help push the monetary considerations through the upper level of management. It also needs a buy-in from the actual workers who will be performing the consolidation. To some people, consolidation means potential job loss, and in some rare circumstances, this may be true. However, in the majority of cases, consolidation will give DBAs more time to work on the more challenging issues facing their organization. With fewer servers to manage, less time needs to be devoted to the mundane tasks of database maintenance.

In addition to buy-ins, if you are a manager, you need to make sure you allocate enough time for your resources. Proper planning and execution takes time, and most DBAs don’t usually have any spare free time to dedicate to consolidation.

If you are interested in or are currently involved in a consolidation effort, take a look at the Resource Governor and Policy Management features in SQL Server 2008. Resource Governor gives DBAs the ability to restrict users’ or applications’ use of SQL Server resources like memory and CPU utilization. Resource Governor was designed to prevent runaway queries from taking over the SQL Server instance, but it also has direct benefits in the SQL Server consolidation scenario.

Policy Management (PM) is another must-consider feature to use in a consolidation effort. PM allows DBAs to define policies and enforce these policies against the SQL Server instances they are managing. For example, imagine a policy that would enforce the Resource Governor settings across all the servers in your organization. Suppose that this enforcement is necessary to maintain the service level agreement that your company’s information technology department has with the organization. If another administrator wanted to tweak his server to gain more CPU bandwidth for his own queries, he would be denied. PM enables a plethora of scenarios, not just for consolidation, but in security, naming consistency, and many other areas. The time invested in learning PM is well worth it.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics