TechEd04: DAT318 SQL Server 2005 (code named "Yukon"): .NET Framework-Based
Mahesh Prakriya and Ramachandran Venkatesh got the group started early. The
room as very full. There was kinda of an electricity in the air.
- Why: Gives us a choice of languages for certain things, but T-SQL as a
1st class citizen
- Leverage existing skills. Especially Fujitsu. (Like we really want COBOL
in the database, right?)
- Use the right tool for job. .NET is right when you can leverage the
framework. Also enables 3rd parts to write libraries and extend the DB.
- Leverage the tools support in .NET, like VS.NET and Borland's toolset.
Make step through debugging work.
- Clear message: No more need to extended stored procedures
- Hosting layers deals with the assembly loaded, memory, secuirty,
reliability, threads, fibers, deadlocks and execution.
- UDF functions are almost always faster a procedural code, T-SQL is
virtually always better at DML operations than the CLR.
- Did a walk through demo of parsing an email address CLR-UDF.
- <sqlFunction()> attribute registers the UDT now There is now an
automatic test script generated.
- Auto-deployment now works. Kick ass.
- Note that the other day, I said that System.Data.Sql superceded
System.Data.SqlServer. That's not was seems to be true though.
- Interesting demo of dumping a table to a text file using a CLR function
that showed debugging.
- Some FX classes are available: Microsoft.VisualBasic, mscorlib.dll,
system.data, system, system.xml, system.security, system.web. Not supported:
System.Windows.Forms, System.Drawing, System.Web.* All of these are denied
for obvious (can you imagine a modal dialog on the server?) or for security
reasons. It's all wall garden.
- CLR Hosting is achieve through the Fusion loader, the assembly comes
from the database.
- Tips and tricks
- Environment.Exit is always denied, console isn't available. This is
done with HostProtection attribte and this is manifested as a new type
of exception.
- Spawning your threads aren't allowed. No shared state or
synchronization. No listening on sockets on server. No finalizers. FxCop
has been updated to check these things.
- Code is heavily inspected before it gets inserted into the database
- You should watch the deployment process with profiler to see what
its doing.
- Functions are always faster in the CLR (2x to 3x is normal.) Performance
is similar to a functional call.
- Moving computational code is a two edge sword. Its not a good idea to
put your middle tier
- Factory method in the future making regardless of the code running and
out of proc.
- Use T-SQL for data access, especially for select, join, sort and group
- Use SqlPipe.Execute when possible.
- SQLCLR is about making your day-to-day DB programming easier.
- DBA tips from Ventkatesh
- Security. You need to run sp_config 'clr enabled' as SQLCLR is off
by default
- Assemblies must be explicitly authorized and enableds
- Creating and executed assemblies is a fine grain permission
- Catalog view security just works as expected.
- Good explanation of code access security. Assembly must be evidenced and
permitted.
- Three permissions: safe, external and unsafe. Safe is most like what you
can do with T-SQL; external can talk to system resources; unsafe can do
anything. External and Unsafe require extra permissions
- By default, everything should be done in safe. External and Unsafe can
impersonate as using "run as."
- Day in the life of DBA
- sp_configure 'clr enabled' to see if CLR is enabled.
- select name, description,* from sys.dm_os_loaded_modules where
description like '%Common Language Runtime%' to see if anything is
running
- select * from sys.assemblies to list known assemblies
- select cast(content as varchar(max)) from sys.assembly_files where
name like '%' to list the source code for the registered functions.
- sys.assmebly_modules to get class name and method names.
- The DMV sys.dm_os_memory_clerks where type like '%SQLCLR%' to show
much memory is in use
- CLR does garbage collection in SQLCLR too.
- Sys.dm_exec_requests to see CPU time for a process.
- Showed the import of counters into profiler. Very nice, very cool
with the Correlator.
- Require your devs to deploy the source whenever possible because it will
help the DBA troubleshoot
- Use showplan and the meterics.
- Alter Assembly (new with SP2) with unchecked to express rollout a
change, or use DBCC CHECKDB to validate persisted data.
Key points
- The combination of profiler, perform and the DMVs are rather powerful.
- .NET is alway about productivity: UDA and UDTs, increased performance,
but ins't always applicable.
- Use the perf to tune manage code and to troubleshoot issues