SQL 2005

1
(No Transcript)
2
SQL 2005

• Kevin Ashby
• Microsoft EMEA
• Enterprise Technology Strategist
• kashby_at_microsoft.com

3
Agenda

• Introduction
• Enterprise Data Management
• Business Intelligence
• Clients
• Using SQL2005, includes Microsoft IT

4
Introduction
5
Todays IT
Desired IT
6
20 average annual increase in RD spend
Note The FY04 figure restated to include
stock-based compensation (as per SFAS 123)
7
FY04 Includes stock-based compensation as per
SFAS 123 Excluding this, the figure would be
4.8B IBMs software RD is estimated to be
0.8B-1.9B, using two contribution figures one
based on revenue and the other based on gross
margin HPs software RD is estimated using the
of software revenue within the Enterprise
Systems Group in Q4FY'03-Q1FY'04 Source Company
annual reports
8
(No Transcript)
9
SQL Server GenerationsHistory of Innovation
1st Generation
2nd Generation
3rd Generation
SQL Server 7.0
SQL Server 2005
SQL Server 2000
SQL Server 6.0/6.5

• True High availability
• Security
• Developer productivity
• Native XML
• First to include Enterprise ETL Deep Data Mining

• Performance, scalability focus
• XML support
• First to include Notification
• First to include Data Mining Reporting

• Re-architecture of relational server
• Extensive auto resource management
• First to include OLAP ETL

• Differentiation from Sybase SQL Server
• Windows integration
• First to include Replication

• Lowest TCO
• Automatic Tuning

• Reliability Security
• Integrated Business Intelligence

Cross-release objectives
10

• Replication
• Auto-tuning Replication Agents
• Oracle Publication
• Improved Blob Change Tracking
• Replication Monitor
• OLAP and Data Mining
• Analysis Management Objects
• Integrated Backup/Restore with Windows Server
• Web Service/XML for Analysis
• DTS and DM Integration
• New Data Mining Algorithms
• Auto Packaging and Deployment
• Integration Services
• New Architecture (DTR DTP)
• Complex Control Flows
• Control Flow Debugging
• For Each Enumerations
• Property Mappings
• Full Data Flow Designer

• NET Framework
• Common Language Runtime Integration
• User-defined Aggregates
• User-defined Data Types
• User-defined Functions
• SQL Server .NET Data Provider
• Extended Triggers
• Data Types
• Managed SQL Types
• New XML Datatype
• Varchar (MAX) Varbinary (MAX)
• SQL Server Engine
• New Message Service Broker
• HTTP Support (Native HTTP)
• Database Tuning Advisor
• Multiple Active Result Sets
• Persisted Computed Columns
• Snapshot Isolation Level
• Scale Up Partitioning

• Database Maintenance
• Backup and Restore Enhancements
• Checksum Integrity Checks
• Dedicated Administrator Connection
• Dynamic AWE
• Fast Recovery
• Highly-available Upgrade
• Online Index Operations
• Online Restore
• Parallel DBCC
• Parallel Index Operations
• Management Tools
• New Management Studio
• MDX Query Editor
• Version Control Support
• XML/A
• SQLCMD Command Line Tool
• Performance Tuning
• Profiler Enhancements

11

• Integration with Visual Studio and .NET
• Native XML technology
• Interoperability via Web Services

• Integrate and Transform Data
• Analyze, Store and Mine Data
• Report and Interact with Data

• High availability for enterprise applications
• Key security and performance features
• Focus on self manageability and optimization

