1 / 38

Impact of SQL Server 2005 on Application Architecture

Impact of SQL Server 2005 on Application Architecture. Eric Nelson Application Architect Microsoft Ltd http://blogs.msdn.com/ericnel Eric.Nelson@microsoft.com. Agenda. Hot News – 7 New Products If we have time … SQL Server 2005 Application Architecture. The New Server Lineup ….

abel
Download Presentation

Impact of SQL Server 2005 on Application Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Impact of SQL Server 2005 on Application Architecture Eric Nelson Application Architect Microsoft Ltd http://blogs.msdn.com/ericnel Eric.Nelson@microsoft.com

  2. Agenda • Hot News – 7 New Products • If we have time … SQL Server 2005 Application Architecture

  3. The New Server Lineup … • Microsoft SOA Broker- Data Edition • 'Secure and scaleable data-centric web services for the enterprise' • Microsoft XML Database Server • 'Delivering XML at the heart of your Enterprise' • Microsoft Application Server.NET • 'Secure and scaleable application server for the 21st century' • Microsoft ETL Server • 'The data consolidator for the Enterprise' • Microsoft Diamond Reports Server • 'A diamond amongst crystals' • Microsoft Insight OLAP Server • 'Changing data into insight for the knowledge worker' • Microsoft Deep Thought Server • 'A new breed of data server' NB: Names are with legal and may change (in about 10mins )

  4. SOA Broker – Data Edition SOA Broker (No IIS) SOAP Clients Internet SOAP High-speed DataConnect SQL Database SOAP

  5. XML Database Server RelationalData SQL Database RelationalData Clients XML XML XML XQuery XML Database Server

  6. Application Server.NET XML WebService Server (No IIS) XML Database Server Oracle Clients DB2 SQL Code High-speed DataConnect Application Server.NET .NET Framework 2.0

  7. Microsoft ETL Server Destinations Sources SQL SQL J Smith 36 DB2 DB2 Mr John Smith, Old, Happy John Smith Male Oracle John Smith Married Oracle ETL Server CleansesCombinesAggregates XML XML

  8. Diamond Reports Server Sources Rich Client SQL DB2 SOAP/http Browser Data Oracle Diamond Reports Server Tiff, PDF, XLS, CSV… XML

  9. Insight OLAP Server Cube Insight OLAP Server OLTP System Excel Complex Queries OLTP System Browser OLTP System Data OLTP System 3rd Party 'I need to know the average increase year on year for each sales person, each department, each office, each country for 1993 to 1996 for widget x and y and z – but only include sales of the large versions in red'

  10. Deep Thought Server (codename) OLTP System Question: 'Is there anything interesting in this data about my sales?' OLTP System OLTP System Answer: 'Yep, employees who live close and do not drive are delivering very few sales on Friday afternoons.' Data OLTP System 42 Deep Thought Server

  11. Suite SKU • Suite SKU includes a licence for all 7 products • Single Media • Single Install • And as an introductory offer, the Suite SKU comes with the new more scaleable, more secure, more manageable … • SQL Server 20005 RDBMSfor FREE!!!!!!!!!!!!!!!!! • Single Media • Single Install

  12. Breaking News – Final Product Names Announced • SQL Server 2005 Web Services and SQL Server 2005 Service Broker • alias 'Microsoft SOA Broker - Data Edition' • SQL Server 2005 XML Data Type and FOR XML • Alias 'Microsoft XML Database Server' • SQL Server 2005 .NET Framework Stored Procedures, Triggers and User-Defined Types • Alias 'Microsoft Application Server.NET' • SQL Server 2005 Integration Services • Alias 'Microsoft ETL Server' • SQL Server 2005 Reporting Services • Alias 'Microsoft Diamond Reports Server' • SQL Server 2005 Analysis Services • Alias 'Microsoft Insight OLAP Server' • SQL Server 2005 Data Mining • Alias 'Microsoft Deep Thought Server'

  13. SQL Server 2005 can significantly impact the Architecture of Applications

  14. Consider Application Architectures … SQL Server 2005 • One extreme TDS VB6 All the code • No code • All data stored as relational or blobs

  15. Consider Application Architectures … SQL Server 2005 • Another extreme Winform Client or WebForm Client talking SOAP Internet • 100% .NET Code • Exposed as Web Service • All data stored as XML Question: How many tables in an XML database?

  16. Drill down into New Technologies which Affect Application Architecture

  17. Agenda • Storage • XML Data Type • Programming • CLR Integration • SOA • Web Services • Query Notifications • Service Broker • Which maps back to … • SQL Server 2005 XML Data type and FOR XML • Alias 'Microsoft XML Database Server' • SQL Server 2005 .NET Framework Stored Procedures, Triggers and User-Defined Types • Alias 'Microsoft Application Server.NET' • SQL Server 2005 Web Services and SQL Server 2005 Service Broker • Alias 'Microsoft SOA Broker - Data Edition'

  18. Storage

  19. XML Datatype • Native SQL type • Use for column, variable or parameter • Can represent: • XML 1.0 documents • XML 1.0 fragments (0 to n element nodes and text nodes at top) • Can be constrained by XML Schema collection • Queryable with XQuery • Updateable with XML-DML • XML Indexing • Well-formed and validation checks • Impact • Allows XML Data to be stored, manipulated and retrieved easily with SQL Server 2005 • WARNING: Not everything should be XML!

  20. Programming

  21. .NET Integration • Choice of programming language • TSQL, for data-intensive functions and procedures • .NET languages, for CPU-intensive functions and procedures • Safe, scalable, modern execution environment inside database • Choice of where to run logic • Database, for logic that runs close to data • Mid-tier, for logic that scales out • Symmetric data access model – ADO.NET

  22. .Net IntegrationBusiness Logic • User-Defined Functions • Scalar • Table Valued • User-Defined Procedures • User-Defined Triggers • Assemblies • Unit of encapsulation, deployment • Impact • Allows complex algorithms to be coded and maintained in a modern programming language • Allows 'middle tier' code to run very near the data • WARNING: Does NOT mean replace all TSQL with .NET code

  23. .Net IntegrationExtensibility • User-Defined Types • Complex structure and behaviour • Custom attributes, contract • Supported in client libraries • User-Defined Aggregates • Extensibility hook into the Query Processor using a well-defined contract • Initialise a group, accumulate a value, merge groups, finish computation, return result • Impact • Can extend the database to better meet your solutions needs • WARNING: Not an Object Oriented Database

  24. SOA

  25. Services, SO, and SOA • Service – An endpoint that reacts to messages • Service Orientation – An architectural paradigm that employs the following four tenets: • Boundaries are explicit • Services are autonomous • Services share schema and contract, not class • Service compatibility is determined based on policy • Service Oriented Architecture (SOA) – Any architecture the adheres to the Four Tenets of Service Orientation

  26. Service-Oriented Architectures • SQL Server 2005 provides the key infrastructure for Service-Oriented Architectures (SOA) • Direct Web Services access to SQL Server (HTTP/SOAP) • Service Broker – Service based, decoupled, asynchronous, reliable, distributed messaging • Query Notifications – Enabling responsive multi-tier data backed caches Web Clients Highly Responsive, Data-Caching Mid-Tier Queries with Notifications Service based transactional workflow SQL requests via HTTP/SOAP Legacy Non-Windows System

  27. SQL Web Services • Native SOAP access • Standards-based access to SQL Server • No client dependency • Improved Interoperability • New 'ENDPOINT AS HTTP' object • Configure connection info • Configure authentication • Expose Functions & SPs • Expose TSQL Batches • Impact • Easily add Web Services to existing applications • Deliver Web Services without the need for IIS/ASP.NET • WARNING: Not your 1st choice http://server1/aspnet/default.aspx Kernel Mode Listener http://server1/sql/pubs?wsdl

  28. SQL Server manages cache invalidation Integrated change detection notifies client as soon as results of query changed Deep Query Processor integration No more 'roll-your-own' cache management! Just add a few lines of code to your existing requests ADO.NET OLEDB Leverages Service Broker architecture Impact Enabling easy implementation of highly efficient mid-tier data caching Off-load query workload from the server to caching clients Requests/ Responses IIS/ ASP.Net Notification of Cache Invalidation Query & Subscription/Results SQL Server 2005 Query Notifications

  29. Service Broker

  30. Service Broker SQL Engine SQL Engine • Framework for building reliable, asynchronous, distributed applications • Communication infrastructure • Two-way ’dialogues' • Reliable delivery to local, remote queues • Exactly-once, in-order semantics • Large Message Fragmentation • Impact • Asynchronous messaging becomes 'easy' to add • Can handle huge peaks, long lived work with ease • WARNING: SQL 2005 to SQL 2005

  31. Conversations and Dialogues • Services communicate via formal, reliable sessions known as Conversations • Conversations are the basic primitive of communication in SQL Server 2005. There are two types: • Dialogues - 1 : 1 communication channel between 2 services • Monologues – 1 : N communication channel between a publishing service and subscribing services (not in SQL Server 2005) • Dialogues provide two-way messaging between two services • Guaranteed delivery • Full-duplex or half-duplex communication • EOIO delivery • May be long-lived (years) or short-lived (seconds) • Are lightweight • Are sessions (similar to TCP conceptually)

  32. Customer Service Travel Service Protocol Endpoint Protocol (Dialogue Semantic here) TDS TDS Database B Adjacent Broker Protocol Adjacent Broker Protocol (Message Fwding here)

  33. Conversation Groups • Business transactions often involve multiple services collaborating to achieve a common end • E.g. Booking a reservation Hotel 2 5 1 Client Travel 3 Airline 7 8 4 Car 6 • Conversation Groups: • Formalise the relationship between dialogues • Have a unique identity • Are a serialisable unit in SQL Server. Only one transaction may lock a conversation group at a time • Dialogues are always associated with a conversation group, hence only one transaction can lock a dialogue at a time as well

  34. Example: Order Entry

  35. Example: Parallel SP

  36. Summary • SQL Server 2005 can have zero impact on application architecture • And • SQL Server 2005 can impact greatly application architecture • Call to action • Try SQL Server 2005 today http://msdn.microsoft.com/virtuallabs/sql/default.aspx • Get the trials http://www.microsoft.com/emea/msdn/visualstudio/enxu/getthetrials/

  37. Resources • General articles on what we covered • http://msdn.microsoft.com/SQL/2005/servprog/default.aspx • XML and Databases whitepapers • http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/ • Service Broker • http://www.exforsys.com/content/view/1712/356/ • http://www.sqlservicebroker.com/

  38. © 2003-2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

More Related