For years, SQL Server consolidation has been a widely used strategy. As servers have become more powerful, they can handle more services on a single piece of hardware. With the introduction of multi-core processors a few years ago, servers with the same number of
physical CPUs can handle even more work without increasing the licensing requirements. The latest version of Microsoft SQL Server is unchanged from a consolidation point of view – techniques remain basically the same in SQL Server 2005 and SQL Server 2008.
Licensing
Licensing is where server consolidation can be a major benefit. Once migration is complete, the number of overall licenses required is reduced because there are now fewer servers. If you are using CPU-based licenses, cost savings are substantially high. Savings are less substantial with CAL-based licenses simply because server licenses are less expensive than CPU licenses.
Licensing cost savings will not be realized unless you're upgrading from one version of Microsoft SQL Server to another. If you're staying on the same version of SQL Server, you may not see licensing cost savings – you probably won't need to purchase new licenses. Because the license version is the same, you can probably transfer the licenses from the old server to the new server. However, you will free up additional licenses that can then be used on new SQL Servers.
Some applications may need to be isolated for a variety of reasons. For example, they might have a dedicated amount of RAM or instance-wide security concerns. When installing additional instances, it's imperative you license the instance correctly. When using CALs, you'll need to purchase another server license for the instance. When using CPU licenses, you do not need to purchase additional licenses for the instance -- a single set of CPU licenses covers all instances installed on the server.
Hardware
Consolidating SQL Servers gives you extra servers that are no longer used. You can then repurpose these servers and use them as application or development servers.
Because of the increased number of databases being hosted on the new servers, additional hardware resources are required when you design the new servers' hardware. This includes more RAM, and having more spindles in the disk subsystem.
When designing your new database server, consider setting up a clustered solution. Doing that would increase your initial system expenses because of the cost of a passive node, but the decreased system downtime would pay for itself in the long run.
Designing and setting up a clustered database solution should be done by an experienced database administrator and Windows administrators. Clustered solutions are a bit harder to set up than a standalone SQL Server.
When clustering multiple instances on a single cluster, you still only need to license the active nodes. If all instances are running on a single node, then only that node must be licensed. If you run the instances on more than one node, then all the nodes running on SQL Server must be licensed. If you are using CPU licenses and have two active nodes each with four CPUs, then eight CPU licenses are needed.
System monitoring
Monitoring a new consolidated SQL Server means you will have reduced costs when purchasing the agents that will be installed on it.
When you monitor the system for performance issues, it's hard to isolate system load from one database to another. This is because Performance Monitor doesn't have different counters for CPU, and memory usage is on a per-database basis. The only way to see this information broken down is to separate the databases onto separate instances.
With the increased visibility of SQL Server, and more applications and more users connecting to the same SQL Server, you'll want to pay extra attention to system performance.
Consolidation gotchas
Because of the increased number of applications that connect to the database server, when a single database has performance problems, the other applications being hosted on the database server will also see performance problems. You must be even more careful when tuning the databases that will be residing on the system.
You'll need additional time to test all the applications against the new versions when installing service packs and hot fixes on SQL Server. Potentially, this could lead to additional time between service packs and hot fix releases and when you can install them, so your server could be vulnerable to security problems for a longer period of time.
You are probably hosting several departments' applications on a single server now, which means interdepartmental politics will become an issue -- even more so the first time one department's application causes a performance problem for another department's application.
Consolidating on SQL Server 2000 vs. 2005 vs. 2008
When working on your consolidation project, consider upgrading the SQL Server version at the same time. As you look from older versions of Microsoft SQL Server to newer versions, you'll see many performance improvements. SQL Server 2005 has many performance enhancements over SQL Server 2000 and by moving your databases from SQL Server 2000 to SQL Server 2005, more databases can be consolidated onto each server.
By moving up to SQL Server 2008 from SQL Server 2005 – or earlier versions – you'll see additional performance improvements allowing you to move even more databases onto the server.
While including a version upgrade in your consolidation project greatly increases the complexity of the project, the end result will be fewer overall servers to manage.
While server consolidation can give you a great return on your up-front investment, it takes a lot of planning to complete the consolidation. But at the end of the day, you will have fewer servers to manage and lower licensing costs. An additional reward is telling your CIO that you are working toward a green data center. This looks great to upper management and investors – they are doing their part to help the environment while still making a profit.
| ABOUT THE AUTHOR: |
|
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog:
SQL Server with Mr. Denny
|
|
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
One item not mentioned is the licensing of SQL in virtual environments. As I understand the article, if I have a 4 processor CPU and run 4 instances of SQL, I only need 4 licenses. If I have a 4 processor CPU that has 4 virtual machines running with a copy of SQL in each virtual machine, do I need 4 or 16 SQL licenses?
—Gary J.
******************************************