12
2004 Relational DB Market
Non-mainframe DB (6 Bil)By OS Platform
SQL Server Leads on Windows
Windows 51
Unix/Linux49
Windows DatabaseMarket (3.1 Bil)
Passed IBM in 2003
Passed IBM in 2003
Other8
IBM16
SQL Server51
Windows is the leading OS platform
Oracle25
Source Gartner
13
The Database MarketUnit Share, Overall and
Enterprise
Source
14
40K
25K
25K
10g
DB2
15
330K
232K
DB2 OLAP 35k DB2 Warehouse 75k Cubeviews
9.5k
BI Bundle 40k
RAC 20k
Recovery Expert 10k
Tuning Pack 3k Diagnostic Pack 3k Partitioning
10k
Performance Expert 10k
25K
10g
DB2
16
Enterprise Data Management
Scalable, Reliable, Secure
17
SQL Server does not Scale?
18
SQL Server 2005 Scales

• With Hardware and Operating System
• With Database Features
• With Application Design

19
Scaling - Hardware Options

• SQL Server optimized for hardware OS
• Known as the "SQLOS" abstraction
• This enables better support on
• 64 bit architectures
• NUMA systems
• Threads managed as tasks
• Enables SQL Server use of new OS features
• Take advantage of Windows Server 2003
• Take advantage of Windows Vista

Remember Dual core support at no extra cost!!
20
Windows Server 2003 Enabled

• Password policy check for SQL passwords
• Hot add memory
• Dynamic AWE
• Native 64 bit support
• SOAP support (HTTP in the kernel)
• Instant file initialization
• 8 node SQL Server failover cluster

Advanced OS features used because SQLOS
abstraction
21
Scaling Data with Services

• Functionality built-in to SQL Server
• Asynchronous Operations - Service Broker
• To achieve scalable, resilient large scale
  Applications
• Uses Queues
• 15k-20k messages per second!
• System/Application continues to work with partial
  outage, things just queue up
• Cache coherency Query Notifications
• Master data management
• Request-response - Web Services
• Industry standard protocol
• Service Programs can be T-SQL or SQLCLR
• Better performance and flexible deployment
• Choose where to run!

22
SQL Service Broker

• A Platform for building reliable, asynchronous,
  loosely coupled database applications
• Queues are database objects
• DDL and DML is the same as vanilla T-SQL
• Input in one transaction/context, execute in a
  different one
• Queue locking reduces conflicts and deadlocks
• Locks are based on dialogs (point-to-point
  conversation)
• Dialogs give unprecedented message ordering
• Reliable, durable, sequenced communications
  session between services
• Ordering even across transactions

23
Services Live In The Databases

• Ongoing work in the database
• Each Service instance is kept in a database
• State is kept in the database
• Services communicate through dialogs (next slide)
• Messages are stored in the database

Transaction
Transaction
Transaction
24
Dialogs

• Dialogs provide two-way messaging between two
  services
• Dialogs offer
• Guaranteed delivery
• Exactly-once delivery
• In-order delivery
• Secure communications
• Dialogs
• May be long-lived (years) or short-lived
  (seconds)
• Are light-weight
• Are persistent sessions (stored in the DB, so
  moving DB moves the dialog)

Messages will get there, only once and in the
right order, even if queue is turned off, so no
coding to deal with message arrival order
Database B
Database A
25
Messaging with Service Broker

• Inbound messages arrive on protocol pipe
• Then the message is
• Authenticated
• Dispatched to appropriate queue
• Then a Service Programs
• Pick up work from queue
• May run inside or outside server (on application
  server)
• May send additional messages

Service Program (decrement_inventory)
Message
Message
26
Query Notifications

• Notify Caches When Master Data Changes
• built into SQL Server 2005
• based on indexed view notifications
• built into ADO.NET
• cache listeners (for when data changes) can be
  scaled to multiple machines using SQL Express
• delivery via Service Broker
• built into ASP.NET
• automatic cache invalidation
• Known as Cache Sync
• two lines of code

Put data closer to the edge!
multiple granular replicas
master data
27
CacheSync
Query
Web Request
Results
Subscription
Results read from master data into cache
28
CacheSync
Web Request
Subscription
Results read from cache as master not changed
29
CacheSync
Cache updated with new master data
Subscription
UPDATE dbo.Products SET
30
SQL Server is not Secure?
Database Security Bulletins
2005
2002
2003
2004
Source Company web sites, http//www.osvdb.org,
http//www.secunia.com (Includes both SQL Server
and MDAC)
31
Security

