New Features in SQL Server 2014

Microsoft introduced new features in SQL Server 2014.   Among them some of the important features for developers and DBA I listed here.

High Availability enhancements

Support s widows azure as secondary replica.

Maximum numbers of secondary replicas are increased from 4 to 8.

Numerous databases can failover at a time.

Clustered shared volumes feature associated with windows 2012 server for storing SQL server virtual machines.

New Dynamic managements views for high availability options & enhancements to existing DMVS                 

sys.dm_io_cluster_valid_path_names

sys.dm_hadr_cluster

sys.dm_hadr_cluster_members – no of nodes running on WSFC cluster

sys.dm_hadr_cluster_networks

Security and DB backup enhancements

 Now with SQL Server 2014

We have the ability to encrypt the data while creating database backup.

Database backup to URLS

Supports backup to windows azure.

Managed backups

Introduced new server level  permissions

Performance enhancements

In Memory OLTP

IO is added to resource governor to pool SQLServer resource workload

Column store index up-datable.

Encrypt data while taking database backup:

SQL Server 2014 has the ability to encrypt the data while backup the database. This backup option

Is only available when the database backup is set to new media .

In order to use this option first master key should be created in master database.

Then create the certificate in master database.

USE MASTER;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Malls@123'

--select * from sys.symmetric_keys

GO

--Create a certificate to use with TDE

--select * from sys.certificates

CREATE CERTIFICATE MYTDECert

WITH SUBJECT = 'TDE Certificate'

 Once we have executed the above we can encrypt the database while taking database backup.

In order to restore this database you need the certificate protected by master key. You can refer this post for restoring the encrypted database

Database backups to URL:

SQL Server 2014 introduced the ability to take database to URLs. Its supports database backup directly to windows azure. 

Similarly using managed backup s we can configure database backup to windows azure.

New Server level permissions:

SQL Server 2014 introduced new server level permissions to further enhance security and accessibility.

o   IMPERSONATE ANY LOGIN (server level)

o   CONNECT ANY DATABASE (server level)

o   SELECT ALL USER SECURABLES (Server level)

o   ALTER ANY DATABASE EVENT Notification (database level)

 In-memory OLTP:

Sample Script for creating the In-memory OLTP Database

CREATE DATABASE [SSISDatabase]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'SSISDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SHANMUKREDDY\MSSQL\DATA\SSISDatabase.mdf' , SIZE = 11264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [memory_optimized_filegroup_0] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT

( NAME = N'memory_optimized_file_345491253', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SHANMUKREDDY\MSSQL\DATA\memory_optimized_file_345491253' , MAXSIZE = UNLIMITED)

 LOG ON

( NAME = N'SSISDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SHANMUKREDDY\MSSQL\DATA\SSISDatabase_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [SSISDatabase] SET COMPATIBILITY_LEVEL = 120

GO

 

By default In-Memory OLTP is not enabled in database. While create the you can specify the keyword CONTAINS MEMORY_OPTIMIZED_DATA

Or we can alter the database to set In-Memory OLTP

ALTER DATABASE Test

ADD FILEGROUP memory_optimized_data_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

Once we created the in memory database then we can able to create the memory optimized tables.Let us take a simple OLTP table to demonstrate the In-Memory OLTP

CREATE TABLE [dbo].[emp_old_old](

       [empid] [int] NOT NULL,

       [name] [varchar](255) NULL,

PRIMARY KEY CLUSTERED

(

       [empid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

The above emp table contains clustered index. Let us insert some sample data to the above table

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (1, N'malls')

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (2, N'reddy')

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (3, N'mamidi')

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (4, N'naga')

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (5, N'shanmukh')

INSERT [dbo].[emp_old_old] ([empid], [name]) VALUES (6, N'reddy')

Once data inserted into the table. Let us take the execution plan and statistics

Execution plan and statistics for normal table

statistics

Table 'emp_old_old'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution plan 

As from the above screen shot the data storage is RowStore and data is stored in row by row. verify the sub tree cost and compare the sub tree cost with the in memory table.

Then create the In-memory optimized table for the same table and load the same data and verify the statistics and execution plan

CREATE TABLE [dbo].[emp]

(

       [empid] [int] NOT NULL,

       [name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [emp_primaryKey] PRIMARY KEY NONCLUSTERED HASH

(

       [empid]

)WITH ( BUCKET_COUNT = 8)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Execution plan and statistics for In-memory table

 execution plan 

  From the above execution plan the storage of table is memory optimized. and subtree cost is very less compared to normal table with same data.

Alternatively we can use the Memory Optimization advisor for upgrading the existing table into In-Memory table.

Right Click on the selected table

It will open the table memory optimization advisor. Click on next. It will open memory optimization check-list

 All the above check-list should pass in order to proceed to next page. otherwise we cannot convert the table into In-memory table. Once all steps passed next page is memory optimization warnings.

Server All the above warning messages are informational messages and need to consider to avoid the usage of the above things given as warning.

Review optimization options to  rename the old table and optimization file path. It provides the ability to copy the old table data to new table data by check the check box copy table data to the new optimized table. by default its off.

Primary key conversion will convert the primary key index into the above type of indexes (non-clustered hash index or non-clustered index)

Summary tab will display information about the content that is going to migrate.

SQL Server introduced column store index which is a ready only non-clustered index for increasing OLAP performance. Now SQL server 2014 enhanced the column-store index for updating data.