1 / 28

SQL Server 2014 Features William Emmert Microsoft

SQL Server 2014 Features William Emmert Microsoft . SQL Server 2014 and the Data Platform. Faster Insights from Any Data. Mission Critical Performance. Platform for Hybrid Cloud. SQL Server 2014 and the Data Platform. Faster Insights from Any Data. Platform for Hybrid Cloud.

fuller
Download Presentation

SQL Server 2014 Features William Emmert Microsoft

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2014 FeaturesWilliam EmmertMicrosoft

  2. SQL Server 2014 and the Data Platform Faster Insights from Any Data Mission Critical Performance Platform for Hybrid Cloud

  3. SQL Server 2014 and the Data Platform Faster Insights from Any Data Platform for Hybrid Cloud Mission Critical Performance

  4. Mission Critical Performance • In-Memory OLTP • ColumnStore Enhancements • Security Enhancements • Query Processing Enhancements • Resource Governor Enhancements • SSD Buffer Pool Extension & Scale-Up • High Availability (AlwaysOn) Enhancements • On-Line Operations Enhancements

  5. In-Memory OLTP • Optimized for in-memory data up to 20–30 times throughput • Indexes (hash and range) exist only in memory; no buffer pool, B-trees • T-SQL compiled to machine code via C code generator and Visual C compiler • Core engine uses lock-free algorithms; no lock manager, latches, or spinlocks • Multiversion optimistic concurrency control with full ACID support • On-ramp existing applications • Integrated experience with same manageability, administration, and development experience

  6. Columnstore Enhancements • Real-time super-fast data warehouse engine • Ability to continue queries while updating without the need to drop and re-create index or partition switching • Huge disk space saving due to compression • Ability to compress data 5X – 15X using archival per-partition compression • Better performance and more efficient (less memory) batch query processing using batch mode rather than row mode

  7. Separation of Duties Enhancement • Greater role separation • Ability to create new roles for database administrators who are not sysadmin (super-user) • Ability to create new roles for users or apps with specific purposes • Examples: • Database administrators but cannot see user data • Database administrators but cannot modify logins • Database administrators but cannot impersonate any logins • New roles (e.g. auditors) to read all data but not database administrators • New roles to read all metadata for monitoring purposes (e.g. SCOM) but cannot see user data • New roles for middle tier app to impersonate any login except sa, sysadmin, or other high privilege logins

  8. Query Processing Enhancements • Better query performance: • Better choice of query plans • Faster and more frequent statistics refresh on partition level • Consistent query performance • Better supportability using two steps (decision making & execution) to enable better query plan troubleshooting • Loading speed into table improved significantly using parallel operation

  9. IO Resource Governance • Better isolation (CPU, memory, and IO) for multi-tenant workloads • Guarantee performance in private cloud and hosters scenario

  10. SSD Buffer Pool Extension and Scale up • BP Extension for SSDs • Improve OLTP query performance with no application changes • No risk of data loss (using clean pages only) • Easy configuration optimized for OLTP workloads on commodity servers (32GB RAM) • Scalability improvements for systems with >8 sockets Example: ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE‘, SIZE = 50 GB)

  11. High Availability (AlwaysOn) • Further scale-out read workloads across, possibly geo-distributed, replicas • Use readable secondaries despite network failures (important in geo-distributed environments) • Improve SAN storage utilization • Avoid drive letter limitation (max 24 drives) via CSV paths • Increase resiliency of storage failover • Ease troubleshooting

  12. Online Ops Enhancements • New partition-level granularity for online index rebuild allows continuous access to the table resource during this operation to better meet the needs of customers who need 24/7 access • Provide customers greater control over impacting running transactions if using switch partition or online index rebuild commands • Combined together, these features allow customers to better schedule resource intensive or blocking operations and better achieve 24/7 availability for their applications

  13. SQL Server 2014 and the Data Platform Mission Critical Performance Faster Insights from Any Data Platform for Hybrid Cloud

  14. Ship in CU4 of SQL Server 2012 SP1 Power View for Multidimensional Models • Power View on Analysis Services via BI Semantic Model • Native support for DAX in Analysis Services • Better flexibility: Choice of DAX on Tabular or Multidimensional (cubes)

  15. SQL Server 2014 and the Data Platform Mission Critical Performance Faster Insights from Any Data Platform for Hybrid Cloud

  16. Platform For Hybrid Cloud • Backup to Azure • Smart Backup • AlwaysOn Replica in Azure • SQL Server with Azure Storage Integration • SQL Server with Azure IaaS

  17. SQL Server Journey to the Cloud AlwaysOn Replica in Azure SQL Server with Azure Storage integration Smart Backup SQL Server with Azure IaaS Backup to Azure

  18. Backup to Azure - Manual • Leverage Windows Azure blob storage • Flexible, reliable, and limitless off-site storage • Backup Archive • No overhead of hardware management • Cost Benefits

  19. Backup to Azure - Automatic • Large scale management and no need to manage backup policy • Minimal knobs – control retention period • Context-aware – e.g. workload/throttling • Manage whole instance, or particular DBs • Leverage Backup to Azure • Inherently off-site • Geo-redundant • Minimal storage costs • Zero hardware management Example: EXEC smart_admin.sp_set_db_backup @database_name='TestDB', @storage_url=<storage url>, @retention_days=30, @credential_name='MyCredential', @enable_backup=1

  20. AlwaysOn Replica in Azure • Easily deploy one or more replicas of your databases to Windows Azure • No need for a DR site (hardware, rent, ops) • Use these replicas for • Disaster Recovery • Workloads (reads/backups)

  21. SQL Server with Azure Storage Integration • Delivers an incremental path to database in the cloud. Can move one database at a time, without application changes • Save storage cost: On-premises computing, with bottomless off-site storage in Azure • Increase HA and DR: • A set of database files stored in Azure storage – backed by Azure Storage SLA • Fast disaster recovery using database attach operation without the need to restore data • Maintain on-prem control of security: Allow TDE key on-prem while encrypted data in Azure Storage

  22. Deploy DB to Windows Azure VM • Easy to use • Perfect for DBAs new to Azure and for ad hoc scenarios • Complexity hidden • Detailed Azure knowledge is not needed • Almost no overhead: the defining factor for time to transfer is dB size

  23. Shipped in SQL Server 2012 SP1 CU2 Sysprep enhancements • Sysprep support for • Analysis Services • Integration Services • Management Tools (SSMS) • Other Shared Features • Performance Improvements

  24. SysprepEnhancements in SQL Server 2014 • What’s being delivered • Extensions to SQL SysPrep functionality in order to support image based deployment of clustered SQL Server instances • Main benefit • Supports full automation of SQL Failover Cluster deployment scenarios • Reduces deployment times for SQL Failover Clusters • Combined together, these features allow customers to automate the provisioning of SQL Failover Clusters both on-premises and through Infrastructure as a Service (IaaS) • Built on top of SQL Server 2012 SP1 CU2 Sysprep enhancements

  25. ReFS support • What’s being delivered • SQL Server 2014 support for Windows Server Resilient File System (ReFS) • Main benefits • Greater protection on data corruption • Efficient checksum on all user data (including Hekaton and FILESTREAM) • Checksum runs proactively in the background • Protects against disk failure • Backward compatible with NTFS • Works with commodity, direct-attached disks

  26. Better Together Features • Works very well with Windows Server 2012 R2, System Center 2012 R2, and Windows Azure refresh • ReFS support • Online resize VHDX • Storage Tiering • SMB improvement • Hyper-V replica improvement • Faster live migration • …

  27. Complete & Consistent Data Platform SQL Server 2014 Mission Critical Performance Faster Insights from Any Data Platform for Hybrid Cloud CUSTOMER SERVICE PROVIDER WINDOWS AZURE 1 CONSISTENTPLATFORM DEVELOPMENT MANAGEMENT DATA IDENTITY VIRTUALIZATION

More Related