SQL Server 2008 - White Papers

Microsoft released a first bunch of SQL Server 2008 whitepapers.

Happy reading ...

SQL Server 2008 Overview Whitepapers

· SQL Server 2008 What’s New

· Business Intelligence

· Data Warehousing

· Data Programmability

· Online Transaction Processing

· Server Consolidation

July CTP of SQL Server 2008 released

As expected, Micosoft released the July CTP of SQL Server 2008. The new download is available on the Connect website. This new CTP contains quite a bunch of nice features; including new datatypes and a first public release of TABLIX. See the full list below and in the updated Improvement Pillars. There is also a series of webcasts on the new features.

Analysis Services Time Series

This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.

Enterprise Reporting Engine

Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.

T-SQL Improvements

Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.

Database Mirroring Enhancements

SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:

• Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.
• Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.
• Miscellaneous performance enhancements:

• using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.
• better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.

• Supportability and diagnosability improvements:

• additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.
• new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.
  

Extended Events

SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.

Performance Data Collection

Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:

• Define what data is collected and organize the collection into collection sets
• Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
• Define where data is stored (relational database)
• View data through reports in SQL Server Management Studio.
• Provide platform to plug in more data collectors in the future.

Improved XML Support

To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.


DATE/TIME Data Types

SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.

Large User-Defined Types Improvement

Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

ORDPATH Improvement

ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.

BIDS Helper - A must have for BI developers

BIDS Helper is a set of Visual Studio.Net add-ins that extend and enhance the functionality of the SQL Server BI Development Studio. The current release (1.1.0.1) containts the following add-ins:

• Aggregation Manager
• Calculation Helpers
• Deploy MDX Script
• Dimension Health Check
• Printer Friendly Dimension Usage
• Show Extra Properties
• Update Estimated Counts
• Visualize Attribute Lattice

I really like the "Deploy MDX Script" add-in. This feature allows you to right click on a cube in an Analysis Services solution and deploy just the calculation script. This comes in very handing while debugging and testing MDX calculations in offline mode.




It's a open source and available as a free download on CodePlex. Toeltje van de week!!!

Rumor: new CTP of SQL Server 2008 coming soon

Rumor has it that a new CTP of SQL Server 2008 is on its way. A recent article on the MCP Magazine mentions the planned +/-60 days release schedule between CTPs. CTP3 aka the June CTP was released on June, 4. The June CTP was nice but lacks a lot of the announced features. So ... looking forward to the new stuff to spend those rainy Belgium summer evenings ;-)

If you wanna get a good overview of the new stuff, check out these resources:

• Bob Beauchemin's list of new features.
• If you have about 1 to 2 hrs to spend: the free online course on SQL Server 2008. It's really good, and for the moment, the best and most (public available) in depth overview available.
  

Next SQLUG.BE Session on Analysis Services

On September, 27th we are organizing our next SQLUG.BE event. The event - an introduction to Analysis Services 2005, will take place at the Kohera Office and will be presented by my colleague Nico Verbaenen.

Full details and free registration can be found on here.

SQL Internals Viewer

Just discovered a great tool via Kalen Delaney's Blog. This tool gives you a low level graphical view on how data is stored in a database. Toeltje van de week!!!

SQL Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works. This tool has been designed to make database internals more accessible.

The Quick Start section gives a guide on how to use SQL Internals Viewer.

The Resources section lists articles and books with information on SQL Server internals.

http://www.sqlinternalsviewer.com/

Office 2007 - mapping the old menus to the ribbon

I really like the ribbon in Office 2007. I think it is a great improvement over the old menus. However, every now and then I get lost in the ribbon and can't seem to find that very nice little menu option. Microsoft released an excellent set of Excel workbooks. The Excel sheets list every single menu option in the old Office applications and their corresponding place in the Office 2007 ribbon.

Excel ribbon mapping workbook

Outlook ribbon mapping workbook

Word ribbon mapping workbook

PowerPoint ribbon mapping workbook

Nice to see this one in the Help Menu ;-)

Show the Office Assistant

>

Removed from product

Bye Bye Clippy :-)

SQL Server Launch Date: February 27, 2008

At it's annual Worldwide Partner Conference yesterday, Microsoft announced the release dates for it's next trio of products: Windows 2008, SQL Server 2008 and Visual Studio 2008. On February 27, 2008, Microsoft will launch these products in Los Angeles with a wave of kickoff events around the world to follow.

Try Catch

Bart Bultinck, fellow at SQLUG.BE, and some other top bloggers in the Belgian Blogsphere started the excellent blog site TryCatch.be. Chummy, Kurt Roggen, Peter De Tender, Tom Vandaele, any many more to come are blogging now on this must have RSS feed.

SQL Server 2005 Best Practices Analyzer RTM

Microsoft released a new version of the SQL Server 2005 Best Practices Analyzer (July 2007). "CTP" was dropped from the name, so we are RTM now, I guess.

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

Download here.

Free online training on SQL Server 2008

Microsoft released a first free online course on SQL Server 2008.

Full details can be found on https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=78337

Title: Clinic 7045: What's New in Microsoft® SQL Server™ 2008

Course Type: Self-paced Course

Available Offline: Yes

Estimated Time of Completion: 2 Hours

Language: English

Description: In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience. To get the most out of this clinic, it is recommended that you have: Knowledge of general SQL database concepts that are largely independent of a specific version of SQL Server. Knowledge of Microsoft SQL Server 2005 features. Knowledge of deploying and upgrading database solutions. Knowledge of how to solve performance issues related to SQL Server hardware. Performed the job role of a SQL Server 2005 database administrator or database developer. Product or technology experience in Microsoft SQL Server 2000 or SQL Server 2005.

