SQL Server 2008 Databzov platforma High Availability Management

SQL Server 2008 Databázová platforma High Availability, Management, Security, Upgrade Ľubo Goryl (lgoryl@microsoft. com) Technology Solution Professional Microsoft Slovakia

Agenda • • • High availability Management Security

Why Do You Need High Availability? Downtime causes lost customer revenue and decreased staff productivity Availability during planned downtime Patching and Service Pack Installation Hardware and Software Upgrade System Reconfiguration Database Maintenance Application Upgrade Protection against unplanned downtime Human Error #1 Cause of Failure Site Disasters Hardware Malfunction Data Corruption Software Crash

Always On Technologies Increasing Availability Decreasing Downtime Improving Manageability

SQL Server 2008 High Availability Features Database Mirroring Transactional Replication Log Shipping Backup/Restore Windows Clustering Database Snapshot

Database Mirroring Overview Hot Standby protects against database or server failure Provides a fault-tolerant database Cost effective as no specialized hardware is required Straightforward setup and administration Automatic or manual failover SQL Server 2008 does not require a database restart after manual failover Automatic, transparent client redirect No shared components; two separate copies of data SQL Server 2008 compresses the log stream from principal to mirror

Database Mirroring Synchronous, high-safety configuration with automatic failover Data is mirrored synchronously as part of a transaction Automatic failover if principal server fails Automatic client redirection Client Witness Principal Mirror

Database Mirroring Synchronous, high-safety configuration without automatic failover Data is mirrored synchronously as part of a transaction Manual failover if principal server fails Client Principal Mirror

Database Mirroring Asynchronous, high-performance configuration Data is mirrored asynchronously Manual failover if principal server fails Client Principal Mirror

Database Mirroring Synchronous, high-safety configuration SQL Server 2008 uses a checksum to validate page writes Inconsistent pages can be recovered automatically from the mirror server Principal Mirror

Disaster Recovery with Database Mirroring

Log Shipping Provides database redundancy Cost effective as no specialized hardware is required Straightforward setup and administration Running read operations such as reports on a secondary server is permitted Users are disconnected when log restore occurs Can maintain multiple secondary servers Optional Monitor server Records history and status of backup/restore jobs May set up to raise alerts when jobs fail

Microsoft Clustering Server hardware redundancy Using a shared disk subsystem Entire instance virtualized and fails over as a unit Can include non-SQL Server resources Clustering can be combined with database mirroring, log shipping, or replication Geographically dispersed failover clustering provides protection even if the disk array fails

Microsoft Clustering SQL Server 2008 Editions Standard 2 -node clusters only Enterprise As many nodes as the operating system supports

SQL Server 2008 Support for Windows Server 2008 Clustering Support for 16 node clusters Cluster Validation tool • Verifies adequate hardware resources for clustering

Transactional Replication High performance ─ latency measured in seconds Minimal load on the server Cost effective as no specialized hardware is required Straightforward setup and administration Two types Standard transactional replication Easy to design, set up, and manage Subscriber (standby) can be used for reporting Peer-to-peer transactional replication Multi-master model; schema is identical on all sites Supports distributed applications with data partitioning; enables load balancing Does not handle conflicts; designed to avoid/prevent conflicts

Transactional Replication Options Transactional Replication Peer-to-Peer Replication Reporting + Redundancy Query Scale Out + Redundancy London New York Boston Shanghai New Jersey Seattle Tokyo

Peer-to-Peer Transactional Replication How does it work? “West” “East” Logreader Agent Dist DB Distribution Agent Logreader Agent “South” Logreader Agent Dist DB Distribution Agent

Peer-to-Peer Transactional Replication SQL Server 2008 Enhancements Configure replication easily with the new graphical topology viewer Add new nodes without taking application offline Protect against accidental conflicts with SQL Server 2008 conflict detection

Backup Permanent copy of data Online restore Bring database online as soon as primary file group is restored Access restored portion of database while remainder is restored Backup compression in SQL Server 2008 Reduce volume significantly Faster restore and backups Applied per instance and per backup Cost effective ─ no specialized hardware is required Straightforward setup and administration

Backup Media Mirroring Protection against lost or damaged backup media All backup types can be mirrored (database, log, etc. ) Each device must be the same type All devices must be present during Backup, but only one backup set is required for Restore

