SQL201 - Microsoft SQL Server 2008 R2

1. Mark Souza Director Microsoft SQL Server SQL201 - Microsoft SQL Server 2008 R2

2. SQL Server 2008 – Strong Release Trusted Platform Business Intelligence Productivity • Strong, winning strategy • Build BI into the stack, through familiar tools for users • Gartner MQ leader for BI platforms • SQL Server IS mission critical • 20+ points increase in all DB Tracker perception metrics • Better value – less expensive up front, easier to manage • Highest DBA productivity • Industry-leading developer tools • Extend apps to the cloud with SQL Azure

3. The SQL Server 2008 R2 Journey • The origins of Kilimanjaro • Self-service Business Intelligence • Application & Multi-server Management • Scaling for the next generation enterprise • High End Scale out Data Warehouses • CEP – Complex Event Processing • Reaching the summit

4. & Project codenamed “Madison” Self Service Business Intelligence IT & Developer Efficiency Scalable & Trusted Platform • Project “Gemini” Excel Add-in • Report Builder 3.0 • StreamInsight, Complex Event Processing • Master Data Services • SharePoint Publishing • Application & Multi-Server Management • Project “Gemini” SharePoint Management Console • StreamInsight .Net Extensions • Enterprise-level security, scalability • Supports up to 256 Logical Processors • SQL Server System Preparation • Enhanced Data Compression Solid Foundation for Enterprise Workloads Project “Madison” Better Together with Windows Server MPP support for 100+ terabyte data warehouses Appliance-like data warehouse on industry standard hardware Hyper-V™ Live Migration Support for largest Windows Server hardware

5. The SQL Server 2008 R2 Journey • The origins of Kilimanjaro • Self-service Business Intelligence • Application & Multi-server Management • Scaling for the next generation enterprise • High End Scale out Data Warehouses • CEP – Complex Event Processing • Reaching the summit

6. What's in a name… • Gemini - Gemini (pronounced /ˈgɛmɪnaɪ/, Latin: twins, symbol ♊) is one of the constellations of the zodiac known as "the twins"

7. The corporate Twins: IT Pro/End UserA widening gap between end user and IT needs If I help this time I’m stuck maintaining it forever… I’m not exactly sure what I need but I know I need it now… • EndUsers: • Access to corporate data • Mix in their own data • Aggregate, augmentdata • Organize, present solutions • Share insights with others • IT Professionals: • Know data is secure • Know data is consistent • Keep systems running • Keep the cost down • Track data access & usage There need not be an end-user versus IT conflict or gap in meeting user needs The gap is caused by lack of enabling technology, heavy “app lifecycle” costs

8. The Challenges Data warehouses do not cover all data or all users New formal BI solutions need time and resources Diverse users have diverse data needs Bottleneck Ad-hoc requests stress I.T. capacity Power users bypass I.T. with unsanctioned sources Chaos

9. Gemini: Uniting the Twins Re-draws the line between I.T. and end-user roles Empowered to create without IT dependence • Managingcompliance and • resources without user obstruction

10. Excel is key for IW/Users • “It has to be Excel” • “We don’t get OLAP & dimensional models” • “What is data modeling anyway?” • “Just make my Excel better" • Use Excel as a catch all tool to • Collect data • Clean, prepare and integrate it • Enrich and Analyze • Create reports and visualizations • Share them with others • Easy sharing of insights is critical • Each power user publishes data to 10’s-100’s consumers • IT needs to know ! BI!

11. Sneak peak Nicholas Dritsas Program ManagerSQL Server Product Team

12. IT manage the "Spreadmarts" • Excel is the IW tool of choice, but for IT: • Excel is a problem - “unmanageable” • Excel is an addiction – users “can’t quit it” • Why not make Excel part of the solution? • Include Excel as part of a complete BI solution • Structured and manageable • Give IT insight into its usage • Provide IT with the technology to • Have insight and management • Become a strategic differentiator • Without being a bottleneck • Enable managed Self-Service

14. The SQL Server 2008 R2 Journey • The origins of Kilimanjaro • Self-service Business Intelligence • Application & Multi-server Management • Scaling for the next generation enterprise • High End Scale out Data Warehoues • CEP – Complex Event Processing • Reaching the summit

15. Challenges: People vs. Hardware Trends • Database apps increasing at a higher rate than DBAs • Overburdened DBAs Underutilized hardware Hardware computing capacity Overburdened Administrators Number of database apps • Hardware computing capacity exploding • Underutilized hardware Number of DBA’s 1990 2000 2010

16. Introducing a better way Today Tomorrow • Control server sprawl with 1 to many management – setup is fast and easy • Manage capacity through policies – save time, optimize resources • Single unit of deployment – increase deployment and upgrade efficiency

