Archive for the ‘SQL’ Category

Not every team at Microsoft is insisting on secrecy about futures. In fact, the SQL Server database team is taking a much different course than its Windows brethren.

Just a couple of months after releasing to manufacturing SQL Server 2008, Microsoft is starting to offer details about its next release of its flagship database. That product, code-named “Killimanjaro,” will ship in the first half of 2010, according to Microsoft officials. The first tech preview test builds of Kilimanjaro are due in the first half of 2009, the Softies said.

Microsoft shared information about Kilimanjaro at the company’s second annual Business Intelligence Conference, which kicked off on October 6 in Seattle.

Kilimanjaro is set to include self-service analysis tools (codenamed “Gemini”) that Microsoft is saying will allow information workers to better “slice and dice data and create their own BI (business intelligence) applications and assets to share and collaborate on from within the familiar, everyday Microsoft Office productivity tools they already use.” Gemini will incorporate an update to SQL Server Analysis Services, plus some new in-memory components, some “splicers” and add-in exerpiences based on Excel, and deeper SharePoint integration, enbling users to share analyses they’ve created, Microsoft officials said.

Update: It seems Microsoft also is planning to fold the master-data-management technology it acquired a while back when it bought Stratature into the Kilimanjaro SQL Server release.  Originally, Microsoft was talking about making MDM part of Office 14; perhaps it will simply be the BI-component complements to O14.

Update No. 2: Microsoft is not making master data management part of Kilimanjaro after all.  From a spokeswoman: “Kilimanjaro will work with MDM solutions but will not include MDM functionality of its own.” So it looks like MDM still, potentially, couldbe part of O14.

At the BI conference, the SQL team announced one more new codename to add to its repertoire: Project Madison. Microsoft is describing Madison as a “solution”; from its description, it seems that means data-warehouse appliance. Madison is based on technology Microsoft recently bought when it acquired DATAllegro, a data-warehouse-appliance vendor. Microsoft is promising that, via Madison, it will be able to help users with data-warehousing workloads spanning hundreds of terabyes of data and thousands of concurrent users.

Microsoft is planning to release a Madison Community Technology Preview (CTP) test build in the next 12 months. No word from Microsoft on when — or in what form — Microsoft plans to ship Madison.

Microsoft’s BI platform includes Excel and other Office applications, SharePoint Server for “team BI,” PerformancePoint (scorecarding) Server for organization-wide BI and SQL Server for data-platform needs.

When I asked the SQL Server team why they already were starting to talk publicly about Kilimanjaro,  Microsoft BI general manager Bob Lokkentold me that his team was committed to providing customers with insight into their future roadmap. He noted that Microsoft has said publicly that it plans to deliver a new SQL Server release every 24 to 36 months and wanted to make sure its partners and customers know where it’s going.

Problem
In preparation for the upcoming MSSQLTips.com SQL Server 2008 web cast, I want to start to introduce some of the new SQL Server 2008 technologies at a surface that will start to shape the industry.  The features are across just about every major component in the product suite so the benefits should be broad and perceived as beneficial not only from an IT perspective, but also from a user perspective.  This should ultimately yield a significant business benefit as SQL Server 2008 is adopted in 2008 and 2009.