Database Snapshot Provides a read-only, consistent copy of database Snapshot remains unchanged while database is modified Protects against user error and data corruption Very fast to create Preserves disk space “Copy-on-Write” technology Can be created for any database Cost effective as no specialized hardware is required Straightforward setup and administration Client

Putting It All Together Database Mirroring Primary disaster site for databases Replication Database Scale Out for Queries Log Shipping Additional disaster sites for databases Logical recovery Database Mirroring Clustering Replication Database reporting and read scale out with redundancy Production Database Log Shipping Hot Standby Warm Standby Clustering Local server redundancy Log Shipping with Restore Delay Always On Storage Partner Solutions Site storage HA Highest hardware reliability Backup Logical Recovery Standby

SQL Server High-Availability Licensing License only active servers • Passive servers do not require a license • If failover occurs, license is not required for 30 days Most cost-effective high-availability model amongst leading database vendors

Enhanced Restore Operations Online Restore • Access restored to data even when the rest of the database is not yet available Piecemeal Restore • Recover the database in stages ─ bring critical file groups online first, then restore remaining filegroups Page-Level Restore • Restore individual pages from a backup to resolve page corruption issues quickly

Online Index Operations Create, rebuild, and drop indexes online CREATE CLUSTERED INDEX idx_Prd ON Products WITH (ONLINE = ON) Scan Sort/Merge Bulk Insert Target (new index) Source (Existing Products table) SELECT Idx_Prd INSERT UPDATE DELETE

Partial Database Availability Database remains partially available when secondary files are damaged Primary File group Additional Data File groups

Locking and Concurrency Row versioning support • READ_COMMITTED_SNAPSHOT isolation level • ALLOW_SNAPSHOT_ISOLATION database option Benefits of row versioning • Read operations retrieve a consistent snapshot of the database. • SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa) • SELECT statements can access the last committed value of the row while other transactions are updating the row, without getting blocked • The number of deadlocks is reduced • The number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks • Fewer lock escalations take place Enhanced locking in SQL Server 2008 • Optimized lock escalation for partitioned tables and indexes

Dynamic Configuration Add hardware resources without taking the database server offline Hot Add Memory Hot Add CPU

Table and Index Partitioning Partition tables and indexes across multiple physical files Reduce the impact of I/O-intensive operations Manage backup and archival based on partitions Control lock escalation at the partition level to increase concurrency

Dedicated Administrator Connection Special diagnostic connection for administrators • Available when other connections are not possible • Use to troubleshoot or shut down cleanly • Only available from clients running on the server by default

SQL Server 2008 Manageability

SQL Server 2008 Manageability Productive Administration Monitor Troubleshoot Configure Tune Scale Audit Report § Manage by Intent § Monitor with Insight § Scale with Ease

SQL Server 2008 Management Capabilities Conform and comply with system configuration policies Scale for any business size Monitor system health and optimize performance Perform interactive administration Gain insight into historical trends

Policy-Based Management Defining Policies Facets Conditions Targets Policies Categories

Policy-based management

Intelligent Monitoring On Demand • Manual checking by administrator On Schedule, log out-of-compliance • SQL Server Agent job checks periodically and logs non-compliance Changes are attempted, prevent out-of-compliance • DDL trigger rolls back non-compliant changes Changes are attempted, log out-of-compliance • Event notification logs non-compliant changes

End-to-End System Health Insight Performance Studio • Data providers: • SQL Trace • Performance Counters • Transact-SQL Low overhead data collection Centralized data storage • Management Data Warehouse Comprehensive reporting

Performance Tuning Tools SQL Server Profiler • Capture SQL Server activity • View and play back captured workloads • Correlate with System Monitor Database Tuning Advisor • Gain insight into indexing and partitions structures of your databases • Optimize indexes and partitions for your workload SQL Server Management Studio Reports • Available at the server and database levels SQL Server Management Pack • For Microsoft System Center Operations Manager • Monitors availability and performance

Interactive Administration Centralized management of all SQL Server instances and services Intellisense for productive management

Gain Management Insight Standard Reports Custom Reports Performance Dashboards

Programmatic Management SQLCMD C: >sqlcmd –I Insert. . • Create batch files • Automate multiple scripts SQL Server Management Objects (SMO) • Create custom. NET management tools

Automated Maintenance SQL Server Agent Jobs, operators, and alerts Maintenance plans Multi-server management

SQL Server 2008 Security-Enhanced Database Platform

