We had a PASS volunteer SIG (Special Interest Group) teleconference today. One item discussed was server consolidation. Microsoft is interested in this area and is seeking input.

During the earlier discussion we had, Bill Wunder and Lynda Rab brought up good points like login transfer, collations, tempdb issues, job management and maintenance, resource allocation, and security management, etc. I will not steal their thunder by repeating what they’ve covered. Here is some of my thoughts after our brief discussion on it over the phone. I sent them to the group but thought I would share that with you, my dear reader:)

1. It will be interesting to compare the scenario of one default instance hosting all databases versus multiple instances hosting their own databases. I guess multiple instances can solve tempdb and collation problems.

For consolidation, the server got to be 64-bit. To avoid a rogue database highjack the whole machine, therefore defeating the purpose of consolidation and increasing risk, it will be interesting to set Max memory for each instance. Or, set a fixed memory size for each instance, depending on what databases each instance has and the total server memory.

In addition, it will be interesting to set CPU affinity. So CPUs are also isolated for each instance. On top of that, each instance will have its own hard disk spindles or SAN LUNs. That will be one way to make sure that one database won’t hog the whole machine.

Having multiple instances also gives one the flexibility to selectively stop and restart one particular instance. Hmmm, now I wonder what kind of machine (CPU, Memory, Storage, Licensing of Sql Server software) we will need to pull this off. In some cases, it may not even be cost-effective. I guess it all depends on how many servers one has and the nature of the applications. The size, cooling, and other infrastructure of your data center also play a role here.

2. I am also intrigued by virtualization. I’d like to know when and how it can be used in a production environment. I’ve been using Virtual PC and installed WinXP and Windows 2003 as guest operating system on top of it. They are all great for experimenting with various test cases and just messing around. It seems to me that virtualization is best suited for testing and learning, not for real production though. I’d like to hear your thoughts and experience on this.