Wednesday, May 26, 2004 - Posts
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
Speaker is Mike Maringas. Great opening and a very personable style.
- Unified install experience
- Goal is to an unified and integrated experience
- Instance names clashing problems are getting address. Starting with B2,
single package per unique instance and per product feature. Solution
involves using an alias.
- Demo of the installation progress
- Service Packs by feature (AS, NS, etc) makes is possible to just patch a
service without affecting other services and means smaller service pack
downloads.
- 64-bit beta BI and DTS after Beta 2.
- Scripted installs for beta 2
- Cluster Analysis with GUI
- Slip streaming of services
- Patch management story is much better
- WUS supported
- Based on MSI 3.0 which has patch uninstall
- Talked about Setup Configuration Check which helps prevent failures
- Install failures hook into Watson
- Some discussion of dynamically updating help
- Cluster installs
- 8-node cluster supported in Yukon
- All machines are treated as a distributed transaction
- No upgrade for MSDE yet
- SQL 2005 can upgrade 2000 Enterprise, Developer and Pro with SP3.
English and Japanese, running on X86, IA64 and AMD64
- NO NEW MDAC BITS
- Good efforts made a minimizing total downtime during the upgrade
process. Full-Text might be a lazy upgrade.
Informative presentation. Looks like they have spend a good amount of time
working on making this a smooth process.
Rather than going to this morning presentations, I'm going to hang out in the Data Cabana and see what happends. There's been some very interesting questions and answers going on here. Stop by and chat!