Transcript SQLSaturday_435_MN_

SQL Server 2014, more than just in-memory
Eric Zierdt
Before we start…
 Thank you Sponsors!
o Please visit the sponsors during the vendor break
from 2:45 – 3:15 and enter their end-of-day raffles
 Event After Party
o Dave and Buster’s in Southdale Center. 3rd floor by
Macy’s starting at 6:15
 Want More Free Training?
o PassMN meets the 3rd Tuesday of every month.
https://mnssug.org/
2 | 10/10/2015
SQL Server 2014, more than just in-memory
Agenda


















About Me
About this deck
SSRS Enhancements
SSIS Enhancements
SSAS Enhancements
Enhanced Windows Server 2012 Integration
Enhancements to Backups
Updateable Columnstore Indexes
Buffer Pool Extension
Inline specification of Clustered and non-Clustered indexes
Partition Indexing
Online Index Rebuild on specific PartitionID
Incremental Statistics
Connect Any Database Permission
Select All User Securables Permission
Resource governor IO
SQL Server Data Files in Windows Azure
In-Memory OLTP Engine
https://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx
3 | 10/10/2015
SQL Server 2014, more than just in-memory
About Me






Eric Zierdt
Senior SQL DBA – Boston Scientific
I’ve worked with SQL Server since 2000
Blog: EricEmployed.blogspot.com
Twitter: @EricZierdt
http://www.linkedin.com/in/ericzierdt
4 | 10/10/2015
SQL Server 2014, more than just in-memory
About these slides



These slides are available for download at the SQLSaturday MN Schedule
Go to: http://www.sqlsaturday.com/453/Sessions/Schedule.aspx
Find my session, click Download.
5 | 10/10/2015
SQL Server 2014, more than just in-memory
About these slides

These slides were made as part of a research project, and intended to help others
research SQL 2014 topics as well.



This is meant to be high level not a deep dive
Demo code is included in the Slide Notes
Each slide/topic will contain a link at the bottom to where I got the information from
and can be used by you to gain more information
6 | 10/10/2015
SQL Server 2014, more than just in-memory
SSRS Enhancements
SSRS Enhancements

No major changes to the SSRS Engine except for added support for the
Google Chrome browser. 1
1 https://msdn.microsoft.com/en-us/library/ms170438(v=sql.120).aspx
8 | 10/10/2015
SQL Server 2014, more than just in-memory
SSIS Enhancements
SSIS Enhancements

SQL Server 2014 Integration Services is unchanged from the previous
release 1
1 https://msdn.microsoft.com/en-US/library/bb522534(v=sql.120).aspx
10 | 10/10/2015
SQL Server 2014, more than just in-memory
SSAS Enhancements
SSAS Enhancements

With exception to added functionality supporting Power View Reports
against Multidimensional Models, SQL Server 2014 Analysis Services is
unchanged from the previous release. 1
1 https://msdn.microsoft.com/en-US/library/bb522628(v=sql.120).aspx
12 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhanced Windows Server 2012
Integration
Enhanced Windows Server 2012
Integration



SQL Server 2014 provides improved integration with Windows Server 2012 R2
and Windows Server 2012. SQL Server 2014 will have the ability to scale up to
640 logical processors and 4TB of memory in a physical environment. It can
scale up to 64 virtual processors and 1TB of memory when running on a virtual
machine (VM). 1
SQL Server 2014 supports the OSs' new Storage Spaces feature. With Storage
Spaces, you can create pools of tiered storage to improve application availability
and performance. 1
SQL Server 2014 can take advantage of the OSs' Server Message Block (SMB)
3.0 enhancements to achieve high-performance database storage on Windows
Server 2012 R2 and Windows Server 2012 file shares. Many enhancements
were made to SMB 3.0, with the most notable being SMB Transparent Failover
and SMB Direct. The new SMB Transparent Failover feature provides highly
reliable SMB storage that's fully supported for applications like SQL Server and
Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote
Direct Memory Access (RDMA) feature to provide access speeds for SMB file
shares nearing the access speed for local resources. 1
1 http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features
14 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups
Enhancements to Backups


Backup to URL/SQL Azure
Backs up only to Azure BLOB storage
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
16 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups

Backup to URL/SQL Azure
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
17 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups
 Encrypted backup
 Multiple encryption algorithms up to AES 256 bit
are supported giving you a variety of algorithms
to choose from1
 Without a certificate or key the backup file cannot
be restored.
 Adding backup encryption to a TDE encrypted
database, gives additional protection for your
data.
 Available in Standard, BI and Enterprise Editions
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
18 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups
 Encrypted backup code:
/*STEP 1: Create a Master Key*/
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987$KL95234nl0Bef';
/*STEP 2: Create a Certificate*/
Use master
CREATE CERTIFICATE DB_BackupEncryptCert WITH SUBJECT = 'DB Backup Encryption Certificate';
/*STEP 3: Backup the Certificate*/
BACKUP CERTIFICATE DB_BackupEncryptCert TO FILE = 'C:\Database Files\Backup\BackupCert.cert'
WITH PRIVATE KEY ( FILE = 'C:\Database Files\Backup\BackupCert.privatekey' ,
ENCRYPTION BY PASSWORD = 'MyCertPassword-2014' );
GO
/*STEP 4: Backup the DB*/
BACKUP DATABASE [AdventureWorks2014]
TO DISK = N'C:\Database Files\Backup\AdventureWorks2014_encrypt.bak'
WITH ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = DB_BackupEncryptCert )
, STATS = 10
GO
I’ll Demo this later
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
19 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups

Encrypted backup GUI:
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
20 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Backups

Encrypted backup GUI:
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
Enhancements to Restores
Enhancements to Restores

Restore from URL/SQL Azure
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
Enhancements to Restores

Restore from URL/SQL Azure
1 http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
24 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Restores

Restore from URL/SQL Azure
Image found on http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/24/backup-and-restore-enhancements-in-sql-server-2014-ctp2.aspx
25 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Restores

Restore from URL/SQL Azure using TSQL
USE master
GO
CREATE CREDENTIAL [My_Credential]
WITH IDENTITY ='raresql'
,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO
USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM
URL = N'https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak'
WITH CREDENTIAL = N'My_Credential'
, FILE = 1
, NOUNLOAD
, STATS = 5
http://raresql.com/2014/12/28/sql-server-2014-how-to-restore-backup-from-url-windows-azure-storage-tsql/
26 | 10/10/2015
SQL Server 2014, more than just in-memory
Enhancements to Restores

Restore an encrypted backup using TSQL
Demo
http://raresql.com/2014/12/28/sql-server-2014-how-to-restore-backup-from-url-windows-azure-storage-tsql/
27 | 10/10/2015
SQL Server 2014, more than just in-memory
Updateable Columnstore Indexes
Updateable Columnstore Indexes
 Introduced in SQL Server 2012
 Limited to non-updateable non-clustered indexes, making it most
useful on read-only tables
 Microsoft claims that Columnstore indexes can provide
upto a 10x performance gain1
 SQL Server 2014 eliminates the restriction on read-only
index/table, allowing them to be updated. 1
 Note a Columnstore index must use all the columns in a
table and it can’t be combined with other indexes1
 SQL Server 2014 allows for a Clustered Columnstore
index2
1 http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features
2
http://searchsqlserver.techtarget.com/feature/SQL-Server-2014-columnstore-index-the-good-the-bad-and-the-clustered
29 | 10/10/2015
SQL Server 2014, more than just in-memory
Buffer Pool Extension
Buffer Pool Extension
 Buffer pool extension provides the seamless integration
of a nonvolatile random access memory (that is, solidstate drive) extension to the Database Engine buffer pool
to significantly improve I/O throughput. 1
 Available in Enterprise, Business Intelligence and
Standard Editions 2
 Reduces the need to add more DRAM
1 https://msdn.microsoft.com/en-us/library/dn133176.aspx
2 https://msdn.microsoft.com/en-us/library/cc645993.aspx
31 | 10/10/2015
SQL Server 2014, more than just in-memory
Buffer Pool Extension
Buffer Pool Extension
1 https://www.sqlpassion.at/archive/2014/03/11/buffer-pool-extensions-in-sql-server-2014/
33 | 10/10/2015
SQL Server 2014, more than just in-memory
Buffer Pool Extension
DEMO
34 | 10/10/2015
SQL Server 2014, more than just in-memory
Inline specification of Clustered
and non-Clustered indexes
Inline specification of Clustered
and non-Clustered indexes

