imageAs more and more databases come into an enterprise, the question of database expansion and consolidation is often brought to the table.  Capacity expansion costs money, not just software licenses, but also in maintenance and hardware.  At some point there has to be a trade-off between the cost of keeping up performance and storage and the performance of other applications.  This is especially true with the proliferation of SharePoint in many organizations, where the first impulse is to treat SharePoint like a file server which quickly eats through storage space.  However, through database consolidation companies can reduce the number of database servers that they have to manage and reduce the demand for servers and storage. Consolidation can also provide greater operational efficiency. If you can show a better Return On Investment (ROI) for capital expenses like servers, your finance department will love you.

Types of database consolidation

There are three basic types of consolidation, this includes physical server consolidation, SQL Server instance consolidation and storage consolidation.

Physical server consolidation

This is one of the most popular consolidations, especially as companies find that they have multiple servers that are all underutilized and Microsoft has gone to the per-CPU licensing model.  The physical database server consolidation is the process of consolidating multiple underutilized physical SQL Servers into fewer physical SQL Servers. It is a common problem that many organizations have a number of physical database servers that are underutilized and time must be spent maintaining and patching them.  This often occurs in project-based environments where a new physical database server was commissioned each time an application requiring SQL Server database is deployed. As a result, these organizations end up having multiple instances of various versions of SQL Server servers scattered among various Windows servers running multiple Operating systems.

This type of consolidation helps organizations reduce the number of physical database servers by identifying and consolidating the multiple underutilized physical database servers into single physical database server running multiple instances of SQL Server. In this way, these organizations could end up with the same number of instances just placed in as few physical database servers.

Physical server consolidation is going to save you in terms of hardware, OS, patching maintenance and third-party software licensing.

SQL Server instance consolidation

Each instance of SQL takes up a chunk of memory.  Often the instances are underutilized, each instance being spun up in an attempt to isolate the application and rights to one particular instance of SQL.  This type of consolidation helps to reduce the number of SQL Server instances by consolidating the databases hosted on these instances to fewer instances, freeing up memory.  Instance consolidation might have to do with just dealing with one server, for example you might have seven or eight instances on one server and you want to combine those databases into one instance on that server. This type of consolidation saves on CPU and memory usage, as well as patch maintenance as you have fewer instances to patch during the maintenance window.

Storage Consolidation

Very common in today’s high end storage solutions where a SAN is used for shared storage.  By consolidating the SQL storage companies can invest in high end solutions that are shared among several physical servers, which allows the enterprise to centralize the storage among multiple servers. By consolidating the storage solution this makes a good case for buying some of the higher performing (and more expensive) storage solutions while at the same time making backup and data archiving much simpler and can even include incorporating things like SAN replication keeping in mind you’ll need to review LUN and RAID levels on a regular basis to make sure you’re not running into I/O conflicts with chatty databases that are more heavy towards read or write, for example transactional databases vs. data warehouses and reporting.

Hybrid consolidations techniques

For most enterprises, a combination of the three types of consolidation is usually the most economical depending on the business needs. For global organizations, it does not make sense to consolidate everything into one North American data center if there are specific applications only used in Europe or Asia.  Thus what you typically see is a main datacenter consolidation with satellite data centers consolidated based on performance needs.  However, experience has shown that where possible it is pest to centralize your data into one location in order to simplify administration and maintenance and reduce the overall administrative costs.

Advantages of Database Consolidation

Licenses

Microsoft has moved away from the per-seat licensing model and is headed full steam into the realm of per-CPU.  This means the more databases you can consolidate on a CPU, the more money you save on licensing.  This also means that if you can reduce the number of physical servers, you reduce the number of OS licenses, administrative tools, antivirus solutions, backup and monitoring tools, even rack space.  This can add up quickly to thousands of dollars in savings.  Especially as when our physical servers have proliferated and we have a whole rack or three of underutilized physical servers that can be services by a couple high performance servers.  Remember, if our databases are dispersed unnecessarily, this means that we are paying for extra licenses, which we probably don’t need. With the help of database consolidation, we can easily reduce this unnecessary license cost, because we don’t need to pay the extra licenses and maintenance fees for those decommissioned SQL Servers.

Servers

I’ve seen a lot of powerful servers out there, bought at the time because the project demanded the best in hardware and performance.  Those same servers are running at 5%-10% CPU utilization and only use 4GB of a 24GB memory stack.  It happens.  Departments are sometimes put in a position of use it or lose it and go a little hog wild with their purchases, or the original estimates for performance or capacity were overblown, or even egos get involved of having the biggest and meanest servers.  Database Consolidation helps us to reduce number of physical servers, and those freed up servers can be used elsewhere on new or existing projects.  This only really works well when you have a capacity management group that understands the history of an application and the resource utilization, but it’s well worth the time and effort.  Especially when you have a $60K server running an application where a $5K would perform just as well.  Better to take those servers and consolidate on a few high performing servers and free up the resources.

