I've already blogged about this topic in some depth (1 2 3 4), so I've highlighted a few notes of interest from Ramachandran and Makesh's session:

Restricting the CLR
Not all of the Base Class Libraries in the .NET Framework are available in the database. Functionality "not applicable" to the database is disallowed. Most of System.* is available, but many non-applicable services are not supported, such as System.Windows.Forms, System.Drawing, System.Web. This is achieved through a "fusion loader hook" in CLR hosting. How does that work? When you look up an assembly today, the CLR finds it using Fusion. SQL Server 2005 intercepts that search for the hosted environment and loads it from the database itself instead.

Even in supported assemblies, some APIs are not available in SQL. This is achieved through a new HostProtection attribute in the CLR (this is extensible by third-party libraries). You can reflect on an assembly and list these methods, and a full list will be available in the SQL Server 2005 documentation. Potentially unreliable constructs are disabled, such as thread creation, socket listening, and finalizers.

Tips for using the In-Proc Provider

• Use SqlPipe.Execute() instead of cmd.ExecuteReader(). This allows you to simply stream back data without marshalling the results into the managed environment.
• Use SqlDataReader instead of T-SQL cursors: it's the fastest way to enumerate rows.
• Use SqlExecutionContext for static SQL patterns to improve performance.

Security Tips for DBAs

• Use sp_configure 'clr enabled' to enable the CLR hosting in the engine - it's off by default
• Use the CREATE ASSEMBLY permission to control the creation of assemblies
• Use the REFERENCES permission to protect schema binding on assemblies
• Use the EXECUTE permission to regulate who can execute routines
• Catalog view security works as expected
• Use the EXTERNAL ACCESS permission to regulate who can create external access code
• Recommendations: use safe assemblies - this is the default and is closest to the T-SQL model. If you are going off the box, you need to question the impersonation strategy. By default, SQL does not impersonate. But you can use EXECUTE AS to specify a specific SQL context for impersonation.
• Avoid unsafe assemblies - they can compromise system reliability and correctness.

Michele Leroux Bustamente presented an insightful guide to the extensible capabilities of the ASP.NET architecture.

IIS and ASP.NET Configuration
HTTP requests in IIS arrive at the appropriate extension: aspnet_isapi.dll or asp.dll. ASP.NET requests get passed through to the ASP.NET runtime. It's this application that processes incoming requests.

In IIS 5, the inetinfo.exe process hosts aspnet_isapi.dll. This passes requests into the managed ASP.NET runtime to invoke an HttpHandler. There is a thread that processes this request, running within the ASP.NET worker process inside an appdomain. In IIS 5, therefore, every application has its own appdomain. Every time an HTTP request comes in, it gets its own thread from the thread pool.

In IIS 6, the process is slightly different. The kernel (http.sys) directly passes requests to the correct pool within the HTTP runtime, thus bypassing a step. We can now have multiple processes running (w3wp.exe).

Configurable pipeline components include HTTP handler factories, HTTP handlers, HTTP modules and SOAP extensions. You can configure any or all of them together as part of the round-trip.

HTTP Modules
These are objects that can interact with every HTTP request, and use an event-driven model to interact with web applications. You can create custom modules that listen to incoming requests and process things such as custom authorisation, error handling, diagnostics, session state management or diagnostics. Shipped in the box are: WindowsAuthenticationModule, FormsAuthenticationModule, PassportAuthenticationModule, FileAuthorizationModule, SessionModule and others. In the same way as for handlers, you can configure modules through a <httpModules> section in machine.config. All modules implement IHttpModule and add an event handler for events occurring within the Application object. Within a event handler, you can access the HttpApplication object - sent as the event source.

Michele showed how you could create a custom authentication module so that guest users could gain access to a site without being fully logged in, using the module to give non-logged in users a set of roles and set the auth cookie.

HTTP Handler Factories
HTTP Handler factories return an HTTP handler to process incoming requests. You can use this to intercept requests for specific resources or format custom types. You could even use an HTTP Handler Factory to create a request for a file that doesn't even exist, and construct it on the fly (a classic example of this is the trace handler that deals with .axd requests). All handler factories implement IHttpHandlerFactory, which has two methods: GetHandler() returns an IHttpHandler given the HttpContext and ReleaseHandler() cleans up any resources. The handler factory could even return different handlers depending on the context (for example, different user roles).

