Consolidating Databases

How many database servers do you have in your organization? Three? Ten? Sixty -ive? Database modelers, DBAs, security administrators, and CxO managers all speak to the problems inherent in a "server sprawl" condition. Many DBAs are plagued with the all-too-common scenario of one database per server; are your own servers struggling to beat the average server utilization rate of 7.8 percent? When you hear the phrase "eliminating SQL Server sprawl," chances are the speaker is talking about server consolidation, but database sprawl is just as pervasive a problem—and consolidating databases isn't as easy as it sounds. Consolidation implies change, and any kind of change to a production or development environment has the potential to disrupt operations.

Related: 7 Conisderations for Server Consolidations

Physical and Logical Consolidation

You can perform database consolidation at both the physical and logical levels. At the physical level, you accomplish consolidation by simply relocating a database from one server to another until the target server has reached some predetermined utilization level—say, 75 percent of CPU utilization (which leaves some room for database growth).This is the first type of consolidation that most DBAs undertake; the risk of disruption is minimal because you're moving the database in its entirety, without changing anything at the logical level. The gains in server utilization and the cost savings that result from the reduction in hardware can be substantial.

Database consolidation at the logical level integrates multiple databases, which all support one common function, into a single database. This process can challenge the planning and design skills of even the best data modelers and DBAs, but when executed correctly it will ensure a significant ROI in the areas of schema management, data integrity, and enhanced performance. Of course, whichever type of consolidation you choose to practice, physical or logical, you'll have to make changes to each of the separate application UIs.

Picture this: It's your first day on the IT staff of a commercial construction company—you're the only DBA and you've just found out about the company's project management system. The system is custom-built because it has some special needs that the commercial software packages don't address, and you're responsible for its care and feeding. This project management system is composed of 26 Microsoft Access databases, one for each current construction project that the company has on the books. Each database contains lists of tasks required for the project, indicated by the name of the Access database. Each contains separate application UIs by which data is added, edited, and deleted. In addition, there are four "reference" Access databases that all the project databases use. The accounting system, which ties into all the "reference" databases, is hosted on SQL Server.

For many reasons, your boss wants to migrate the Access databases into SQL Server, and he wants to consolidate the many project-management databases into just one. These project databases are schema clones; all 26 Access databases share a common structure and UI. Where they differ is content. One project database will contain information about a high-rise being built in the downtown area, and another will contain information about a new building at the university. Every time the end users (e.g., project managers, project coordinators) request a new feature, the schema changes and UI application modifications must be made 26 times—once for each of the 26 individual project databases.

Start at the Top

In this case, you'd want to start at the top, with a system diagram, similar to the one that Figure 1 shows. The system diagram lays out the major components as they exist at the start of the consolidation project. The individual project-management databases appear in color and are labeled Project A through Project Z. Each of these project databases has links to—and uses data stored in—the "reference" databases: People, Contracts, Inventory, and Human Resources. In turn, these four databases feed data into and get data from the Accounting database. The actor symbols on the left side of Figure 1 represent the field users—project managers, project coordinators, and field supervisors—and the actor on the right represents the office staff that gathers statistics and generates reports. Each actor symbol represents about 10 people.

To consolidate all these databases, you'll want to analyze the schema of each of the databases. Each one of the databases that you see in Figure 1 is composed of a set of interrelated tables. If there's any overlap between tables in business meaning and data, this overlap should be resolved with some careful redesign. Referential integrity that currently doesn't exist will have to be established when all these table sets are relocated into one database. There's a high probability that you'll be defining new keys—primary and foreign—and new indices to enhance performance. Also, don't forget to take into account all the application UI modifications that'll be made because of the consolidation.

Where's the Master Project Table?

There is no master list of project names in this scenario, so the first new object you'll want to create is a Master Project table. Figure 2 is a high-level graphical representation of the SQL Server database after you've converted all the Access tables into SQL Server tables and migrated the Access data into SQL Server. The Master Project table will contain a project identifier or number, a project name—such as "Hospital" or "University Building"—a project start date, the estimated project duration, the actual project duration, and any other data that's pertinent to the project.

Figure 1: A representation of the consolidated database with a new Master Project table

Each table contains data for all the projects, so it's conceivable that— depending on the number of rows within each table—you might want to consider horizontal partitioning. Finished projects could be moved into a set of tables that comprise an archive or history of projects completed. Extremely large or long-running projects could have their own set of project tables. Retrieving data from projects that share space in the project tables could be optimized by creating materialized views.

In Figure 2, the set labeled People Tables are those tables that were once part of the Access People database. Likewise, the sets named Contract Tables, Inventory Tables, and Human Resources Tables all correspond to the Access databases of the same name. The Accounting Tables, if you recall, were originally stored in SQL Server. I haven't indicated any relationships between these sets of tables, but you can rest assured that there will be many.

Cleaning up

Importing all this disparate database content into one SQL Server database is going to mean a lot of maintenance for the UIs—almost every one, with the exception of the UIs for the Accounting system, will have to point to a new data source. In the Access version of the project databases, each project database had links to other Access databases, and correct behavior of the UIs was dependent in part on ODBC connections. This scenario will change; all UIs will now look to the SQL Server database as their data source. Depending on your company's philosophy, you might want to wait until the back end (the database) is stabilized before you start restructuring the UIs. Eventually, you'll want to rewrite the UIs, replacing the Access client-server scheme with something that's Web-enabled, and incorporating additional features and functionality made possible by the database consolidation. UI maintenance should become significantly easier with this move; you'll no longer need to update 26 individual project computers with each change to a table or a UI. Project teams in the field will be able to access data from handheld devices (if that's a direction the company wants to move in), rather than having to haul around computers and synchronize .mdb files every night. Consolidating databases, in this case, has opened up a world of opportunities.

Worth the Time

Taming sprawl through database consolidation is an approach that many companies are starting to evaluate. Consolidating databases isn't as easy as it might seem at first, but careful planning and some judicious redesign can enhance the usefulness of the databases and availability of the data. One unavoidable chore that is a result of database consolidation will be modifying and possibly upgrading the UIs. However, consolidating databases, especially from isolated desktop environments to a centralized SQL Server infrastructure, can offer the potential for increased data availability and new ways of doing old tasks.