Transcript Slide 1

Session Code: DB.08
Programming the CLR in
SQL Server 2005
Mark Blomsma
Develop-One
Programming the CLR in
SQL Server 2005
• Introductions
• The End of T-SQL
• How does it work?
– Hosting Layer, SQL OS
• How do I use it?
– Stored procedures, Functions, Triggers, Custom aggregations,
User Defined Type
• Deployment
• Monitoring
• Questions
Mark Blomsma
• Professional developer since 1992
• Microsoft Certified Professional
• Microsoft Most Valuable Professional
(MVP) for three years running
• Current employment
– Software Architect : Develop-One
– Consultant : Omnext.NET
– Sales Manager North America : MRA Group
The end of T-SQL?
• No! Managed code will not solve all your
problems.
– Select/Insert/Update/Delete
are here to stay!
– CLR is an alternative to the procedural portion
of T-SQL
So why put C# in the database?
•
•
•
•
•
•
•
Complex string manipulation
Encryption
XML manipulation
No more need for external procedures
Tooling support for managed languages
Leverage .NET FCL & third party libraries
For everything that you cannot do with T-SQL
Show me the magic
• Stability is everything for a database
• CLR 2.0 offers more control to host environment through
“HostProtection” attribute
–
–
–
–
–
–
No thread creation
No listening on sockets
No finalizers
Allow/deny allocation of memory
Deny killing the host process
CLR is sandboxed through CAS
• Checked during assembly creation process
• CLR 2.0 AppDomain is hosted in SQL Server
– Loading and unloading of assemblies is controlled via SQL
Server
Limited FCL support
• Not available
– Environment.Exit()
– System.Console
– System.Windows.Forms
– System.Drawing
– System.Web
–…
SQL OS
SQL Engine
CLR
Hosting
Layer
SQL OS
Windows
• CLR uses SQL OS
for:
– Memory
– Threads/fibers
– Synchronization
Hosting layer
SQL Engine
CLR
Hosting
Layer
SQL OS
Windows
• Hosting layer
provides coordination
of:
–
–
–
–
–
–
–
Assembly Loading
Memory management
Security Model
Reliability
Threads & Fibers
Deadlock detection
Execution context
How do I use it?
•
•
•
•
Stored Procedures
Functions
Triggers
Custom Aggregations
• SqlContext
– SqlExecutionContext
– SqlTriggerContext
Stored procedures
•
•
•
•
Public class
Pubic method
Method must be static
Use [SqlProcedure] attribute
• Use in, out inout and return parameters
Stored procedures
Functions
•
•
•
•
Public class
Public method
Method must be static
Use [SqlFunction] attribute
• Must return a value
Functions
Triggers
•
•
•
•
Public class
Public method
Method must be static
Use [SqlTrigger] attribute
– [SqlTrigger(Event=“FOR INSERT”)]
• Use SqlTriggerContext for getting at the
data related to the trigger.
Triggers
Custom Aggregations
• Public struct
• Use [SqlUserDefinedAggregate]
attribute
• Implement:
– Init
– Accumulate
– Merge
– Terminate
Custom Aggregations
User Defined Type
• Public struct
• Use [SqlUserDefinedType] attribute
• 8KB size limit.
• Do not use for business objects.
Deployment
• Use sp_configure ‘clr enabled’, ‘1’
to enable sqlclr
– Off by default
• Use create assembly
• SqlClr uses 3 CAS permission buckets
– Safe
– External access
– Unsafe
Deployment
Monitor assemblies
•
Profiler trace events:
– CLR:load assembly monitors assembly load requests (successes and failures)
– SQL:BatchStarting, BatchCompleted
– SP:Starting, Completed, StmtStarting, StmtCompleted monitor execution of
Transact-SQL and CLR routines
•
Performance counters:
– SQL Server: Total CLR time
– .NET CLR Memory
– Processor
•
DMVs and catalog views:
–
–
–
–
–
–
sys.assembly* shows basic information about the assemblies stored
sys.dm_os_memory_clerks
sys.dm_clr*
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_cached_plans
Monitoring
Tips
• Database server CPU is valuable
• SQLCLR is about making DB
programming easier
• Managed code has a performance
advantage over T-SQL with respect to
most procedural computation, but for dataaccess T-SQL generally fares better.
Questions?
References
• SQL Server 2005 distilled
by Eric Brown, ISBN: 0321349792
• http://msdn.microsoft.com/sql/learning/pro
g/clr/default.aspx?pull=/library/enus/dnsql90/html/sqlclrguidance.asp
• http://blog.develop-one.com
• This presentation on:
– www.sdn.nl
– www.develop-one.com
Addendum
New SQL Server 2005 features
• Presentation by Dinesh Chandrasekhar
• www.microsoft.com/sql/prodinfo/overview/
whats-new-in-sqlserver2005.mspx
• http://msdn2.microsoft.com/enus/library/ms170363(SQL.90).aspx
Evaluatieformulier
Vul je evaluatieformulier in en maak kans
op een van de prachtige prijzen!!
Session Code: DB.08