Keep large databases on separate sql server instance
I'm in the process of migrating some databases to SQL 2012 one of the database is a large database in comparison to the rest, it about 64GB and can grow to about 200GB. This database is currently sitting on a separate instance because of its size but it shares the same data drive as the rest. I'm now trying to weigh up whether or not I want to continue keeping this database on a separate instance or put all the databases on one instance. I was wondering if some DBA's out there can advise me on some of the thing I have consider in my choices and decision making And also which is the best solution
Well, by separating the instance, but combining the storage, you've only achieved a segregation of the CPU, memory and the disk controller, but you're looking at all your databases sharing a common disk drive. Reading and writing to the disk, IO, is traditionally the slowest and most painful operation in a SQL Server instance. Probably, you wouldn't notice a serious problem if you put it all back together since you've already been sharing the most likely bottleneck. However, to be certain, I'd suggest capturing metrics on the two systems in question. How much CPU is used by both (and are the number and speed of those CPUs equivalent, or do you have to factor that into the equation too)? How much memory and IO are used by each system? What are the most common waits on each system? Get these measurements and then you can make the determination if you can successfully combine them to a single server instance.