Ability to move create index statements into the create table statement.
CREATE TABLE [dbo].[t4]
(
[c1] [INT] NULL,
[c2] [INT] NULL,
[c3] [INT] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [ix_1] ON [dbo].[t4]
([c1] ASC,[c2] ASC)
GO
CREATE NONCLUSTERED INDEX [ix_2] ON [dbo].[t4]
([c3] ASC)
GO
CREATE NONCLUSTERED INDEX [ix_3] ON [dbo].[t4]
( [c2] ASC,[c3] ASC)
GO
36 | 10/10/2015
SQL Server 2014, more than just in-memory
Inline specification of Clustered and non-Clustered indexes


Ability to move create index statements into the create table statement. 1
Currently, inline indexes do not support the options UNIQUE, INCLUDE and WHERE. 2
CREATE TABLE [dbo].[t4]
(
[c1] [INT] NULL,
[c2] [INT] NULL,
[c3] [INT] NULL,
INDEX ix_1 CLUSTERED (c1,c2),
INDEX ix_2 NONCLUSTERED (c3),
INDEX ix_3 NONCLUSTERED (c2,c3)
) ON [PRIMARY]
1
1 BOL
2
- https://msdn.microsoft.com/en-us/library/ms174979.aspx
Itzik Ben-Gan - http://sqlmag.com/sql-server-2014/improvements-table-variables-and-temporary-tables-sql-server-2014
37 | 10/10/2015
SQL Server 2014, more than just in-memory
Partition Indexing Changes
Partition Indexing Changes
 Prior to 2014 (2005+) you can rebuild specific
partitioned indexes, but only in offline mode.
 Starting in 2014, you can now rebuild partitioned
indexes in ONLINE mode.
 Requires partitions, which are an Enterprise only feature
 ALTER INDEX <index Name> ON <table name>
REBUILD Partition = <partition id>
WITH (ONLINE = ON);
 DEMO
1 http://sqlperformance.com/2013/09/sql-indexes/partition-index-ops-sql2014
39 | 10/10/2015
SQL Server 2014, more than just in-memory
Incremental Statistics
Incremental Statistics
 Prior to 2014 (2005+) you can update statistics on an entire table or
entire index 1
 Starting in 2014, you can now update a specific partition
 Requires partitions, which are an Enterprise only feature 1
 UPDATE STATISTICS <TableName> <StatName> WITH
INCREMENTAL=ON 1
 UPDATE STATISTICS <TableName> <StatName> WITH RESAMPLE
ON PARTITIONS(2) 1
 Using RESAMPLE can result in a full-table scan. For example, statistics
for indexes use a full-table scan for their sample rate. When none of the
sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the
query optimizer samples the data and computes the sample size by
default. 2
 DEMO
1 http://sqlperformance.com/2014/02/sql-statistics/2014-incremental-statistics
2
https://msdn.microsoft.com/en-us/library/ms187348.aspx
41 | 10/10/2015
SQL Server 2014, more than just in-memory
Connect Any Database Permission
Connect Any Database Permission





New server level permission 1
Allows connection to all databases 1
Does not give any database level permissions (only allows connect) 2
Useful in auditing with SELECT ALL USER SECURABLES 2
DEMO
1 http://sqlserver-help.com/tag/connect-any-database/
2
http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx
43 | 10/10/2015
SQL Server 2014, more than just in-memory
Select All User Securables
Select All User Securables





New server level permission 1
Allows read access to all user databases1
Does not give database level connection
Useful in auditing with CONNECT ANY DATABASE
DEMO
1 http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx
45 | 10/10/2015
SQL Server 2014, more than just in-memory
Resource governor IO
Resource governor IO
 Additional resource which can be controlled via Resource Governor1
 Still an Enterprise only feature
 Ability to use settings MIN_IOPS_PER_VOLUME and
MAX_IOPS_PER_VOLUME1
USE master;
GO
CREATE RESOURCE POOL <Pool_Name> WITH
(
MAX_IOPS_PER_VOLUME = 30,
MIN_IOPS_PER_VOLUME = 1
);
GO
1
http://www.databasejournal.com/features/mssql/restricting-io-using-sql-server-2014-resource-governor.html
47 | 10/10/2015
SQL Server 2014, more than just in-memory
Resource governor IO
 You can configure Maximum Outstanding IOPS per volume
which is set at the instance Level and is not Resource Pool
Specific
 Limits maximum outstanding I/O operations per disk volume
 Value: 0 – 100, value is number of queued I/O’s, not a percent.
 Designed to tune IO resource governance to the IO
characteristics of a disk volume.
ALTER RESOURCE GOVERNOR
…
WITH ( MAX_OUTSTANDING_IO_PER_VOLUME = value)
1
https://www.mssqltips.com/sqlservertip/3120/whats-new-in-sql-server-2014-is-it-worth-the-upgrade/
48 | 10/10/2015
SQL Server 2014, more than just in-memory
SQL Server Data Files in Windows
Azure
SQL Server Data Files in
Windows Azure



1
SQL Server Data Files in Windows Azure enables native support for SQL Server database files
stored as Windows Azure Blobs. 1
Works in all editions 1
This feature allows you to create a database in SQL Server running in on-premises or in a virtual
machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob
Storage. 1
https://msdn.microsoft.com/en-US/library/dn385720(v=sql.120).aspx
50 | 10/10/2015
SQL Server 2014, more than just in-memory
SQL Server Data Files in
Windows Azure
 Benefits
 Easy and fast migration
Simplifies the migration process by moving one database at a time
between machines in on-premises as well as between on-premises and
cloud environments without any application changes
 Cost and limitless storage
Limitless off-site storage in Windows Azure while leveraging on-premises
compute resources
 High availability and disaster recovery benefits
If a virtual machine in Windows Azure or an instance of SQL Server
crashes, you can re-create your databases in a new machine by just reestablishing links to Windows Azure Blobs
 Security
You can have a fully encrypted database with decryption only occurring on
compute instance but not in a storage instance. In other words, using this
new enhancement, you can encrypt all data in public cloud using
Transparent Data Encryption (TDE) certificates, which are physically
separated from the data
1
https://msdn.microsoft.com/en-US/library/dn385720(v=sql.120).aspx
51 | 10/10/2015
SQL Server 2014, more than just in-memory
Last Slide in Deck Please
 Remember to fill out your online evaluations for the
event and any sessions you have attended. They will be
online until 10/17/15.
http://www.sqlsaturday.com/453/eventeval.aspx
http://www.sqlsaturday.com/453/sessions/sessionevaluation.aspx?dfsid=14882
 Presenter: Eric Zierdt
 Session Title: SQL Server 2014, more than just in-memory
 Questions?
 Twitter: @EricZierdt
 Email: [email protected]
52 | 10/10/2015
SQL Server 2014, more than just in-memory