Objectives: At the end of the course, students will be able to: Describe the features of SQL Server 2008 and their benefits. Describe the features of enterprise data platform that help you to secure data in applications. Describe the dynamic development features that facilitate the development of database applications. Describe the features of SQL Server 2008 that provide data storage solutions beyond relational databases. Describe the enhanced features in SSIS that help you to integrate data effectively. Describe the enhanced features in SSAS that help you to improve the BI infrastructure. Describe the enhanced features in SSRS that help you to improve the scalability of the reporting engine.

Speaking at Community Day

On Thursday I will be presenting on the first Belgian Community Day.

Five belgian usergroups, BIWUG, VISUG, SQLUG, IT-Talks, and Pro-Exchange, have decided to combine their efforts to organize a joint-event just before summer starts. This event will be the biggest community event in Belgium! So if you are eager to learn, eager to get answers to your questions and eager to socialize, come and join us for our very first Belgian Community Day.

I will deliver a fun introduction to Microsoft BI. My collegue Werner Geuens will give an in depth session on statistics.

Full details and free registration can be found on http://www.communityday.be/

Here are the session abstracts.

Intro Session - biTunes – A fun introduction to Microsoft BI with SQL Server 2005 and… iTunes !

Session abstract:

SQL Servers 2005 gives you all the tools you need for building end-to-end business intelligence (BI) applications. In this demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come take a walk on the BI-side…

Speaker:
Frederik Vandeputte, Senior Consultant at Kohera and Vice President of the Belgium SQL Server User Group

Deep dive: Statistics in SQL Server 2005, what can go wrong

Session abstract:
Maybe you are not aware of it but every index is accompanied by its statistics. You can create statistics without index, but why would you do that? Even the engine creates statistics when it feels like doing so, how can that be of use to us? We have new database settings concerning statistics in SQL Server 2005; let's take a look at these. Once you know the use of statistics you'll realize that they need maintenance, how should we do that?

Speaker:
Werner Geuens, Senior Consultant at Cronos.

SQL Server 2005 builds that were released after SP2

This nice KB article lists the Microsoft SQL Server 2005 builds that were released after Microsoft SQL Server 2005 Service Pack 2 (SP2) was released.
http://support.microsoft.com/kb/937137

SQL 2008 - Declaring variables and assigning values in 1 statement

I just discovered a little nice to have in Katmai (oops, SQL Server 2008). Finally we can declare variables and assign values in one statement.

If you try this in SQL Server 2005 or earlier:

declare @myVar VARCHAR(10) = 'Yoohoo';

This is what you get:

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.

In SQL Server 2008 it just works:

declare @myVar VARCHAR(10) = 'Yoohoo';

The cool thing is that it also works for multiple variables:

declare
@myVar VARCHAR(10) = 'Yoohoo',
@myVar2 VARCHAR(10) = 'Cool!!'


Command(s) completed successfully.

So ... bye, bye to:

DECLARE ...

DECLARE ...

DECLARE ...

DECLARE ...

DECLARE ...

DECLARE ...


SET ...

SET ...

SET ...

SET ...

Yoohoo - fully certified on SQL Server 2005

A quick note from a happy IT boy. Today I passed exam 70-446 (PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005) which was actually the last SQL Server 2005 exam on my to do list.
Yoohoo :-)

SQL 2008 - Truncating the logfile

In the SQL Server 2005 the BACKUP LOG WITH TRUNCATE_ONLY statement was marked depricated, but still worked. In SQL Server 2008 it returns an error.

BACKUP LOG testtrunc WITH TRUNCATE_ONLY

Msg 155, Level 15, State 1, Line 3
'TRUNCATE_ONLY' is not a recognized BACKUP option.

So how can we truncate the logfile in SQL Server 2008? Well, just switch to SIMPLE RECOVERY and back to FULL.

ALTER DATABASE testtrunc SET RECOVERY SIMPLE
ALTER DATABASE testtrunc SET RECOVERY FULL

Be carefull, don't run this on an production database. You are erasing the transaction log file and will loose the possibility to do point in time restores.

SQL 2008 - MERGE is Back

So what's is the MERGE statement all about? Well basically it's doing an INSERT, UPDATE and DELETE in a single statement. It used to be part of the early betas of SQL Server 2005 but didn't make in the RTM version. And now it's back :-)

Here's a little example:

MERGE Departments AS d
USING Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN SOURCE NOT MATCHED THEN
DELETE;

Departments_delta contains updated Department information. Based on the primary key we decide wether to insert, delete, or update the Department table.

Community day 2007

Five belgian usergroups, BIWUG, VISUG, SQLUG, IT-Talks, and Pro-Exchange, have decided to combine their efforts to organize a joint-event just before summer starts. This event will be the biggest community event in Belgium! So if you are eager to learn, eager to get answers to your questions and eager to socialize, come and join us for our very first Belgian Community Day.

The event will take place at the premises of SemCom in Keerbergen on Thursday, the 28th of June 2007.

Free registration and full details can be found on communityday.be

Blog Silence Over

Due to some technical problems with Blogger and SFTP issues I dind't blog very much recently. The problems are now fixed, so we are ready to go again :-)

Microsoft released the first public CTP of Katmai yesterday ... so we have new stuff to play with and to blog about :-)

Katmai announced

Today at the first Microsoft Business Intelligence (BI) Conference, Microsoft Corp. presented its vision for the next version of its flagship data management and analysis platform. SQL Server™ code-named “Katmai,” the next release of Microsoft® SQL Server.

Read the full press release.

SQL Server “Katmai” is scheduled to be delivered in 2008. More information is available at http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx