2 Hosting the Runtime: SQL Server as a

2 Hosting the Runtime: SQL Server as a Runtime Host THIS CHAPTER DISCUSSES what it means to be a .NET runtime host. Topics include how SQL Server differs from other runtime hosts such as Internet Information Server (IIS) or Internet Explorer (IE) and how it is similar with respect to loading and running code. Finally, we ll show how you would catalog a user assembly to be stored in SQL Server. Why Care How Hosting Works? If you are a SQL Server developer or database administrator, you might just be inclined to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C# or VB.NET without knowing how it works. But you should care. SQL Server is an enterprise application, perhaps one of the most important in your organization. When the CLR was added to SQL Server, there were three goals in the implementation, considered in this order: 1. Security 2. Reliability 3. Performance

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

INTRODUCTION SQL Server Notification Services is an easy-to-use

INTRODUCTION SQL Server Notification Services is an easy-to-use but powerful framework around which you can build scalable notification applications that can notify millions of users about millions of interesting events, using standard notification protocols like SMS (Simple Message Service) and programs like MSN Messenger. The unique pieces, such as an application, are defined using XML and integrate with the framework (which runs as a series of stored procedures over a mostly system-defined schema). We ll discuss Service Broker and Notification Services in Chapters 15 and 16. In addition, SQL Server 2005 adds a Report Server to further extend the data-related platform, but this feature is outside the scope of this book. Where Are We? In this chapter, we ve had a whirlwind tour of the plethora of new technologies in SQL Server 2005, the problems they are intended to solve, and, in some cases, the entirely new data models they represent. SQL Server 2005 supports .NET programming, user-defined data types and aggregates, and an XML data type. The support for these alternate data types extends from the server out to the client, with System.Xml and an object- oriented API known as ObjectSpaces. In the rest of the book, we re going to explore the implementation and best practices when using these new data models, and see that SQL Server 2005 and improvements to the client libraries truly represent the integration of relational data, object-oriented data and concepts, and XML data.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services


EXTENDING SQL SERVER INTO THE PLATFORM messages must be sent to another SQL Server, but the messages can be sent to the same database, between databases in the same instance, or to a different instance on the same machine or different machines. The asynchronous messages work similarly to a message queuing system (they even use queues), but because SQL Server is controlling both the database data and the messages, messages can participate in a local database transaction. The Transact-SQL language is being extended to support the handling of queued messages that can be retrieved asynchronously. SQL Server Service Broker uses dialogs to achieve coordinated messaging with multimessage correlation semantics. A dialog is a bidirectional series of messages between two endpoints, in which each message contains a conversation ID to enable the other side to correlate messages, as shown in Figure 1-5. One of the main advantages of a dialog is that it maintains message order across transactions, threads, applications, and database restarts, something no other messaging system does. A dialog also allows guaranteed delivery of messages to a number of different subscribers. Multiple dialogs can be grouped into a single application known as a Conversation Group. A Conversation Group can maintain its state and share state between multiple dialogs. This goes beyond the concept of simple correlation ID message queuing systems, and it s handled automatically by Service Broker, rather than manually by the application programmer. Using asynchronous messaging inside the database enables building scalable systems, because work that had to be handled serially in the past can now be multiplexed in the same transaction. In addition, Service Broker guarantees that the messages will arrive in order, even across transactions, and guarantees once-only delivery. Messages Awaiting Processing by Endpoint Correlated by Conversation Endpoint Endpoint Endpoint Conversation 2 Conversation 1 Conversation 2 Conversation 1 Conversation 1 Conversation 2 Figure 1-5: Messages and Dialogs

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

INTRODUCTION implementation. After Booch, Jacobson, and Rumbaugh popularized

