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.
