Post reply

Using SQL Instances to manage resources for a combined OLTP/OLAP server

  • March 29, 2012 at 12:25 pm

    #258418

    I am setting up a SQL 2008 R2 two node cluster for an existing database server that currently supports 6 databases (30-35 gb total), most of which are OLTP type databases supporting front end web based apps that receive about a million hits a year. One of the databases is an OLAP type and I am wondering if by creating two SQL Instances, one for OLTP and one for OLAP, I might better manage resource allocation between the OLTP and OLAP databases. Priority would be given to the OLTP databases, which are more critical to operations. Additionally, I am wondering if this approach would work for a given OLTP database that required priority access to resources. As I also have the opportunity to configure the SAN from scratch I would be able to allocate dedicated disk resources to the individual instances.

  • March 29, 2012 at 12:39 pm

    #1466559

    Standard or Enterprise Edition? If the latter, Resource Governor will be your friend. There will be a better chance of using more of your available hardware if keeping everything in one instance and configuring Resource Governor to give priority to your OLTP queries. Split your I/O workload on the backend for different I/O workload patterns.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 29, 2012 at 12:52 pm

    #1466565

    Thanks for the quick response. That is the kind of answer I am looking for, as of course this needs to be implemented yesterday. The edition is Starndard. Also, I made a mistake the total disk size of the databases, it is 120 GB.

    Cheers

  • March 29, 2012 at 1:16 pm

    #1466583

    Without Resource Governor the biggest concern will be RAM and CPU contention, as we know how to easily separate databases onto different I/O resources.

    Any problem children that drill CPU? If so, or worried, then go with separate instances and assign only some of the CPUs to the problem instance, and the rest to the other.

    How much RAM? Enough to hold all regularly accessed data for all hosted databases in RAM at one time? If not, and not even close, or if you have problem queries that regularly cause major portions of the buffer pool to be replaced with new data from disk, then separate instances may be the way to go.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply