As part of my recent quest to learn about SQL Server from an infrastructure perspective, I’ve been attending a number of events, one of which was Michael Platt‘s keynote at the May 2005 Microsoft Technical Roadshow – SQL Server 2005 for IT Pros.

In my opinion, SQL Server 2005 (codenamed Yukon) is probably Microsoft’s most significant product release since Windows 2000. Central to the data management functionality within the Microsoft platform, this new version of SQL Server, due for release in November, is a massive rewrite (around 3 million lines of C# code), including a huge number of new features with improvements in three key areas, all underpinned by new levels of security and reliability:

• Developer productivity:

• Visual Studio 2005 integration.

• SQL Server database.
• SQL Server replication services.

• SQL Server integration services (SSIS) – replacing data transformation services (DTS).
• SQL Server analytical services.
• SQL Server reporting services.
• SQL Server notification services.

In terms of developer productivity, the Microsoft .NET Framework common language runtime (CLR) is now part of the SQL engine, allowing database-side coding using any Microsoft .NET language. XML support is no longer a bolt-on component, with XML now supported natively, feeding into the web services strategy, with integrated web services features including the new SQL Service Broker for messaging between SQL Server and other systems.

Looking at enterprise data management: availability and security is enhanced with support for database mirroring and online operations as well as data security and privacy (encryption and enhanced auditing); manageability is improved with a self-tuning database, fast recovery and restore, and a host of management tools; and the platform scales to cover a huge range of devices from smartphones to massive 64-bit installations using technologies such as partitioning and snapshots to allow the system to scale in line with business growth.

For business intelligence, all of the previously separate tools are pulled together into a single front-end management interface with two components – Management Studio for management and BI Studio for design – both of which are closely integrated with Visual Studio. SQL Server 2005 has more real-time analytical support built into the platform as well as a comprehensive extract, transform and load (ETL) capability for the entire enterprise, supporting heterogeneous databases.

In the past, DTS used the database engine to carry out the transform whereas SSIS carries out the transform itself, with some standard transforms included, but also expandable with custom transformations. With SQL Server 2005, the SQL Server reporting services reporting solution is supplemented with a report builder for user generated reports and an multidimensional expressions (MDX) builder for developers; providing an interactive enterprise reporting environment, integrated with Office System applications . Analysis Services allows the integration of relational databases and OLAP cubes to cache the OLAP data in synchronisation with relational updates and perform analysis on real time data. Finally, data mining capabilities (previously the domain of expensive high-end technologies) become available for general purpose use in SQL Server 2005 offering exploration, pattern discovery and pattern description.

In line with the Microsoft policy of using its new products internally (a process which they refer to, somewhat delightfully, as “dogfooding”), Microsoft is already using SQL Server 2005 heavily for its enterprise applications (e.g. their SAP R/3 1.7Tb database, the staging data warehouse for all Microsoft data and the Microsoft sales revenue and reporting system – basically all of Microsoft’s business-critical applications!). Looking at the metrics for just one of those applications – the SAP R/3 system in Redmond which handles Microsoft global financial, human resources, sales and distribution resources – this 1.7Tb database over 25 production servers services 2500 named users (over 57000 users in total with between 200 and 600 using the system concurrently) handling 300 000 SAP transactions a day and 100 000 batch jobs a month whilst maintaining greater than 99.9% SAP availability and less than 0.5 second response time – on beta code!

In my opinion, SQL Server 2005 will be Microsoft’s most significant product release since Windows 2000 and is central to the Microsoft platform. The constant battle between Microsoft and Oracle will continue for some time to come but this new version of SQL Server (which has been a long time coming) might finally help to persuade IT Directors that Microsoft is a serious contender in the enterprise data management space.

For more information about SQL Server 2005, I recommend the Microsoft TechNet SQL Server Tech Center, Mat Stephen’s blog, and Jamie Thomson’s blog (particularly for SSIS). SQL Server 2005 is due for release in November 2005, with four product editions – Express, Standard, Workgroup and Enterprise.