machine.config has an <httpHandlers> setting to select which class factory is used to handle incoming requests. For example, .aspx files are handled by System.Web.UI.PageHandlerFactory and .config files are handled by System.Web.HttpForbiddenHandler (which prevents their download). The default configuration in machine.config can of course be overridden in web.config to alter settings at the application level. For example, you might choose to handle XML files and apply some settings, or disallow additional file extensions by routing other calls to the HttpForbiddenHandler.

Handlers themselves implement IHttpHandler - an interface with a ProcessRequest() method to do the work, and an IsReusable read-only boolean property. To access the session from a custom handler, you also need to implement a marker interface IRequiresSessionState in the HTTP handler. There's also an asynchronous Begin/End design pattern that enables you to offload incoming requests to their own thread. But this may not improve performance as the work still needs to be done!

Don't forget that you need to add a mapping to the aspnet_isapi.dll extension if you want it to handle file types that aren't already passed to ASP.NET. Otherwise the file will be handled by inetinfo.exe directly and will never be seen by the handler.

You can also use handlers without having to modify a config file by creating an endpoint with an .ashx extension. This allows you to create a web handler class using a <%@ WebHandler %> attribute - the handler factory is already provided for you.

SOAP Extensions
SOAP extensions provide a method to interact with incoming web service requests. You can create your own custom SOAP extensions in a pipeline process to handle the data en route between aspnet_isapi.dll and the web service method. This is how WSE is implemented.

Incoming requests are routed to an HTTP handler that derives from WebServiceHandler. (There are four variants to handle aspects such as sessions and asynchronous communication.) The handler routes calls to a SOAP extension before and after execution of the web service method.

To implement a SOAP extension, you can either create an extension class that derives from SoapExtension, or create an attribute class that derives from SoapExtensionAttribute. The extension class has a ProcessMessage() method (allowing the message to be handled) and a ChainStream() (allowing the stream to be edited). These extension classes are once again invoked using web.config or machine.config.

More samples here and here.

SQL Server 2005 contains a completely rearchitected web services layer. Rather than relying on IIS to provide the web protocol stack, Yukon merely uses HTTP.SYS (the new kernel mode driver in Windows Server 2003 and Windows XP Service Pack 2). This means you don't need IIS to be installed, which will be a great boon to many database administrators.

Architecturally, to generate a web service, you create an endpoint to a stored procedure that can be accessed as a remote procedure call or via batch processes. Lastly you can request WSDL from this endpoint, at which point the server invokes an internal stored procedure to generate the relevant WSDL.

Endpoints
What is an endpoint? It's simply a point of entry into SQL Server. As an abstraction, it combines both a transport and the payload, in two separate sections. For example:

   CREATE ENDPOINT Demo 
   AS HTTP ( -- transport
      authentication = (integrated), 
      path = '/sql/demo', 
      ports = (clear), 
      state = started ) 
   FOR SOAP ( -- payload
      webmethod 'http://test.ms.com'.'testproc1' (name=demodb.dbo.testproc1),
      batches = enabled, 
      wsdl = default )

So an endpoint has a name, a set of options for the transport (e.g. the URL), and a set of options for the payload (e.g. the methods callable at that URL).

Endpoints are therefore not protocol-specific, but could be extended to include other transports such as TCP. Anonymous access to endpoints is not supported - all calls must use basic, digest or integrated authentication, or via SQL Auth using WS-Security.

Generating WSDL
The WSDL describes the application (services) that an endpoint exposes. A request for WSDL follows a similar format to ASP.NET - the URL including path, suffixed with ?wsdl. Out of the box, we support two types of WSDL: either using full rich types or a simplified version that increases interoperability (use ?wsdlsimple instead).

You can also extend this by creating your own custom stored procedure to generate WSDL, by setting the wsdl property in the payload element of the DDL to point to the sproc. Internally you can use sys.http_generate_wsdl_default to generate the original WSDL and then customise it. There will be a KB article on generating custom WSDL  for different interoperability clients such as JBuilder or GLUE as well as Everett and Whidbey.

The type for the SOAP response you receive varies depending on what kind of result is being returned. For a user-defined function, the result is simple, for example varchar(100). For a stored procedure or batch execution, however, there is no fixed schema for what will be returned, so what does the result set contain? SQL Server 2005 generates a ResultStream element that can contain one or more of the following types as part of the stream:

• SqlRowSet - the output of a SELECT statement
• SqlXml - the output of a SELECT ... FOR XML statement
• SqlMessage - the output of error, warning, print messages sent to the output pipe
• SqlRowCount - the number of rows affected
• SqlResultCode - the return code for a s/proc

