SQL Server 2008 was released to manufacturing yesterday (and simultaneously to the Web, without a hitch).  This works out well, as the SQL 2008 update to my MS Press book on SQL Server 2005 is almost done!  The book, now under the leadership of Lenni Lobel, should be out in October.  It will be even better than the last one, trust me.

With SQL Server 2008 ships Service Pack 1 to the .NET Framework 3.5. Its companion, SP1 to Visual Studio 2008, should be shipping very, very, soon.

“So what?” you might say. “It’s just a service pack.”  Well, not really.  This service pack is also a roll up of a collection of technologies, many of them data-related, that had been previously released as stand-alone Betas, Community Technology Previews and “futures” releases.  These include the ADO.NET Entity Framework (an Object Relational Mapping —ORM— framework), ADO.NET Data Services (formerly project “Astoria” — which allows you to create RESTful Web services around your data, very easily) and ASP.NET Dynamic Data (which creates entire functional data bound Web sites, simply by inspecting your data model).  And with these technologies, will come an added emphasis on LINQ To SQL (another ORM framework), which was only recently released itself, in November.

“And that’s not all.”  With SQL Server 2008 comes a new version of SQL Server Compact, as well as the Sync Framework and Sync Services for ADO.NET.  And don’t forget SQL Server Data Services, a cloud-based data service from Microsoft, currently in private Beta.

Is 2008 the year of the database?  Is Microsoft trying to compete for attention with the Beijing Olympics?  (I doubt that, given that NBC is using Silverlight to show every single Beijing event live and on demand.)  Are people at Microsoft so bored that they had nothing better to do than come out with five different data tools and a new release of their flagship database?

Nope.  Let go of your conspiracy theories.  Here’s an anecdote that might shed light: when I started writing for Visual Basic Programmer’s Journal (now Visual Studio Magazine) 14 years ago, I focused on database topics.  When I was offered a regular spot, it was to share the Database Design column with Roger Jennings.  It’s what I wanted; it’s what I fought for.  Because back then, business software development was all about database management and access.  And today’s no different in that respect. 

But what is different is that we have AJAX, Rich Internet Applications, Web services, cloud computing, smart phone applications, and a strong desire to automate the production of code that is common to a critical mass of applications.  That’s what all these new tools are about: addressing the new platforms and reducing menial coding tasks on any and all of them.  And with a new version of SQL Server ready to tie it all together.

Will all these tools survive?  Maybe not, but I think most of them will, and they’ll integrate more and more.  Some of the products are ground breaking, others represent Microsoft’s adoption (and adaptation) of tools that have abounded in the third party and open source spaces for a while.  Some are must-haves, others need to be treated more skeptically.  But all of them will strengthen the .NET platform, because active enhancement is a software platform’s lifeblood.  As in 2001 when .NET was still in Beta, developers should take advantage of the slower economy and study this new stuff hard.  When things turn up again, they’ll be ready, and customers are going to be happy.

I’m updating chapters in my book on SQL Server 2005 for the impending release of SQL Server 2008. Right now, I’m focusing on the chapters that cover Analysis Services (AS), the BI component of SQL Server.  I’ve come across an “improvement” in the new version of AS, and I’m not sure I like it: the wizards do less.

In AS 2005, the Cube Wizard could create a time dimension for you, and the Dimension Wizard could create a parent-child dimension/hierarchy for you. 

In AS 2008, only the Dimension wizard will create a time dimension, but won’t do so very easily when you supply your own dimension table for it.  And, as best as I can tell, under AS 2008, there is no wizard interface at all for creating parent-child dimensions.  Even for regular dimensions, the Cube Wizard creates only a key attribute for each generated dimension.  If you want multiple attributes, you’ll need to add them manually or use the Dimension Wizard.  The cube wizard actually gives you no insight into attributes at all:

This is most perplexing.  So let’s play devil’s advocate.  The AS 2005 wizards had a feature called Auto build (it was called IntelliCube when AS2005 was still in Beta).  It didn’t work that well, and I usually disabled it. 

I can understand that giving rise to a point of view that the AS 2005 Wizards were over-engineered.  Fine.  I can therefore see streamlining the wizards.  A little bit.

