SQL Server - Database Server Consolidation

Host consolidation or SQL Server Consolidation allows more  SQL Server Database Server Instances on a physical host. On a single physical host – resources such as memory and CPU can be monitored per Instance and adjusted accordingly.

SQL Performance, licensing, cost and management overhead  are factors in deciding whether to maintain shared database  servers. Objectives of consolidation include managing proliferation of servers i.e server sprawl and managing minimum levels of utilisation per server 

 Designing a  Database Server Consolidation model , requires some assumptions to be made. A key assumption is that full database worker isolation is not possible as they are operating on a shared OS , utilising a shared IO subsystem.

 Consideration in provisioning for database server consolidation should be given to :

a) Disk IO buffers – Data IO and Log IO

b) Memory allocation per database server read more on SQL Server memory configuration

c) SAN\ Disk Configuration -

d) Different database platforms such as Oracle and SQL Server use differing Process models – therefore should be maintained on different servers

e) Intra-query parallelism

f) Query compilation and Recompilation – A variety of application models implemented across the application range – such as static &  dynamic query –   and different vendor approaches such as self –tuning and predictable performance - differing memory requirements are used. Virtual Address Space limitations of 32 bit  - where the execution plans are generated, require that  minimum standard of 64 bit infrastructure should be implemented . 

By upgrading the SQL Server architecture to 64 bit and adequate memory – larger datasets can be manipulated more efficeintly, which will  lead to less disk IO

SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

g) OLTP v. DSS – Requirements are different . For example , a datawarehouse may require fast load, ad – hoc query support .

h) SQL Server Licensing – Multiple Instances and Clustering .SQL Server 2008 licensing - SQL Server DBA

i) Security – Data Access Control ,protecting Sensitive data sets , Monitoring\audit user activity. Most  Database server environments support : data encryption, Kerberos, SSL

Read More

SQL Server Performance Killers - SQL Server DBA

Storage Checklist FAQ - SQL Server DBA

SQL Server – More processor speed or cpu cores - SQL Server DBA