• SQL Server is part of the Trustworthy Computing
  initiative
• Whether your company is small, medium, or large,
  security is not optional
• Data is your company's view of "reality
• Make data secure
• You must be secure for accurate picture
• Database security consists of
• Security by design - integrated with policy
• Security by default (only turn on what you need)
• Secure deployment and maintenance
• Secure communications and storage

32
SQL Server 2005 Security InitiativesFocus on
security, privacy, and tools

• Enhanced security features
• Encryption and decryption of data with key
  management
• Advanced auditing, authentication, and
  authorization
• Tracing, Events, DDL, custom triggers (Data dict
  is exposed )
• Reduction in surface area
• More optional installation options
• Minimized Attack surface Features require
  explicit configuration
• Deployment improvements
• Integration with Microsoft Update services
• Common Criteria
• Certification targeted at EAL4
• Security Tools
• Continued MBSA investments
• Best Practices Analyzer

33
Off by Default
Off by Default! DBA must actively decide to turn
ON a feature
34
Secure Data Metadata

• User-schema separation - database objects need
  not be tied to users
• Fixes "user leaves company" problem
• Allows DBA to allow installation of packages with
  owners other than DBO
• So users will not need dbo access to access
  packages
• Allows separation of database object owners even
  within a single database
• Secure Metadata
• You can only see what you can access
• No view of objects you are not allowed to see
• Consolidation without seeing others' data
• All Permissions Grantable
• Granular permissions
• Give only the required permissions to the correct
  users

35
Encryption and Privilege

• Some industries require encryption
• Encryption keys securely stored in database
• Instance key protected by DPAPI
• (data protection API)
• Logins are always encrypted
• Procedures can be signed or run as certain
  accounts
• Principle of least privilege
• Original login always available for auditing
• Proxy accounts for SQL Agent jobs

36
Cryptography 101
Symmetric Key Encryption
Encryption
Faster
Same key to lock and unlock
1234-5678-1234-5678
0x0088840517080E4FA2
Decryption
Asymmetric Key Encryption
Slower
Encryption with public key
Lock with public key unlock with private (visa
versa)
0x0088840517080E4FA2
1234-5678-1234-5678
Private Key
Public Key
Decryption with private key
37
SQL Server Encryption
?
?

• Good Scenario
• Encrypting secrets during login
• Using asymmetric keys to generate session keys
• Using symmetric keys for data encryption
• Faster!
• Using SQL Server certificates from trusted
  sources
• Encrypting data as required by law
• Not all data, it restricts its use

• Bad Scenario
• Encrypting all network traffic inside an
  organization
• Using asymmetric keys for data encryption
• Slow
• Using symmetric keys for main key distribution
  mechanism
• Since it is the same key
• Using SQL Server as a certificate server
• Encrypting all data
• SLOW, and data can't be used for indexes and
  joins, eg order by would not work

38
Addressing Barriers to Availability

• Database Server Failure
• Failover Clustering
• Database Mirroring
• Database Maintenance Operations
• Online Index Operations
• Fast Recovery
• Fine-Grained Online Repairs
• Data Access Concurrency Limitations
• Snapshot Isolation
• User, Application Errors
• Database snapshots

39
Database MirroringAchieving high availability
while managing costs

• Database Failover
• Very fast failoverless than 3 seconds
• Automatic or manual failover
• Automatic, transparent client redirect
• Database Snapshots for Reporting
• Works with standard hardware and storage

40
Basic Principle of Mirroring
Acknowledge
Commit
Acknowledge
Constantly Redoing on Mirror
Transmit to Mirror
Write to Local Log
Write to Remote Log
Committed in Log
Log
DB
DB
Log
41

• Scenario
• Itanium 8 principal, Itanium 9 mirror, Itanium 10
  witness