17. Confidential – Internal Use Only Key Concepts DAC Logical Tables, Views, Constraints, SProcs, UDFs Users, Logins Physical Indexes, Partitions FileGroups … Data-Tier Application Component (DAC) • Think of this as the new unit of deployment for T-SQL apps and providing similar benefits of a MSI in a very general sense. • There is a definition of all the parts that make up the app along with services such as Install, Uninstall, Upgrade, and eventually Repair. DAC Deployment Profile Deployment Requirements, Management Policies, Failover Policies Unit of Deployment DAU – (C)DB • Data-Tier Application Unit (DAU) • Think of this as the overall unit of management. Or the deployed instance of a DAC • Maps to a plain database in KJ. In SQL 11, a CDB - a more self-contained database (with additional dependent objects). • Provides namespace and resource isolation. Schema Tables, Views, Constraints, SProcs, UDFs, Users, Logins Indexes, Partitions, FileGroups DAC Properties & Metadata Deployment Requirements, Management Policies, Failover Policies Unit of Management

18. Confidential – Internal Use Only Key Concepts (continued..) • Utility Control Point (UCP) • Think of this as the central reasoning point of the utility. • From here operations such as policy evaluation, discovery, deployment, impact, and what if analysis can be performed. • Connection Virtualization (Medusa) • Think of this as DNS for connection strings • Decouples application from the physical location of DAU (CDB) • Uses Active Directory (KJ). SQL02 SQL01 SQL03 SQL05 SQL04 DBA UCP Managed Instances Management Studio

19. Microsoft Confidential—Preliminary Information Subject to Change Control• Optimization • Efficiencies Key Benefits Gain Visibility and Control • New wizards in SSMS – fast and easy setup • Create a Control Point • Enroll instances • Insights refreshed every 15 minutes Management Studio Managed Server Group Database Administrator SQL Server Control Point

20. Microsoft Confidential—Preliminary Information Subject to Change Control • Optimization• Efficiencies Key Benefits Improve Resource Optimization • At-a-glance views for insights • ID consolidation opportunities • Quickly drill-down to detailed views • Simple UI for policy adjustments

21. Application & • Multi-Server Management • Creating the UCP • Insights – Health Check • Demo

22. Microsoft Confidential—Preliminary Information Subject to Change Control• Optimization • Efficiencies Key Benefits Improve Efficiencies Client • Single unit of deployment • Integration with Visual Studio • Streamlined deployments & upgrades Data-Tier Developer “Finance” Management Studio Database Administrator Managed Server Group Central management

23. Application & • Multi-Server Management • Creating the DAC • Migrating the DAC demo

24. Application & Multi-server Management • Productive database application development and management via • Introduction of new Database Application Components (DAC) • Application of Policy Based Administration to DACs • Intellisense integration with Visual Studio • Ability to version, deploy and reverse engineer a DAC • Multi-server Management made easier through • DAC experiences integrated with Management Studio and Visual Studio • Import and Export of database application artifacts • Support for reverse engineering a DAC from down-level systems • Deployment to one or more target systems • Monitoring of multiple instances of a database application on several servers via Management Studio

25. The SQL Server 2008 R2 Journey • The origins of Kilimanjaro • Self-service Business Intelligence • Application & Multi-server Management • Scaling for the next generation enterprise • High End Scale out Data Warehouses • CEP – Complex Event Processing • Reaching the summit

26. Project Madison Massive scale-out to 100’s TB Massive Scale with Low TCO Integration with Microsoft BI MTP – August 2009 RTM 1H CY10 The Data Warehousescale journey FastTrack Reference Architecture – 10s TB Easier, predictable and cost effecient Massive Scale-out Scale-up 10s of TB

27. Fast Track DW Accelerate scalable Data Warehouse deployments at lower TCO Pre-configured, pre-tested HW reference architectures (4-32 TB) SI Solution Templates Appliance-like time to value Flexibility through choice of HW platforms Low TCO through commodity hardware and value pricing. Reduced risk through pre-tested and pre-tuned configurations Provides a clear upgrade path to “Madison” via Hub/Spoke Microsoft Confidential—Preliminary Information Subject to Change • 27

28. Scale out Data Warehousing • Project “Madison” integrates the MPP innovations in DATAllegro with SQL Server • Massive DW scale at lower TCO targeting many 10s to many 100s TB data warehouses • ‘Appliance-like’ user experience via hardware partners • 1st Half CY2010 with delivery as a new “edition” of SQL Server “Madison” Reference hardware platforms INDUSTRY STANDARD SERVERS INDUSTRY STANDARD NETWORKING INDUSTRY STANDARD STORAGE

29. Massively Parallel Processing MPP • True MPP, Shared Nothing Architecture • Server/CPU’s have their own dedicated resources • Secret Sauce is MPP Query Optimizer supporting Parallel operations • Lightning-fast Queries, Data Loads And Updates • Linear Scalability • Lower TCO- Reduced DBA time

30. High-Level Madison Architecture Database Server Nodes Storage Nodes Control Rack Data Rack Control Node Active/Passive Compute Node Infiniband ETL Load Interface Client Drivers Landing Zone Backup Node Management Node Active/Passive Corp. Backup Solution Fibre Channel Spare Node