INTRODUCTION implementation. After Booch, Jacobson, and Rumbaugh popularized decomposition of any computing problem with a set of well-known modeling techniques, the saying in the computing world became Programmers just like to program with objects. It makes their job easier because it makes the problem domain easier to visualize. Techniques such as top- down design and structured programming have taken a backseat ever since. But data in a relational database such as SQL Server is decomposed into tuples and relations, not into object graphs. Therein lies the famous object-relational impedance mismatch. ObjectSpaces is a client-side object-relational integration technology that will be delivered as part of ADO.NET. This technology solves the problem of every programmer writing different code to coerce multiple rectangular Rowsets into object graphs that make up their programming object model. Since most client applications are built around object models, a standard set of APIs to ease the interaction between relational data and objects will be a welcome addition. In the past few years, the concept of entity objects was popularized, but this concept has suffered, in general, from poor performance. In addition, entity objects usually were implemented in such as way as to produce far too many database round-trips and/or database locks held for too long to be useful. ObjectSpaces employs optimization techniques such as partial and lazy loading of object graphs, including programmer specification of graph loading depth and smart updates and deletions of database rows corresponding to graph instances, to increase performance. ObjectSpaces graph accessor queries and mutators can be specified as stored procedures, to take advantage of SQL Server s processing power. In addition, you can query object graphs on the client side through an ObjectQueryclass and a simple but powerful predicate language known as OPath. Using OPath is reminiscent of using XPath to select a subset of nodes in an XML document or using a WHEREclause in a SQL query. Chapter 14 rounds out the discussion of relational data, object data, and XML with a discussion of Microsoft s object solution for the client or middle tier and its integration with SQL Server. Extending SQL Server into the Platform Service Broker and Notification Services Finally, SQL Server 2005 adds two more pieces of data access and application programming functionality that bear mention. SQL Server Service Broker allows you to use T-SQL to send asynchronous messages. These

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services


OBJECTSPACES OBJECTS ON THE MIDDLE TIER OR CLIENT XQuery Compiler XLT Compiler 2 Compilers Intermediate Format SQL Server Query Generator SQL:1999 Query Generator SQL-92 Query Generator 3 Query Generators SQL Server Specific Query SQL:1999 Specific Query SQL-92 Specific Query Example Only Compilers and Generators Added Independently Figure 1-4: Using the Common Query Abstraction to Support Multiple Interoperable Query Languages additional query languages and data sources other than native XML can be plugged in. This is pictured in Figure 1-4. The most common scenario for the common query abstraction will be to pass the resulting intermediate format through an IL generator to generate .NET IL code, which would then be executed against .NET XML classes (like the XPathDocument) on the client to provide native support for XML query and transformation languages like XQuery, XPath, and XSLT. The common query abstraction will be combined with a mapping technology that can map between relational data, XML data, and objects. One use of this will be to enable XQuery syntax over XML views of relational data, the natural successor to XML mapping schemas mentioned previously. The common query runtime will be able to compile an XQuery against an XML mapping (view) of data in SQL Server, and the resulting intermediate format will run through a SQL generator (rather than an IL generator), to generate Transact-SQL code for execution on SQL Server. The common query abstraction and mapping technologies will also be used to provide object-to-relational mappings and an object query language (OPath) in a technology known as ObjectSpaces. ObjectSpaces Objects on the Middle Tier or Client Some object-oriented techniques are enabled by the use of complex user- defined types in SQL Server as discussed previously, but object-oriented design is used to a much greater degree on the client and middle tier. Most graphic user interface systems, large client-side applications, and even the .NET base class libraries themselves are based on an object-oriented

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

INTRODUCTION Client-Side XML-Based APIs and SQL Server 2005

INTRODUCTION Client-Side XML-Based APIs and SQL Server 2005 Integration XML is ubiquitous in the SQL Server 2005-era client APIs. User-defined types use .NET XML Serialization to be able to be marshaled or persisted as XML directly. The FOR XML syntax has been extended to allow a user to fetch data in an XML type. In addition, SQL Server Analysis Services can directly consume queries in XML format and produce XML results. This is called XML for Analysis and has been around for a while; in SQL Server 2005 it becomes a first-class API directly supported and on a par with OLE DB for Analysis. If you ve gotten used to OLE DB for Analysis (or its automation equivalent, ADOMD), don t fret XML for Analysis uses an OLE DB style syntax, for both queries and properties. The XML data type and the XQuery engine inside SQL Server 2005 are complemented by a rich middle-tier or client-side model for XML. This model exposes XML data outside the server using a variety of data models. These models include the XML Document Object Model (DOM) and abstract XmlNavigator model, in addition to streaming models known as XmlReader and XmlWriter. The standard XML query and transformation models, XPath and XSLT, have been part of the .NET platform since its inception and are being extended to support client-side XQuery and XPath 2.0. The XmlReader and XmlWriter classes are being enhanced to enable support of the XQuery 1.0 and XPath 2.0 XML data model. This is necessary because the new XML data model differs from the XML 1.0 and name- spaces model. The XQuery 1.0 and XPath 2.0 data model extends the weakly typed nodeset model to include strongly typed sequences consisting of nodes, atomic values, or a combination of both. In addition, this data model is a closed data model, meaning that running an XQuery against an instance of the data model is guaranteed to produce an instance of the data model. In addition, enhancements to the XPathDocument class will enable it to support in-place editing and change tracking, and marshaling of sets of changes. This makes the in-memory client-side XML model on a par with the client-side relational DataSetclass. In a revolutionary approach to querying XML data from the client, a generalized client-side parsing and execution engine, known as the common query abstraction, will allow XPath, XSLT, and XQuery syntax to be compiled into an intermediate query format, in a method analogous to high-level languages being compiled into IL code. This allows programmers to use the query language that is most suitable for the job or the one that they are most familiar with. The query abstraction is extensible so that

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services


