Features of SQL Server 2012

Features

}Column Store Index

}Sequence objects

}Contained Database

}Always On (HADRON)

}Ad Hoc Query Paging

}FileTable

}Audit Enhancements

}Enhanced PowerShell Support

}Big Data Support

Column Store Index

}Columnstore indexes provide an easy way to significantly improve data warehouse and decision support query performance against very large data sets

}Performance improvements for “typical” data warehouse queries from 10x to 100x

}Ideal candidates include queries against star schemas that use filtering, aggregations and grouping against very large fact tables

How Are These Performance Gains Achieved?

Two complimentary technologies:

◦Storage

–Data is stored in a compressed columnar data format (stored by column) instead of row store format (stored by row). 

–Columnar storage allows for less data to be accessed when only a sub-set of columns are referenced

–Data density/selectivity determines how compression friendly a column is – example “State” / “City” / “Gender”

–Translates to improved buffer pool memory usage

◦New “batch mode” execution

–Data can then be processed in batches (1,000 row blocks) versus row-by-row

–Depending on filtering and other factors, a query may also benefit by “segment elimination” - bypassing million row chunks (segments) of data, further reducing I/O

Creating a columnstore index

Defining the Columnstore Index

}Index type

◦Columnstore indexes are always non-clustered and non-unique

◦They cannot be created on views, indexed views, sparse columns

◦They cannot act as primary or foreign key constraints

}Column selection

◦Unlike other index types, there are no “key columns”

–Instead you choose the columns that you anticipate will be used in your queries

–Up to 1,024 columns – and the ordering in your CREATE INDEX doesn’t matter

–No concept of “INCLUDE”

–No 900 byte index key size limit

}Column ordering

◦Use of ASC or DESC sorting not allowed – as ordering is defined via columnstore compression algorithms

Supported Data Types

Supported data types

◦Char / nchar / varchar / nvarchar

–(max) types, legacy LOB types and FILESTREAM are not supported

◦Decimal/numeric

–Precision greater than 18 digits NOT supported

◦Tinyint, smallint, int, bigint

◦Float/real

◦Bit

◦Money, smallmoney

◦Date and time data types

–Datetimeoffset with scale > 2 NOT supported

Maintaining Data in a Columnstore Index

}Once built, the table becomes “read-only” and INSERT/UPDATE/DELETE/MERGE is no longer allowed

}ALTER INDEX REBUILD / REORGANIZE not allowed

}Other options are still supported:

◦Partition switches (IN and OUT)

◦Drop columnstore index / make modifications / add columnstore index

◦UNION ALL (but be sure to validate performance)

Sequence objects

The new CREATE SEQUENCE statement can be used to create a database wide SEQUENCE object to use.

-- Create a SEQUENCE object on schema "dbo".

CREATE SEQUENCE MySeq AS tinyint

START WITH 0

INCREMENT BY 5

MINVALUE 0

MAXVALUE 255

CYCLE; GO

                        OR

CREATE SEQUENCE MySeq;

-- Verify if the MySeq is created.

SELECT * FROM sys.sequences WHERE name = N'MySeq';

-- Use Sequence object like:

SELECT NEXT VALUE FOR MySeq;

Contained Database

}A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

}The real question is, “What about users who are connecting to this database?” Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In short

Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.

}Create Contained Database

CREATE DATABASE [ContainedDatabase]
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME = N'ContainedDatabase', FILENAME = N'C:\ContainedDatabase.mdf')
LOG ON
( NAME = N'ContainedDatabase_log', FILENAME =N'C:\ContainedDatabase_log.ldf')
GO

}Create User in Contained Database

USE [ContainedDatabase]
GO
CREATE USER ContainedUser
WITH PASSWORD = 'pass@word';
GO

Enable Contained Database

Set the Containment Type to Partial in the Database Properties

Always On (HADRON)

The new High Availability and Disaster Recovery (HADR) features of Denali will offer a vast improvement over the database mirroring capabilities of SQL Server 2008.  AlwaysOn allows you to create availability groups of databases that can be failed over simultaneously.  This is especially important when you wish to fail over interdependent applications that rely upon more than one database.  AlwaysOn also allows you to create active secondary servers, making use of the redundant databases for part of your read workload.

}AlwaysOn Availability Groups is a new feature that enhances and combines database mirroring and log shipping capabilities

}Ability to configure availability groups through T-SQL, SSMS, and PowerShell

}Synchronous or Asynchronous commit

}Automatic and Manual failover

}Multiple databases support in availability groups

}Read-only access to the secondary

}Support for Filestream data type

}Failing over client connections using the new connectivity story based on virtual network names and virtual IP addresses

}Including logins in user databases through a Contained Database

}SSMS, Catalog Views, and DMVs to view and monitor state

}Support for multiple availability groups on the same instance

}Support for availability groups on standalone instances and/or failover cluster instances

Ad Hoc Query Paging

The ad hoc query paging enhancements to TSQL add the OFFSET and FETCH keywords to the ORDER BY clause, allowing you to specify the number of rows you wish to retrieve from a long result set and page through those results in a manner specified in the query.

FileTable

The FileTable is a neat bridge between databases and the operating system allowing database users to directly access unstructured data stored in the Windows file system.  Each FileTable uses a predefined schema to access a hierarchy of directories and files stored in the file system.  Each row in the FileTable corresponds to a single directory or file in the file system.  Files can be modified either through SQL Server or through Windows and updates are reflected in both places.

GUI after creating the table

Here is how the folder looks

Query The FileTable

Audit Enhancements

Hippa Compliance with SQL Server 2008:
(These all Audit Features are more enhanced in SQL Server 2012)

Enhancements to SQL Server auditing capabilities bring added flexibility and usability for auditing across the SQL Server environment, helping to make it even easier for organizations to meet compliance policies:

}SQL Audit is now available for all editions of SQL Server (previously was only available via the Enterprise edition

}User-Defined Audit allows an application to write custom events into the audit log to allow more flexibility to store audit information

}Audit Filtering provides greater flexibility to filter unwanted events into an audit log

}Audit Resilience delivers the ability to recover auditing data from temporary file and network issues

On Audit Log Failure Options

In SQL Server 2012 they have added two more options for audit log failure. In earlier version the only option was to shut down the server when there was audit log failure. Now you can fail the operation as well continue on log failure. This new options now give finer control on the behavior of the audit failure scenario. When target is not available due to any reason and audit cannot log the event, it can be now continued, in another word the audit continues to attempt to log events and will resume if the failure condition is resolved. This is very important feature because earlier when Audio was failing the option which we had was to shutdown the server. There were the cases when shutting down the server is not the good option but continuing the business operation is the priority, this option should be exercised.

Maximum Rollover Files

Earlier in 2008, there were two options – have infinite number of log files or roll over the files after fixed number. Now in SQL Server 2012 there is option to keep the fixed number of the files along with no roll-over. This gives additional control to user when they want to save every single data and do not want to lose any critical information due to rollover.

Enhanced PowerShell Support

Powershell leverages many components to bring a powerful scripting interface that surpasses the old WMI and VBScripting, making use of the .NET framework and other rich programming interfaces and libraries to leap over the boundaries that each of the general MS applications and components encounter.

Big Data Support

Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

Compiled By: Rajesh Rolen