Kalen Delaney

(1)

(2)

Kalen Delaney



Background:



MS in Computer Science from UC Berkeley



Working exclusively with SQL Server for over 26 years



Contracted by both Sybase and Microsoft to develop and teach internals

courses to Tech Support staff



Primary Author -- SQL Server 2012 Internals (MS Press 2013)



Author: SQL Server In-Memory OLTP Internals and SQL Server Concurrency (Red

Gate)



Author or Primary author of 5 previous SQL Server Internals and Inside SQL Server

books



SQL Server Magazine columnist and contributing editor

(3)

Topics



Lesson 1:

Overview, Discontinued and Deprecated

Features, Licensing Changes, Hybrid Cloud



Lesson 2: Engine Enhancements, Part 1



Lesson 3: Engine Enhancements, Part 2



Lesson 4: Updatable Columnstore

(4)

(5)

Overview



Announced April 1, 2014



RTM Build date is February 20, 2014



Version 12.0.2000.8

(6)

Discontinued Features

(7)

Deprecated Features

Removed in next version:



BACKUP DATABASE | LOG WITH

PASSWORD | MEDIAPASSWORD



Returning result sets from triggers



Remote logins and servers



SET ROWCOUNT for data modification operations



HOLDLOCK with no parentheses

(8)

Deprecated Features



Removed in future version:



sp_dbcmptlevel



Compatibility level 100



Backup to tape



ANSI_NULLS and other ANSI related

database options



sp_ procs for datatypes, rules, defaults



sp_ procs for logins, users, roles



‘text in row’ table option



Text, image, ntext



sp_attach_db



sp_rename_db



sp_change_object_owner



Bound sessions



DBCC: PINTABLE, DBREINDEX,

INDEXDEFRAG, SHOWCONTIG



Extended properties



fn_get_sql



DB Mirroring



sp_configure: allow updates,

priority boost, remote proc trans,

locks, open objects, working set

sp_lock



SETUSER



Default trace

(9)

(10)

Licensing Changes



Passive nodes require purchase of Software Assurance



Each active server licensed with SA coverage allows the installation

of a single passive server used for failover support



Passive means really passive… no reports or testing



Software Assurance also give you ability to move VMs around

inside a server farm



All the cores must be licensed



Server plus CAL is only available for Standard and BI Editions

(11)

Hybrid Cloud



What is Hybrid Cloud?



Disaster Recovery



SQL Server Data Files in the Cloud



Deploying a Database to Windows Azure

(12)

What is Hybrid Cloud



Multiple Definitions



Mix of two or more clouds (either public or private)



Mix of private and public

http://www.virtualizationpractice.com/how-do-you-define-a-hybrid-cloud-21146/



Mix of on-premises data with cloud storage and technology



Microsoft SQL Server Hybrid Cloud

(13)

Disaster Recovery



Backup/Restore and Always On enhancements

discussed in Lesson 3



The key benefits of maintaining secondary replicas in

Windows

Azure include:



Low-cost disaster recovery solution



Transparent application failover

(14)

SQL Server Data Files in the Cloud

The key benefits of storing SQL Server data files

Windows Azure Storage include:



Unlimited low-cost storage in Windows Azure Storage



Facilitate disaster recovery by separating the compute

instance and the data

(15)

Deploying a Database to Windows Azure

Move a SQL Server database to Windows Azure



Move just the database



Several tools and techniques available



New wizard: Deploy a SQL Server Databases to a Windows Azure

Virtual Machine



Move the entire VM



Entire SQL Server VMs can by uploaded

(16)

Extending on-premises apps to the Cloud



Allows you to have ready resources capable of

handling heaviest load

E.g. Holiday sales

(17)