The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while.

Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire order in one server round trip, regardless of the number of items that I order (that is, 1 order header and 1->n order detail lines). You'd even settle for two round trips, one for the order header, one for the details. Before SQL Server 2008, there is no built-in mechanism that supports this. In the past, I've seen some pretty interesting workarounds, such as:

1. Compose an arbitrarily large SQL batch as a single "command text" by using string concatenation on the client/middle-tier. All SQL Server database APIs support one (and only one) batch per Command. ADO classic did something like this when you inserted/updated/deleted multiple rows in a disconnected Recordset and called for a "batch update".

2. Create a stored procedure with some "static" parameters and an arbitrarily large number of repeating parameters, most of which will always be NULL. The limit to the number of parameters in a stored procedure is 2100. Both this method and the previous one make for some pretty hideous-looking code.

3. Use multiple parameter sets. OLE DB does support multiple parameter sets and some databases can optimize inserts that use multiple parameter sets. The SQL Server providers, at least last time I looked with SQL Profiler, turn multiple parameter sets into multiple calls, that is, one round trip per parameter set. That's not what I wanted.
 
SQL Server 2008's solution to this age-old problem is table-valued parameters (TVPs, for short). You start using a TVP by creating a custom type, using the CREATE TYPE statement, like this:

CREATE TYPE lineitem_type (line_number INT, order_id INT, product INT, quantity INT);

Information about these table types appear in sys.types and also in a new metadata view, sys.table_types. Once you've created such a table type, you can use it in T-SQL like this:

CREATE PROC new_order (@order_id INT, @line_items lineitem_type)
AS
-- silly table names used for clarity
INSERT INTO orders_table VALUES (@order_id ... ) ;
INSERT INTO line_items_table
  SELECT * FROM @line_items;

One round trip. Compact, clean code. Nice.

SQL Server 2008 will include GROUPING SETS; a (very) short explanation is that these allow the equivalent of multiple GROUP BY clauses in a single SQL statement. The result is a UNION ALL of the resultsets. SQL Server 2008 also contains/allows standard syntax for ROLLUP and CUBE, which have been in SQL Server for a while.

One way to use (or to think of) grouping sets is that, while ROLLUP with N columns produces a UNION of N+1 results and CUBE produces N-squared -1 results, grouping sets can produce an intermediate number of results, when not all the dimensions produced by CUBE are needed. Grouping sets should allow better optimization of this type of dimensional query.

SQL Server 2008 Reporting Services will contain a new type of control, called the TABLIX. The SQL Server 2008 CTP BOL defines a TABLIX as: "A Reporting Services RDL data region that contains rows and columns resembling a table or matrix, possibly sharing characteristics of both." Grouping sets sound like a good fit with this component. When TABLIX is available, a short profiler session would confirm this.

Of course, in addition to performance benefits and TABLIX support, GROUPING SETS are part of the ISO-ANSI SQL-2006 spec. Another plus.

There was a fairly well-known Powerpoint slide that attempted to summarize the new SQL Server 2005 features in bullet points of a single slide. By the release, there were so many new features, the feature list had to be rendered in a 5-point font to fit. At TechEd 2007, Microsoft presented the new features of SQL Server 2008 (was: SQL Server Katmai) in an analogous format. Although it's not yet down to a 5-point font, there are quite a few impressive new features on tap. Here's the list, modulo (my own) potential spelling errors. There is currently a CTP of SQL Server 2008 as well as beta1 of the next version of Visual Studio.NET (Orcas) and the ADO.NET synchronization framework. The features I can find in the current betas have asterisks. [Note: Asterisks updated for the July CTP]

Transparent Data Encryption
External Key Management
Data Auditing
Pluggable CPU
Transparent Failover for Database Mirroring
Declarative Management Framework*
Server Group Management*
Streamlined Installation*
Enterprise System Management*
Performance Data Collection*
System Analysis*
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model*
LINQ*
Visual Entity Designer*
Entity Aware Adapters

SQL Server Change Tracking*
Synchronized Programming Model*
Visual Studio Support*
SQL Server Conflict Detection
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types*
Date/Time Data Type*
LOCATION data type
SPATIAL data type
Virtual Earth Integration
Partitioned Table Parallelism
Query Optimizations*
Persistent Lookups
Change Data Capture*
Backup Compression
MERGE SQL Statement*
Data Profiling
Star Join*

