Using SQL Instances to manage resources for a combined OLTP/OLAP server
Posted on 2012-03-29
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.