SQLMCM Training – Day 17

Today Bob spoke about Auditing, SQLCLR, XML, and Spatial Data. Because I already know these concepts very well, my study notes for today are very sparse.

Auditing

• SQL Trace has too much drawbacks from Auditing
  
  • Separate program
    
• Column Level GRANT overrides a DENY
  
• Auditing audits changes to Auditing
  
• Auditing Targets can’t be shared
  
• Auditing uses Extended Events in the background
  
• You always need to do a CREATE SERVER AUDIT
  
  • Defines the target
    
  • Defines what to do when the audit is not available
    
  • QUEUE_DELAY = 0
    
    • Synchronous auditing without data loss
      

SQLCLR

• SQLCLR is always loaded, because it is internally used
  
  • Spatial, HierarchyID data types
    
• Only for functions
  
  • WITH RETURNS NULL ON NULL INPUT
    
  • If you can’t change the .NET code to use nullable types
    
• SQLCLR uses the Multi Page Allocator
  
  • Uses memory outside the Buffer Pool
    
  • “MemToLeave” area, can be controlled through the “-g” startup parameter on x32
    
• SQLCLR is not controlled by Max Server Memory
  
  • So you need to set Max Server Memory, so that SQLCLR also has some memory available
    
• You get for each owner of an assembly per database an AppDomain
  
• SQL Server Log shows which AppDomains are loaded
  
  • DDL AppDomain
    
    • For Assembly Verification during startup
      
  • Runtime AppDomain
    
    • For executing SQLCLR code
      
  • sys.dm_clr_appdomains
    
• SQL Server calls the .NET GC, when memory pressure occurs
  
• sys.dm_clr_tasks
  
  • forced_yield_count
    
• SQL Server creates invisible assemblies, when you call one assembly from another assembly
  
  • The dependent assembly is invisible
    
  • You can’t register .NET code in an invisible assembly
    
  • You have to make that assembly visible by ALTER ASSEMBLY
    
  • Dependent assemblies are automatically dropped when the “root” assembly is dropped
    
  • sys.assembly_references
    
• sys.dm_exec_query_stats
  
  • clr_time
    
• When you execute SQL code in SQLCLR, it’s dynamic TSQL Code
  
  • This breaks Ownership Chaining
    
  • Use EXECUTE AS OWNER instead
    

Thanks for reading

-Klaus