Enterprise Reporting Engine*
Internet Report Deployment
Block Computations
Scale out Analysis
BI Platform Management
Export to Word and Excel
Author reports in Word and Excel
Report Builder Enhancements
TABLIX
Rich Formatted Data
Personalized Perspectives
… and many more

It should be straightforward to eventually produce a 5-point slide because these features haven't made "the list" yet.

Grouping Sets*
Table-valued parameters*
Table-valued constructors*
"Delighters" (1-stmt variable declaration/assignment, increment operators)*
HierarchyID data type*
Large User Defined Aggregates
New versions of ODBC driver*, OLE DB provider*, and ADO.NET data provider
SQLCE Version 3.5*
Database Mirroring Enhancements*
Data Mining Algorithm Enhancements*
SSIS support for the new date/time data types*
SSIS VSTA support*

That's an impressive set of features. And I'm pretty certain, since this is a summary, that's there are more to come.

SQL Server "Katmai", which some folks are already calling "SQL Server 2008" because of its scheduled release date, was officially annonuced yesterday. I've been following things and noticed, a while back, that there are a number of Katmai sessions scheduled at TechEd in Orlando, in June. Now that there is an official announcement, I guess I can blog about these without possibly hurting anyone's feelings. Note that, as with the official announcement, the descriptions are pretty sparse. So you'll have to come and see for yourself...

DAT201 - The Next Release of Microsoft SQL Server: Overview
DAT202 - The Next Release of Microsoft SQL Server: Beyond Relational
DAT303 - The Next Release of Microsoft SQL Server: Data Warehousing Enhancements
DAT204 - The Next Release of Microsoft SQL Server: Manageability Overview

DAT17-TLC - Spatial Support in Microsoft SQL Server
DAT06-TLC - New T-SQL System Types in the Next Release of Microsoft SQL Server
DAT18-TLC - Overview of New T-SQL Programmability Features in the Next Release of Microsoft SQL Server

I'll be doing a one-day workshop on SQL Server 2005 in Amsterdam in June, as a postconference event of Microsoft DevDays. Although I've done quite a few events on SQL Server 2005 before, this one will be quite a bit different from any that I've done before. It deals with best practices.

It's been about 4-5 years since I started teaching and working with early adapters of technologies like SQLCLR, Service Broker, and XML data types. During the "break in" period, people were most interested in how they worked, syntax and semantics. Now that we've had a while to practice, folks are wondering how its working out. Did these revolutionary technologies change the database landscape?

I'll begin the workshop by discussing the impact of items like statement-level recompilation, SQL algebrizer and plan caching changes, plan guides, plan forcing and the like have had on the way people write procedural code. The effect of snapshot isolation on how people approach locking issues. And go from there into the uses and guidelines for features, like Service Broker, that folks normally associate with "SQL Server 2005 change". With some real-life examples. It's a different outlook that will benefit you even if you're not on the bleeding edge. And because some 'next generation' features will be introduced at TechEd the precededing week, I'll answer questions on where I think we go from here.

Also not to be missed: Dino Esposito will be presenting an excellent workshop on Ajax next door.

See you there.

I'll be at TechEd 2007 in Orlando next month doing a breakout session on SQL Server 2005 scaleout technologies, as well as a couple of chalktalks (on eventing and WMI and on Powershell and SMO) in the SQL Server area. Stop by and say hi.

And...if you're thinking of going to a pre-conference talk, Kimberly Tripp and I will be delivering an information-packed day on Leveraging SQL Server Always-On Technologies to Acheive High-Availability and Scalability. I'll be covering internals of some of the many scaleout solutions available with SQL Server 2005. We'll also have some of our much-requesting SQL Server Always-On DVDs. Have a look at Kimberly's blog entry for even more details. See you there.

