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.
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.
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.
You can post your thoughts and concerns about database consolidation, and its
potential for business efficiency, at the SQL
Server Magazine Database Design forum,
http://www.sqlmag.com/go.dbdesign.
End of Article