Note, for errors and faults, a standard HTTP error or SOAP fault is returned.

Accessing Endpoints

Sessions can be enabled on an endpoint, and are analogous to cookies. Sessions allow multiple executions to share the same context despite HTTP being an inherently stateless protocol. Interestingly, you can terminate and reopen a session within the SOAP header: this is different to TDS, where a session is fixed to last as long as a connection.

You can access a SQL endpoint from VS.NET by adding a web reference. If you enable batches, you can send dynamic SQL using a sqlbatch method; other functions and s/procs will appear in the proxy using their name generated in the CREATE ENDPOINT statement. To make it easier for your client, you can set a hint on the endpoint that the result stream from a s/proc will only contain rowsets: FORMAT=ROWSETS_ONLY. Rather than returning an object array, the result from the execution will then be a DataSet.

Visual Studio is moving from a developer-focused to a development-focused environment. Visual Studio Team System is an extensible life-cycle tools platform that helps software teams collaborate to reduce the complexity of delivering solutions. The three design goals were:

1. Reduce complexity of managing software products;
2. Facilitate communication across a project;
3. Enable a viable third-party ecosystem for add-ins.

VS Team Architect, VS Team Developer and VS Team Test are three role-based products that will address needs such as application and class modelling, code profiling and analysis and load testing. There will also be a server product: Visual Studio Team Foundation that will support change management, work item tracking, reporting, project management and integration services. Several of these components also integrate into other Office System products - for example, VSTS includes an add-in for Microsoft Project that allows a project manager to integrate their planning with the development project server.

The Community Tech Preview May bits (as distributed at TechEd) will include the VSTS client tools, but won't include the server components at this stage. VS 2005 Beta 1 will be the Professional Edition and so won't contain VSTS at all, however the next CTP after this (July/Aug) will include a more complete implementation of VSTS.

Full details of the Team Server product line can be found online as part of MSDN.

Tips for OneNote

• Create separate work and personal folders and store everything else below that level
• Can create custom note flags and then search easily for them; for example, to store items for follow-up with your line manager or (for academic use) to highlight whenever a lecturer says "this will appear on the exam"!
• When you send a OneNote page as email, it sends both the OneNote page as a .one file and also as HTML in case the recipient doesn't have OneNote installed.
• Press Windows + N to create a new side note. This can be handy when you're in another application and you want to take notes in a smaller window without having to tile windows: side notes have a reduced UI to minimise clutter. Side notes are also ideal for taking "Post-It" style notes such as telephone numbers.
• You can also drag and drop from a web page in IE into a OneNote page and it automatically adds the URL that it came from.
• Use audio notes to take a recording of a meeting and have this bookmarked against what you're typing - you can then bring back the audio that matches your notes with a single click.

New SP1 Features

• Screen clippings allow you to do a screen capture of a selected area and have it dumped directly into OneNote.
• Insert Outlook Meeting Details automatically dumps the calendar details from Outlook at the top of a page: this makes it really easy to (for example) search notes for the attendees.
• You can create a peer-to-peer shared session: great for status meetings, where everyone can add updates for their own project area. This can also be used in a read-only mode for broadcasting your own notes out (for example, in a presentation).
• You can insert an Office document (along with a link to the original document) and then annotate that document using ink or text.
• Video notes - they work in the same way as audio.
• Create a group notebook by sharing out a directory containing a notebook; if someone is writing in the section at the time, you see a read-only view until they finish or time out. You could use this to share a single notebook across multiple machines.
• There's a Pocket PC and Smartphone import feature that allows notes and voice notes to be imported into OneNote. This is unidirectional, however.
• COM Extensibility API - one class (CSimpleImportClass) containing two methods: Import and Navigate. There's a preview of the documentation available on the web. The Import method takes an XML document that allows you to specify a page (keyed by GUID) and even insert ink.

Insights

• When you search for handwritten text, OneNote automatically searches for alternates, so even if the text hasn't been properly recognised it shouldn't stop it from working effectively.
• There was a lot of internal debate about whether OneNote would be accepted without a manual save button; in the end it turns out that not many people found this hard to deal with, and that most customers quickly got used to the auto-save feature.
• The OneNote team use Feedster to search blogs for entries relating to OneNote - so whenever you blog about OneNote the team will automatically read it as customer feedback!
• The development team are "thinking" about a OneNote viewer - presumably a free redistributable for sharing notebooks with users who don't have it.