By: Jeremy Kadlec | Updated: 2007-08-30 | Comments | Related: > Upgrades and Migrations
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 @ [email protected].
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-08-30
