Transcript Mission-Critical Applications

En ny standard for høytilgjengelighet
Jon Jahren
Produktsjef, Microsoft
[email protected]
Mission-Critical Applications
Secure data access
Protect your system
Simplify compliance
Ensure system
availability
Minimize planned
downtime
Concurrent data access
Optimize performance
Manage mixed
workloads
Scale performance
Highly secure data access
Highly secure configuration
Rich authentication
Granular authorization
Help protect your data
Transparent data encryption
Extensible key management
Simplify compliance
All actions auditable
Enterprise-wide
security policies
Transparent data encryption
Encrypt data and log files
Protect backup files and detached
database files
Transparent to application
No application modifications required
to take advantage of encryption
Secure with Database Encryption Key
(DEK); DEK encrypted with
SQL Server 2008
DEK
A password
Service Master Key
Can use a Hardware Security Module
(HSM) to store keys
Use encrypted data in another server
Import DEK to new server
Attach encrypted database files
Restore an encrypted backup
Encrypted Data Page
Client Application
Extensible key management
Simplify key
management
Consolidate and simplify
encryption across
the enterprise
Store keys in removable
hardware such
as USB drives
Enhanced auditing
Create an Audit object
Automatically log actions
Log To various formats
A file, the Windows Application Log,
or the Windows Security Log
Create an audit specification
Include server and database actions in an audit
Predefined action groups
Individual action filters
Enterprise security policies
Define security policies
Define audit, access policies by using Policy
Management Framework
Authentication standard
Password policies
Define surface area configurations as policies
Apply across the enterprise
Using Configuration Servers
Helps to ensure enterprise-wide compliance
Trustworthy Computing
Secure Access
Protect Data
Simplify Compliance
Granular
Authorization
Rich
Authentication
Data
Encryption
Secure
Configurations
Principals
and Securables
Roles
Catalog Security
Execution
Context
User Schema
Separation
Kerberos/NTLM
Password Policy
Enforcement
End Point
Authentication
Encrypted Login
Credentials
Single Sign-On
Native
Cryptographic
Capabilities
Transparent Data
Encryption
Extensible Key
Management
Enterprise-wide
Security Policies
Security Updates
Surface Area
Minimized
Auditing
All Actions
Audited
Audit Actions to
File, Windows
Application Log,
and Windows
Security log
Capture and Audit
DDL Activities
Mission-Critical Applications
Secure data access
Protect your system
Simplify compliance
Ensure system
availability
Minimize planned
downtime
Concurrent data
access
Optimize performance
Manage mixed
workloads
Scale performance
Help ensure system
availability
Failover clustering
Database mirroring
Log shipping
Replication
Minimize planned
downtime
Backup compression
Dynamically add resources
Online operations
Enhance concurrent data
access
Database snapshots
Snapshot isolation levels
Major changes in cluster setup:
In place upgrade not possible (but probably not desirable anyway)
SQL Server cluster setup has moved to the 7.0 model of setting SQL Servers up
and then reconfiguring individually into the cluster
Other changes:
New Cluster Validation Tool
Improvements in operations (e.g. troubleshooting using Event Tracing instead of
the cluster log)
16 nodes now supported
No longer need all cluster nodes in the same subnet
For more details see the AppCompat/Upgrade course or:
http://www.microsoft.com/windowsserver2008/failover-clusters.mspx
Comm
it
1
7
Acknowledg
e
Acknowledge
6
Constantly
redoing on
mirror
2
Write to
local
log
DB
2
3
Log
Transmit to
mirror
Committe
Write to
d in log
remote
log
4
5
Log
DB
Three modes of operation
High Availability - synchronous mirroring with a witness
Automatic detection/failover
No data loss
High Protection - synchronous mirroring without a witness
Manual failover
No data loss, but possibly downtime
High Performance - asynchronous mirroring
Manual failover
Some data loss possible (but not guaranteed)
Consequences of failure depend on what
failed and the mode of the database
mirroring partnership
SQL Server crashes
Instant detection of failure
Windows/hardware crashes
Failure detection depends on partner timeout setting
Log drive pulled out
Failure detection takes 40 seconds
Corrupt page
Failure detection depends on what hits the corrupt page
Database mirroring
Performance
improvements
Log compression
Automatic page repair
Prevents a corrupt data
page from making
a database unavailable
Mirroring partner detects
bad database page
Requests valid copy
from partner
Log Stream compression
Comparison of transactions per second against network bandwidth,
with and without log compression
Percentage increases in throughput is most dramatic for low network
bandwidths
Comparison of CPU usage against network bandwidth with and
without log stream compression
CPU goes up when compression is on, both because of
compression/decompression, but also because the server can now
process more transactions per second
Automatic page repair
Witness
2. Request page
3. Find page
5. Transfer page
XData
6.
1. Write
Bad Page
Page
Detected
Principal
Log
4. Retrieve page
Data
Mirror
Log
Peer-to-peer replication
Add and remove nodes without
stopping
Visual configuration with
Topology Wizard
Ability to detect conflicts
Improved Replication Monitor
Select columns and filter rows
for viewing
Backup compression
Standard backup
commands and interface
Off by default,
configurable
Restore from any SQL
Server 2008 Edition
Dynamically
add resources
Online-Add CPU
Online-Add memory
Backup Compression
System Monitor snapshot of backup of 322MB
AdventureWorks DB
Compressed
Uncompressed
Hardly any CPU used
(avg 5%), runtime =
39.5s, compression ratio
of 0.
A LOT more CPU used (avg
25%) BUT runtime = 21.6s
(45% improvement) and
backup stored in 76.7MB
(4.2x compression ratio)
21
System Monitor snapshot of backup of 322MB
AdventureWorks DB
Compressed
Uncompressed
Hardly any CPU used
(avg 8%), runtime =
71.0s
More CPU used (avg 14.5%)
BUT runtime = 36s (almost
50% improvement)
22
Common questions:
“How much compression will I see?”
“Will it be comparable to, say, SQL Litespeed?”
One simple answer: “It depends!”
All data compresses differently – the
compression ratio achieved depends on:
The type of data in the database
Whether the data in the database is already compressed
Whether the data/database is encrypted
SS2005 added the ability to use hot-add memory
SS2008 extends the dynamic workload handling
capabilities of SQL Server by allowing hot-add
CPU
‘Hot-add’ means being able to plug it into the computer while the computer
is running, and then reconfiguring SQL Server to make use of the new
hardware ONLINE
There are some restrictions for use:
The hardware must support hot-add (obviously )
SS2008 Enterprise Edition only
64-bit Windows Server 2008 Datacenter/Enterprise
SQL Server won’t automatically start using new
CPUs
They may not be meant for SQL Server’s use
DBCC CHECKDB is a critical tool for early discovery of database
corruptions (usually caused by the IO subsystem)
Deep-dive notification
Some algorithms in CHECKDB are written for speed, and so
will detect a corruption, but not where exactly the corruption is.
In this case, a 2nd pass must be done to pinpoint the corruption
This can be very time consuming and its unclear what is going
on – CHECKDB seems like it’s not progressing but IOs are
happening
SQL Server 2008 has new error log output to highlight this
process
Higher efficiency parallel page scanner
Long-running indexed view and XML index contents checks moved
under WITH EXTENDED_LOGICAL_CHECKS option to reduce
default run-time
25
Advanced eventing infrastructure for servers
Ability to define events to monitor and a variety of
ways to consume the events
Synchronous or asynchronous
Trigger actions when an event fires
Use predicates to filter events from consumers
Causality tracking (through inherited IDs)
High performance and good scalability
Cost of firing a single event is extremely small
ETW (Event Tracing for Windows) enabled
Controlled through T-SQL DDL statements
26
Example scenario (taken from Books
Online):
Problem
Troubleshooting excessive CPU usage on the server
DMVs show that all queries are ad-hoc user queries but don’t have
enough info about queries that have been run
Solution
Create an Extended Events session that:
Fires an event when a T-SQL statement executes
Defines an event action to collect the query plan
Defines a predicate on a CPU usage threshold
Defines a consumer to write the event payload to a trace file
Examination of the traced query plans allows pinpointing the
problem
27
Lock escalation on partitioned tables reduces
concurrency as the table lock locks ALL partitions
Query 1
IX
X
Partitioned
Table
Query 2
update
update
Partition 2
Partition 1
FG1
Partition 3
FG1
FG1
Only way to solve this currently is to disable
escalation
SQL Server 2008 allows lock escalation to the partition
level, allowing concurrent access to other partitions
Query 1
IX
Partitioned
Table
Query 2
update
update
X
Partition 1
FG1
Partition 2
Partition 3
FG1
FG1
Escalation to partition level does not block other queries
Mission-Critical Applications
Secure data access
Protect your system
Simplify compliance
Ensure system
availability
Minimize planned
downtime
Concurrent data
access
Optimize performance
Manage mixed
workloads
Scale performance
Optimize performance
Data compression
Enhanced partitioning
DW query optimizations
Improved indexed views
Performance data collection
(covered in manageability session)
Manage mixed workloads
Resource Governor
Scale up/scale out
SQL Server 2008 provides
Increased levels of security
Availability
Scalability to meet your growing data needs
© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, 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.