What I Learned About SQL Server at Ignite 2015

What I Learned About SQL Server at Ignite 2015

• 1.
  What I LearnedAbout SQL Server at Ignite 2015 v.1, May 7, 2015
• 2.
  About this deck •Author: Brent Ozar • Original source: BrentOzar.com/go/ignite • License: public domain. (You can do anything with this deck, even take out this slide and sell it, and you don’t have to credit Brent.)
• 3.
  SQL Server 2016is coming. • The first public preview will be this summer. • Will support Win2016, up to 12TB RAM • We yet don’t know: – Final release dates – Pricing – Licensing/editions (what features will be in Std)
• 4.
  2016: ALWAYSON AG IMPROVEMENTS
• 5.
  AlwaysOn Availability Groups •Like database mirroring, but: – Handles groups of databases, fails them over together – Can have multiple readable secondaries – Can offload your backups to secondaries – Can be done with physical machines or VMs – Built atop Windows Server Failover Clustering
• 6.
  SQL Server 2016Improvements • Adds Distributed Transaction Coordinator (DTC) support • Faster throughput • Load balancing for readable secondaries • Database-level health detection, failover • Available in Standard Edition, but: – Only one replica – Only one database per Availability Group (you can set up multiple AGs per server though) – Secondaries aren’t readable, can’t do backups
• 7.
  2016: COLUMNSTORE INDEX IMPROVEMENTS
• 8.
  What are ColumnStoreIndexes? • Kinda like each column getting its own index. • In SQL Server 2012: – Nonclustered only – Made the table read-only when created • In SQL Server 2014: – Clustered columnstore introduced, writeable – Massive compression (60-90%) – Nonclustered still read-only
• 9.
  2016 Columnstore Improvements •Nonclustered CS indexes now updateable, can be filtered • Clustered CS indexes can get regular b-tree indexes atop them – That improves concurrency with row-level locking
• 10.
  Both clustered andnon-clustered: • Better performance for: – Aggregates (MIN, MAX, SUM, COUNT, AVG) – String predicates (myfieldnamem LIKE ‘%foo’) • Better concurrency (support of snapshot isolation and RCSI) • Readable on AlwaysOn Availability Group secondary replicas • Better index reorganize results (removes deleted rows, less memory pressure)
• 11.
  2016: SECURITY IMPROVEMENTS
• 12.
  Row-Level Security (RLS) •In a data warehouse, you need to filter data so not everybody sees all of it – Store managers: see just their stores – Regional managers: see just their sales – Accounting: sees all the stores • In the past, we had to roll our own using views • SQL 2016 builds this into the product
• 13.
  Dynamic Data Masking •Sometimes, developers or report users need to query production. • We just don’t want them to see private data like social security number, credit card, etc. • The old way: restore the database to another server, then scramble the data with SSIS or T- SQL. – Slow, painful: did a lot of disk writes.
• 14.
  Dynamic Data Masking •Available in both SQL Server 2016 and Azure SQL Database • You define privileged logins, everybody else sees the masked data • You pick the tables and columns to be masked • You pick the mask (like fake dates or email addresses or XXX) • This only applies to data being returned to the app. You can still query for the right data, like SELECT * FROM Users WHERE SSN = ‘123456789’
• 15.
  Always Encrypted • Whenyou store personally identifiable information (PII) like social security number or credit card, you want it encrypted. • If you use Transparent Data Encryption, it’s encrypted at rest, but the DBA and reporting users can still see it unencrypted. • In the past, we had to roll our own encryption in the app layer, and it was hard work.
• 16.
  Always Encrypted, continued •SQL Server 2016 enables it by doing the encryption in the client layer – ADO.NET. – The company’s security officer creates keys – Keys are stored securely (like in an HSM, a certificate store, Azure Key Vault, etc) – Application connects to the store for the keys – Database server never actually gets the keys • Ideally, minimal app code changes required.
• 17.
  WATCH BRENT TUNESERVERS (SQL SERVER 2012/2014)
• 18.
  Watch Brent TuneServers • Brent Ozar showed several workloads, and how they could be sped up by config tweaks • Use sp_AskBrent® to check: – Batch Requests per Second – SQL’s speedometer – Wait Time per Second – the tachometer, how hard SQL is working and what it’s waiting on
• 19.
  Brent’s Fixes forCommon Waits • PAGEIOLATCH – in data warehouse style tables, columnstore indexes help (and get better in 2016) • LCK* - with Read Committed Snapshot Isolation, readers don’t block writers, writers don’t block readers • SOS_SCHEDULER_YIELD – get better execution plans with SQL 2014 compat level, or trace flag 4199 in 2012 • WRITELOG – SQL 2014’s Delayed Durability
• 20.
  NEXT YEAR’S IGNITE: CHICAGO,MAY 9-16, 2016