• To list the existing Endpoints
• select from sys.database_mirroring_endpoints
• To delete existing Endpoints
• drop endpoint "endpoint"
• Start with a backup and create the first end
  point (from Itanium 8)
• backup database Database1 to disk
  'C\BACKUP1.bkf' with init
• create endpoint endpoint statestarted as tcp
  (listener_port5022) for database_mirroring
  (rolepartner)
• Restore the backup on intended mirror server and
  create the second endpoint(from Itanium 9)
• restore database Database1 from
  disk'C\backup1.bkf' with norecovery , replace
• create endpoint endpoint statestarted as tcp
  (listener_port5023) for database_mirroring
  (rolepartner)
• Create the third endpoint on the Witness (from
  Itanium 10)
• create endpoint endpoint statestarted as tcp
  (listener_port5024) for database_mirroring
  (roleWITNESS)
• Set up Database Mirroring
• From ITANIUM9
• ALTER DATABASE DATABASE1 SET PARTNER
  'TCP//ITANIUM8.SQL.NET5022'
• GO
• From ITANIUM8

42
HP Proliant
Witness
Mirror
Mirror
Principal
Principal
HP Integrity
HP Integrity
SNACSQL Native Client
Demonstration
43
Online Index Operations

• Online index maintenance
• Create, Rebuild, Reorganize, Drop, Add or drop
  constraint
• Fully parallel
• Online/offline are both supported

44
Other Availability Features

• On-Line Page and File restore
• Database remains online - Only data being
  restored is unavailable
• Dedicated Admin Connection
• Access to Server when regular connections fail
• Fast Recovery
• Database is available when undo begins

Undo
Redo
Available
45
Snapshot Isolation

• Increased data availability for read
  applications
• Allows non-blocking consistent reads in an OLTP
  environment
• Writers dont block readers
• Readers dont block writers
• Scenarios
• Read-mostly database with relatively few writes
  Reporting
• Migration from Oracle to SQL Server

46
Database Snapshots

• Snapshot of a database at a point in time
• Created instantly
• Read only
• Does not require a complete copy of the data
• Shares unchanged pages of the database
• Requires extra storage only for changed pages
• Uses a copy-on-write mechanism
• Recover from User, Application or DBA error
• Rewind database to Database Snapshot

47
Snapshot (Copy on Write)
Northwind
Northwind_SS
Command
Create Northwind_SS
Update Northwind
Read Northwind_SS
Result
D
D
Space Used
0
12.5
48
Oracle PublishingTransactional Replication

• Designed specifically for Oracle Publishers
• v8 on any operating system
• Administered like SQL Server, from SQL Server
• No Oracle side software install necessary
• Requires minimal knowledge of Oracle
• Leverages existing SQL Server skills
• Standard Transactional and Snapshot Publications

SQL Server 2005 Distributor
Subscribers
49
Manageability Goals

• Integrated Management, Rich UI
• Scale down time/effort for common management
  tasks
• Server Transparency
• Everything scriptable

50
Management Studio

• Integrated management
• Relational DB, Analysis Services, Reporting
  Services, Notification Services, SQL Server
  CE
• Multiple versions of SQL from one interface
• Rich management authoring
• Performance
• Manage multiple servers, MANY objects
• Operations Profiler, Query Analyzer

51
Manageability - Tools

• New SQL Server Management Studio
• New Rich GUI
• Integrated Authoring, Management
• Support all SQL Server components
• New SQLCMD
• High Performance CMD line tool
• New SQLiMail Replacement
• SMTP Support, Cluster Support, No Outlook
  required

52
Manageability - Tuning

• Database Tuning Advisor
• Time Bound Tuning
• What-If Analysis
• Scalability
• XML Show Plan
• Publicly available Schema
• Programmatic Access
• Portability

53
Manageability DiagnosticsWhat is happening in
my DB

• Dynamic Management Views
• Server Transparency
• sys.memory_clerks how much memory is each
  component consuming ?