31. D D D D D D C C C C C C I I I I I I CD CD CD CD CD CD P P P P P P S S S S S S Data Distribution with Replication Madison Appliance Nodes Large Tables Are Hash Distributed Database Tables Smaller Tables Are Replicated Date Dim D_date_sk D_date_id D_date D_month … SS Customer C-Customer_sk C_customer_id C_current_addr … Item i_item_sk i_item_id i_rec_start_date i_item_desc … SS Store Sales Ss_sold_date_sk Ss_item_sk Ss_customer_sk Ss_cdemo_sk Ss_store_sk Ss_promo_sk Ss_quantity … SS SS Promotion P_promo_sk P_promo_id P_start_date_sk P_end_date_sk … Customer Demographics Cd_demo_sk Cd_gender Cd_marital_status Cd_education … SS Store S_STORE_SK S_STORE_ID S_REC_START_DATE S_REC_END_DATE S_STORE_NAME … SS

32. DBA Work Made Easy Create Database <dbname> With(AUTOGROW = ON | OFF DISTRIBUTION_SIZE = value_in_GB REPLICATION_SIZE= value_in_GB LOG_SIZE = value_in_GB • CREATE DATABASE sampledb_288 • ON PRIMARY • (NAME = N'sampledb_288', • FILENAME = N'[DRIVE_LETTER]:\primary\sampledb_288.mdf', • SIZE = 3MB, • MAXSIZE = UNLIMITED, • FILEGROWTH = 10%), • FILEGROUP DIST_A • (NAME = N'DIST_A_1', • FILENAME = N'[DRIVE_LETTER]:\data_01\sampledb_288_DIST_A_1.ndf', • SIZE = 625MB, • MAXSIZE = UNLIMITED, • FILEGROWTH = 4MB), • FILEGROUP REPLICATED • (NAME = N'REPLICATED_9_1', • FILENAME = N'[DRIVE_LETTER]:\data_01\sampledb_288_REPLICATED_9_1.ndf', • SIZE = 125MB, • MAXSIZE = UNLIMITED, • FILEGROWTH = 4MB), • LOG ON • (NAME = N'sampledb_288_LOG_1', • FILENAME = N'[DRIVE_LETTER]:\log_01\sampledb_288_LOG_1.ldf', • SIZE = 1000MB, • MAXSIZE = UNLIMITED, • FILEGROWTH = 10%); • ALTER DATABASE sampledb_288 SET AUTO_CREATE_STATISTICS ON; • ALTER DATABASE sampledb_288 SET AUTO_UPDATE_STATISTICS ON; • ALTER DATABASE sampledb_288 SET RECOVERY SIMPLE; Madison Generates

33. The SQL Server 2008 R2 Journey • The origins of Kilimanjaro • Self-service Business Intelligence • Application & Multi-server Management • Scaling for the next generation enterprise • High End Scale out Data Warehouses • CEP – Complex Event Processing • Reaching the summit

34. What Is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

35. Microsoft’s CEP Solution Data Sources, Operations, Assets, Feeds, Sensors, Devices Input Data Streams OutputData Streams Input Data Streams CEP Engine Monitor & Record Mine & Design Manage & Benefit f(x) f'(x) g(y) h(x,y) Results Deploy History Operational Data Store & Archive CEP Engine f(x) g(y) f'(x) h(x,y)

36. CEP Deployment Alternatives CEP CEP for lightweight processing and filtering CEP CEP for aggregation and correlation of in-flight events CEP CEP for complex analytics including historical data • Event processing engines are deployed at multiple places on different scales • At the edge – close to the data source • In the mid-tier – consolidate related data sources • In the data center – historical archive, mining, large scale correlation Web servers Sensors CEP CEP Feeds Devices CEP CEP CEP CEP CEP CEP CEP • Complex Analytics & • Mining

37. LINQ Query Examples LINQ Example – JOIN, PROJECT, FILTER: from e1 in MyStream1 join e2 in MyStream2 on e1.ID equals e2.ID where e1.f2 = “foo” select new { e1.f1, e2.f4 }; Filter Project Window Grouping Join LINQ Example – GROUP&APPLY, WINDOW: from e3 in MyStream3 group e3 by e3.i intoSubStreams from s4 inSubStreams from e4 in s4.SlidingWindow(FiveMinutes,ThreeSeconds) selectnew { pl = newMyNewPayload(e4.i, e4.f)};

38. Recap: CEP Platform from Microsoft Event Event Event Event Event Event Event Event Event Development experience with .NET, C#, LINQ and Visual Studio 2008 CEP Application Development CEP platform from Microsoft to build event-driven applications Event targets Event sources Event-driven applications are fundamentally different from traditional database applications: queries are continuous, consume and produce streams, and compute results incrementally CEP Engine Standing Queries Output Adapters Input Adapters Flexible adapter SDK with high performance to connect to different event sources and sinks The CEP platform does the heavy lifting for you to deal with temporal characteristics of event stream data Static reference data

39. question & answer