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 … • 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 21 st 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 10 mins )

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

XML Database Server Clients XML Relational Data XQuery XML Relational Data XML Database Server SQL Database

Application Server. NET Oracle Clients DB 2 SQL Code Web. Service Server (No IIS) Application Server. NET High-speed Data Connect . NET Framework 2. 0 XML Database Server

Microsoft ETL Server Destinations Sources SQL J Smith 36 DB 2 Oracle XML Mr John Smith, Old, Happy DB 2 John Smith Male John Smith Married ETL Server Oracle Cleanses Combines Aggregates XML

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

Insight OLAP Server Excel OLTP System Complex Queries Browser OLTP System Cube 3 rd Party Insight OLAP Server Data OLTP System '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'

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

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 RDBMS for FREE!!!!!!!!! • Single Media • Single Install

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'

SQL Server 2005 can significantly impact the Architecture of Applications

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

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

Drill down into New Technologies which Affect Application Architecture

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'

Storage

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!

Programming

. 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

. Net Integration Business 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

. Net Integration Extensibility • 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

SOA

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

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 SQL requests via HTTP/SOAP Legacy Non. Windows System Service based transactional workflow

SQL Web Services • • • Native SOAP access • Standards-based access to SQL Server • No client dependency • Improved Interoperability http: //server 1/aspnet/default. aspx 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 1 st choice Kernel Mode Listener http: //server 1/sql/pubs? wsdl

Query Notifications • 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! Requests/ Responses IIS/ ASP. Net 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 Query & Subscription /Results Notification of Cache Invalidation SQL Server 2005

Service Broker

Service Broker 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

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)

Protocol Customer Service Travel Service Endpoint Protocol (Dialogue Semantic here) TDS Adjacent Broker Protocol (Message Fwding here) Adjacent Broker Protocol

Conversation Groups • Business transactions often involve multiple services collaborating to achieve a common end • E. g. Booking a reservation Hotel 2 5 Client 1 8 Travel 3 Airline 7 4 • Conversation Groups: 6 Car • 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

Example: Order Entry

Example: Parallel SP

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/

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/Building. XML/XMLand. Datab ase/ • Service Broker • http: //www. exforsys. com/content/view/1712/356/ • http: //www. sqlservicebroker. com/

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