By Yuval Shavit, Features Writer
The first step in any database consolidation project is to determine which servers to include in the project. This step is necessary because, while SQL Server sprawl can cost your clients time and money, addressing that sprawl comes with its own cost: It will likely require hardware upgrades, and you might need to reconfigure some of the databases or the applications that use them.
New hardware is the most expensive factor in database consolidation, and the project's benefits may not justify the cost, said Bill Graziano, a partner at ClearData Consulting Inc., a consultancy for Microsoft SQL Server in Lenexa, Kan. And the consolidated server will have to do the work of several existing servers, so your client may need to upgrade to technologies that can manage the load. For instance, the project could require migrating your client's storage from a low-end disk array to a high-end SAN, Graziano said.
In many cases, the cost of hardware could be mostly or fully offset by lower licensing costs as your client runs fewer instances of the database software, said SQL Server consultant Geoff Hiten, a Microsoft SQL Server Most Valued Professional (MVP).
Even if it isn't, your client may save time and money by having fewer machines to maintain, said Hilary Cotter, another SQL Server consultant and MVP. Consolidation makes IT's job easier by putting a company's databases in one central location, under its jurisdiction, instead of having them spread out across various departments. This also helps with backup and disaster recovery, Hiten said.
Database consolidation isn't an all-or-nothing proposition; you may want to keep certain servers separate even if the rest are consolidated, Graziano said. For instance, if a server has high security requirements, such as those imposed by regulations like HIPAA or PCI-DSS, you may want to keep it as a separate database rather than going through the overhead of ensuring that the consolidated server is fully compliant.
If the applications that use a server are hard-coded to it and would require time-consuming changes to the code, that server might not be a good candidate for database consolidation either, Cotter said. In addition, other servers may have complex dependencies that are difficult to re-create, or may run old versions of SQL Server that cannot be easily upgraded, he said.
Although SQL Server can emulate previous versions, that emulation may create subtle differences in behavior that are difficult to fix or adjust to. One option for sites that want to maintain multiple versions of SQL Server is to run multiple instances of the database, each a different version, on the same machine. Although this practice doesn't afford all of the benefits of database consolidation, it does make maintenance easier by reducing the number of physical servers your client needs to keep patched, and it lets the older database take advantage of the newer hardware.
If there are no major complications, the database consolidation process itself can be as simple as backing up the database on one server and importing it to a database on another, Graziano said. However, it's important to carefully reconstruct any dependencies the databases expect, such as drive names or external applications, Cotter said. You may also need to resolve naming conflicts with usernames and passwords, and any application that uses the databases should be redirected to the new server.
The other crucial step in consolidating databases is ensuring that the new server's performance matches or exceeds that of the old databases, Cotter said, since the consolidated server will have to perform as well as the old servers, even under maximum strain. By the same token, uptime is a greater concern than it may have been in a sprawled environment, since more applications rely on the consolidated server.
This was first published in January 2008