Home / server / Questions / 363529
Accepted
Benjamin Bryan
Asked: 2012-02-25 10:47:22 +0800 CST

Consolidating SQL Server

  • 772

We have a number of SQL Server 2008 R2 standard servers, each for a single application that we'd like to consolidate into one physical server. Running perfmon on these servers they're not using a high amount of CPU, IO, or network traffic so they're good candidates for consolidation. A couple of questions:

Is it better to create a single instance with multiple databases, or multiple instances (e.g. an instance for each application?)

I don't anticipate much contention, but is there a way to do some sort of governance on IO so that no database/instance can take up all the IO resources? I know I can do CPU with the resource governor.

On RAID configuration, we'll have 22 disks available (and 2 hot spares). Writes are heavier than reads so we'll do RAID-10.. Is it better to setup one large array for all the databases, and then two smaller arrays for log files and tempdbs, or setup a dedicated array for each database/instance.

Thanks.

sql-server-2008 raid storage database-administration database-performance

1 Answers

  1. Best Answer

    Here are some things to think about:

    • You may want to stick with separate instances from a security perspective. If you have a single administration team for all existing servers then this isn't an issue and a single instance with multiple databases may be the way to go. If you break your administration up or if you have special security concerns then a single instance might not be the way to go.
    • SQL Server Enterprise is the only production edition where Resource governor is available. If you're consolidating onto SQL Standard that won't be an option for you.
    • dba work for a single instance can be easier than multiple instances (from a general operations perspective).
    • Assuming no resource governor it'll be easier to cap memory used by each application using separate instances and setting the max memory option for each instance. That will also add more administrative overhead, though. That said, SQL Server is VERY GOOD at managing physical resources. There isn't enough information about your environment to know whether or not this is a worthwhile endeavor, but my gut says no.
    • Generally you have more variables working against a single application in a shared (ie- single instance) environment. For example, if a bad query plan gets stuck in a plan cache and you're using a single instance then all other databases can be affected adversely until you can clear that bad plan out. But again, this doesn't sound like a very big risk for you.

    If the databases are not doing heavy IO currently then you should be ok by just having a volume for data then a separate volume for logs.

    • 3