Novosti v Microsoft SQL Server 2005

2. Novosti vMicrosoft SQL Server 2005 Danijel Novak

3. Urnik • Uvod • Orodja • Novosti pri programiranju • Service Broker • Novosti pri upravljanju • Novosti pri razpoložljivosti • Novosti pri učinkovitosti • Replikacija • Integration Services • Analysis Services • Reporting Services • Notification Services

4. Uvod

5. .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 • VIA support • NUMA support Database Failure and Redundancy • Fail-over Clustering (up to 8 node) • Enhanced Multi-instance Support • Database Mirroring • Database Snapshots XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6.0 (Native) • .Net XML Framework Full-text Search • Indexing of XML Datatype 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 • Profiling Analysis Services • Exportable Showplan • Exportable Deadlock Traces MDAC • SNAC • Microsoft Installer base setup • Support for Active Directory Deployment SQL Client .NET Data Provider • Server Cursor Support • Asynch Security • All Permissions Grantable • Fine Grain Administration Rights • Separation of Users and Schema • Data encryption primitives Replication • Auto-tuning Replication Agents • Oracle Publication • Improved Blob Change Tracking • Replication Monitor OLAP and Data Mining • Analysis Management Objects • Windows Integrated Backup and Restore • Web Service/XML for Analysis • DTS and DM Integration • New Data Mining Algorithms • Auto Packaging and Deployment Data Transformation Services • New Architecture (DTR + DTP) • Complex Control Flows • Control Flow Debugging • For Each Enumerations • Property Mappings • Full Data Flow Designer • Full DTS Control Flow Designer • Graphical Presentation of Pkg Execution • Immediate Mode and Project Mode • Package (Advanced) Deployment Tools • Custom Tasks and Transformations Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition Notification Services & SQL Server CE

6. Orodja • Orodja za upravljanje in nadzor: • Enterprise Manager • Query Analyzer • SQL Server Management Studio • Business Intelligence Development Studio • SQL Computer Manager • SQL Server Books Online • Database Tuning Advisor

7. Demo: Orodja

8. Novosti pri programiranju • .NET Framework V2.0 • Nov XML podatkovni tip • Izboljšan T-SQL: • Lovljenje napak TRY..CATCH • DDL Triggers (CREATE, ALTER, DROP) • Rekurzivne poizvedbe

9. Service Broker • Novo v SQL Server 2005 • Programski vmesnik za pošiljanje sporočil in ukazov • Namenjen za delo z aplikacijami, ki uporabljajo več podatkovnih baz Shranjena procedura Aplikacija Service Broker Aplikacija T-SQL Aplikacija

10. Novosti pri upravljanju • Varnost • Sheme (User ≠ Schema) • Dinamična uporaba AWE spomina • Onemogočanje indeksov • Rebuild – manjša poraba prostora • Rebuild in Reorganize z ALTER INDEX

11. Demo: Sheme

12. Novosti pri razpoložljivosti • 8-node Cluster • 50 instanc na enem računalniku • Database mirroring • Database snapshots • Mirrored Backup • Restore

13. Novosti pri učinkovitosti • Snapshot isolation level • Particioniranje tabel in indeksov • “Razbijanje” velikih tabel na manjše dele • Izboljšani indeksirani pogledi • Več uporabe s strani optimizerja • Uporaba funkcij v indeksiranem pogledu • Asinhrono posodabljanje statistik

14. Replikacija • Enostavnejša izdelava (manj oken) • Vzporedna izdelava “snapshota”: • Več člankov hkrati • Uporaba “backupa” namesto “snapshota” • Replikacija iz Oracla • Replikacija pa HTTPS (merge replikacija) • Izboljšana učinkovitost: • Paralelno izvajanje

15. Integration Services • Popolnoma novi DTS: Integration Services • Novo orodje:Business Intelligence Development Studio • Ločena “Data flow” in “Control flow” • Možnost uporabe več virov in ciljev • Možnost ponovne uporabe vira ali cilja • Nova opravila • Novi podatkovni viri • Nove transformacije

16. Demo: Integration Services

17. Analysis Services • Enostavnejša uporaba: • Business Intelligence Development Studio • Novi čarovniki • Novi pogledi • Nove funkcionalnosti: • Triggers • Izboljšanje komponente: • Kocke • Dimenzije • Data Mining • Nove razvojne možnosti

18. Reporting Services • Izdelava, vzdrževanje in uporaba poročil • Formati poročil: • HTML, PDF, TIFF, Excel, CSV, … • Viri poročil: • SQL Server, Analysis Services, Oracle, ODBC, OLEDB

19. Notification Services • Prvič kot del SQL Serverja • Novo platforma za razvoj in uporabo aplikacij • Glavni funkcionalnosti: • Izdelava obvestil za aplikacije • Pošiljanje obvestil za aplikacije • Poenostavlja obveščanje med aplikacijami

20. V & O Danijel Novak, danijel.novak@snt.si