1 / 20

Enhancements Overview

Enhancements Overview. Level 200. Geyzerskiy Dmitriy Chief Architect, Microsoft Technologies dimag@dbnet.co.il. Info World, 12.11.2008. “New regulations will soon swell IT workloads…“. 3. Security. 2. Scalability. Agenda. 1. Availability.

ciara-wong
Download Presentation

Enhancements Overview

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. www.themegallery.com Enhancements Overview Level 200 Geyzerskiy Dmitriy Chief Architect, Microsoft Technologies dimag@dbnet.co.il

  2. InfoWorld, 12.11.2008 “New regulations will soon swell IT workloads…“

  3. 3. Security 2. Scalability Agenda 1. Availability

  4. Synchronous Mirroring with a witness. Automatic detection/failover. No data loss. High Availability Synchronous Mirroring without a witness. Manual failover. No data loss, but possibly downtime. High Protection Asynchronous Mirroring. Manual failover. Some data loss possible (but not guaranteed). High Performance Availability Enhancements Database Mirroring: Refresher

  5. Availability Enhancements Database Mirroring • Corrupt pages may be silently repaired on Mirror and Principal. • New DMV sys.dm_db_mirroring_auto_page_repair allows tracking of previous 100 corrupt pages found in all mirroring sessions. • New SQL Server Profiler “Database Suspect Data Page” Event Class. • Log Compression • Adds up CPU cycles • Can be switched off using trace flag 1462

  6. Availability Enhancements Database Mirroring • Fast Manual Failover • In SQL Server 2005 the database on the mirror server is restarted when a failover occurs. All database files are opened sequentially. • New Performance Counters • Log Bytes Redone from Cache/sec • Log Bytes Sent from Cache/sec • Log Compressed Bytes Rcvd/sec • Log Compressed Bytes Sent/sec • Log Harden Time (ms) • Log Remaining for Undo KB • Log Scanned for Undo KB • Log Send Flow Control Time (ms) • Mirrored Write Transactions/sec • Send/Receive Ack Time.

  7. Availability Enhancements Other goodies • Backup Compression • In SQL Server 2005, only 3rd parties provide backup compression technology. • Compression available in Enterprise Edition only, but all compressed backups may be decompressed in any edition. • Adds extra CPU cost in favor of backup performance improvement. • Hot-Add CPU • In SQL Server 2005 only hot-add memory is supported. • Subject to restrictions (hardware support, EE only, 64 bit Windows Server 2008 Data Center or Enterprise Edition). • SQL Server 2008 should run RECONFIGURE to support new CPUs.

  8. Availability Enhancements Other goodies • DBCC CHECKDB Improvements • Runs faster. • WITH EXTENDED_LOGICAL_CHECKS clause allows to control long running checks (indexed views, XML indexes). • Deep-dive notification helps to detect corruption.

  9. Availability Enhancements Other goodies continued • Peer-to-Peer Replication • Changing PTP topology doesn’t require stopping all activity. • New graphical Topology Wizard. • Conflict detection (can be configured).

  10. Availability Enhancements Other goodies continued • Failover Clustering • SQL Server cluster nodes are set up individually. • Support for 16 nodes • New Cluster Validation Tool. • Geographically Dispersed Failover Clustering • VLANs should be set up to connect geographically separated cluster nodes.. • SAN replication.

  11. 3. Security 2. Scalability Agenda 1. Availability

  12. Scalability Enhancements • Data Compression • Compression rate depends on the data. • ROW and PAGE compressions are available. • Dictionary compression for PAGE compression. • New DMV sp_estimate_data_compression_savings. • New Performance Counters for monitoring. • Graphical wizard for managing compression.

  13. Scalability Enhancements • Service Broker • Diagnostic tool for debugging. • External activation (may launch external process). • Enhanced Management Studio support (Add, Edit, Enable). • Conversation priorities. • NUMA support • dm_os_memory_nodes • dm_os_memory_node_access_stats • dm_os_memory_nodes • dm_os_memory_node_access_stats

  14. Scalability Enhancements • Lock Escalation • Partition Level Lock Escalation. • Escalation setting is per-table. Query 1 Query 2 Partitioned Table IX ESCALATE update X update Partition 2 Partition 1 Partition 3 FG1 FG1 FG1

  15. 3. Security 2. Scalability Agenda 1. Availability

  16. Employ Enterprise wide encryption. Separate data from keys with hardware security modules. Security Enhancements • Extensible Key Management Use 3rd party key management systems.

  17. Security Enhancements • Transparent Data Encryption • In-place encryption at the database level. • Transparent to applications. • Encrypt data and log files. • Entire database is protected. • Performance cost is relatively small. • Backups are unusable without key. • Can be used with ExtensibleKey Management. • Tools support. SQL Server 2008 DEK Encrypted Data Page Client Application

  18. Security Enhancements • Integrated Authentication Enhancements • Kerberos available with ALL protocols (TCP/IP, named pipes, shared memory). • SPN may be specified in connection string. • Kerberos possible without SPN registered in AD.

  19. Security Enhancements • Tracking Database Activity • SQL Server Audit is now a 1st class server object. • Automatically log actions to File, Windows Application or Security Log. • Create an Audit Specification to include server and database actions in an audit. • Audit Action Groups. • New Auditing DMVs. • Leverages high performance Extended Events infrastructure. • Tools support.

  20. www.themegallery.com Thank You !

More Related