Consideration of Hardware Resource Sharing
After the change of SQL Server license scheme, many of my customers are asking help to consolidate their SQL to maximize the investment of hardware/software. There are 3 methods to share the hardware resource:
Method 1: One SQL Server instance shared by multiple databases
Method 2: One Windows/Cluster shared by multiple SQL Server instances
Method 3: One VM host (only applicable for virtualization environment) shared by multiple guest OS servers running SQL server instance individually.
The boundary of application varies from method to method, below table highlight the main difference and consideration of these 3 methods:
| Method 1: Multiple DBs on Same Instance | Method 2: Multiple Instances on Same Windows | Method 3: Multiple VM Guest on Same VM Host | |
| Boundary of Applications | Same Instance | Same Operation System | Same Hardware |
| How to Set Hardware Boundary | The available option is Resource Governor inside SQL Server to separate CPU, Memory and IOPS by customized classifier function.Single TempDB. | Set Max Memory and CPU affinity between instances.Place database files into different disks.Multiple TempDB. | Set memory and CPU between VMs.Place database files into different VHDs or different LUNs.Multiple TempDB. |
| Difficulty of next Hardware Migration | High as multiple application should be moved at the one time. Or you could create DNS C-Name for each application to decouple the dependency between application/DB server. | Medium as one application could be moved at one time. | Low. |
| License Cost | One license cost. | It depends on the license scheme. | It depends on the license scheme. |
| Patching Cost | Low as there is only one SQL and one OS. | Medium as there are multiple SQLs and one OS. | High as there are multiple SQL and multiple OS. |
| Maintenance Windows | No flexible if the maintenance windows are different across application when applying any change. | No flexible if the maintenance windows are different across application when applying OS change. | Flexible |
| Data Security Risk | SQL logins are in the same instance. High risk for cross database access if unnecessary SQL permissions are granted to login. | Medium risk if SQL Server service account is with unnecessary OS permission. | Low risk |
Hope this help you to make the decision.
-- Posted by Shiyang Qiu, July 18, 2016