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