SQL Server 2005 – introduktion til nyhederne for DBA’er

2. SQL Server 2005 – introduktion til nyhederne for DBA’er Claus Busk Andersen Technology Specialist clausba@microsoft.com

3. Agenda • Management Tools • SQLiMail, SQLCMD • Tuning • Diagnostics • High Availability • Other • Security • Scaleability

5. SQL Server GenerationsHistory of Innovation 1st Generation 2nd Generation 3rd Generation SQL Server 6.0/6.5 SQL Server 7.0 SQL Server 2000 SQL Server 2005 • Differentiation from Sybase SQL Server • Windows integration • First to include Replication • Re-architecture of relational server • Extensive auto resource management • First to include OLAP & ETL • Performance, scalability focus • XML support • First to include Notification • First to include Data Mining & Reporting • High availability • Security • Developer productivity • Native XML • First to include Enterprise ETL & Deep Data Mining Cross-release objectives • Reliability & Security • Integrated Business Intelligence • Lowest TCO • Automatic Tuning

6. Simplifying the IT Lifecycle Our Vision

7. High availability for enterprise applications • Key security and performance features • Focus on self manageability and optimization • 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

8. 200,000+ desktops • 72,000 mailboxes • 5-7M+ e-mails/day • ~60K employees • 300+ supported Microsoft sites • 8,000+ servers • 1,000+ DB servers • 100% Microsoft products • Runs Microsoft products first • Single Instance SAP 60+ Countries

9. Microsoft R/3 Statistics • Global System in Redmond • Financials • Sales & Distribution • HR • 1.7+ TB Database • 2,500 named users; 57,000+ total users • 200-600 concurrent users • 300k+ SAP transactions/day • 100k+ batch jobs/month • <0.5 sec. response time • 99.9+% SAP availability • 25 production servers SAP R/3 SQL Server 2005 Log Shipping

10. Microsoft Feedstore Statistics • Centralized Corporate Data Integration Point • MC Data Publishing and Data Distribution • ETL & Replication processes • Publish/Subscribe Model • 42 Published Datasets • 1,822 Published Tables • 500+ Subscribers (100+ replication & 400 3DAPI) • VLDB’s 1.5->2 TB (EVA SANs, 8 proc servers) Providers SQL Warehouse Regional Distribution Subscribers

11. External Sales Data Microsoft MS Sales Statistics • Consolidated revenue data • 9000+ users worldwide • 25,000 ad-hoc reports run each week • Peak 200+ concurrent users for >6 hours on Mondays • >10 million transactions per month stored • 0.5 TB reporting database • 4 DBAs • 42 servers in production • Mostly 8-proc systems • One 16-proc Unisys ODS Factory Data Load SQL Partitioning Load Balancing

12. Runs Microsoft • Runs 100% on SQL Server • Runs Microsoft products before customers • 15+ apps live on SQL Server 2005 today! • 50+ applications on SQL Server 2005 before RTM • Key mission critical apps live on Beta 2 • SAP R/3 Deployment, 1.7 TB • “Feedstore”: Staging DW for all Microsoft data • Microsoft Sales Revenue Reporting and BI system • Full data life-cycle in SQL Server 2005 • [Sales]  SAP  Feedstore  Microsoft Sales

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

14. Management Studio • Integrated management • Relational DB, Analysis Services, Reporting Services, Notification Services, SQL Server CE… • Multiple versions of SQL Server from one interface • Integrated with DTS • Rich management & authoring • Performance • Manage multiple servers, MANY objects • Operations: Profiler, Query Analyzer

15. Manageability - Tools • New: SQL Server Management Studio • New Rich GUI • Integrated Authoring, Management • Support all SQL Server components • New: Management Objects (SMO) • Managed code, new design patterns • Improved Scalability, Scripting • New: SQLCMD • High Performance CMD line tool • New: SQLiMail Replacement • SMTP Support, Cluster Support, No Outlook required

16. Demo – Tools, SQLiMail, SQLCMD

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

18. Demo – Tuning

19. Manageability - Diagnostics • 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

20. General Server DMVs and DMFs • dm_exec_* • Execution of user code and associated connections • dm_os_* • Memory, locking & scheduling • dm_tran_* • Transactions & isolation • dm_io_* • Input/Output on network and disks • dm_db_* • Databases and database objects

21. Component level DMVs and DMFs • dm_repl_* • Replication • dm_broker_* • SQL Service Broker • dm_fts_* • Full Text Search • dm_qn_* • Query Notifications

22. Demo – Diagnostics

23. 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

24. Microsoft Cluster Services • Supported in Microsoft® SQL Server™ 7 • Enhanced in Microsoft® SQL Server™ 2000 • Further refined in SQL Server 2005 • More nodes (match operating system limits) • Unattended setup • All SQL Server services participate • Database Engine, SQL Server Agent, • Analysis Services, Full-Text Search, etc.

25. 2 3 1 5 4 2 3-6 3-6 Witness Principal Mirror Application SQL Server SQL Server Log Data Log Data Database MirroringAchieving high availability while managing costs • Database Failover • Very fast failover…less than 3 seconds • Automatic or manual failover • Automatic, transparent client redirect • Database Snapshots for Reporting • Works with standard hardware and storage

26. Database Mirroring

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

28. 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 Redo Undo Available

29. Demo – Admin connection

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

31. 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

32. Demo – Database Snapshot

33. 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

34. Sliding Window Time Obsolete Near-Line Read-Only Read-Mostly Hot Archived Partition Manageability • Common operations • Split, merge, add, and drop partitions • Bulk data loading, Index maintenance • Switch data instantly between • Table and partition of another table • Two partitions of different tables

35. 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 • 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+ around RTM • Security Tools • Continued MBSA investments • Best Practices Analyzer

36. Cost Savings Scalability Manageability 64-bit Database PlatformHigh-performance computing • Optimized for Windows Server 2003 • Support for AMD Opteron & Intel Itanium with Beta 2 • Intel EM64T coming with Beta 3 • 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 • 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

37. Spørgsmål