The day after Orcas Beta1 appeared for public download reports appeared on the ADO.NET Technology Preview forum that the EDM Wizard (that is, "Add New Item/ADO.NET Entity Data Model) wasn't working. And, sure enough, if you choose "Generate From Database" and carefully choose which tables to use, you get a model with no entities. If you choose an empty model, you get "Specified argument out of range of valid values", and get the three mapping files, but no language file. What to do?

One way around this is to use the files generated from the March CTP wizard. Because the XML schemas for the mapping files haven't changed since March CTP, these work fine. In fact, all of the EDM code that worked with March CTP continues to work unchanged. That's what I used in the SQLCE and EDM blog entry posting.

If you can't keep an old March CTP around just to generate EDM Data Models, you can use the EDMGen.exe command line utility in the C:\WINDOWS\Microsoft.NET\Framework\v3.5.20404 directory. This utility appears to work and generates useable code. It also generates a richer model than the wizard did (with AssociationSets and Associations) but it takes a little more work to put together a project. I'll post a really simple one if there's interest. And EDMGen doesn't generate the same output as the wizard used to (for example, columns of type NTEXT are ignored in the model) and uses different default naming conventions. So at least there are a few workarounds for now.

SQLCE 3.5 comes with an ADO.NET data provider that supports entities, by supporting the ADO.NET 3.5 entity data model (EDM). I noticed this in Orcas March CTP with the inclusion of a new DLL, System.Data.SqlCe.Entities. Although the DLL was there, there was no support in Visual Studio for any provider but SqlClient, and trying to do this manually failed (DbProviderFactory.GetService returned null, IIRC).

In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

Here's a simple example of using EDM with SQLCE 3.5, database included. You need to put the pubs.sdf database in c:\temp or change the connection string in the app.config to make this work. The sample uses both EntityClient and Entity Services. It could just as easily use LINQ for Entities.

An interesting idea is that if the internals of Sync Services were tweaked sightly, they could use the EDM and EDM's generated SQL in addition to (or instead of) DataSets. Imagine replicating/synchronizing Employee entities with associated Job information instead of synchronizing Employee and Jobs tables. The logic could be hooked in from what AcceptChanges does.

One of the latest developments in the SQLCE space is the beta for ADO.NET Sync Services. Sync Services ships as a standalone download, runnable on Visual Studio 2005 and is also built-in to Visual Studio Orcas. Both versions require a new version of SQLCE, version 3.5. This runs side-by-side with SQLCE 3.1, although both version's DLLs have the same names they are installed in different directories. The Orcas B1 version has a newer version of the DLLs and install GUI support.

The Orcas GUI support consists of a new component "Local Database Cache" that can be added a various types of C# and VB.NET projects. This bring up a Configure Data Synchronization dialog. In this dialog, you can configure a Server connection and a Client (SQLCE) connection, select "Cached data objects" (tables to be replicated) and replication specifics. You can also configure the feature of Sync Services that allows you to make synchronization service-based, that is, use a middle-tier WCF, Web Service, or other middle-tier component for connect to the database and perform the synchronization.

Besides service-based synchronization, Sync Services allows you to sync to data sources other than SQL Server. You can sync to most any database that supports ADO.NET, for example, Oracle. It works by instanciating a DataSet (although I'd though it could/might use EDM in future, see next post) and performing synchronization through the DataSet. Sync's DbServerSyncProvider uses a SyncAdapter, making it even more ADO.NET-like. Although DbServerSyncProvider can use any compliant ADO.NET provider, SqlCeClientSyncProvider is SQLCE specific.

For some excellent examples of Sync Services in action, check out The Synchronizer's (aka Rafik Robeal) blog. I think Rafik is up to six samples now, one of which works for an Oracle backend database.

For some excellent videos showing Sync Services with Visual Studio Orcas, check out the links on Steve Lasker's blog. Although I haven't tried it out yet, I think his WCF service-based example is running from the Orcas Beta1 version of the designer.

Next: SQLCE and Entities

So now that I've found SQLCE and know which version I have, what can I do with it besides use the ADO.NET and OLE DB APIs? Well, turns out I need some auxiliary pieces. If you use 3.0, the pieces "come with", but when you upgrade to 3.1, you need to upgrade these.

I must admin I found this somewhat confusing, probably because I "started from scratch" and also wanted to use 3.1. I had to intall some pieces before it would "recognize" others. For example, unless you have the Windows Mobile dev tools installed, the 3.1 VS tools won't install. And after installing the Windows Mobile dev tools on VS SP1, I had to install VS SP1 again. And none of these installs made the "Configure Web Synchronization Wizard" app available until I installed the Server Tools.

This turns out to be more important than you might think. For example, the GUI DLLs are used to CREATE the database itself. I've not yet investigated if there is a flie format difference in different SQLCE versions, as there is with different SQL Server versions. Or how/when SQLCE database files with data are upgraded. And using the 3.1-specific features, that is the |Data Directory| connection string directive, and Click-Once deployment support require the updated version of the dev tools. When you install SQL Server 2005 SP2, your bits are updated, but these are a subset of the bits you use for development in VS.

Here's the list of additional bits (most are 3.1 updates) I came up with, along with some terse notes:

-- Server Tools (SQLCE30setupen - localized)
   Updates IIS to support connectivity solutions (Merge Repl)
   Special considerations for IIS 7.0 (IIS on Vista)
   No need if SQL Server and IIS on same machine
   These are not updated for SQLCE 3.1
   Includes Configure Web Synchronization Wizard app

-- Tools for VS2005 SP1 (SSCE31VSTools)
   Requires installating VS2005 SP1 first
   Adds Click Once Support for SQLCE 3.1
   Updates design-time UI to work with SQLCE 3.1
   Adds 3.1 device cab files
   Requires VS WindowsMobile dev tools install
   If VS not upgraded to SP1, uses SQLCE 3.0 design time UI

-- Developer SDK (SSCE31SDK)
   Cab files for devices
   MSI for desktop and tablet PC editions
   Header files
   MSI for Compact Edition help files
   Northwind sample app

-- Books Online (SSCE31BOL)

-- SQL Server 2005 Compact Edition Access Database Synchronizer
   Supports Access 2000 SP3, 2002 SP3, 2003 SP2, 2007
   Not sure if this supports 3.0, 3.1 or both
   Runs as a service, Uses RDA access and HTTP transport.
   Requires Active Sync 4.0 or later

-- SQL Server 2005 Sync Services CTP
   Installs 3.5 side-by-side, but no new device support yet
   Installs new ADO.NET-based sync
   Included with Orcas B1, works with VS2005 also
   There is a separate documentation and demos download

I'll only be using 3.1 and above because (blush) I don't have a compact device except for the emulator in Visual Studio. So I'm working desktop and emulator only for now.

Next: Newest stuff

As part of a project, I've had occasion to look at the SQL Server Compact Edition in earnest. Although I've taken a cursory glance before this, its my first attempt to put all the pieces into place. So I thought I'd write it down as I went along.

SQL Server Compact Edition's original name was SQL Server Mobile, but as of version 3.1 its now supported on a variety of mobile devices but its also supported on Tablet and Desktop PCs. For simplicity, I'll refer to all versions as SQLCE from now on.

SQLCE ships with SQL Server 2005 and will also ship with the next version of SQL Server (Katmai). I believe it installs if you install the SQL Server 2005 adminstrative tools, but doesn't show up in Add/Remove Programs.  It also ships with Visual Studio, if you install the Compact Framework dev tools. You can also download it stand alone.

It consists of 6 DLLs, no services, not even an exe. You can program it with the ADO.NET data provider or OLE DB provider. Each is included as a separate DLL. When installed with VS or standalone it lives in the directory C:\Program

Files\Microsoft SQL Server Compact [or Mobile] Edition\v3.x. There are subdirectories that contain the redistributables for various compact devices and versions of Windows CE. When installed with SQL Server it lives in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE directory.

The versions that come with various product versions are:

SQL Server 2005 RTM and SP1: Version 3.0, build 3.0.5207.0
SQL Server 2005 SP2: Version 3.1, build 3.0.5300.0
Visual Studio 2005: Version 3.0, build 3.0.5207.0
Standalone download of SQL Server Compact Edition 3.1: Version 3.1, build 3.0.5300.0
Mar CTP standalone and Mar CTP of VS Orcas: Version 3.5, build 3.5.5305.0
VS Orcas Beta 1: Version 3.5, build 3.5.5334.0

Interestingly, the DLLs always end with "30" in all versions. You know which version by looking at the file properties, not the DLL names. Version 3.5 will supposedly also ship with SQL Server Katmai, but this is not available yet.

The reason SQLCE ships with SQL Server and Visual Studio is that these are the development and admin environments for it. You don't just start up the .exe and work from the command line, you use either SQL Server Management Studio or Visual Studio's Server Explorer. Each of these tools come with dialog boxes that allow you to create a database and interact through the GUI (VS's Server Explorer/Data Connections or SSMS's Registered Servers/Object Explorer/Query Window). You can also create and manipulate a database through the programmatic APIs. You must have SSMS to make this work with SQL Server, there is no support for SQLCE in SQL Server Management Studio Express.

In addition to DDL and DML, both environments allow you to configure merge replication, as support for what's called "Occasionaly Connected Systems" is one of the main points of SQLCE. Besides SQL Server Merge Replication, you can interact with the "main server database" through RDA (remote data access) or the new ADO.NET Sync Services, currently in beta.

Next: Associated pieces

I always look through the SQL Server 2005 samples with each new incantation, and always seem to find things in there that are new and interesting. In the SP2 version, the samples included a new sample database, "AdventureWorks Light". Also known as AdventureWorksLT.

Because folks that teach SQL Server do have a bunch of canned queries for exposition and know exactly how those queries are supposed to behave, sample databases really never disappear. Pubs was the sample database inherited from Sybase, although Sybase itself moved on to "Pubs2" last time I looked. The problem with Pubs was that it really didn't have much data at all. I always point out that, of the 8 publishers in Pubs, only 3 have actually published books. Northwind was inherited from Microsoft Access, being the original Access sample database. It had more data, but not a lot.

In SQL Server 2005, no sample databases are installed with the product by default, which is a good thing. But the "AdventureWorks series" is the new sample database, including an OLTP, data warehouse, and analysis databases, as well as case-senstive and case-insensitive collation versions. It has a reasonable amount of data and its design is said to reflect current thoughts on best practices. It is built around multiple departments in a putative organization, including Sales, HR, and Manufacturing. This allows it to include a employee/manager hierarchy and a bill-of-materials table.

But the Adventure Works database is a nice 3NF database, with lots of tables (~70) and it sometimes difficult to use for exposition, because of the long multipart object names and lack of familiarity. AdventureWorks Light is a bit more approachable, weighing in at about 3MB, but still over available as an MSI (no simple create script). It contains:

10 tables and 3 views
500 or so rows in each table
All in single object schema SalesLT
No stored procs, but 1 scalar and 2 table-valued functions
A single XML schema collection

It even includes a version of Sara Tahir's uspPrinterror and uspLogerror procedures for error handling, but interestingly uspRethrowError is left out.

We'll see if this new sample database is simple enough for widespread pedagogical use. At least it may get folks more use to using (memorizing) the "AdventureWorks family" table and column names.

PS: The SQL Server Samples now have their own Codeplex project at http://codeplex.com/SQLServerSamples

I just returned from Microsoft Developer and ITPro days in Ghent, Belgium. The hospitality was great, and the event itself drew some big crowds of top notch developers and IT pros. Ghent was an amazing city, with medieval architecture sharing the city with modern buildings. Besides the four talks on SQL Server 2005 and ADO.NET that I did for the main conference, Wednesday, I gave a special talk on Event Notifications for the Belgian SQL Server user group. On the previous, the user group and I went out for ribs and beer. I sampled quite a few different excellent Belgian beers, there is quite a variety!

Thanks to all for a really good time. The conference demo scripts are posted up on the SQLskills website. 

For folks that have been asking...my latest whitepaper "SQL Server 2005 Security Best Practices - Operational and Administrative Tasks" was posted on the Technet website this week. It also covers the nuances of security when using SQL Server SP2 and Vista. Enjoy!

Next week (28-29 Mar) I'll be doing some talks on SQL Server 2005 and ADO.NET vNext at DevDays Belgium in Ghent. On 28 Mar, I'll also be doing a special additional talk on SQL Server 2005 Event Notifications (including a cross-database notification demo) for the Belgian SQL Server user group at the event. If you're a registered member of the user group, I'll see you there. You can also sign up to be a member of the user group at the user group website. 

Thanks to the user group for inviting me.

Since SQL Server 2005 was introduced with .NET programming support, folks have been trying to push the boundaries of what can be used in SQLCLR or at least trying to determine where those boundaries are. Here's two things that, as far as I know, can't be done in SQLCLR.

1. Use dynamically generated code. The canonical example of this is dynamic serialization assemblies generated when you use "Add Web Reference" in the generated web service proxy code. The way around this is to use the sgen utility. But lately, it's been brought up that dynamic programming languages such as Iron Python always generate dynamic code. SQLCLR forbids using this, even in UNSAFE assemblies. No dynamically generated languages.

2. Use the SMO libraries. A combination of SMO not supporting partially trusted callers and using a special type of connection result in SMO being unusable even in UNSAFE assemblies. The obvious workaround is to use SQL DDL, but SMO encompasses more than DDL, for example, configuring service settings via WMI. If you really want to use SMO, it would be possible to call out to a web service or better yet, a Service Broker-based service that uses external activiation, does the SMO calls and returns the script and/or results.

One thing that I thought was particularly interesting in the Orcas Mar CTP was support for stored procedures. This support exists in LINQ to SQL and EDM ObjectServices; I thought I'd start with ObjectServices. There almost no documentation on this topic at this point, about half a page with an incomplete mapping schema example. That's to be expected at this point, though.

In Mar CTP, you can specify stored procs for insert/update/delete, but not for EDM queries yet. You need to change the SSDL (store schema definition language) and MDL (mapping definition language) files. With the correct mapping filechanges, AcceptChanges just calls the sprocs automatically. I started with a VS-generated set of mapping files and used XSD-based intellisence and error messages with line numbers to guide me along. Although you may have gotten rid of the XSD/XML errors or compile-time errors, your mapping files can fail to agree with one another. This occurs as a runtime error when you "new-up" an instance of your model class. Try-catch is your friend here.

SSDL changes were easier (just add the Function and Parameter elements for the proc) with one gotcha. My SSDL Namespace attribute was called "people"; if you specify "dbo.myproc" as the store name, EDM looks for [people].[dbo.myproc] at execution time. That's invalid in SQL Server. Specifying "myproc" as the store  name (or letting it default to the value of the "Name"attribute) causes the runtime to look for [people].[myproc]. The name of my sproc was actually [dbo].[myproc]; I had to change to SSDL Namespace attribute to "dbo" to make it work. Doing so made me change the MSL to match, but had no global ill effects.

The MSL was a little more interesting. I needed to put an "EntityTypeMapping" and "TableMappingFragment" in between my "EntitySetMapping" and "ScalarProperties" elements. Then I could add my ModificationFunctionMapping and Insert/Update/DeleteFunction elements as children of EntityTypeMapping. Because the XML schemas require elements be defined in order, I almost thought this feature was masked out in this CTP. Although I could see it in the schemas. It was there, of course, you just have to hit the intellisense in exactly the correct place in the document to show the element you're looking for.

OF COURSE, this is going to sound like complete gibberish without an example. I started with the easiest possible example: one table, few columns, primary key (its required) but not even an identity column. I also have two projects, one with the "vanilla" tool-generated CSDL/SSDL/MSL so I could refer back if I had problems. I did have problems. Coding three files of XML by hand, even with XSD-base intellisense, is right up there with [insert your least favorite chore here].

Three hours later, thanks to perseverance and SQLProfiler, I had a functioning prototype. It's posted here. I'm trying to decide which undoc'd part of this interesting set of mappings, models, and query lanaguages to try next. Or what tool to create to make this less of a chore. Enjoy!

SQL Server SP2 was released last Monday. The links were posted on many blogs, so I won't repost any but the main one here. SP2 is a "major" service pack because there are a few "must have" features in addition to some rather useful improvements and quite a few bug fixes. The ones I'd consider must haves are:

1. Support for running SQL Server 2005 on the Windows Vista operating system. Vista will inform you of the SP2 requirement when you install SQL Server 2005. You install the "base" SQL Server 2005 first and apply SP2 immediately afterward, except with SQL Server 2005 Express. Express ships an SP2-specific product.
2. Support for business intelligence features of Office 2007 in Analysis Services. Unless you have SP2 installed, these features will be greyed-out in Office 2007.

There are some brand new features too like Sharepoint Server 3.0/SQL Server Reporting Services integration, the vardecimal storage format, and multi-schedule/multi-server Database Maintanance Plans.

But with that many changes, there are bound to be some incompatibilities. Be careful with these.
1. If you enable vardecimal storage on a database, you can't restore or attach an SP2-post vardecimal format database backup on an SP1 instance.
2. You can't use SP2 Analysis Services backups and data folders on pre-SP2 instances of Analysis Services
3. Analysis Services databases with linked measure groups can have problems on SP2. The most likely ones will be databases migrated from AS2000. See the SP2 Readme file for specifics.
4. You can't use Database Maintanance Plans from an SP2 instance will an SP2 instance, because the SP2 maintanance plans can have multiple schedules.

So be sure and be aware of these to have a successful upgrade.

Next month I'm looking forward to speaking at DevDays in Ghent Belgium on SQL Server 2005 and on ADO.NET vNext. The conference is being held on 27-29 Mar, but most (all) of my talks will be on Thursday, the 29th. On Wednesday at noon, I'll be doing a book signing at the A/W booth. If you're around at DevDays drop by and say hi. Besides ADO.NET vNext, I'm talking about SQL query tuning, making an application run faster using Service Broker, and SQL Server SP2 and SQL Server on Vista.

See you there!

My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live on the Microsoft web site today. For anyone who is confused by all of the whitepaper announcements lately, here is a list of titles and direct links.

This one is about SSIS:

Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc

And these two (from last week) are about scaleout technologies and solutions. That is Service Broker, Scalable Shared Database, Peer-to-Peer Replication, Query Notifications, Distributed Partitioned Views, and Data Dependent Routing.

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc

Enjoy!

Peter DeBetta recently tagged me in what is called "blog tag". When I described it to Mary, she named it "blog chain letter" and said I should ignore it. But it got me thinking. Although I run my mouth often and have some fairly weird stories, here's 5 things you may not know about me. But then again, you might...

1. I attended the first home game of the New York Mets baseball team on a Friday at the Polo Grounds in 1962. My dad took me when I was 8. It was, as Casey Stengel would say, amazing, when they shocked everyone by winning it all in 1969. I'm waiting for the Seattle Mariners to do the same today.

2. I have a degree in Chemistry, rather than Computer Science. In fact, I dropped the only CS course I took in college, because I didn't have enough time in the day for labs. The labs consisted of waiting in (a fairly long) line to punch lab assignment programs onto cards, with a limit of 15 cards at a time. I was taking six other courses that semester...

3. I worked on the first commercial implementation of Kerberos that I'm aware of. In 1992, I joined a startup called Open Computing Security Group (OCSG). We put out Kerberos implementations for almost every variant of Unix and for IBM mainframe, as well as clients for Macintosh and Windows 3.1. Really. The company is now known as Cybersafe.  My first port was NeXT OS 1.0 (which is just BSD 4.4 Unix over a Mach kernal + GUI). Our biggest competitor at the time was Cygnus support, who compiled, packaged, and sold support for the open Project Athena code. I never forgot their slogan "We make free software affordable". IBM had (in addition to RACF) a competing security system called CryptoKnight.

4. In 30 years in this business, I've only carried an on-call pager or cell phone for a total of 2 months. Not that I was never "on call", I WAS on call for YEARS. In the pre-pager days you just had to tell the operations folks where to find you at all times.

5. I wrote the first (that I'm aware of) class on ASP (ASP classic, not ASP.NET) in the alpha2 or beta1 days of it. It was for an internal Microsoft gig. Just to provide some time reference, the day before I taught this class, I attended a pre-release seminar on Active Directory Version 1.0. Teaching across the hall during the class was a guy named Don Box, who I'd never heard of, teaching COM.

I'll do everyone I know I favor, and not "tag" anyone. Cheers.

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have the "main" link; the direct link is here. Enjoy.

I've been working on some whitepapers on scaleout technologies in SQL Server 2005. The first whitepaper is now available; I don't have the main link, but here is the direct link to the doc file on the Microsoft download site. The whitepaper is about the implementation steps when using scaleout technologies like Service Broker, Scalable Shared Database, Query Notiifcations, and Peer-to-Peer Replication and how to choose which technology or combination of technologies is the best fit.

It will be followed by a companion whitepaper about internals and troubleshooting of these same scaleout technologies. I'll let you know when that one's available. Hope you find them useful.