SQL Server 2005 Database Engine

1
SQL Server 2005 Database Engine

• Sommarkollo Microsoft

2
Agenda

• SQL Server 2005 Components
• Tools and Utilities
• .NET Framework integration
• Service Broker
• Database Mirroring
• Security Overview
• Database Engine Enhancements
• Conclusion

3
SQL Server 2005 Product Family

• Components

SQL Server Integration Services
Notification Services
Analysis Services
SQL Server 2005
Relational Database Engine
Full-Text Search
Reporting Services
.NET CLR
Service Broker
Replication
Native HTTP Support
4
Tools and Utilities

• SQL Server Management Studio
• SQL Server Configuration Manager
• SQL Server Surface Area Configuration
• sqlcmd Utility
• Database Engine Tuning Advisor
• SQL Server Profiler
• SQL Management Objects
• Business Intelligence Development Studio

SQL Server
5
.NET Framework integration

• Procedural database objects
• Extends T-SQL
• Logic features
• Managed Execution
• Visual Studio
• Rich Libraries
• In some cases, better performance

.NET Assembly
Agg
SP
Trgr
UDT
UDF
6
Service Broker

• Asynchronous messaging
• Message types, Contracts, Queues, Services
• Ordering and coordination
• Database integration
• New DDL

7
Database Mirroring

• Support by service pack 1
• Software solution
• Per-database basis
• Full recovery model
• Possible
• No data loss
• Automatic failover

Witness
Partners
Principal Database
Mirror Database
Client
8
Security Enhancements Overview

• Restricted set of features installed or enabled
• Password Policy
• Execution Context
• Server Agent
• All Permissions Grantable
• Separation of Users and Schemas
• Native Encryption

9
Database Engine Enhancements Overview

• System Enhancements
• Data Storage Enhancements
• Partitioned Tables and Indexes
• New Index DDL
• Snapshot Isolation Level
• Database Mail
• Metadata

10
System Enhancements

• Dynamic AWE memory
• but still a workaround
• Dynamic I/O and CPU affinity
• CPU from an operating system point of view
• CPU from an SQL Server point of view

11
Data Storage Enhancements

• varchar(max), nvarchar(max), and varbinary(max)
• Some string functions can be used
• After Triggers
• Native XML
• Index
• FOR XML enhanced
• XQuery support added

12
Partitioned Tables and Indexes

• T-SQL
• MERGE
• SPLIT
• SWITCH

• Partition
• Function
• Scheme
• Filegroups

13
New Index DDL

• ALTER INDEX
• New syntax
• REBUILD (per partition)
• REORGANIZE (per partition)
• LOB_COMPACTION
• DISABLE
• ltSET OPTIONSgt
• ONLINE (Enterprise Edition, including CREATE
  DROP)
• ALLOW_ROW_LOCKS
• ALLOW_PAGE_LOCKS
• MAXDOP

14
Snapshot Isolation Level

• Versions of data are kept in tempdb
• No uncommitted data read
• Repeatable reads without blocking
• Calls for extra monitoring

tempdb
READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
15
Database Mail

• SMTP

16
Metadata

• New style
• Old style

17
Conclusion

• Well