Seperate VMDK for each MSSQL2005 DB ?

[MattJoyce]

Hi,

We planning on moving a bunch of MSSQL 2005 DBs to a VM.

Are there any compelling reasons not to use a sperate VMDK for each DB?

My feeling is, it has benefits

- Can move DBs do different physical disks/pool/raid/volume, etc

- Can be moved to different server if required.

- Snapshots won't degrade performace for all DBs so much.

Thanks

[polysulfide]

You have the right idea. The same best practices for physical disks apply to virtual ones. For all the reasons you describe use multiple VMDK files. (Unless you're talking about so many VMDKs that file handles become an issue) Depending on how your SAN is carved you may want to consider putting the separate VMDKs on separate LUNs as well.

http://communities.vmware.com/blogs/polysulfide

VI From Concept to Implementation

[jhanekom]

Managing the space on one disk is easier than managing the space on 20. Keep in mind that you can only grow a disk when the computer is offline.

Also, unless you plan on keeping the number of databases under 20, you'll need to start using mount points due to lack of drive letters. Not a huge problem, but it does introduce another level of complexity.

If you want to improve performance, you'll be better off splitting each database into multiple data files and placing those onto separate disks than placing each database on its own, single disk. (SQL multithreads reads from disk if there are multiple data files and more than one CPU. For this reason, it's a good idea to use multiple data files from the start, in case you add more processors at a later stage. Moving from one data file to more than one can be done, but is tedious to do correctly.)

My methodology up until now has been to have an OS disk, two data disks and a log disk. I spread each DB over four data files (placed on the two data disks) and place the logs on the log disk. This way, I can use the ESX multipathing to put half the data load on one storage controller and half on another.