Solution
Let’s jump into each the of new SQL Server 2008 product categories, features and provide references to some of these technologies as additional points of reference.

  • Security and Data Auditing
    • Transparent Data Encryption – This is encrypting the data while it is on disk and remains transparent to applications
    • External Key Management – This new functionality relates to consolidation of key management and integration with external products
    • Data Auditing – This is one core feature of SQL Server 2008 that will include a number of new features to include:
      • The introduction of first class ‘AUDIT’ objects
      • Auditing DDL (data definition language) commands
      • Support for multiple logging targets
    • Additional information – http://www.microsoft.com/sql/prodinfo/futureversion/datamanage/default.mspx
  • Availability and Reliability
    • CPUs – Support for pluggable CPUs which means that a CPU can be added on the fly and recognized by SQL Server 2008 just like memory in SQL Server 2005
    • Database Mirroring – Enhanced Database Mirroring to include compression of mirror streams, enhanced performance and automatic page-level repair for the principal and mirror
  • Performance
    • Data Compression – This new feature provides the ability to easily enable or disable data compression as an online command as well as offer more efficient data storage above and beyond traditional data compression
    • Backup Stream Compression – The ability to configure compression with server level control or backup statement control of all backup types (full, differential, transaction log)
    • Performance Data Collection – When you are experiencing a performance issue the biggest problem is pinpointing the problem, so with SQL Server 2008 Microsoft is introducing a single common framework for performance related data collection, reporting, and warehousing
    • Improved Plan Guide Support – With SQL Server 2008 plans can be frozen for permanent query usage as well as pull plans directly from plan cache with SQL Server Management Studio integration
    • Resource Governor – If you have had the need to segment your SQL Server resource utilization then you should be looking forward to SQL Server 2008 because you will have the opportunity to create pools and groups to segment the resources and govern them independently
  • Management
    • Policy-Based Management Framework – The ability to manage objects via policies as opposed to traditional scripts with inherent monitoring and enforcement
    • Microsoft System Center – Integration with Microsoft System Center which a product from Microsoft to improve operational costs
    • Extended Events – Another new feature is Extended Events which is a high performance yet light weight tracing infrastructure with insight into the core engine independent of SQL Trace
  • New Data Types
    • Date Time Data Type – The datetime data type will now be able to support the following:
      • Precision to the 100th nanosecond which is 7 digits past second
      • Time-zone datetime offset to translate the datetimes across numerous time zones
      • Rather than having to parse the datatime for just the date or just the time, now SQL Server 2008 will have date only support as well as time only support
    • HierarchyID – With the introduction of the HierarchyID data type this data type will be hierarchical-aware and will be accompanied by built-in functions, methods, etc. to support complex hierarchies in your data with .NET
  • Development Enhancements
  • Service Broker
    • Interface – A new user interface and tools will be released for working with Service Broker in order to add, drop or edit Service Broker objects directly in SQL Server Management Studio
    • Conversation Priority – The ability to set message ordering with a send and receive impact with levels one to ten
  • Data Storage
    • Data Compression – Reference the Performance section above
    • FILESTREAM Attribute – With this feature get the best of both worlds with functionality from BLOBs in the DB vs. BLOBs on filesystem
    • Integrated Full Text Search – With SQL Server 2008 Full Text Search is fully integrated into the relational engine with no external storage, no external service as well as more efficient and reliable costing
    • Sparse columns – SQL Server 2008 has more efficient storage for ‘wide’ tables with many columns that repeat and do not contain data
    • New index types – New indexes include:
      • Spatial indexes
      • Hierarchical indexes
      • FILTERED indexes (indexes on filtered values within columns)
  • Data Warehousing/ETL
    • Partitioned Table Parallelism – This feature eliminates the one thread limit per partition
    • Star Join support – SQL Server 2008 now supports a star join with no special syntax which is completely optimizer based with full backward syntax support
    • Data compression – Reference the Performance section above
    • Resource Governor – Reference the Performance section above
    • Persistent Lookups in SSIS – There is no longer a need for re-querying for lookup operators and cache lookups in multiple ways with the ability to persist lookups to disk
    • Improved thread scheduling in SSIS – This is accomplished by a shared thread pool and pipeline parallelism
    • SQL Server Change Tracking – Reference the Development Enhancements section above
    • MERGE statement – The MERGE statement will add a great deal of value with slowly changing dimensions
    • Scale-out analysis services – With read-only storage multiple Analysis Services SQL Servers can be leveraged
    • Subspace computations
    • New tools for cube design
    • Best practice design alerting
    • Backup cubes with better scalability
    • Excel – Data-mining add-ins for Excel
  • Reporting
    • Reporting Services Deployment – IIS is no longer required to run Reporting Services
    • Rich-text support
    • Enhanced visualization graphing
    • Word – Reports can be rendered to Microsoft Word
  • Deprecation

Next Steps

Related Tips

Forum Posts

10 reasons why SQL Server 2008 is going to rock

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. 

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 

Instead of:

DECLARE @myVar int 
SET @myVar = 5
you can do it in one line:
DECLARE @myVar int = 5

Sweet.

8.  C like math syntaxSET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It’s a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It’s a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it’s not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 

Anyway, I don’t really care about space savings – storage is cheap.  What I do care about is that the feature promised (key word here “promises”) to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great – will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it’ll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that’s last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it’ll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another – a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc…).  If you’ve achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server – well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature…well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form – a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int) 
DECLARE @myPeeps PeepsType 
INSERT @myPeeps SELECT 'murphy', 35 
INSERT @myPeeps SELECT 'galen', 31 
INSERT @myPeeps SELECT 'samuels', 27 
INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps 

 
And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with Intellisense use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing). 

Built-in intellisense is huge – it means new people can easily learn the database schema as they go.