Soft costs

Electricity costs money, both to power the server and cool it down.  If you’re renting space in a CoLo datacenter rack space costs money.  Every SQL server you run should be managed by a DBA – generally the ratio is something like 12 servers to 1 DBA.  If you have 50 or 60 servers, then that forces you to hire an additional DBA that you might not need if you actually consolidated your databases down to 20 or 30.  Freeing up a DBA means they can work with development to help fine tune processes and be able to tweak systems to get the best performance possible instead of just barely being able to handle the day to day maintenance tasks.

Centralization

Database consolidation also allows us to spend less on network traffic for things like configuration management and monitoring software. For example, let’s say we’ve got some third party monitoring tools that is installed on each our windows servers that monitor and automate patching. If we consolidate our databases down to just a few servers, then this means that we have to do fewer configurations every time we changes to the software configuration, specially with Patch Tuesday and the various security bulletins that Microsoft puts out.  Less servers means a lighter load on the network from monitoring data packets and transmission of patch files.  It may also mean going from a 50 port switch to a 10 port switch.  Database consolidation reduces the networking costs, not only from physical assets such as routers, switches, and cables; But it also reduces the utilization because fewer payloads are being transmitted and potentially with fewer servers that means quicker patch cycles so less risk surface is exposed.

Standardization of SQL Server infrastructure

So this has managed to bite more more than once.  You think you have all your standards in place, you’ve tested your patched before deploying, and then you deploy and something breaks because there was this one server over here that is about 5 patch cycles behind that nobody remembered to test at the time.  If we have defined set of standards for our SQL Server instances and fewer instances to monitor, then there are less chances of a surprise when we are required to make changes.  More importantly, as your standards change you have fewer servers to update to stay in compliance with your standards.  It’s all about no surprises.

Security

Database consolidation also helps us to improve infrastructure security.  We can review the security levels and perform audits in less time because there are less servers.  If someone leaves the company and the policy is to change all passwords of accounts they have access to… imagine the overhead of doing this if they had access to SQL accounts instead of just rights given by AD (Which is a whole other topic).  By reducing servers you reduce surface levels of attacks. For example, if we have our databases dispersed among a one hundred servers as supposed to just ten servers then this means that there are 10x the number of chances of attack because we have to manage and maintain more users and logins.

Where to Start?

With a solid case for consolidation under your belt, the next step for enterprises is deciding how best to go about it. In general, like most projects, there is a logical set of steps to take in order to take on a database consolidation process, which can loosely be translated to the ALM process that Microsoft has made so popular in the Developer world with Team Foundation Server.

Typically database consolidation will involve at least these three steps, but of course mileage may vary and you’ll add additional steps as needed depending on the size of the consolidation, scope and any regulatory requirements.

Envision

Figure out what you’re going to do.  Why are you doing it?  What is the cost benefit ratio of doing this project and the risks if you do not.  The project may die on the vine at this step, and that’s OK.  This is the step where you figure out not only do you want to do the project and does it make financial sense, but where is the finish line.  What does the final result look like and how do you know if the project has succeeded.  Set your goals.

Create a Plan

One of my early managers was always fond of saying, “Plan the work, work the plan.”  If you don’t have a task list and people don’t know who is responsible for what, how do you hold them accountable and how do you know if you’re on track?  At what point are you behind schedule and what impact does it have on the rest of the timeline?  In this stage we identify and document the major steps that are involved in database consolidation project. For example, what kind of hardware do we need, who is going to order it, how much does it cost?  What are the lead times for installation and configuration?  We need to identify resources involved and make sure we’re on their calendar.  Most importantly we need to identify who the key stakeholder of the project is and who is bringing the money to the table to pay for all of this.  If they don’t agree with the goals you set during the Envisioning phase that needs to get hammered out now.  Ultimately, as fun as new technology is, if the business isn’t happy then nobody is going to be happy.  Make sure the goals and steps are clearly defined and that you also include in your plan frequent status meetings to keep key stakeholders up to date.  The biggest reason to have a plan is so that there are no surprises down the road, so remember that plans change and always keep yours up to date.

Implementation

Work the plan.  Order the hardware, get resources working on the project, assign tasks and make sure they are completed when expected.  Each step should be verified and checked off the list when complete.  The plan should be available to all (put it on a SharePoint site!) who are involved in the project.  Remember, a successful project means no surprises.

In Conclusion

Database consolidation has several benefits such as reducing administrative and operational costs, simplified administration, improved infrastructure security, and better usage of computing resources and capacity.  This is a topic that should be regularly visited.  Remember that the enterprise evolves and you consolidate when and where it makes sense.  One of the most frustrating things I’ve heard at companies is “Well, yes we looked at database consolidation a few years ago but it just didn’t make sense.”  This was back when they only had 10 servers and now they are running 40…  This should be a strategic policy and at least once a year should be revisited because businesses change, technology changes, and what may not have been a business need a year ago may be a money maker/saver today.