Introduction & What’s new for IT PRO Community

Introduction & What’s new for IT PRO Community by Satya Shyam K Jayanty sqlmaster@sqlserver-qa.net Microsoft Campus, Thames Valley Park, Reading 09th April 2008

Agenda – a tour Introduction - Speaker Introducing - SQL Server 2008 SQL Server 2008 – features & fixtures SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

This page is intentionally left blank J SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Introduction – Speaker (About) • IT Experience Been in the IT field over 16+ years (SQL Server Specialist Consultant) SQL Server DBA for over 12 years (working since ver. 4.2) Principal Consultant & Director – SSQA.net Limited (www.ssqa-net.co.uk) • Recognition SQL Server MVP (Microsoft Most Valuable Professional) Speaker : Tech-ed, SQLPASS, SQL Bits, User Group (Scottish Area SQL Server User Group) • Community Contributions • Webmaster(SQLMaster) of www.sqlserver-qa.net [SSQA.net] Contributing Editor & Moderator - www.sql-server-performance.com [SSP] Active participation in assorted forums such as SSP, MSDN, SQL Server magazine, dbforums etc. Community participation in ‘Ask The Experts’ lounge within TechED (US), SQLPASS, SQLBITS (UK) and Scottish SQL Server User Group. SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

4 pillars Enterprise Data Platform Enterprise Data Platform • Secure, trusted platform for your data • Optimized and predictable system performance • Productive policy-based management of your infrastructure Dynamic Development Dynamic Development • Accelerate your development with entities • Synchronize your data from anywhere Beyond Relational Pervasive Insight • Integrate all your data in the Enterprise Data Warehouse • Reach all your users with scalable BI platform • Empower every user with actionable insights Pervasive Insight SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications

Covering... Enterprise Data Platform Beyond Relational Dynamic Development Pervasive Insight What and How it helps: - Features & Fixtures Spend less time on ongoing operations Manage via policies instead of scripts Define Enterprise wide data management policies Automated monitoring and enforcement of policies Simplify your installation and configuration Integrated with your enterprise system management Define Policies that are compliant with System Definition Model Integration with System Center Ops Mgr History Types of Editions SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

SQL Server 2008 – Features & Fixtures • What’s new • Following components have new & improved features • Relational Database Engine • Analysis Services Database, Data-mining • Integration Services • Replication • Reporting Services • Backward Compatibility • Deprecated Features • Deprecated features such as BACKUP DATABASE/LOG WITH PASSWORD or RESTORE DATABASE WITH DBO_ONLY • Breaking Changes • Create Default/Rule/SP_DBOPTION/Remote Servers/SP_DROPALIAS • Behavioral Changes • DBLibrary / Embeded SQL for C • Workaround: Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

SQL Server 2008 – Features & Fixtures • The following changes and enhancements are new in SQL Server 2008: • Support for new and updated collations from Windows Server 2003 and Windows XP, including Far East collations that support supplementary characters. • Support for supplementary character string comparison based on code point with new collations in SQL Server. • Addition of a binary flag for true code point comparisons: Binary-code point.Improved migration path from earlier versions of SQL Server. SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

List of….. • Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more • SQL Server Change Tracking • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • FILESTREAM data type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type • LOCATION data type • SPATIAL data type • Virtual Earth Integration • Partitioned Table Parallelism • Query Optimizations • Persistent Lookups • Change Data Capture • Backup Compression • MERGE SQL Statement • Data Profiling • Star Join • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Failover for Database Mirroring • Declarative Management Framework • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

History…so far! here we are… SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net Source & Credit: Euan Garden

Enterprise Data Platform Services Reporting Integration Analysis Synch Entity Data Model Dynamic Development Query Search FILE RDBMS OLAP XML Beyond Relational Mobile and Desktop Server Cloud Pervasive Insight SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Enterprise Data Platform • Secure, trusted platform for your data • Optimized and predictable system performance • Productive policy-based management of your infrastructure Dynamic Development • Accelerate your development with entities • Synchronize your data from anywhere Pervasive Insight • Integrate all your data in the Enterprise Data Warehouse • Reach all your users with scalable BI platform • Empower every user with actionable insights SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications

