By Yuval Shavit, Features Writer
If you're helping a client with database consolidation, performance and uptime are crucial. Not only will the consolidated server experience a greater load than any of the databases did separately, but more applications will be affected if the server goes down or slows to a crawl. Because the databases are sharing resources, all it takes is one badly formed query to affect all of the databases.
You can mitigate the risks of database consolidation by planning carefully. The more robust you make a server, the more expensive it will be, so it's often a good idea to take a tiered approach, said Geoff Hiten, a Microsoft SQL Server Most Valued Professional (MVP). Hiten usually creates three tiers.
Because database performance affects end users -- and possibly even your client's customers -- it's important to put each database in an appropriate tier, Hiten said. Make sure to include management from the business side of your client's company, not just IT staff, in those discussions; the management team may have a different idea of which applications are vital.
Important systems should run on a clustered server to improve reliability, Hiten said. He usually uses clustering for the upper-two tiers, with only the lowest-priority databases unclustered. One of the disadvantages of server consolidation is that a single point of failure can now affect multiple applications; clustering reduces that risk.
Clustering is important for more than just ensuring that an accidental hardware failure won't take down your client's most crucial applications, said Hilary Cotter, a SQL Server consultant and Microsoft MVP. It can also maintain uptime during scheduled maintenance, whether it's a hardware replacement or a software patch that requires a reboot. Essentially, your client will have to take some of the money it saves on licensing costs and invest that in the consolidated server.
"So, even though all of your eggs are in one basket, it's a really, really good basket," Hiten said.
Hardware issues aside, the major performance concern with database consolidation is the risk of a runaway process slowing the entire server. Although Microsoft SQL Server 2008 will have a resource manager that should help, for now there is no silver bullet to stop one query from taking over the server, experts said.
If load balancing becomes an issue -- especially if you're finding that one database is continually the culprit -- you may consider running multiple instances of SQL Server on the consolidated server. That will let you assign each one a separate priority, and you'll be able to more closely monitor resource use.
One option you should probably not consider for database consolidation is server virtualization, Hiten said. While virtualization does offer good load balancing management, and is fairly fast on modern hardware, it doesn't offer enough performance in I/O to be practical in production settings, he said. On the other hand, if some of the old systems are on very old hardware -- especially if they're running old operating systems that may be hard to upgrade -- the performance gains of running on modern hardware may outweigh the costs of virtualization, he said.
This was first published in January 2008