Download Microsoft SQL Server 2008 R2

Consolidating Databases and Instances
A very common SQL Server consolidation strategy involves placing many databases on a single instance of SQL Server. This approach offers organizations improved operations through
centralized management, standardization, and improved performance. For example,
multiple databases belonging to the same SQL Server instance facilitates shared memory
optimization, and database consolidation helps to reduce overhead due to fixed resource
costs per instance. There are some limitations with database-level consolidation, however. For
example, in this scenario, all databases share the same service account, maintain the same
global settings, and share a single tempdb database for processing temporary workloads.
Figure 5-1 shows many databases being consolidated onto a single physical host running one
instance of SQL Server.
SQLInstance01
FIGURE 5-1 Consolidating many databases onto a single physical host running one instance of SQL Server
Many times, it is not possible to consolidate all of your databases onto a single instance,
possibly because additional service isolation is required or a single instance cannot sustain the workload of all of the databases. In addition, a single tempdb database could be a
performance bottleneck. Your organization might also find this scenario problematic if it has
requirements to maintain different service level agreements for each database, if there are
too many databases consolidated on the system, if databases need to be isolated for security
and regulatory compliance reasons, or if databases require different collation settings.
You can still consolidate databases if you have these types of requirements; however, you
may need more instances or physical hosts to support your consolidation needs. For example,
the diagram in Figure 5-2 illustrates the consolidation of many databases onto a single physical host running three instances of SQL Server, whereas the diagram in Figure 5-3 represents
an alternative, in which many databases are consolidated onto many instances residing on
two separate physical hosts.
86
CHAPTER 5
Consolidation and Monitoring