CLIENT ACCESS AND THEN THERE ARE CLIENTS supposed to ensure that ordinary clients could access the data, performance issues and the complexity of SQL queries (and XPath and XQuery queries, for that matter) ensure that very few (if any) users actually go right to Query Analyzer for the data they need, and no enterprise applications that we know of use Query Analyzer as a front end to the database. Applications, both rich Windows applications and Web applications, are written using high-level programming languages like C++, Visual Basic, and the .NET family of languages. With all the new features in SQL Server 2005, client libraries such as OLE DB have quite a lot of work to do just to keep up. Although the designers of OLE DB and ADO designed support for user-defined types into the model, the intricacies of supporting them weren t made clear until support for these types was added to popular mainstream databases like SQL Server 2005. OLE DB and ADO are very Rowset/Recordset-centric and have limited support for user-defined types, invoking methods on database types and extending the type system. The next version of these libraries adds support for fetching complex types in a couple of different ways: as a data type Object, or as a nested resultset (Rowset or Recordset). Most of the support in OLE DB and ADO leverages existing objects and methods, and extends them to support the new types. Support for the SQL:1999 information schema Rowsetsis another new feature in data access. The client-side .NET data provider for SQL Server, known as SqlClient, has an easier time of it. Since user-defined types are .NET types, code to process these types might be downloaded to a client from a SQL Server or stored on a network share. It s possible to coerce a column in a DataReader from type Object to type Person and use it directly in a .NET program. Techniques such as network-based code make this work. Handling complex data in place or storing complex types in a .NET DataSet presents a different set of problems. The first book of one of the authors (Bob Beauchemin), Essential ADO.NET (Addison-Wesley, 2002), describes many of these problems and theoretical solutions to them based on the existing .NET libraries. Now that SQL Server supports columns that are classes or columns that are XML, this becomes an interesting area. One limitation of SQL Server clients was that only a single Rowset or Recordset could be active at a time in the default cursorless mode of SQL Server data access. Different APIs solved this problem in different ways, and we ll talk about the repercussions of this in Chapter 11. SQL Server 2005 breaks through the one-active-rowset barrier by allowing the user APIs to multiplex active results on a single connection. This empowering feature is known as MARS (multiple active resultsets).

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

INTRODUCTION used. Using XML as a marshaling format,

INTRODUCTION used. Using XML as a marshaling format, framed by SOAP, possibly over HTTP, is known as Web Services. The popularity of XML and Web Services, like the popularity of SQL, is fairly easy to explain. Managers of computer systems have learned over time to shy away from proprietary solutions, mostly because companies often change hardware and operating system (and other) software over time. In addition, a company may have to communicate with other companies that use an unlike architecture. Therefore, protocols like HTTP, formats like XML and CSV (comma-separated value) files, and languages like SQL, XPath, XSLT, and XQuery tend to be used for a single reason they are available on every hardware and software platform. Consider as an example the RDS (remote data services) architecture used by Microsoft to marshal resultsets (known as recordsets) over HTTP in a compact binary format, as opposed to XML, a rather verbose text-based format. Because Microsoft invented the RDS marshaling format (known as Advanced Data Tablegrams, or ADTG), other vendors (such as Netscape) refused to support it. This is known as the NIH (not invented here) syndrome. On the other hand, visit any large software or hardware manufacturer on the planet and ask, Who invented XML? The answer is always the same: We did. Since XML (or SQL, to expand the analogy) could not possibly be perceived as a Microsoft thing or an Oracle thing, support is almost universal. SQL Server as an XML Repository and Web Service Server SQL Server 2005 supports creating Web Services and storing data to be used in Web Services at a few different levels. The XML data type and XQuery support mentioned previously is a good start. Data can be stored in XML format inside SQL Server and used directly with XQuery to produce or consume Web Services. With the addition of direct support in SQL Server for HTTP, we could think of SQL Server 2005 as a Web Service server. This reduces the three-tier architecture usually required to support Web Services (database, middle tier, and client) to a two-tier architecture, with stored procedures or XQuery/XSLT programs being used as a middle tier. Client Access And Then There Are Clients Client-Side Database APIs and SQL Server 2005 Database programmers, especially those who specialize in procedural dialects of SQL, tend to forget that without client access libraries, a database is just a place to keep data. Although the SQL language itself was

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services