SQL Server 2008 Security Trustworthy Computing Reliability Confidentiality Highly Secure Configuration Rich Authentication *Surface Area Kerberos/NTLM Password Policy Enforcement End Point Authentication Login credentials encrypted Single sign-on Reduction through automated policies Security updates *: New Features in SQL Server 2008 Granular Authorization Principals and Securables Roles Catalog security Execution Context User Schema Separation Integrity Data Encryption Native cryptographic capabilities * Transparent Data Encryption * Extensible Key Management Auditing * All Action Audit actions to file, Windows Application Log, and Windows Security log Capture and audit DDL activities

Transparent Data Encryption Scenarios A lost or stolen laptop contains SQL Server database with sensitive data An unauthorized user obtains the backup tapes for a database Without the required password or HSM to decrypt the DEK, the database cannot be opened. A discontented employee copies the database files and takes them to a competitor

Data Encryption Why consider encryption? Additional layer of security Required by some regulatory compliance laws Threat Detected Emergency Procedure In SQL Server 2000 Vendor support required In SQL Server 2005 Built-in support for explicit data encryption In SQL Server 2008 Transparent data encryption Extensible key management Server Highly Protected

Transparent Data Encryption

Encryption Algorithm Support XP SP 2 WS 2003 56 (64) 128 184 AES 128 - 128 AES 192 - 192 Performance depends on size of data AES 256 being ciphered - 256 RC 2 128 RC 4 40 40 RC 4_12 8 128 RSA 2048 DES Algorithms and key lengths vary by 3 DES Op. Sys; depends on CSP (Cryptographic Services Provider) DESX

It’s all About the Keys Encryption keys must be protected Some keys must be backed up If the keys are lost, data may be lost Therefore, ability to restore keys must exist Keys may need to be regenerated In case of compromise of the keys Keys may need to be shared across systems In the case, where encrypted data is being shared

Encryption Hierarchy Key Secured By Password Certificate Wraps Key Associated with Private Key Public Key Secured By Master Key Password Secured By DP API Service Key Secured By

Extensible Key Management – New in SQL Server 2008 Use third-party key management systems Employ enterprise-wide encryption • Simplify key management • Consolidate and simplify encryption across the enterprise Separate data from keys with Hardware Security Modules • Store keys in removable hardware such as USB drives

Extensible Key Management Benefits Physical separation of data and keys Additional authorization check (separation of duties) Higher performance for hardware based encryption/decryption Ability to store keys from all across the enterprise in one place for easy management Enterprise Key Managers enable and enhance functionality not available in the SQL Server Engine: Key Generation Key Storage – Keeping data separate from the keys that protect it is a best practice Key Retrieval Key Retention – Rotating keys is important to reduce the risk of one key getting compromised Key Recovery Key Distribution Key Disposal

Transparent Data Encryption – New in SQL Server 2008 Encryption/decryption occurs at the database Uses Database Encryption Key (DEK) Applications do not need to handle encryption/decryption of data Treat encrypted and unencrypted data in an identical way SQL Server 2008 DEK is encrypted with: Password Service Master Key Hardware Security Module Encrypted data page Client Application DEK must be decrypted to attach database files or restore a backup

All Action Auditing – New in SQL Server 2008 Create an Audit object to automatically log actions to: File Windows Application Log Windows Security Log Create an Audit Specification to include server and database actions in an audit Pre-defined action groups Individual action filters

Auditing

SQL Server Security Vulnerabilities to Date 160 144 SQL Server Oracle Database 80 61 # of CVE 120 46 40 41 25 24 3 12 0 2002 2003 0 0 2004 2005 2006 2007 Source: NIST National Vulnerability Database Notes: Updated as of 10/18/2007. Vulnerabilities are included for SQL Server 2000 , SQL Server 2005. Oracle (8 i, 9 i. R 2, 10 g. R 2) Query for Oracle was run with vendor name: ‘Oracle’ , and product name: ‘any’ (all database product name variations were queried). Query for Microsoft was run with vendor name: ‘Microsoft ‘ ; product name: ‘Microsoft SQL Server’; version name: ’Any’

Summary Surface Area Configuration Manager New features are disabled by default using automated policy-based management framework Policy-based user authentication More granular permission model Separation of users/schemas Execution context switching Catalog security Data encryption both in transit and at rest Transparent data encryption Extended third-party key management Auditing All Actions Audit DDL Triggers

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.