July 13, 2011
Microsoft releases SQL Server Code Name Denali CTP3
On July 11th Microsoft released SQL Server Code Name "Denali" CTP3 (Community Technology Preview 3). According to the official blog from Microsoft Tech, SQL server code name "
Before getting into the new features provided by CTP3 we will give you a run down of current features provided by "Denali" CTP1 and then cover CTP3.
SQL Server Code Name "
Multi-Subnet Failover Clustering:
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.
Read more about Installing a SQL Server "Denali" Failover Cluster
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
INCREMENT BY 1;
/****** Create Temp Table ******/
DECLARE @Person TABLE
ID int NOT NULL PRIMARY KEY,
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:
1 Jim Johnson
2 Bob Thompson
3 Tim Perdue
Read more about Sequences
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 2011. 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
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
Read more about Ad-hoc Query Paging
Full Text Search:
The Full Text Search in SQL Server 2011 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 helps users by creating a self-service reporting model. The good news is that PowerPivot is being enhanced in SQL Server 2011 (
Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:
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 is the Microsoft code name for the new reporting and visualization tool “expected” in SQL Server 2011 (
Read more about Project Crescent
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.
SQL Server Code Name “
Delivery of the required 9s and data protection needed with AlwaysOn which delivers added functionality over CTP1.
Unprecedented performance gains with Project “Apollo”, a new column store index that offers 10-100x star join.
Rapid data exploration across your organization using Project “Crescent”, which is available now for customers for the first time.
Credible and Consistent Data:
BI semantic Model (BISM) - Provides a consistent view across heterogeneous data sources with a single model for Business Intelligence applications, from reporting and analysis to dashboards and scorecards.
Master Data Services add-in for Excel – assures data quality as part of a new Data Quality Services package that is integrated with third party data providers through Windows Azure Marketplace. Available integrated Marketplace providers include:
Cdyne: CDYNE phone verification will validate the first 7 digits of your phone number(s) and return what carrier the phone number id is assigned to, whether it is a cellular number or a land line, the telco, and additional information including time zone, area code and email address if it is a cellular number.
Digital Trowel: Powerlinx allows users to send data for refinement and enhancement and receive back cleansed and enriched data. The database contains 10 million company website addresses, 25 million detailed company profiles, and 25 million executives, including 5 million in-depth profiles with email addresses and phone numbers.
Loqate: The Loqate Verify enables users to parse, standardize, verify, cleanse, transliterate, and format address data for 240+ world countries. The Loqate Geocode enables a latitude-longitude coordinate to be added to any world address with worldwide coverage to city or postal code for over 120 countries.
Melissa Data: WebSmart Address Check parses, standardizes, corrects and enriches
Productive Development Experience:
Optimizes IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified and modern development experience across database, BI, and cloud functions. Additionally, Express customers can test a new LocalDB version for fast, zero-configuration installation.
CTP3 is a production quality release that includes access to upgrade and migration tools like Upgrade Advisor, Distributed Replay and SQL Server Migration Assistant (SSMA). Upgrade Advisor and Distributed Replay allow you to perform thorough analysis and testing of your current SQL Server applications before upgrading so you know what to expect. You can also use SSMA to automate migrate non-SQL Server databases to SQL Server Code Name “
Updated: July 13, 2011 1:40 PM