But why take away all that good, helpful functionality?  Has thin-client computing led to thin Wizard computing?  Frankly, I think this is absurd.  Under the guise of having the wizards build leaner cubes (and dimensions) in fewer steps, we end up with a product that simply does less.  It doesn’t affect me personally. I know how to use the Analysis Services designers and the Properties window to do on my own what the Wizards would do for me.  But beginners don’t know how to do that.  And aren’t Wizards for beginners?

Recent articles like this one have been speculating on the possibility that a potential flaw in IIS might be responsible for a rash of malicious iFrame attacks that have plagued the Web recently.

It would appear that IIS, ASP[.NET, and SQL Server are not the culprits.  A response to me and others, direct from Microsoft follows.

***

We have been investigating these reports today and just posted two blog posts about them:

http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx

http://blogs.iis.net/bills/archive/2008/04/25/sql-injection-attacks-on-iis-web-servers.aspx

The high-level summary is:

These *are not* a result of any known security issue with IIS, SQL, ASP or ASP.NET (or any other Microsoft product)

These are instead the result of SQL injection issues within the web pages/applications hosted on these sites

 The above blog posts provide more details on the attacks and have pointers on how to make sure your site doesn’t have SQL injection issues.

Materials for the workshop Lenni Lobel and I presented on April 3rd are available here.

Something’s probably a little screwy with me.  I’ve chosen tonight, my first night of vacation, to try and get back to posting here.  No matter, whatever it takes, it takes.

Honestly, I am still convalescing.  About 2 months ago I finally finished work on Programming Microsoft SQL Server 2005, the book I’ve been working on with Stephen Forte, and nine other people too.  If you include the index, the book comes in at a hefty 900+ pages, and it still feels like we just scratched the surface of the product.

Our book was really, really late.  We should have released it in conjunction with the product itself.  Shame on us; I won’t bore you with the reasons why it took so long.  Except one: the product itself was late.  Want a fun factoid that illustrates this well?  We signed the contract for the book with Microsoft Press right around the time of the Yukon Technical Preview conference in Bellevue, in February 2003.  Yes, you read that right.  From the Tech Preview Conference, when the first alpha/preview bits were released in the form of VMWare images (Microsoft hadn’t yet acquired Virtual PC from Connectix!), to product release was almost three years.  Our book merely took another six months to finish and an additional month to hit availability on Amazon.

It’s not just SQL Server 2005 of course; Vista and Office 2007 are massively delayed too.  And what about “Longhorn” Server?  What about IE7, which went into Beta a year ago?  And when will Exchange 2007 really ship?

What’s going on in Redmond?  Clearly, I am not the first to observe this problem or ask this question.  But it bears asking nonetheless.  Forget all this talk about “innovation.”  I prefer to focus on simple “organization” and “dedication.”  What will it take for folks at Microsoft to get some hustle in them?  To feel the panic of a deadline?  To pull an occasional all-nighter?

Maybe I’m just bitter.  During the last three months of working on the book, I worked every day of the week, save for maybe four days off.  I closed deals at the office, I spoke at conferences, I wrote, I edited, and I answered hundreds email of messages a day.  I’m guessing many other software book authors go through the same thing.

As shareholders, as business partners, as platform advocates and as colleagues, we need everyone at Microsoft to do the same.  If the Zune is to succeed, if the XBox platform is to continue its upward climb, and if the Windows Server stack is to complete it ascension not just to serious contender, but to a ubiquitous no-excuses platform for the corporate IT world, Microsoft needs to bear down and push.

I'm not pushing again until next week though.  Now, back to vacation.

While the value of BI has been well-demonstrated for quite some time, that value has seemed hypothetical to many, because of the difficulty in keeping OLAP cubes up-to-date.  Compared to an OLTP database, OLAP cubes have, for many, projected an image of inferiority because their data has for the most part been reflective of historical facts rather than of current transactions. 

Historical data, and analysis of it, is very important, but appreciating that importance takes a certain vision and leap-of-faith.  In most over-stressed IT organizations, dealing with historical data feels less urgent than dealing with current data.  This has pushed BI down on the list of priorities…far enough down, in fact, that it has never gotten off the backlog list in many organizations.  And while techniques have always existed for keeping cubes reasonably up–to-date, doing can involve a lot of work and require a lot of resources (of both the hardware and human variety), pushing BI projects further onto the back burner

SQL Server 2005’s Integration Services and Analysis Services 2005 change the landscape of the BI market by making real-time BI easy.  New advances in these components allow you to load data directly into your cubes bypassing your data warehouse and/or allow Analysis Services to process cubes automatically in the background, in response to changes in your data warehouse.

I believe these advances will eventually change the whole dynamic of the BI market.  People at all levels of management and even non-management will now be able to perform sophisticated analysis on current data, and they’ll be able to do so much faster than they could with a relational database.

As groundbreaking as these advances are, for people new to SQL 2005, it will not be immediately obvious how to take advantage of them and thus it will not be obvious that they even exist: the direct data push capabilities of SSIS are stashed away in the Visual Studio toolbox and the proactive caching features of Analysis Services are buried deep inside a wizard and some dialog boxes.

Take a look.  You can adjust proactive caching settings directly from Management Studio or from Analysis Services projects in Visual Studio:

You can also do so from the Aggregation Design Wizard (available in both Visual Studio and Management Studio):

And the SSIS data push tasks are highlighted below:

I am posting these screen captures to make it clear how easy these tools are to get at, if you know where to look.  In many cases, using them requires only a modicum of effort.  I will try to coverage some basic usage scenarios in another post.

On April 24th, I will be presenting a session called "Real Time BI with SQL Server 2005 Analysis Services" at the sold-out Microsoft Financial Services Developers Conference, at the Millennium Broadway Hotel in New York City.  My firm, newly branded as twentysix New York, is a Platinum sponsor, and my twentysix colleague Kent Brown, will be presenting as well.

In my session, I will cover a number of methods for implementing real-time Business Intelligence, including use of the new proactive caching features of Analysis Services 2005 and the SQL Service Broker, as well as some approaches that work well in SQL Server 2000.

While I was in San Francisco for VSLive, I took half an hour to talk about BI with Ron Jacobs, host of ARCast, Microsoft's Internet audio podcast for software architects.  The show went up online yesterday.

You can read a blurb about it, and click a link to play it here:
http://channel9.msdn.com/shows/ARCast_with_Ron_Jacobs

Direct links to wma- and mp3-formatted copies of the show are:
http://channel9.msdn.com/Podcasts/164350_ARCast020106-BIWithSQLServer.wma
http://channel9.msdn.com/Podcasts/164350_ARCast020106-BIWithSQLServer.mp3

The podcast is timely, given that I just returned from the Microsoft BI PAC (Partner Advisory Council) meeting.  While most of what was discussed there is under non-disclosure, what I can tell you is that Microsoft is serious about BI on the server, on the client, and from line of business applications.  Watch out Business Objects, Cognos and Hyperion!

I am this week’s guest on “.NET Rocks,” the popular Internet audio talk show covering .NET development.  This week’s show features me chatting about Microsoft BI technologies, with specific attention paid to Analysis Services in SQL Server 2005, including its OLAP and Data Mining feature sets.  I mention Business Scorecard Manager as well, and talk about Microsoft’s (increasingly well played) position in the BI market.

For those who don’t know, the main Web URL for the show is:
http://www.dotnetrocks.com

And Microsoft runs a mirror site here:
http://msdn.microsoft.com/dotnetrocks
(although my show’s not up on the mirror site yet)

Short of spell-checking and proofreading, work on the "Advanced OLAP" chapter for my upcoming book on SQL Server 2005 (with co-authors Stephen Forte and Bill Zack) is done.  This one chapter (which we may end splitting into two) covers an array of new features in Analysis Services 2005. 

I believe very strongly that with this release of SQL Server, all Microsoft-centric developers should learn at least a little about OLAP.  The biggest reason for this is that building cubes, building application functionality around them and keeping them up-to-date is now something that any good database administrator and/or developer can do.

It's time to step up.  To explain why, let me just outline some common-sense principles that this release is based upon.  They make the technology more accessible to you, and to the BI market in general:

• Keeping your cubes up-to-date shouldn't involve a lot of work.  Proactive Caching, which in its basic form can be configured with a Wizard, can be used to maximize availability of your cube and currency of its data.
• You shouldn't need to be an OLAP expert to build simple cubes. It's not just that the tools used to build cubes are eaiser to use and more sophisticated, it's that the principles themselves are easier.  Simple things are simple to do.  Parent-child dimensions are easier, ordering of members in a dimension is easier, supporting multiple hierarchies is much more straightforward, etc.
• You shouldn't need to be an analyst or statician to use the cubes that get built.  Built in visualization tools (KPIs) make cube data much easier to comprehend.  Display folders allow for an immediate taxonomy of what can be an otherwise overwhelming list of dimensions and measures.  And new perspectives make it simple to publish a subset of a cube and make it look like a separate cube to client applications.
• You shouldn't need high-level permissions on your database to build a cube on it.  Analysis Services 2005 just needs your untransformed tables, views or stored procedures.  Special queries stored in the AS database's Data Source View can help you transform the data if need be, and single tables can be used for multiple dimensions, so you don't need to create multiple views on the same table.  You can even use your fact table as a dimension table.
• OLAP development should be more like other development.  When you do go beyond simple cubes, and you need to write some server-side MDX code to make things work the way you want, you should have development and debugging support akin to what most developers are used to.  Designing cubes inside Visual Studio makes this possible, and it's really cool!
• Learning MDX should be less necessary and less difficult.  OLAP/MDX developers now have the same kind of drag and drop code generation and Intellisense support (albeit limited) that other devs do.  They also have access to a huge library of templates for both server-side MDX expressions and client-side MDX queries.  I'm all for coding into a blank page, but if used properly, tools and templates help you learn.  MDX is a bear; that shouldn't be compounded with a velvet rope and a bunch of bouncers denying you entrance to the learning club and, thankfully, it no longer is.
• OLAP reporting should be easy.  Check!  Reporting Services' support for OLAP (and data mining) is strong, with an MDX query designer built right into the product.  That same designer is now built into SQL Server Management Studio too, making Analysis Services a first class client inside SSMS, right alongside SQL's relational engine.
• There should still be cool stuff for the experts.  Don't worry OLAP experts, your franchise has not been eradicated.  There's a slew of features that you, and only you, can help customers realize the full value of.  What this release does is lower barriers to entry, and most likely increase the market and demand for your expertise.

'Nuff said.  If you want to learn more, you gotta wait for the book.

Code and slides for my ADO MD.NET session can be found here.

Code zips from both of my Tech Ed talks are now posted.  Code from my "Windows Forms: Making the Most of WinForms 2.0 Data Binding" session (CLI322) with Steve Lasker (including Steve's code) can be found here.  Code from my "Developing SQL Server 2005 OLAP Applications with ADO MD.NET" session (BIN326) can be found here.

I enjoyed giving both talks.  They took a ton of work and the positive responses to both were a great reward.

(Free copy of Office 2003 Pro for the first person who posts a comment correctly explaining that reference.)

AS 2005 is an intensely compelling product.  If I may borrow a concept from OLAP, the Analysis Services product improves on its predecessor in a number of “dimensions,” including ease of use, feature richness, sophistication of client tools, and programmability.  This release truly feels like a serious tool for developers, statisticians, and scientists. It makes AS 2000 look like a bare-bones, proof-of-concept product by comparison.

In preparation for some upcoming presentations (including one at Tech*Ed in a month and a half), I’ve been working especially hard over the last week studying Analysis Services Data Mining and, on the OLAP side, ADO MD.NET programming for both the server and the client.  My general impression while examining all of this technology is that it is rich, elegantly extensible, and much easier to use than its predecessors.  Some highlights follow:

• ADO MD.NET will be familiar to ADO MD and ADO.NET programmers alike.  Do you prefer thinking in terms of axes and cellsets?  It’s all covered.  More comfortable using DataReaders and DataSets?  That’s accommodated too.  Jealous of the CLR programmability on the relational side of SQL Server 2005?  Don’t be: you can now use the objects in the Microsoft.AnalysisServices.AdomdServer namespace in a .NET assembly to create MDX-callable functions that can accept and return sets, tuples, and scalar values, all with the same security model as on the relational SQLCLR side.
• The number of Data Mining Algorithms has been extended significantly and the ability for you to create your own plug-in algorithms is carefully documented, rather than merely being implemented.
• Tools for browsing models have been extended significantly and are available from both Analysis Services Projects in Visual Studio (a.k.a. Business Intelligence Development Studio) and directly from SQL Server Management Studio.  Tools for visual development of prediction queries are also readily available in both tools and are easy to use (in AS 2000, the Prediction Query Wizard is buried in DTS, and viewing prediction query results is rather inconvenient).
• Mining Structures, a new entity in AS 2005 databases, contain a common data source/input column set/predictable column set configuration that can be used by multiple models within the structure.  Each constituent model can be built using a different algorithm, the same algorithm with different parameters, or a combination of both.  Better yet, new “lift” charts allow you to graph the relative accuracy of all your models, both for predictable columns in general and specific values of those columns.  As with other tools, lift charts are available from both Analysis Services projects in Visual Studio and from within SQL Server Management Studio.  Here’s a screenshot of the latter (forgive the large size, but I want you to be able to read all the text within):

I continue to believe that the real story to SQL Server 2005 is its business intelligence feature set, and I don’t just mean Reporting Services and ReportBuilder.  OLAP and Data Mining technologies from Microsoft are powerful and eminently useful analysis tools.  Their potential for adding value to the entire Microsoft stack, from Office to SharePoint, BizTalk to Commerce Server and every member of the Microsoft Business Solutions family, is tremendous.  Continue to ignore these technologies at your own peril. 

I've been playing with the February CTP of both Visual Studio 2005 and SQL Server 2005 this weekend.  One of my exercises for the day was trying to get a Visual Studio SQL Server CLR project that I wrote for SQL 2005 Beta 2/VS 2005 Beta 1 to compile.  The project has a stored procedure, a UDT, and a stubbed out function, aggregate and trigger.  I couldn't get this to compile in the VS December CTP, but I was determined to get it to compile in the new February CTP.

I got it to work, though not easily.  I found that some of my code was using constructs that are now deprecated (that was fast!) and I also encountered a few compiler and deployment errors.  In reverse-engineering the latter, I discovered that CLR UDTs and Aggregates now must be implemented as structures, rather than as classes.  With hindsight this makes sense but it's a rather stark change...now (it would appear) you can no longer store "objects" in the database.  Of course, nothing stops you from serializing a .NET object as XML and storing it in an XML column, but that's another story.

Here are a few other changes I "discovered" (I use quotes because these changes are in fact documented on other SQL Server-oriented blogs like Bob Beauchemin's and Wally McClure’s and that's how I worked my way through them):

• The SqlContext.GetPipe method is replaced by the SqlContext.Pipe property.  This is extremely important because it is through the returned SqlPipe object that you can send data and text back to a client from a CLR stored procedure.
• Likewise, the SqlContext.GetConnection method is replaced by SqlContext.Connection property
• The SqlConnection.CreateCommand method is effectively replaced by SqlContext.CreateCommand method (making less necessary the use of the SqlContext.Connection property, described above) 

I also had the darnedest time getting even the stubbed out trigger to deploy.  Since my CLR project points to the AdventureWorks database, I defined the code as a FOR UPDATE trigger on the Production.Product table, using the SqlTrigger attribute and it just wouldn’t work.  As best I can tell, this attribute will not work on a namespaced table!  When I pointed the trigger to the non-namespaced “DatabaseLog” table, everything worked fine.  Since that’s not a table I would want to create my own trigger on, I worked around to the problem.  I removed the SqlTrigger attribute altogether, compiling the code as a plain VB Sub (function in C#), then after deploying the assembly, executed the following T-SQL code:

Create Trigger trgInsertTest

On Production.Product

For Update

As External Name [SSProjTestFebCTP].[SSProjTestFebCTP.Triggers].[trgInsertTest]

If anyone knows how to get the SqTrigger attribute to work with a namespaced table, I’d sure like to know.

Microsoft today announced a new Workgroup edition of SQL Server (and also announced an OEM arrangement with Dell for it!).  A SQL 2K version is available immediately, and SQL 2005 will have Express, Developer, Workgroup, Standard and Enterprise editions.  Standard and Enterprise will continue to be the only non-developer versions that include Analysis Services, and their prices will increase a bit.  Meanwhile, the lower cost of Workgroup Edition (and the lack of cost for Express Edition) combined with the no-extra-license policy for passive failover servers and multi-core servers contribute to making SQL Server a pretty very cost-effective product compared to its competitors.  Tom Rizzo discusses the new product lineup here, and the press release is here.