SQL Server 2012 (Denali)

New Features in SQL Server 2012 - RC0 Released

Photo © Microsoft

Microsoft SQL Server 2012 RC0 was recently released. RC stands for Release Candidate which is basically the version virtually production ready. Microsoft referred to this release as SQL Server Code Named "Denali" but has settled on SQL Server 2012 as the final name for the product.. Business intelligence (BI) is critically important to organizations both large and small. In the latest release of SQL Server, there is no shortage of BI enhancements in addition to many other enhancements. This article will give you a preview of the requirements, new features and enhancements in SQL Server 2012 (code named Denali) including:

  • Hardware and Software Requirements
  • Multi-Subnet Failover Clustering
  • Programming Enhancements, including sequences, ad-hoc query paging and full-text search tweaks
  • BI and Web Development Environment Improvements
  • Web-based Visualization
  • Data Quality Services

Keep in mind that this information is for preview only and is subject to change by Microsoft.

Hardware and Software Requirements

  • Microsoft recommends using NTFS file format instead of FAT32. FAT32 will work but you should probably not use it.
  • You can't install SQL Server 2012 (code-named Denali) on mapped drives or compressed drives.
  • You have to have the "no-reboot" package installed prior to installing SQL Server 2012 (code-named Denali). This is included in Windows 7 SP1 and Windows Server 2008 R2. Otherwise, you can download the no-reboot package from Microsoft.
  • SQL Server 2012 (code-named Denali) requires the .NET Framework 4.0.
  • Virtualization is supported using Microsoft's Hyper-V technology.
  • You will need at least 3.6 GB of free disk space.
  • Microsoft recommends that you do not install SQL Server 2012 (code-named Denali) on a domain controller.
  • Recommended Processors & RAM
    • 64-bit version: AMD Opteron, AMD Athlin 64, Intel Xeon with Intel EM64T Support or Intel Pentium IV with EM64T support running 2.0 GHz or faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
    • 32-bit version: Pentium III or compatible running at 2.0 GHz of faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
  • Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012 (code-named Denali). You can get this software from the Windows Management Framework page.

Multi-Subnet Failover Clustering

With SQL Server 2012 (code-named Denali), you can configure SQL Server where failover cluster nodes can be connected to a completely different subnet. The subnets can be spread out to different geographical locations providing disaster recovery along with high availability. In order for this to work correctly, you will need to replicate the data across the databases involved in this configuration. The SQL Server failover cluster is dependent on the Windows Server failover cluster so this has to be set up first. Keep in mind that all of the subnets involved in this configuration must be in the same Active Directory domain.

Programming Enhancements

  • Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. /****** Create Sequence Object ******/
    • CREATE SEQUENCE MySequence
    • START WITH 1
    • /****** Create Temp Table ******/
    • DECLARE @Person TABLE
    • (
    • FullName nvarchar(100) NOT NULL
    • );
    • /****** Insert Some Data ******/
    • INSERT @Person (ID, FullName)
    • VALUES (NEXT VALUE FOR MySequence, 'Jim Johnson'),
    • (NEXT VALUE FOR MySequence, 'Bob Thompson'),
    • (NEXT VALUE FOR MySequence, 'Tim Perdue');
    • /****** Show the Data ******/
    • SELECT * FROM @Person;
    • The results would look like this:
    • ID FullName
    • 1 Jim Johnson
    • 2 Bob Thompson
    • 3 Tim Perdue
  • Ad-Hoc Query Paging: Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bringing back only the results you want to show to your users when they are needed. The following TSQL code runs against the Person table in the AdventureWorks sample database (available from Microsoft). In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.
    • SELECT BusinessEntityID, FirstName, LastName
    • FROM Person.Person
    • ORDER BY BusinessEntityID
    • OFFSET 10 ROWS
  • Full-Text Search: The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.

BI and Web Development Environment Improvements

Microsoft moved BI (Business Intelligence) closer to the end user with SQL Server 2008 R2. The Excel PowerPivot tool help users by creating a self-service reporting model. The good news is PowerPivot is being enhanced in SQL Server 2012 (code-named Denali). Microsoft is adding KPIs and drill through, which will be really useful for all users.

Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:

  • Data Model
  • Business Logic
  • Data Access

BISM will enhance Microsoft's front-end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

Web-Based Visualization - Project Crescent

Project Crescent is the Microsoft code name for the new reporting and visualization tool expected in SQL Server 2012 (code-named Denali). Project Crescent provides drag and drop ad-hoc reporting functionality and was built entirely on Silverlight. It includes a powerful query tool and interactive storyboarding to allow a user to share visualizations of large datasets.

Data Quality Services

Data Quality Services is a knowledge-based approach that runs in SSIS (SQL Services Integration Services). Data quality is one of those things that you never get perfect. Microsoft is introducing "Impact Analysis and Lineage" which will give you information about what your data depends on. It also shows the lineage of the data, including where it comes from and the systems that are behind it.