What’s New with Database Engine Enterprise Data Platform • Protect your information • Encrypt your data without requiring an application re-write • Consolidate security keys within the data center • Integrated auditing support • Increase the reliability of your applications • Add system resources without affecting your users • Leverage database mirroring to increase reliability • Database Mirroring Enhancements • Automatic page repair • Log stream compression • Fast recovery on failover • Enhanced diagnostics SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

What’s New with Performance Analysis Enterprise Data Platform • Better Performance • Increase query and backup performance • Reduce your storage costs • Optimize System Performance • Monitor your system by collecting and storing system events • Analyze and report on your system performance • Predictable Performance (Resource Governor) • Manage concurrent workloads on single system • Prevent runaway queries • Ensure consistent query performance • Define workload groups and resource pools with various limits • Categorize connections so they are governed by the limits • Dynamically alter the resource governor configuration SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

New: Productive Policy-based Management Enterprise Data Platform • Spend less time on ongoing operations • Manage via policies instead of scripts • Define Enterprise wide data management policies • Automated monitoring and enforcement of policies • Simplify your installation and configuration • Integrated with your enterprise system management • Define Policies that are compliant with System Definition Model • Integration with System Center Ops Mgr • Full-text indexes are stored within the database files • Removes a lot of management headaches • E.g. database mirroring works properly SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

DemoDMF SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

New: Accelerate YourDevelopment With Entities Dynamic Development • Develop with business entities • Define your business not tables • Model your complex business relationships • Retrieve entities instead of rows and columns • Connect to SQL Server with entity aware adapters • Reliable application performance • Store entities locally on your clients SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Connect to your data From any device Dynamic Development • Access your data from anywhere • Store your data locally while disconnected from server • Synchronize Incremental changes between client and server • Detect conflicts during synchronization including deletes • Add disconnected scenarios without re-writing existing applications SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Store and Consume Any Type of Data Beyond Relational • Seamless transition between relational and non-relational • Store your documents in cost effective storage platform • Access documents as data • Encode your complex hierarchies within XML • Extend the database to meet your unique needs • Find your data • Query across relational and text data • Build global applications • Capture precise time from clients across the globe SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Deliver Location Intelligence Beyond Relational • Build location-aware applications • Capture location data from across your organization • Integrate location intelligence to your existing applications • Visualize your location information • Standard Spatial applications • Integrate with industry standard spatial applications • Supported by 3rd parties • Spatial Indexes • Indexes to support the new Geography and Geometry data types • Used when comparing spatial data types to prune the need for complex intersection calculations • Complicated trade-off to manage SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

DemoSpatial SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Enterprise Data Platform • Secure, trusted platform for your data • Optimized and predictable system performance • Productive policy-based management of your infrastructure Dynamic Development • Accelerate your development with entites • Synchronize your data from anywhere Pervasive Insight • Integrate all your data in the Enterprise Data Warehouse • Reach all your users with scalable BI platform • Empower every user with actionable insights SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications

Enterprise Data Warehouse Pervasive Insight • Scale and Manage large number of users and data • Improve Query performance on large tables • Optimize Queries for data warehousing scenarios • Increase I/O performance with efficient and cost effective data storage • Manage concurrent workloads of ad-hoc queries, reporting and analysis • Integrate growing volumes of data • Optimize ETL performance • Reduce the data load volumes by capturing operational changes in data • Simplify the insert/update data processing • Profile your info to identify dirty data SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Enterprise Data Warehouse Pervasive Insight • Data Capture • SQL Agent jobs periodically scan the transaction log for change data on CDC enabled tables • Change data is placed in change tables, transaction metadata is recorded to provide a timeline • CDC reasons: Efficient updates of DW/Reporting systems (OLTP) and more efficient synchronization between data stores, conflict detection • FILESTREAM • Attributes on varbinary(max) columns and data is stored in the file system but rooted in the database • BLOB data in transactional consistent with structured data • Accessible through T-SQL AND NTFS Streaming APIs SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Reach All your Users withScalable BI platform Pervasive Insight • Deliver insights throughout your organization • Deliver reports of any size at enterprise scale • Scale out through read-only Analysis Services storage • Enhance analytical capabilities with more complex computations and aggregations • Deploy and manage your BI infrastructure • Streamline development of the analysis infrastructure with new cube design tools • Optimize cube design with real time best practice alerts • Backup cubes with enhanced scalability • Deploy Reporting Services without IIS dependency SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Empower Every UserWith Actionable Insight Pervasive Insight • Deliver information via Microsoft Office • Render reports to Microsoft Word • Enjoy improved rendering to Microsoft Excel • Bring data mining to new, much broader audience • Enable users to create powerful reports • Build powerful ad-hoc reports • Create reports with any structure using Tablix • Add rich text regions your reports • Embed powerful graphical data visualizations into reports (Dundas) • Empower users with enhanced analysis • Empower users with enhanced write back scenarios • Accelerate end user prediction capabilities through enhanced Data Mining structures SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Editions – Features & Limitations • Enterprise • Offers high levels of scalability and availability • Includes 64-bit support • Standard • Affordable option for small and medium-sized organizations • Includes 64-bit support • Limited to 4 CPU sockets and 16 instances per server • Limited to 2-node failover clusters • Limited to Safety FULL mode of Database Mirroring SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Editions – Features & Limitations • Workgroup • Suitable for small organizations • Limited to 3GB memory, 2 CPUs • No clustering or database mirroring • Express • A free, easy-to-use, lightweight version • Limited to 1GB memory, 1 CPU, 4GB database size • No clustering, mirroring, or log shipping • Compact • A free, limited functionality in-process version • Features for DBAs and IT-Pros • Need to manage databases and servers • Need to make their workloads scale • Need to troubleshoot performance problems • Need to know about so they can manage deployment by developers SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Performance Studio • Data Collection • Performance and Diagnostics Monitoring • Data Collection Sets • Dashboard (post-SQL2K8) • Policy-based management • Management Data Warehouse • Troubleshooting and Tuning • Historicaland baseline comparisons SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Traditional Reporting Challenges Problems with Reporting in Enterprise IT Expensive and complex server-side solutions Proprietary report formats Limits to scalability Difficulties Integrating with internal applications Embedding in line-of-business applications Automating delivery to a large internal or external audience Extending with additional features SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

SQL Server 2008 Reporting Services Delivers enterprise, Web-enabled reporting functionality Queries a wide variety of data sources Publishes reports in various formats Supports pull- or push-driven report delivery Manages security on content and tasks centrally Scales to support thousands of users Enables extensions to core functionality Delivers ad hoc reporting capabilities SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Resources Books Online • Primary documentation for SQL Server • Includes the following types of information • Setup and upgrade instructions • Information about new features and backward compatibility • Conceptual descriptions of the technologies and features • Procedural, “how to” topics • Tutorials that guide you through common tasks • Reference documentation for graphical tools, utilities, programming languages, and APIs • Always review & download the latest online version at • http://technet.microsoft.com/en-us/library/bb418491.aspx • SQL Server 2008 Product Overview doc http://www.microsoft.com/sql/techinfo/whitepapers/sql2008Overview.mspx • SQL Server 2008 Product Overview demo http://www.microsoft.com/sql/2008/overviewdemo/ SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Q & A Thanks for attending! SQL Server 2008 - Introduction & What’s new for IT Pro www.sqlserver-qa.net

Introduction & What’s new for IT PRO Community