Microsoft SQL Server 2008 R2 Specifications download pdf (Page 106)

86 CHAPTER 5 Consolidation and Monitoring

Consolidating Databases and Instances

A very common SQL Server consolidation strategy involves placing many databases on a sin-

gle 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 xed 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 sus-

tain the workload of all of the databases. In addition, a single tempdb database could be a

performance bottleneck. Your organization might also nd 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 physi-

cal 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.

1 2 ... 101 102 103 104 105 106 107 108 109 110 111 ... 235 236

No comments

Microsoft SQL Server 2008 R2 Specifications Page 106