WEB SERVICES XML AS A MARSHALING FORMAT XPath support in SQL Server 2000. XPath support in SQL Server 2000 is accomplished by a portion of the SQL Server OLE DB provider (SQLOLEDB) that took a mapping schema and an XPath query, produced a SELECT… FOR XML query and sent that to SQL Server. Native support for XQuery, combined with XQuery s design for optimization, and support for multiple documents (a series of XML columns) should improve on the already good support for querying XML data. Web Services XML as a Marshaling Format Marshaling data to unlike platforms has always been a problem. In the past vendors could not even agree on a marshaling format, let alone a common type system. Microsoft used COM (the component object model) for their distributed object model and marshaling format; they did not support CORBA, the common object request broker architecture. Processor differences such as endianness (byte ordering), floating point representation, and character set were considered in both these models; marshaling between systems required a reader make right approach that is, the receiver of a message had to determine the format and convert it to something understandable to their processor. In addition, the distributed programming models were plagued by the requirement to have a specific naming model or security system. As an example, porting COM to the Solaris platform required installing the equivalent of a Windows registry and an NTLM security service. But the biggest problem was network protocol and access to specific ports on network servers. COM not only used a proprietary protocol and network ports when run over TCP/IP, but required opening port 135 for the naming service to operate correctly, something that few system administrators would permit. By contrast, most system administrators gladly opened port 80 and allowed the HTTP protocol, even setting up special proxy servers rather than denying internal users access to the World Wide Web. Systems such as DCOM over HTTP and Java RMI over HTTP were the first steps away from a proprietary distributed programming system. Vendors such as Microsoft, IBM, Oracle, and Sun are moving toward supporting distributed computing over HTTP using a framing protocol known as SOAP and using XML as a marshaling format. SOAP itself uses XML to frame XML-based payloads; elements and attributes used in SOAP are defined in two XSD schemas. SOAP also defined a portable way of representing parameters to remote procedure calls (RPCs), but since the completion and adaptation of XML schemas, a schema-centric format is now

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

INTRODUCTION Essential ADO.NET 49.95 Like a SQL query,


Essential ADO.NET

Like a SQL query, an XPath query simply produces a resultset consisting of possibly multiple instances of items; unlike in SQL, these results are not always rectangular in shape. XPath results can consist of nodesets of any shape or even scalar values. In SQL, database vendors can implement a variation of SQL-PSM (persistent stored modules) that composes possibly multiple SQL queries and some procedural code to produce a more complex result. SQL Server s variation of SQL-PSM is known as Transact- SQL. XML processing libraries implement an analogous concept by using an XML-based nonprocedural language called XSLT. Originally meant to produce nice looking HTML pages from XML input, XSLT has evolved into an almost full-fledged programming language. Vendors have even added proprietary extensions to XSLT to allow it to execute code routines in procedural programming languages like Visual Basic or C#. Since XPath and XSLT were not originally developed to process large amounts of data or data from multiple sources, a new programming language for XML, known as XQuery, has been developed. XQuery implements many of the best features of XPath and XSLT, and is developed from the ground up to allow queries that include multiple documents. It is also designed specifically to be optimizable. In addition, it adds some of the syntax features of SQL. XQuery s data can be strongly typed; this also assists in query optimization. XQuery includes a query language, the equivalent of SQL Server SELECT, but does not define a standard implementation of DML, SQLServer s INSERT, UPDATE, and DELETEstatements. SQL Server 2000 allowed users to define mapping schemas (normal XML schemas with extra annotations that mapped XML items and concepts to SQL items and concepts) that represented all or a portion of the database as a virtual XML document, and issue XPath queries against the resulting data structure. In addition, SQL Server 2000 extended Transact- SQL to enable relational resultsets to be returned as XML. This consists of support for a FOR XML clause; three different subcategories of FOR XML are supported. The SQL Server 2000 support allowed XML document composition from relational data and XML document decomposition into multiple relational tables; this will be discussed further in Chapter 8. SQL Server 2005 extends this support by adding direct support for XQuery. The XQuery engine runs directly inside SQL Server, as opposed to

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services