It’s hard to remember a time without Microsoft SQL Server. After all, it’s almost 50 years since its initial release. That’s enough time for SQL instances to have multiplied across any enterprise. With SQL servers distributed throughout a network, IT departments may spend more time maintaining the databases than necessary. Reducing the time needed to support SQL instances is just one of the benefits of SQL Server consolidation.
What is SQL Server Consolidation?
In their 2009 article, SQL Server Consolidation Guidance, Sung Hsueh, Antony Zhong and Madhan Arumugam define consolidation, in general terms, as
“the combining of various units into more efficient and stable larger units.”
This article, which we reference through ours, discusses how consolidation strategies can take different forms. Three of the more popular methods are:
- Consolidate Multiple Databases
- Consolidate Multiple Instances
- SQL Server Virtualization
The best approach depends on why consolidation is being considered.
Consolidating multiple databases in a single SQL server means applications share the same server instance. Current applications running on different SQL releases must migrate to the same release. Moving multiple databases to a single instance reduces licensing costs.
However, performance may be an issue if the databases are large. Before consolidating databases, organizations need to look at the maximum size of each database and then the total size of all databases. This calculation can help determine if database consolidation is appropriate.
Consolidating databases may not work in production environments. Still, it may work in development, testing, or staging where performance is not a concern and the databases remain relatively small.
Instance consolidation involves moving applications to a single physical server running multiple instances of SQL Server. Each application uses its own SQL Server instance. Unlike multiple database consolidations, each server instance is unique. That allows companies to operate different release levels of SQL on the same physical machine.
However, the potential exists for conflicts because resources are shared. Database administration is isolated to the specific instance, but the operating SQL Server environment is shared. The shared environment requires careful management of resources such as memory and CPU consumption.
Each SQL instance resides in a separate location on the physical server with a unique name and port. The deployment requires separate logins for added security. The instance consolidation can reduce hardware and software costs for individual SQL servers.
SQL Server Virtualization
Virtualization moves physical servers into virtual machines (VMs). A physical server can host multiple VMs, each supporting a single SQL Server instance. Virtualization simplifies migrating a distributed configuration to a consolidated SQL Server environment because each physical SQL Server corresponds to a virtual machine installation.
Virtual machines are separate from other VMs. They interact with each other as if they were physically different machines. The network views each VM instance as a different installation. While the consolidation can reduce the physical hardware required, it does not reduce the licensing costs unless the number of SQL Server instances is diminished.
VMs can support multiple SQL Server instances. This configuration requires system and database management that may be resource prohibitive. Other consolidation strategies may combine all three approaches by consolidating databases within a VM environment.
What Are the Benefits of SQL Server Consolidation?
Most organizations consider SQL Server consolidation because they want to achieve specific goals such as:
- Reduce costs
- Improve efficiencies
- Standardize operations
- Increase IT agility
- Support Green IT
The consolidation strategy depends on an organization’s goals.
Reducing costs is a primary factor in SQL server consolidation.
Growing companies need hardware to support their growth. If they don’t have the space, they can look at acquiring more area at a significant expense or consider consolidation. IT departments should look at under-used machines as a place to consolidate applications.
Moving more applications to underutilized machines allows hardware to run at capacity. Less hardware means lower maintenance costs. The move not only reduces inefficiencies but it requires fewer machines.
Additionally, consolidation will reduce software costs. Fewer servers mean fewer instances of Microsoft Windows Server and less SQL Server licensing – particularly Enterprise edition.
SQL consolidation reduces system and database administration, lowering the amount of overhead. It can reduce power usage, cooling needs, and rack space. The process can help minimize licensing costs.
Reducing the number of physical machines also lessens the administrative load for maintaining the hardware and software. Lowering the number of instances of SQL Server from 25 to five means less time is spent applying patches and addressing SQL Server performance concerns.
IT departments can use the freed time to enhance security or address a backlog of projects. Staff no longer needs to monitor resource usage and establish baselines for under-used machines.
Instead, they can devote time to projects that improve a company’s bottom.
Data-driven decisions rely on data that is often stored in SQL databases. These databases have developed over time and do not share the same management and format standards. Pulling all this disparate data into a single data model that results in those data-driven insights takes time and costs money.
If databases exist on a common platform, they must share an infrastructure. That means sharing a set of methodologies and requirements. Consolidation can produce standardization.
Standardization increases application interoperability.
Standardization makes data control easier, improves security management, and eases demands on provisioning and deployment.
Increase IT Agility
Building an infrastructure that improves control and flexibility of server resources makes it easier for organizations to adjust to changing demands. Moving SQL-based applications to newer hardware can improve performance. They can decrease downtime with better-managed updates.
Consolidation isn’t just about SQL. It’s about the entire environment in which it operates. If improved agility is a core goal, SQL server database consolidation should be a part of the plan. Agility requires resilience, another aspect of a SQL server consolidation project plan.
Support Green IT
Environmental, social, and governance (ESG) concerns have become increasingly important in the last few years. The “greening of IT” is one spoke in the ecological wheel, especially for technology-heavy organizations. While ESG-based changes may result in reduced costs and increased efficiencies, the critical difference is reducing the carbon footprint.
Fewer machines require less space. Since buildings and construction contribute almost 50% of emissions, minimizing space requirements helps the environment. Smaller computer rooms require less energy, and newer technologies allow energy-efficient CPU usage.
ESG concerns should be part of every SQL server database consolidation approach. Microsoft’s IT department found that consolidation on new servers reduced power requirements by three million volt-amps. The change in power requirements saved about $11 million a year in operating costs.
How to Pick a Strategy
After determining consolidation priorities, businesses should consider the following factors when planning an SQL server consolidation project.
- Disaster Recovery
- Resource Management
Deciding the priority of each factor should be part of an SQL server consolidation project plan. Relying on experts in SQL server consolidation can help organizations identify the best strategy.
With increased cybersecurity concerns, organizations must ensure that all regulatory and compliance requirements are met in the consolidated environment. The first step is identifying where information is stored and what regulations apply. Securing data during and after consolidation should be part of every plan.
Lax security procedures expose a company to possible breaches, resulting in leaked data and fines. Well-designed security architecture, especially during consolidation, minimizes security and financial risks.
Businesses must include disaster recovery and business continuity when approaching SQL server consolidation. Critical applications could be moved to machines with the best hardware and capacity to minimize possible downtime. Clustering critical applications on a single machine allow resources to focus on those devices in case of machine failure.
Consolidated servers should be configured to support all applications running at peak usage. They should handle spikes without compromising reliability. All instances should take advantage of resource management tools so staff can focus on critical machines.
Density refers to the number of applications that operate on a single machine. Different applications and databases have various overheads that impact performance.
For example, instance-level consolidation shares operating resources but not applications. Database consolidation has to address the capacity of a single operating and SQL environment.
Calculating the throughput and response time of all systems running on a single server indicates the density of a machine.
Managing consolidated servers after deployment should be factored into any project plan. How flexible will management be? What skill level will be required?
Finding tools to help with resource management can reduce the daily operations requirements. Tools can help allocate internal resources to maintain SQL databases.
Performance may become a concern if the proper analysis is not done during consolidation planning. Density and resource management should not degrade after moving to a consolidated environment. If the applications do not function as well as before consolidation, IT staff will need performance management tools to determine potential conflicts after deployment.
Define and Meet Your Consolidation Goals with IncWorx
When to consolidate and what strategy to deploy depends on multiple factors, including internal goals and operational capabilities. If you’re considering SQL server consolidation, schedule a call to speak with one of our experts, or check out our blog for more information.