SQL MCM Training – Day 9

Today Jonathan was talking the whole day about Consolidation and Virtualization. There were again a lot of new concepts, and new things to learn.

Module 4: Consolidation

• Event Notifications for Monitoring
  
  • Deadlocks, Locking/Blocking Scenarios
    
• Multiple Instances for TempDb bottleneck
  
• Soft-NUMA should be used to bound multiple instances to dedicated CPUs
  
  • CPU Affinity Maks should not be used, because they are not supported anymore in SQL Server Denali
    
  • CPUs can be only taken from the local CPU
    
    • there is no cross CPU exchange
      
    • Soft NUMA nodes can’t be crossed
      
    • only local memory access
      
    • Improves performance
      
  • TCP ports can be bound to Soft NUMA instances
    
• Multiple concurrent Backups can lead to Buffer Pool Contention
  
• Multiple instances when you have high concurrent workloads and not enough worker threads
  
  • Number of worker threads is bound to the instance level
    
• SQL Server 2008 R2 will report if you run on a Hypervisor
  
• Detach, Copy, Attach
  
  • Maybe you’re copying free space over the network
    
• Raw Device Mapping (RDM)
  
• Migrate Databases through Database Mirroring/Log Shipping
  
  • Restore a FULL backup
    
  • Initiate Database Mirroring/Log Shipping
    
  • Finally make a tail log backup and restore it
    
  • http://www.sqlservercentral.com/articles/Backup+%2f+Restore/66962/
    
    
• On-the-Fly VHD file migration
  
  • Put everything on a VHD
    
    • Including master database etc.
      
  • Move the VHD file from one VM to another VM
    
• There were no providers for 64bit Excel/Access, you can use the following workaround
  
  • Install SQL Express x32
    
  • Linked Server from SQL Server x64 to SQL Express x32
    
  • Linked server from SQL Express x32 to Excel/Access
    
• Multiple Instances because of security issues
  
  • Each database needs sysadmin/securityadmin role
    
  • Put each database on its own instance
    
• Virtual Machine Failover Cluster for Patching SQL Server Instances
  
  • Patch the Passive Node
    
  • Fail over
    
  • Patch the other Node
    
  • Fail back (if needed)
    
• The total of min server memory should be smaller than the total amount of physical memory available
  
• Max server memory is ALWAYS preferred for multiple instances
  
• Memory\Available MBs > 150-300MBs
  
• Thread Pool Starvation
  
  • Not too much worker threads are available
    
  • E.g.
    
    • Query runs with MAXDOP 4 across 100 different connections
      
    • Each query needs 9 worker threads (4 producer threads, 4 consumer threads, 1 coordinator thread)
      
    • These are almost 900 concurrent threads
      
    • Queries can timeout, because this amount of worker threads is not available once a time, see
      
      • select max_workers_count from sys.dm_os_sys_info
        
• SQL Server Consolidation at Microsoft
  
  • http://technet.microsoft.com/en-us/library/dd557540.aspx
    
    

Module 5: Virtualization

• Too much CPUs can lead to co-scheduling issues
  
• The RAM of a VM should not be larger than a NUMA node
  
  • The assigned number of CPUs should be also not larger than a NUMA node
    
• Min Server Memory setting should be set inside a VM, because of the Balloon driver
  
  • Balloon driver fakes memory pressure in the guests to prevent actual memory pressure on the host
    
  • Ballooning kicks in when you overcommit the host (the assigned memory to the VMs is higher than the physical available memory)
    
• Hyper-V Dynamic Memory not recommended for SQL Server!
  
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/01/31/sql-server-and-hyper-v-dynamic-memory-part-1.aspx
    
    
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/02/27/sql-server-and-hyper-v-dynamic-memory-part-2.aspx
    
    
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/03/09/sql-server-and-hyper-v-dynamic-memory-part-3.aspx
    
    
• When you cluster SQL Server in a Virtual Machine, you have to put each SQL Server node on a different physical Virtual Machine Host node
  
  • Otherwise you have not gained any high availability, when you put each SQL Server node on the same physical Virtual Machine host node
    

Thanks for reading

-Klaus