The upcoming SQL Server 2008 provider for Powershell was a big hit and sparked quite a bit of discussion when I talked about and demonstrated it during TechEd/Developers last week. At the time I thought that this might be a good discussion topic for database administators, and, after asking around, a room was found, and I'll be re-presenting this session, entitled "Using Windows Powershell with the SQL Server 2008 Provider and SMO" here at TechEd/ITForum tomorrow (Friday) at 13:30-14:45 in room 131.

If you do administrative tasks with Powershell or are interested in SQL Server, drop by and bring your opinions. See you there.

I'm up early this morning for a repeat of my T-SQL in SQL Server 2008 talk. Things really went well for the last two days, folks really seemed to like to see lots of actual working code, albeit my examples are always "minimalist". There was a lot of interest around the Spatial data talk and some discussion of upcoming and ongoing projects that will take advantage of this new functionality. The increase in interest could be been caused by announcements about spatial data at the conference and also the opening of the new Spatial Data section on the SQL Server website. My SQL query tuning segment generated a lot of interest too.

If you're around the conference this morning and interested in SQL (and who isn't?) stop by and say hi.

Today is day 1 of TechEd/ITForum in Barcelona. I have a bit less hectic of a workload at this one, a total of 4 sessions, 3 of them being "interactive sessions" (this year's word for chalktalk). Today's session is going to be on T-SQL enhancements in SQL Server 2008, at the late-in-the-day time of 5:45-7:00pm. C'mon out and I'll see you there, or, if you can't make the late session, I'm repeating it on Wednesday.

Tuesday, I'm doing my only breakout, the "day's worth of material in 75 minutes" on SQL query performance tips and tricks. And, in the afternoon, a session on spatial data (and indexes) in SQL Server 2008. See you there...

Actually yesterday, but today was the first time I'd had a chance to write about it.

Having done quite a bit with what's now being called "traditional web services", my first impression of REST were, I'll have to admit, the thought that it was web services without schema. I kinda like my metadata; it's always been irritating that stored procedures do not store ANY metadata on the number or shapes of the rowsets returned, only metadata on the parameters are stored. The closest that ANSI comes is allowing specification of the number of rowsets, a piece of the standard the SQL Server doesn't implement. So web services without schema seemed less useful than web services with.

But one of the up-and-coming data access technologies at Microsoft is Astoria, which is described as "a REST-ful set of interfaces to relational database (and other) data". It's causing a lot of excitement.

After the conference I mentioned this to an old friend, Jon Flanders, as he was wearing his "real programmers care about URIs" (or something close) teeshirt. Asked him to explain the zen of REST. To summarize, I got the impression it was all about the location specification and using HTTP verbs like GET/POST/DELETE to effect "state transfer" operations. And, although its not very common, metadata can be specified using WADL (Web Application Description Language). So specifying the location is perhaps like a "connection string" to the service? And to the data the application interacts with?

I'm still a bit skeptical of the (seeming) typelessness and contract-lessness of it all, the "HTTP is the only protocol"-ishness, and IIRC, I can find out the location of a traditional web service using WSDL's soap:address element's "location" attribute in the service portion. But at least I have an somewhat of an understanding of what all the buzz is about.

More on Astoria in future posts.

Whew! I had a full week at TechEd Barcelona, just getting around to finishing up writing about it. It was great to get out and see everyone, there were a lot of familiar faces. Thanks for showing up and saying hello. I fully enjoyed each and every presentation and interactive discussion.

A special thank you to Gunther Beersaerts for allowing me to attend and present. Got to meet a lot of "the old gang" as well as some people who I'd only known through their blogs, like Ward Pond and Johannes Kebeck. And I got to attend the Belgium/Luxembourg country drinks party, where I had some great technical discussions over beers.

I don't have exact figures, but because I had the same room for each breakout, I "counted" interest by counting the number of people who showed up. Transactions and Optimizing T-SQL still rule, although there was almost a full room for SQLCLR. And there was more than I expected for Service Broker and quite a few folks for SQLXML. Packed a double "interactive" room for the Powershell in SQL Server 2008 presentation/discussion, although the liveliest discussions were in the "OLE DB and ODBC" and "tell us where it hurts in SQL Server" sessions. The ODBC folks were happy they aren't being forgotten, it was enlightening to see the numbers using OLE DB and ODBC. Thanks to John de Longa of DataDirect for his input on ODBC/OLE DB and Elisa Flasko for helping me moderate the discussion. A number of people seem quite worried about the advent of LINQ to SQL/EDM and what they see as the upcoming deluge of dynamic SQL and its effect on the plan cache. I'll have more to write on that subject soon.

Next week is ITForum. I won't be quite as busy, but I will be doing a few cool talks on Spatial Data in SQL Server, T-SQL improvements in SQL Server 2008 and T-SQL query tuning. See you there.

Today, I'm finishing up my (many) talks at TechEd/Developers' Barcelona with a breakout on best practices for transactions and isolation and leading an interactive discussion on OLE DB and ODBC. I love talking about transactions and, in addition to demonstrating all of the isolation levels SQL Server supports their behavior and repercussions, I'll even show the fairly esoteric "MARS batch-scoped transaction".

The "OLE DB and ODBC" discussion should be good. After seemingly moving away from ODBC since the late 1990s (actually they never moved away) there has been a movement toward embracing the ODBC lifestyle and reiterating support in the pre-SQL Server 2008 timeframe. I'll be co-hosting this one with Elisa Flasko of the Microsoft data access team.

See you there!

Tomorrow (Wednesday) I'm changing to multiple best practices session in a single day. In the morning, I'll be speaking about T-SQL query plans and "why queries run slowly" in a session called "Best Practices for Procedural SQL Code". And in the afternoon, a different data model and query language (but the same optimizer) in "Best Practices for XML data type and XQuery". Be sure to drop in if you're interested in either of those topics.

Today's surprise was a preview of the upcoming SQL Server 2008 Powershell Provider. It seemed to be a big hit with the folks that came to the session and promises to be yet another step in consolidating administrative tasks in a common form by using Powershell.

See you at TechEd/Developers Barcelona!

Today is day of SQLCLR (that is, use of .NET CLR code in SQL Server) for me at TechEd/Developers in Barcelona. I'm doing two talks on it: one before and one after lunch. The before lunch talk is a breakout-style talk, illustrating with code when its a good idea to use SQLCLR and when its not a good idea. I'll also touch on some SQL Server 2008 SQLCLR enhancements. Questions and comments at end of the talk.

The "after lunch" talk is more of an open discussion. Some folks think that SQLCLR is one of the fine features of SQL Server 2005. Other just want to know: "how do you turn that off, programmers will just abuse it". Please bring your opinions along (don't check them at the door), and contribute to the discussion. I think SQLCLR is one of the most misunderstood features of SQL Server.

After the SQLCLR discussion, I'll be doing an interactive session on SQL Server and Powershell. These two are integrated by means of a .NET library called SMO, though Powershell can also be used with SQLDMO. They'll be a little surprise in this one, but you'll need to be there to find out.

See you there!

I like free, standard, sample databases. One that I've been looking at for quite a while is the Mondial database currently available at Institute for Informatics Georg-August-Universität Göttingen. This set of data (from an old CIA World factbook and other sources), is available not only as a relational database, but as XML, RDF, and even F-logic.

The problem with using this with SQL Server has been the lack of a DATE datatype with the appropriate value range. The DATE datatype is used as "Date of Independence" and some countries have independence dates before 1753. So you'd have to represent it as a VARCHAR. Yuk.

With the new datatypes in SQL Server 2008, this is do-able without compromise, so here it is. In addition, since many of the locations (in two tables) come with latitude and longitude columns, I've added a GEOGRAPHY column, so when CTP5 comes out we'll have something to use GEOGRAPHY with.

Source scripts included. Enjoy!

Ever since the hierarchyid data type was introduced in SQL Server 2008 CTP4, there's been a fair amount of discussion about renaming some of the methods of the data type to make them a bit more intuitive. In addition, there's been discussion about "missing" methods that don't exist in the hierarchyid data type. Well...

Because hierarchyid is a .NET-based system UDT, you can implement these "missing" methods yourself. There's a couple of ways to go about this:

1. Use your own assembly that uses the hierarchyid (that's Microsoft.SqlServer.Types.SqlHierarchyId to you) as parameters. Because its a "normal" SQL Server type, you can use you use it anywhere you can use a "native" SQL Server data type, like DATE.
2. Code your own UDT that inherits from SqlHierarchyId. Perhaps call it HIERARCHYID2, to further upset those folks who are offended by DATETIME2.

Wow. Did you say inheritence from a system UDT? Before we go down this path, bear in mind that this works with the CTP4 version of SQL Server 2008, but there's no guarentees about it working in the next CTP or release. I've heard nothing to that effect, but you never know. I've coded up a little stub and it seems to work, but...

I thought that T-SQL doesn't support UDT inheritence. Technically, it doesn't. That is, the SQL Server system catalogs (sys.types, etc) don't track UDT inheritence. This means that in order to allow T-SQL to "see" methods and properties in the base class, you need to write methods that do nothing but delegate to the base class. Because all the methods that you care about are public (by definition) you can do this. Simply override the methods that you don't want to pass through. I wrote about this a long time ago, see this blog post from 2004.

A few other considerations. Your UDT must be a class, not a struct, of course. Your UDT must implement IBinarySerialize and use UserDefined serialization, because the base class does. Microsoft.SqlServer.Types allows partially trusted callers, so your assembly should be able to work with permission_set safe. Finally, remember that SQL Server does not allow you to expose overloaded methods in assemblies, although you can use them in your internal implementation.

I'm working to expand the stub implementation, so if anyone has some neat ideas for derived methods and properties, I'd be interested in hearing from you. And, when CTP5 comes out...GEOGRAPHY2 anyone? If it doesn't do exactly what you want, change it.

By the way, I've wondered if deriving from a system data type isn't analogous to using undocumented system stored procedures, a practice which everyone I meet seems to disapprove of, but they do it anyway. I'm think at this point that it's more analogous to using a documented/supported system stored procedure, like sp_spaceused, in your own script. They can't change HIERARCHYID post-SQL Server 2008 without break all existing code. So you're safe, I think. Other opinions?

There's been some rumors going 'round about the immenent release of SQL Server 2008 CTP5. OK, maybe I've been asking around too. The main reason for the rumors is that there was a published CTP timetable at one point and its getting to be about that time. The other reason is the plethora of sessions, both here in Barcelona at TechEd/Developers and TechEd/ITForum and at DevConnections in Las Vegas (where some of my SQLskills collegues are this week) about features that won't appear until CTP5. Looking at the calendar of talks I can easily spot talks on Spatial Data and Filestream storage, two of the most compelling features in the next release, due in CTP5. I'll even be doing a spot of my own on Spatial data next week at TechEd/ITForum.

There have also been a number of postings by Microsoft employees about the upcoming CTP5 features, some of them including code. If you're at TechEd, don't miss Johannes Kebeck's talk on Virtual Earth, which may have some tidbits on usage of the SQL Server 2008 spatial data types. He's been working with the spatial data types since they were just a gleam in the SQL Server team's eye. Unfortunately, I'll miss it, because I've got a talk at the same time. Maybe Johannes can fill me in. And Michael Rys' talk on spatial data in SQL Server 2008.

But back to CTP5...the answer so far is just "soon, wait and see". But do attend the sessions and I believe you'll agree its something worth waiting for.

This week I'm at TechEd/Developers Barcelona. I arrived Saturday and I'm always delighted, being a Portland Oregon resident, to see the sun, even in winter. It's supposed to be sunny and mid-upper 60s for the next 10 days and that's good because I'll be staying on for TechEd/ITForum next week too. Oh boy, my sun quota for the rest of the year...

I'm going to be doing a breakout or two and some "interactive sessions" (that's what chalktalks evolved into) every day from Mon-Thurs. Talking about best practices and lessons learned for developers in SQL Server 2005. I've been working with developers since before SQL Server 2005 shipped and now that we've had a few years to practice I feel comfortable talking about it. In each session, there will be a "look forward" to SQL Server 2008 new features.

Today is day of Service Broker, one of the least understood features of SQL Server 2005. In the best practices session I'll be rewriting some of my older code to correspond with best practices and demonstrating how to use broker for performance. Performance, robustness, and co-location with the data are broker's best features when compared to other ways to implement a service-oriented architecture. 3500 transactional messages/second on commodity hardware is nothing to sneeze at.

After that, I'll be "hosting" and interactive session on Service Broker lessons learned. We'll discuss real world use cases, see what design issues folks have been running into and how they've been addressed. Bring your questions and comments about your own projects to contribute or just come along to listen in.

Later in the week, I'll be covering T-SQL queries and plans, SQLCLR, transactions and isolation, XML data and query, and have some discussion sessions on two of my favorite topics: native data access APIs (OLE DB and ODBC) and SQL Server/Powershell integration. There will even be some surprises: you'll have to show up to find out.

See you there!

In the last few years, I've done a few talks at various conferences on the integration of SMO (SQL Server Management Objects) and Powershell. My friend and co-author of the SQL Server 2005 books, Dan Sullivan, got me into using Powershell and SMO and has written quite a number of excellent blog entries and articles about it. Because SMO is just another loadable .NET library, they're a perfect fit. For TechEd US, I even wrote a simple powershell provider that makes SQL Server look at a file system (NavigationCmdletProvider through the database objects), and showed the code.

I'll be doing another chalktalk at TechEd Developers, Europe, next week... with a twist. A few weeks ago, I'd been informed of plans to include a Powershell NavigationCmdletProvider provider that's shipped as part of SQL Server 2008! Really! It won't be in the very next CTP (due soon, according to the original official CTP schedule from long ago), but, barring untowed circumstances, it will be in the final product.

So if you have any interest in SQL Server and Powershell, I'll see you in Barcelona at the chalktalk. It will be a good time, promise.

I've been talking with folks for (it seems like) a long time about modeling sparse attributes in a relational database. Seems like I run into a new design where there's a need for sparse attributes every few weeks. If you remember the "hardware store" example (where each new sales item may have unqiue properties), that's just the tip of the iceburg. Basically, your choices boil down to: Sparse Columns (new column for each new attribute), Sparse Tables (new table for each new set of related attributes, if they are related), Entity-Attribute-Value (the "traditional" design, often eshewed because of scalability concerns), and XML (attributes model that sparse attributes, elements model the common attributes).

Last week I ran into a person with a modeling decision like this. He also informed me that he'd tried the sparse table design and ran into SQL Server's hard limit of 256 tables in a join (if you want all the sparse attributes for all products?). Wow. I can't image what a 256 table join would look like, and how the query processor would have time to load all the statistics for this one. He'd also run into the 1024 column limit with sparse columns. I told him to wait on that one; SQL Server 2008 will have sparse columns.

The XML design is interesting too, because you can do a search to which rows have which sparse attributes and spit out the right data.  It's what the XML VALUE index was designed for. And hoist the common attributes to persisted computed columns for best query perf. Seems that sparse columns may have that covered too, if its implemented like what was shown at TechEd US. There would be an optional column defined as "XML COLUMN_SET FOR ALL_SPARSE_COLUMNS". A value index on this should do the trick for a fast search too.

I (and quite a few other folks, if my networking is correct) can hardly wait...

I'll have to admit it, when I first saw that SQL Server 2008 was adding spatial data support, I thought of it as a niche. The province of geographers, cartographers, and maybe a few others. Complex, involving a lot of higher mathematics, each province having their own geographic encoding, and so on... And that level exists, to me it's the production of spatial reference data. When I think of spatial reference data, I think of map data you'd buy from ESRI and data posted by government agencies. Or made available by utilities, so you don't hit a power cable while digging in your garden. As opposed to spatial line of business data.

Now, before you go searching your LOB application for latitude and longitude columns, how about looking for columns that contain "address". It's a short hop from address to lat/long by using a geocoder. The one I used is the MapPoint web service. Now you have line-of-business spatial data. I'll bet every app has a field that contains address. And how about looking for the nearest salesperson for a potential customer? Or the nearest warehouse? Mapping programs like Virtual Earth, Google Earth, and Yahoo Maps can give you general business information and maps but how about encoding information in your own business?

I'm quite excitied over this upcoming "niche" feature and think it could make its way into each and every application. That's spatial data "for the masses" (so I'm not the greatest at sloganizing, forgive me).

A couple of people have been asking and, in preparing for my upcoming talk on spatial data on SQL Server 2008 at ITForum in Barcelona in a few weeks I got to ask about using the new system UDTs types on the client. In an upcoming CTP release there will be an MSI installer file specifically to install these types on clients. The appropriate files are installed and assembly (Microsoft.SqlServer.Types.dll) registered in the GAC.

Since these are .NET data types, these (HierarchyID, Geography, Geometry) are easily usable in SQLCLR functions and procedures too. No possible data type mismatches or nullability (the new types implement INullable and have a static property to return a NULL instance) concerns.

Just saw Aaron Bertrand's post from PASS on SQL Server 2008 Change Tracking vs Change Data Capture (change data capture (or CDC) is in the current CTP; change tracking is not). I'm not at PASS this week myself, but home while the house is being re-roofed. His post seems to confirm something I'd suspected all along.

Change tracking seems to go hand-in-hand with Sync Services for ADO.NET. I've been following Sync Services for a while; it's a set of libraries for controlling and implementing synchronization for disconnected database applications. Its current shipping vehicle is the Visual Studio Orcas Beta 2 release, along with SQL Server Compact Edition version 3.5. The fact that SQL Server 2008 Change Tracking provides a mechanism to keep track of which user (the sync OriginatorID) made a change (CDC doesn't) and also seems to provide automatic change table management for DELETEs (CHANGE_RETENTION) and a mechanism to "get the set of changes that have occured from a baseline" (the "sync_last_received_anchor" in sync services) makes Change Tracking line right up with what Sync Services requires.

Although Aaron mentions "offline stores like Outlook in cache mode", SQLCE is an exciting offline store because its currently deployed in places like Windows Media Player, Zune, Media Center PC, and more. SQLCE runs on mobile devices and desktops. Sync Services isn't available for mobile devices yet, but is said to be "in progress".

When you set up a Sync Service app, you currenly must make changes to the database (triggers, "tombstone tables for deletes", and such) referred to by the "ServerProvider", in order to track the information Sync needs. The Sync "ServiceProvider" architecture layers over/shares concepts with the ADO.NET provider model. But you don't have to make changes for the SQLCE 3.5-side (SQLCE is the only current "ClientProvider" that Sync Services supports) because, "support for sync is built in". Well...maybe it's built in to the server too, with SQL Server 2008. Bet we'll see (at least one) demo with Sync Services when the Change Tracking feature ships.

I'll be at TechEd and ITForum in Barcelona in November, doing sessions on SQL Server 2005 and SQL Server 2008. One of the TechEd sessions is called "To ODBC or to OLE DB?" and is a discussion of using ODBC and OLE DB is applications these days, whether one or the other is "better", if you should convert existing applications, etc. On that note...

Back at TechEd US, I'd spoken with Chris Lee, who's in charge of SQL Native Client. That's the OLE DB provider and ODBC driver that ship with SQL Server. SQL Server 2008 has a new version of the provider and driver, and when I'd asked if support for the new features (like the new DATE/TIME data types and table-valued parameters) Chris had not only replied "yes", but given me a demo of using table-valued parameters from ODBC to show off and post if I wanted to.

Here's the code. To build and run it:

1. Use the Visual C++ compiler that comes with Visual Studio 2005 or Visual Studio 2008 beta. I used VS2008 Beta 2.
2. Convert the project if needed. I ignored the warnings about 1 source file not being converted.
3. Make sure that sqlncli.h and sqlncli10.lib are available to the compiler. They're in C:\Program Files\Microsoft SQL Server\100\SDK\Includes and Lib, respectively.
4. Install SQLNCLI from the SQL Server 2008 CTP distibution on the client machine. Just run the SQLNCLI.msi in Servers\Setup.
5. Setup an ODBC System DSN from Control Panel/ODBC Administrator named TVPDemo. It must use the SQL Server Native Client 10.0 ODBC driver. Use the database of your choice, the demo will run DDL to create the database objects you need.
6. Compile the demo, set breakpoints and walk through the code

Cheers! See you in Barcelona!

I received mail today from the SQL Server Compact Edition folks at Microsoft on my blog post on using SQL Server CE 3.5 beta, Visual Studio Beta 2, LINQ, and EDM. The current plan is:

1. Visual Studio 2008 will not ship with LINQ to SQLCE designer support. SQLMetal works just fine with SQLCE, though, as I'd mentioned.
2. There is planned future support for using SQLCE with EDM and LINQ to Entities when EDM ships after Visual Studio 2008 (VS 2008 SP1?).
3. There is a fix in the works for the FK issue that I had with the SQLCE Northwind sample database and SQLMetal.

Along these lines, I asked about the integration of LINQ to SQLCE with the updateable, scrollable, cursor-like behavior of SqlResultSet. Because SQLCE is an embedded database ("the engine" loads into your application) using the DataSet with SQLCE programming adds a layer of buffering (read: memory allocation and data copying) between the data and you. SqlResultSet is a perf win over using DataSet, and using LINQ to SQLCE or EDM to SQLCE, although they don't use the DataSet, doesn't allow in-place updating like SqlResultSet does. And they do use memory allocations rather than read directly from the SQLCE database. The current LINQ to SQLCE doesn't support SqlResultSet-like behavior yet, but perhaps in future...

So, people always ask... now that .NET Framework 3.0 installed on my system and there's going to be a new version of .NET that includes LINQ, what version of the framework will SQL Server load now, in SQL Server 2008? Does 2.0 still load? Or does it load 3.0 or 3.5?

Theoretically, and I've written this in books and previous blog entries, SQL Server loads the most recent version of the .NET framework. .NET service packs therefore affect SQLCLR. Especially if they contain updates to say, System.Data.dll. Or System.Xml.dll. Or [your favorite "approved" library goes here].

At present, one .exe can load one and only load version of the .NET framework. ASP.NET, for example, supports multiple versions with multiple worker-processes, that is, multiple .exe-s. Perhaps in .NET 4.0, we'll be able to run multiple .NETs in a single process. Perhaps not. The "main" .NET assembly is mscorlib.dll; this contains quite a few of the "main" .NET namespaces/classes. Namespaces don't necessarily correlate to DLLs, in Visual Studio 2008 version of .NET "System.Data" will be spread across a few DLLs. To me, there is "a new version of .NET" when there is a new version of mscorlib.dll. That's not the way its represented normally.

.NET 3.0 did not replace mscorlib.dll. Or System.Data, System.Xml, ASP.NET, or others. In my C:\WINDOWS\Microsoft.NET\Framework\v3.0 directory there is no DLLs, only three subdirectories: Windows Communication Foundation, Windows Workflow Foundation, and WPF. So, if you have a machine that "comes with .NET 3.0" it also must come with (at least) .NET 2.0.

My machine (with SQL Server 2008 and Visual Studio 2008 Beta 2) has a .NET 3.5 directory in it. And a subdirectory named "Microsoft .NET Framework 3.5 (Pre-Release Version)". Neither one has an mscorlib.dll in it. It's mostly Visual Studio-related DLLs and utilities.

The main additonal assemblies for 3.0 and 3.5 (like LINQ, for example) actually live in C:\Program Files\Reference Assemblies\Microsoft\Framework. V3.0 and V3.5. That's were the main action is, except that this is all mostly hidden by the presence of and registration in the global assembly cache (GAC).

So, an instance of SQL Server 2008 will load mscorlib.dll, version 2.0.50727.42, as always. However, in SQL Server 2008 (and 2005 for that matter, if it ships as a .NET upgrade through Windows Update), you'll see a new version of System.Data.dll, version 2.0.50727.1378. Or, perhaps, we'll be able to hold the versions constant, at the possible expense of interop between SQL Server 2005 features that use .NET. Like SSIS, SSMS, SSRS, and so on.

Got it?

Let's talk about clients and SQL Server 2008. First, a little history...

Every new version of SQL Server seems to include a new feature that requires a change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by both SQL Server and Sybase. Since version 4.21, SQL Server's version of TDS and Sybase's version have "grown apart". Because TDS includes version negotiation you can still use old libraries to talk to newer versions, albeit at reduced functionality.

Even if they don't update the TDS protocol, there will always be new features that require changes to the client stack at some level.

SQL Server comes with support for the following client stacks:
ODBC - Open Database Connectivity, related to the ANSI SQL standard, vendor neutral
OLE DB - a COM-based vendor neutral library
ADO.NET - a .NET-based vendor neutral library

ADO (classic) is an IDispatch-friendly library over OLE DB
LINQ to SQL and Entity Data Model use ADO.NET to talk to the database. Remember LINQ to SQL is actually LINQ to SQL Server at present.

In addition, Microsoft ships a JDBC (which does not stand for Java Database Connectivity, the docs say so driver separately. DBLIB also still works, but not shipped with SQL Server any more. It's functionality is limited to features that existed in SQL Server 6.5, IIRC. Other vendors (e.g. DataDirect) ship SQL Server clients too. DataDirect licenses the TDS stack.

Before SQL Server 2005, providers and drivers were part of MDAC. MDAC (Microsoft Data Access Components) was once a separate install, but now its part of the OS. TDS libraries were separate DLLs (e.g. DBMSSOCN.dll). This stopped in

SQL Server 2005, and there are two main distribution vehicles:
SQL Native Client (SQLNCLI): OLE DB, ODBC, and network libs
ADO.NET System.Data.dll: SqlClient provider and network libs

So how does this happen in SQL Server 2008? For OLE DB and ODBC, there is a new version of SQL Native Client, version 10. Since MDAC is now part of the operating system, if you install SQL Server 2008 over SQL Server 2005, you'll now see three ODBC drivers, on my CTP4 + VS 2008 beta 2 system:

SQL Server          version 2000.86.1830.00
SQL Native Client   version 2005.90.3042.00
SQL Server Native Client 10.0 version 2007.100.1049.14

For OLE DB there are three providers:
Microsoft OLE DB Provider for SQL Server
SQL Native Client
SQL Server Native Client 10.0

For ADO.NET, the situation is a bit more interesting. There's a revision to System.Data.dll to include the new functionality. The new version currently ships with Visual Studio 2008 Beta 2 and its version number currently is 2.0.50727.1378. It simply replaces the version that's installed on the system (and in the GAC) at the time. There's only one ".NET 2.0" version registered in the GAC, as "Version Number 2.0.0.0". Hmmm....seems very "MDAC-like with MDAC as part of the operating system" (that is, the operating system includes .NET 2.0).

If you want to use the new SQL Server 2008 functionality, like date, time, datetime2, dateoffset, table-valued parameters and large UDTs/UDAggs (and perhaps FILESTREAM support when it arrives) with OLE DB or ODBC, you need to use the new driver/provider. This means changing the connection string. AND RETESTING. This also applies to SSIS packages, Reporting Services reports and anywhere else you used OLE DB or ODBC.

If you only install the operating system and SQL Server 2008, you won't have the original (version 9.0) provider and driver. These will be provided as a separate download, for folks that have installed only 2008 but haven't restested.

BTW, once upon a time, there was talk of "multiple versions of SQL Native Client running side-by-side, using the fusion SxS loader". That didn't happen. The new providers are separate and are registered in the registry separately. No SxS magic needed.

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup.

I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE/TIME-related data type series are not .NET-based, but you can use them in SQLCLR procs, as you can use NVARCHAR data type. There are some restrictions on DATE/TIME series, more about that later. My second user assembly is named hiertest. It uses the HierarchyID data type in SQLCLR code. That's (of course) OK too. Both my user assemblies are owned by DBO. Both catalog as SAFE_ACCESS.  There are no user assemblies in pubs database. Turn on SQL Profiler.

In pubs: SELECT * FROM sys.assemblies;

In profiler:
Assembly Load event:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
In SQL Server Log:
 Common language runtime (CLR) functionality initialized using...
 AppDomain 2 (32767.sys[runtime].1) created
   
Still in pubs: declare @h hierarchyid; select HierarchyID::GetRoot(); -- invoke static method of hierarchyid data type

In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
In SQL Server Log:
 Unsafe assembly 'microsoft.sqlserver.types..." loaded into AppDomain 2 (32767.sys[runtime].1)

USE test
GO

In test: SELECT * FROM sys.assemblies;
In SQL Server log:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
  
In test: EXEC dbo.somedateproc -- my SQLCLR proc that uses date.
In SQL Server log:
 AppDomain 3 (test.dbo[runtime].2) created
In profiler:
 Assembly Load Succeeded for datetimetest
  
In test: SELECT * FROM dbo.AncestorAndSelf('/'); -- my SQLCLR UDF that uses HierarchyID
In SQL Server log:
 Unsafe assembly 'microsoft.sqlserver.types..." loaded into AppDomain 3 (test.dbo[runtime].2)
In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
 Assembly Load Succeeded for hiertest

So what happened??

There is only one resource database appdomain, it gets created when system functions (sys.assemblies) use system assemblies. The actual assembly that contains the system .NET data types loads only when it's required, into the resource database's appdomain. By the way, the declaration of a (NULL) hierarchyID variable isn't enough to load the assembly, you must actually use the variable. If you use only TSQL and the new data types, only this one appdomain is needed, regardless of how many different databases use them.

Additonal appdomains are created on a per-database, per-assembly owner basis as in SQL Server 2005. Each "user" appdomain will also load the system assembly Microsoft.SqlTypes.Types, if and only if it needs it. That is, if you use HierarchyID (or Geometry/Geography) in a SQLCLR procedure.

One last bit. Why are the appdomains referred to as: "AppDomain 2 (32767.sys[runtime].1)" and what happened to AppDomain 1? When .NET is loading into any executing process, there is a single appdomain created, the default appdomain. AppDomain 1. SQL Server doesn't load Microsoft.SqlServer.Types into this default appdomain, but starts another for the resource database. That's AppDomain 2 (into the process). The ".1" in "32767.sys[runtime].1" is first user appdomain.

Database administrators like to know about everything going on in "their" database, and with good reason...if the database fails (or even runs slowly) its (s)he who gets the first phone call for help. SQLCLR is still relatively new. Hope this was helpful in explaining what's going on. But remember, the exact appdomain implementation could be refined further in later releases. Cheers.

I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly Loading events. SQLCLR is nowhere to be seen.

USE TEMPDB
GO

SELECT * FROM sys.assemblies;
GO

The assembly list contains an entry for Microsoft.SqlServer.Types, that's the assembly that contains the system UDTs. It actually lives in the resource database, but shows up in system metadata lists for every database. In the SQL Server log, just listing the assemblies in a database produces:

Common language runtime (CLR) functionality initialized using...
AppDomain 2 (32767.sys[runtime].1) created

Database 32767 is the resource database, although its not for the most part directly visible in SQL Server 2005 metadata or in SSMS.

.NET appdomains are created on a per-database basis, currently one appdomain per assembly owner. So this functionality runs under an appdomain created for the owner "sys". The assembly is actually owned by principal_id 4, which is INFORMATION_SCHEMA according to sys.database_principals. But sys.schemas indicates that the sys schema is owned by principal_id 4 as well, INFORMATION_SCHEMA schema is owned by principal 3. They're likely referring to principal_id in the resource database, not the current database. Sys.assemblies also has this assembly marked as is_user_defined = 0 (false). And, the assembly has a safety level of UNSAFE. Hmmm...

Knowing how SQLCLR exception escalation works, I was concerned by the system assembly being UNSAFE. It is  running in its own appdomain. However, when I looked at the SQL Server log for the SQLCLR message, another seemingly unrelated message attracted my attention:

Using xpstar.dll version 2007.100.1049 to execute extended stored procedure xp_instance_regread...

SQL Server internals have always included extended stored procedures to perform system functions. User-written extended stored procedures can cause problems if poorly written, but this one (xp_instance_regread) was written by the SQL Server team, its part of SQL Server itself. Hmmm...so is Microsoft.SqlServer.Types part of SQL Server. And .NET code has more built in safeguards than unmanaged code. And BOL has indicated since SQL Server 2005 betas:

"This feature (i.e. extended stored procedures) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead."

So I guess its OK, they're taking their own advise. And this assembly has been tested with SQL Server for hardening, it doesn't produce the error message that cataloging, say, System.Runtime.Remoting, does.

One final item. SQL Profiler does not load Microsoft.SqlServer.Types.dll just because I execute "select * from sys.assemblies", but profiler reports:

Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002

Perhaps there's a stray assembly dependency somewhere in CTP4. System.EnterpriseServices is not on the list of tested assemblies either, so it shouldn't load automatically.

I was listening to a replay of the webcast recording on the HierarchyID by Michael Wang (thanks, Michael) and as he mentioned the considerations for the CLR-based type with respect to DDL, I thought it would be interesting to go back and see how this type showed up in the various facilities that we have for monitoring what SQLCLR is doing. These facilities are:

1. Ability to see assemblies registed in each database
2. Watch code-running appdomains (but not transient DDL-only appdomains) being created/torn down in SQL log
3. SQL Profiler event for Assembly.Load
4. Monitor memory and CLR-related processes using DMVs and perfmon

Before reporting the results obtained with SQL Server 2008 CTP4, we need a few clarifications. First, the way SQLCLR manages appdomains is an implementation detail and subject to change in future releases, service packs, or can even change before SQL Server 2008 ships. I'm just observing. Second, let's talk about what exactly the "sp_configure 'clr enabled', 0" does. You can also set this option using SQL Server Service Area Configuration utility. This option indicates whether or not its possible to run *user-written SQLCLR code*, that is, SQLCLR stored procedures, UDFs, trigger, UDTs, and UDAggs written by programmers.

The switch *does not*:

1. Keep SQLCLR from loading in SQL Server's process. This always loads the first time that you need it.
2. Prevent DBAs from using SQLCLR-related DDL, such as CREATE/ALTER/DROP ASSEMBLY, and define SQLCLR objects.
3. Prevent system functions that use SQLCLR from running. In SQL Server 2005, there were no SQLCLR system functions that I was aware of.

In SQL Server 2008, system functions that use SQLCLR that immediately come to mind include:
   a. The HierarchyID and upcoming spatial data types, Geometry and Geography
   b. Change Data Capture and the Dynamic Management Framework

So, its not that using system functions written in SQLCLR is "a trick" that bypasses an established control mechanism. The reality is that the mechanism was never defined to prevent SQLCLR loading, DDL, or system functions.

One things that will prevent SQLCLR from running is to enable lightweight pooling, or "fiber mode scheduling". This is also a configuration option, and its incompatible with SQLCLR. The CLR is not "fiber aware", although it may be implemented sometime in the future. A few other SQL Server system features are incompatible with fiber mode as well.

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "different" data sources. This post covers using SQLCE 3.5. The latest version of SQLCE 3.5 comes with Visual Studio 2008 Beta 2.

Using SQLCE 3.5 data sources doesn't work with the built-in LINQ for SQL designer in Visual Studio 2008 beta 2 or the CTP EDM designer released shortly after beta 2. You can add a Data Connection for a SQLCE 3.5 database. I used the Northwind.sdf sample database that was supplied. But dragging a SQLCE table on to the LINQ to SQL designer produces "The selected object(s) use an unsupported provider". The EDM Wizard doesn't even list the SQLCE ADO.NET data provider as a choice. So there's no LINQ to SQL or EDM designer support for SQLCE 3.5.

SQLCE works quite well with SQLmetal, although my attempt to generate a .dbml for the Northwind.sdf sample database produces the error message:

error DBML1055: The DeleteOnNull attribute of the Association element 'Order Det
ails_FK01' can only be true for singleton association members mapped to non-null
able foreign key columns.

Removing the "Order Details" table provides a 'hit it with a hammer' solution; I haven't figured out a nicer way to solve this problem yet. Then, the SQLMetal-generated classes worked fine.

Using EDMgen against the Northwind.sdf file produced the following error:

error 7001: Could not load System.Data.SqlServerCe.Entity.dll. Reinstall SQL Server Compact.
Could not load file or assembly 'System.Data.SqlServerCe.Entity, Version
=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its depend
encies. The system cannot find the file specified.

Hmmm...the version of SQLCE that came with Visual Studio 2008 B1 did come with this DLL. And, I had it working with EDM then. But the version in VS2008 B2 doesn't. As a last resort, I installed the SQLCE 3.5 version from VS 2008 B1, it's out on the web as a separate download. This changed the error message to:

error 7001: Method 'CreateDbCommandDefinition' in type 'System.Data.SqlServerCe.
SqlCeProviderServices' from assembly 'System.Data.SqlServerCe.Entity, Version=3.
5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not have an implementation.

Sure enough, the required implementation of the ProviderServices class required for EDM support has changed between VS Beta1 and VS Beta2. I confirmed this by checking the methods in the SqlProviderServices in System.Data.Entity.dll, they did change recently, adding CreateDbCommandDefinition. Unless I can find an updated System.Data.SqlServerCe.Entity.dll somewhere, it doesn't look like SQLCE is usable with EDM at this point in beta-time.

That's all for now.

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "non-traditional" data sources. Namely SQL Server 2008 (CTP4) and SQL Server Compact Edition 3.5. I'll cover using SQL Server 2008 CTP in this blog entry, and using SQLCE 3.5 in the next one.

Using SQL Server 2008 data sources doesn't work with the built-in designers in Visual Studio 2008 beta 2. Adding new "LINQ to SQL classes" produces an empty design surface onto which you can drag items from Server Explorer. Problem is, you can't make a Server Explorer Data Connection for a SQL Server 2008 database "only versions 2005 and before are currently supported. The EDM Wizard actually includes "Use this selection to connect to Microsoft SQL Server 2000 or 2005..." when you attempt to create a Data Connection. Not SQL Server 2008. So there's no LINQ to SQL or EDM designer support. Or Server Explorer support, for that matter.

But each of the mapping products comes with a command line tool as well. LINQ to SQL uses SQLMetal, while the EDM uses EDMgen.

Using the command line tools, I was able to generate the appropriate artifacts (and use them) for SQL Server 2008. I can even load the artifacts (.dbml or .edmx) files into the Visual Studio designer after they were generated from the command line and edit them. Very cool.

I then did an experiment to see if LINQ to SQL or EDM supports the new data types in SQL Server 2008, namely, the new date/time-related types and the hierarchyID, which is implemented as a SQLCLR UDT.

SQLmetal did not produce an error or warning on the date/time types, but it did create definitions for the new data types as "NVarChar" with appropriate lengths. Using these definitions in LINQ code prints database-correct values, albeit as strings. Haven't tried an insert/update/delete yet. I had less success with hierarchyID, this produces the message:

warning SQM1021: Unable to extract column 'c1' of Table 'dbo.h1' from SqlServer
because the column's DbType is a user-defined type (UDT).

SQLmetal then error'd out and didn't generate a dbml file at all.

Trying date/time-related data types with EDMgen produces errors indicating that the date, time, datetime2, datetimeoffset aren't supported. Neither are SQLCLR UDTs, hierarchyID is reported as date type <unknown>.

Well, these are still beta. That's all for now.

Just found out about this one today. This SQLCLR function works right now in CTP4 of SQL Server 2008.

public static Nullable<int> AddTwo(Nullable<int> x, Nullable<int> y)
{ return x+y; }

create function dbo.addtwo (@x int, @y int)
returns int
as
external name asmname.[Mynamespace.Class1].AddTwo;

select dbo.addtwo(2, null);
-> null

Great! Turns out, I suggested this back in 2005...after SQL Server 2005 shipped. And they listened. Actually, going back over the list from 2005... SSMS does display NULL for a NULL UDT already, as of SP1. And large UDTs and UDAggs are also on the agenda for SQL Server 2008.

That may also explain why there's no System.Data.SqlTypes.TimeSpan/DateTimeOffset in Visual Studio Orcas Beta2. They're not needed.

11:00AM: I'm sitting in the lounge room at TechEd Hong Kong, getting ready to do my first talk on SQL Server. It's an interesting setup that we have this year, the wireless hub is located next to an area of booths. Lots of interaction (I think) between vendors, attendees, and speakers.

The preconference talk on SQL Server 2005 best practices on Saturday seemed to go really well, with T-SQL query tuning taking center stage. There seemed to be a lot more interest in XML functionality in SQL Server than I've seen previously. As I'm writing this, I'm getting ready to talk on SQL Server SP2 and SQL Server on Vista. The demos are available on the SQLskills website, on the Current Events page.

Yesterday (at least I think it was yesterday, with the timezone difference) the ADO.NET team released the Orcas Beta2 synchronized version of the entity data model (EDM), along with the associated (and much anticipated) designer. More catching up to do...

If you're at the conference, drop by the lounge room and say hi. I'll be there.