• sys.schedulers Is a scheduler hung ?
• SQL Trace
• Query data from live traces
• SQL Profiler
• Deadlock and ShowPlan Visualization

54
Range Partitioning

• Range Partitioning breaks a single object into
  multiple manageable pieces
• Partitioning is transparent to the application
• Partitioning applies to
• Tables
• Indexes
• The row is the unit of partitioning
• All partitions reside in a single database

55
Partition Function

• Maps ranges of a data type to integer values
• Defined by specifying boundary points
• N boundary points define N1 partitions

Boundary 1
Boundary 2
Boundary 3
Boundary 4
Partition
1
2
3
4
5
56
Partition Function DDL

• CREATE PARTITION FUNCTION annual_range (DATETIME)
• as RANGE RIGHT
• for values
• ( -- Partition 1 -- 2001 and earlier
• '2002-01-01', -- Partition 2 -- 2002
• '2003-01-01', -- Partition 3 -- 2003
• '2004-01-01', -- Partition 4 -- 2004
• '2005-01-01' -- Partition 5 -- 2005 and later
• )

57
64-bit Database PlatformHigh-performance
computing

• Optimized for Windows Server 2003
• Support for Xeon Itanium
• Great performance
• Large memory addressability (up to 32 TB)
• Nearly unlimited virtual memory (up to 8 TB)
• I/O savings due to larger memory buffer pools
• Numa Support
• T-SQL code-compatibility with SQL Server 2000
• 8 node clustering support
• Same on-disk format as 32-bit for easy migration
• 64bit support for relational engine and BI
• Compelling alternative to expensive Unix solutions

The highly scalable database platform for memory
intensive, performance-critical business
applications
58
Summary DBA Productivity with SQL Server 2005

• Scalable, Reliable and secure
• Ease-of-use and TCO leader for database
  management
• Achieve high availability without added
  complexity, cost
• Scales from mobile device to the datacenter

59
Developer Productivity
Rapid Time to Market
60
.NET and Visual Studio IntegrationBreakthrough
in Developer Productivity

• Choice of programming language
• T-SQL for data-intensive functions and procedures
• .NET languages for CPU-intensive functions and
  procedures
• Choice of where to run logic
• Database or mid-tier
• Symmetric data access model ADO.NET
• Integrated debugging experience across mid-tier
  and database tier
• Seamlessly step cross-language TSQL and .NET
• Set breakpoints anywhere, inspect anything
• Flexible and extensible
• Users defined functions, procedures, triggers
• User defined types and aggregates
• XML data type

61
Business Intelligence
62
BI Part of CIOs Top 10 Priorities
Technology Priorities Clearly Reflect the
Business Priorities
Top-Ten Technology Priorities, 2005
Ranking
2005
2004
2003
Security enhancement tools Business Intelligence
applications Mobile workforce enablement Workflow
management deployment and integration Enterprise
resource planning (ERP) upgrades Storage
management Voice and data integration over
IP Customer relationship management
(CRM) Business process integration tools Server
virtualization
1
1
3
10
13
2
-
3
-
-
-
4
-
-
5
-
-
6
-
-
7
8
-
-
9
-
-
10
-
-
63
1 in Business Intelligence (OLAP)Unranked in
1998!
Source olapreport.com
64
Note I changed builds and added logos change
colors so we get 3 distinct colors

• Data analysis for a variety of user needs
• Centralized business logic in a unified model
• Transparent data storage
• Reduced data latency
• Enhanced data mining
• Streamlined infrastructure

• Data transformation and synthesis
• Efficient processing of large data volumes
• Reduced hand coding with pre-built adapters and
  transformations
• Complex data handling
• Smart data cleansing (DW specific)

• Data presentation and distribution
• Business user report design
• Report controls
• Infinite drill down
• Sharepoint integration

65
Clients
66
Plan to go Live on CTP
67
Early Customer FeedbackWill go Live by Launch