Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Consolidation Pros and Cons


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations

Attend these FREE MSSQLTips webcasts >> click to register


Problem
In the recent years, there have been a few different trends related to the hardware platform for SQL Server.  One trend was to have a dedicated SQL Server for each application.  This trend has been countered in some organizations by a major consolidation effort.  The consolidation effort in some circumstances consists of consolidating the hardware and storage to a unified set of devices although the same number of logical machines exists.  In other circumstances, the consolidation is for the hardware, storage, Windows and SQL Server instances.  Both alternatives have advantages and disadvantages, so what is the correct path?  What should organizations follow?  When considering migrating to SQL Server 2005 or 2008, should a consolidation effort be considered as well if we have a large number of SQL Servers are in scope for the project?

Solution
You are correct, the opposing paths of dedicated versus shared SQL Servers have been major trends for different reasons at many organizations.  Neither path is necessarily right or wrong as an approach, but the support and development related to each path should be analyzed from an organizational level and then a decision should be made.  In some circumstances, both approaches could be used for different reasons.  Let's take a look at some of the considerations when making the SQL Server consolidation decision.

Considerations - SQL Server Consolidation

  • The Numbers Game - Depending on the number of Windows machines supporting SQL Server, the SQL Server environment (production, test or development), the number of databases per environment, the storage requirements, the storage available and planned growth, should give you a good indication if the SQL Server consolidation decision should be considered or not.  For example, if you have 10 SQL Server's (machines) each with a total of 5 GB of user defined databases then consolidating to a single machine may be a very reasonable consideration.  On the other side of the coin, if you have only one or two SQL Servers with a mid scale amount of resources (CPU, memory, disks, et.) with a single user defined database that is ~100 GB on each SQL Server, then consolidating these machines may not be as great of a need.
  • Management and Support - At some level, managing any SQL Server instance takes time especially if the overall management process is manual.  For example, applying service packs takes time and coordination at a number of different levels.  So if applying these patches is a large percentage of someone's job because it is completed manually, then the time savings could be a consideration for consolidating your SQL Server instances.
  • The "all of your eggs in one basket" Scenario - One of the biggest concerns that many individuals have at organizations with the dedicated SQL Server approach is that if a problem occurs with a single machine, then all of the applications are affected.  Compare this the dedicated approach where a single problem will only affect a single application and the scale of the problem is considerably different than when the SQL Server databases are consolidated.
  • SQL Server Environment - This consideration relates to whether or not the SQL Server instance supports production, test or development needs.  In some scenarios a single SQL Server machine or instance supports all of these needs.  As such, what may be needed is to setup test and development environments independent of the production platform, so the production environment can be dedicated to those responsibilities.  This is really a matter of expansion than consolidation.
  • Application\Database Purpose - One typical approach for consolidating SQL Server databases is based on there purpose.  Depending on your organization, your categories (purpose) may be different, but the premise is the same as using these type of distinctions:
    • Internet based applications\databases
    • Internal or administrative applications\databases
    • External or customer facing applications\databases
    • Line of business applications\databases
  • Security - One set of opposition to consolidating SQL Servers is related to security concerns.  In some circumstances, this is just a fact of life depending on the sensitivity of the data and the industry.  In addition, contracts may require the dedicated machines due to security concerns.  As such, be sure you fully understand these considerations before going too far down the analysis path.  If you have a contractual agreement, these may trump the technology decision.
  • Timing - The old saying "if it isn't broken do not fix it" becomes applicable based on the timing of the consolidation exercise.  When upgrading hardware, hardware is out of warranty or upgrading SQL Server, the consolidation decision could make a great deal of sense.  However, building a business justification for changing approaches for the sake of the technology may be a hard sell to management if the business does not reap a benefit.
  • Third Party Vendors Requirements - Depending on the third party application, some vendors mandate a dedicated SQL Server in order to be compliant with the support agreement.  As such, read the fine print and possibly consider some of the alternatives in the next section.
  • Cost Savings - Depending on how you license your SQL Servers, there may be an inherent cost savings in the licensing for Windows and SQL Server.  This could be reason enough to consolidate and could save significant budget over time.  Another area to consider is a savings is in your data center.  With rising power costs and high rack space fees at data centers, may be significant and push the pendulum over to the other side of the equation.
  • Charge Backs - If you have an environment where charge backs occur, then consolidation could become much more complex if machine resources and personnel resources are not evenly distributed or identified.  As such, working out this detail could be difficult when working with the end user management and budgets.  At times, this is reason enough to prevent consolidation efforts.

Technology Alternatives

From a technology perspective, alternatives do exist beyond just the dedicated machine approach or a pure consolidation approach.  Some of these alternatives may include:

  • SQL Server 2005 Express Edition - For small SQL Server needs this may be a viable option in scenarios where a small amount of data needs to be collected or available to the users.  In some scenarios, SQL Server 2005 Express Edition is a viable alternative to support web based needs while still benefiting from all of the security advancements as the other editions of SQL Server.  Besides, its free so that cost savings could really kick in on this alternative, if the SQL Server resources are sufficient.
  • Multiple Instances of SQL Server - Functionally, SQL Server can support a single default instance and up to 15 named instances.  If security from a SQL Server instance perspective is an issue, then this may be a viable alternative.  In addition, from a development and test perspective, multiple instances may offer the possibility to functionally test a variety of builds with the flexibility to shut down instances, reallocate resources to a single instance and perform load testing.
  • Virtual Server Technologies - Whether or not you agree virtual server technologies can support large scale production SQL Server applications or not, a few different alternatives may be available to support smaller production needs as well as test and development environments.  Another area where virtual server technologies may take off on a wide scale is supporting disaster recovery needs.  Often times, the disaster recovery needs are just as important as the production needs, not a second tier to production other times this is not the case, which is where virtual server technologies may meet a common business need.

SQL Server 2005 or 2008 Considerations

When upgrading to the latest version of SQL Server, considering a consolidation decision may be a good exercise.  When hardware upgrades and software licensing are budgeted and downtime is expected and acceptable.  As such, this could be a reasonable time to perform the analysis and capacity planning to identify commonalities among the databases and determine which databases could ultimately reside on the same SQL Server instance after the upgrade.  At this juncture, you may also be able to easily show the cost savings in tangible numbers when comparing your current approach versus a tangible set of alternatives.

Next Steps

  • If you are having a debate at your organization whether to stay the course or switch your approach to your SQL Server architecture, hopefully this tip has outlined some additional considerations as you are making your decision.
  • If you do go down the path of consolidating your SQL Servers, get organized.  The biggest challenges are typically not technical, but logistical.  Proper analysis to determine the SQL Servers that can live together is the first challenge followed closely by having to coordinate the consolidation steps with the networking and development teams so the users do not miss a beat.
  • If you have recently completed a SQL Server consolidation project or are in the midst of one right now, we would like to hear from you.  We are trying to assemble some audio interviews with DBAs, Developers and Technical Management as notes from the field.  If you are interested in participating, please email us @ tips@mssqltips.com.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools