The database role’s responsibility is store, maintain and return data to the other roles in the farm. This role has the highest amount of disk IO activity and can often have very high memory and processor requirements. Virtualization of SQL Server 2005 and 2008 is supported. Depending on your environment’s requirements will determine whether you choos physical or virtual deployment options.
The argument for physical:
- SQL Server is already a consolidation layer and will host 7-10 SharePoint databases at a minimum. Recommended content database size is 100 GIG. Some enterprise deployments have 100’s of content databases over multiple SQL instances.
- Longer response times impacts ALL downstream roles in a SharePoint farm: Virtualization introduces some latency downstream in the application and UI server roles of a MOSS farm. If every SQL request takes longer that impact might be magnified in the application and UI layers, especially considering that multiple roundtrips occur frequently to complete a single transaction.
- It will experience heavy CPU, Memory, Disk I/O, and NIC usage. It is importanty to understand that virtualization by itself adds some resource requirements to the system. Which need to be taken into account. Secondly the resource requirements of the workload need to fit into a virtual machine in order to make sense in a virtual environment. If either the available hardware is not able to support the load of multiple virtual machines or the smallest unit of a workload does not fit into the envelope of a VM then physical deployment is the right thing to do. In the end, the important thing here is that this role needs to perform independent of if it is running in a VM or physical.
- Possible ramifications to SharePoint: If the overall performance evaluation or virtual machines are not adequately spec’ced, this might result in slower response times for the end user and, in the background processes, slower performance of long running background operations which could increase operation time outs. This would be similar to running on physical hardware with insufficient capacity.
If you decide to virtualize the database layer:
- Assess first using Microsoft Assessment and Planning Toolkit (www.microsoft.com/map)
- Implement SQL Aliases in your SharePoint farm
- Assign as much RAM as needed and CPU as possible
- Offload the Disk I/O from the virtual machines and prefer pass through or fixed size disks over dynamically expanding disks.
- Split SQL cluster over two physical hosts for minimum data layer availability. (hardware redundancy and load distribution)
- Check the SQL CAT whitepaper on virtualizing SQL on Hyper-V: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx
- SQL Clustering – Do not virtualize the passive node of a SQL cluster: It defeats the reason you clustered in the first place, which is high availability of your databases. Either virtualize the entire database layer or keep all physical! You cannot virtualize part of a SQL cluster. You can, but virtualizing the passive node of the active-passive SQL Server cluster is not recommended. Why? Typically you lose about 15-25% percent in performance using virtualized database hardware and over time portal usage will grow resulting in greater database load requirements. Over time your physical host will get squeezed by the other virtual slices until the point when the virtual passive node may not handle physical node’s load. It defeats the reason you clustered in the first place.
- SQL Clustering of virtual database nodes is supported.UPDATE: The guidance has now been updated to support guest clustering provided the virtualization technologies are certified using the SVVP program. See http://support.microsoft.com/kb/956893 (Thanks James Daniel)