How SQL Server 2005 Enables Service-Oriented Database Architectures

  • Article

Writer: Don Kiely

Applies To: SQL Server 2005

Summary: Databases are a strategic part of a distributed architecture built using the Service-Oriented Database Architecture (SODA). This paper explores the concepts behind SODA and how SQL Server 2005 fits into this architecture.

On This Page

Introduction
Data in a Service-Oriented Architecture
SQL Server 2005 SODA Features
SODA Computing Topologies
Conclusion
References

Introduction

The dominant client-server and n-tier application architectures of the 1990s ran into serious scalability and availability issues when used to implement massive Internet e-commerce sites. One of the major problems is that data tended to be stored in a massive, centralized database that all client components had direct access to. Virtually all communication with the database was in the form of SQL statements or batches of statements in a stored procedure, so that the client received a set of data for the specific task at hand.

Other problems arose when trying to incorporate “legacy” systems into newer applications. After decades of deploying a wide variety of systems using various proprietary technologies and platforms, the world was awash in systems that did their job perfectly well but had no clear path to interact with other applications in an increasingly connected environment. Achieving the agility needed by today’s applications has been extremely difficult. Business-to-business (B2B) interactions complicate things even further, requiring standard and reliable ways of conducting business electronically. Clearly, evolving systems that meet the needs of today’s global business environment require an architecture that use legacy systems efficiently and provide an agile commerce infrastructure.

In response to these kinds of needs, the last three to five years have seen the emergence of a large-scale, loosely-coupled, distributed system architecture, particularly as Internet e-commerce sites have grown into major commercial operations. Service-Oriented Architecture (SOA) has emerged as the dominant loosely coupled, service-centric architecture. Applications based on SOA are more resistant to failure and are more easily scaled up by adding resources using a variety of methods as necessary to meet changing demands, and they allow integration of legacy systems into B2B and other systems.

SOA service providers, consumers, and other components handle data as a natural feature of their roles in an SOA application. An SOA application typically still uses central databases to store and protect data, but is likely to have many such large databases that hold classes of data, such as separate storage of sales, manufacturing, and operations data, and specialized subsets of each. Each service provider and consumer may have a localized need for cached data or its own specialized data store. And, the messages that travel between the distant parts of the application are themselves often data that is worth archiving for various uses.

Data can be partitioned based on its characteristics in the system in four general ways:

  • Reference data is used to create service requests, such as a product catalog. It must be in a format that is usable by all parties, and is identified in a way that doesn’t change over time, such as a catalog date.

  • Activity data is ephemeral data used to perform a specific activity, such as a pick list that is used to retrieve purchased items from inventory. Since it is private to the service, the format doesn’t need to be understood by other parties.

  • Resource data is long-lived data that is used internally by a service, such as SKUs, customer data, and account data.

  • Service Interaction data is used to communicate between services. It must be in a format that is understood by all parties, and must remain constant over time. For example, an order form is communicated between services. If the order is lost, it must be able to be regenerated in the same form as the original and transmitted again.

Figure 1 shows a few of the endpoints that might make up a loosely coupled application that is built using SOA principles. A service consumer—which could be a client application, a server application such as a Web server, or any other kind of application—sends a message to a service provider. In complex systems, a message router might initially receive the message and apply some logic to route the request to the appropriate service provider. The service provider then receives the message, perhaps unpacks and reformats it, does whatever work is required, and then might send a response back to the service consumer.

Cc966516.soda_dk01(en-us,TechNet.10).gif

Figure 1:  Small portion of a Service-Oriented Architected application

The important detail in Figure 1 is that each node in the transaction is receiving, storing, and transmitting data in various forms. Sometimes the data is transient, and other times each node might persist the data either to a cache or to its own local database. (For a provocative discussion of messages as “interesting data” that is worth persisting, see “Queues Are Databases” by Jim Gray of Microsoft Corporation. A list of references is included at the end of this white paper.)

In light of these new ways of handling data within an application, the databases at the core of SOA applications face a different set of challenges than did monolithic, n-tier applications. Data integrity is just as important as ever, but now there are additional requirements:

  • The database must operate in an environment where requests come via XML-based messages rather than dedicated connections

  • Repositories of cached data need to know when to refresh the data more efficiently than doing a refresh on a set schedule

  • The database has to participate in dialogs that must occur in a set sequence

  • There is complex logic that must be hosted on or near the database.

XML makes a good message format for widely distributed systems. It is easily parsed by almost any system and has a schema-modeling language to define the proper structure of the data. Systems exchanging messages can attach information to an XML message such that data accumulates in the message as it flows through the system. Systems can parse and process what they understand and ignore the rest. Simply put, XML was designed to be an adaptable format to support distributed systems.

The architects at Microsoft recognized these architectural trends and built Microsoft® SQL Server™ 2005 to meet new challenges while continuing to support the many existing non-SOA applications. This white paper discusses native Web service access, database change notifications, Service Broker, and SQLXML in the context of using SQL Server 2005 within an SOA application. It does not introduce you to SOA nor does it give an overview of all new features in SQL Server 2005, but assumes that you are familiar with these topics. For more information on these topics, see the references at the end of the paper.

Data in a Service-Oriented Architecture

When exploring the concepts of SOA, it quickly becomes clear that each component in the overall system is receiving, processing, and transmitting data as one of its primary functions. Even if a service provider’s response to a message sent from a service consumer is to simply flip a bit to turn something on or off without interacting with a database, the provider must process the data in the message in order to determine the work that is to be done. But modern business applications deal extensively in data, so it is common for an SOA component to have access either to a local or centralized database or frequently both.

Many of the new features in SQL Server 2005 are part of an integrated architectural design that supports the use of the database as an SOA service provider. The SQL Server team at Microsoft calls this the Service-Oriented Database Architecture, or SODA.

There are a number of compelling reasons for implementing SOA features directly in the database engine, including:

  • Scaling up and down. In even the largest enterprise SOA application, an individual service might be instantiated at almost any scale; a lightly used service might have less activity than a typical small departmental database. Integration with SQL Server means that a service program can take advantage of all the native support for scaling from embedded devices to the most substantial enterprise database server, without increasing administrative complexity. Service logic code can execute at any scale, and any implementation can scale out to a separate middle tier at deployment time. With SQL Server 2005, service logic can run either in the data tier or be deployed in the middle tier. If you design an application carefully, how to scale can be a deployment decision rather than a design or development-time decision.

  • Scaling out. You can scale out data-centric computing in a number of ways, generally either by scaling out the database or by distributing the processing by using Service-Oriented Architecture. Scaling out the database results in a database cluster that is relatively tightly coupled, while the service-oriented solution is more loosely coupled. Building support for SOA directly in the database reduces the component processes required for a true grid solution.

  • Messages are data. The various request and response messages are “interesting data” that may have enough value to be archived in a database. Keeping messages available over time provides a history that allows you to audit and analyze transactions. Because messages are stored in tables and have system catalog views available, you can easily use Transact-SQL to see the status of any part of the system.

There are enormous benefits to implementing SOA features in SQL Server so that it can act as a stand-alone service provider in an SOA application. But to do so, it must be able to act like a service provider, which demands a minimum set of capabilities.

Requirements for an SOA Service Provider

For SQL Server 2005 or any database engine to take on a role as a stand-alone SOA service program, it must implement several features beyond its native ability to handle data:

  • Endpoint Support. The SODA provider must provide support for communication to receive and transmit messages, typically as a TCP socket, HTTP GET or PUT, SOAP endpoint, or other type of endpoint.

  • Process Service Requests. Most messages in SOA are formatted using XML, so the service provider must be able to process and possibly transform the enclosed data into other forms as needed by the components that make up the service. It must also be able to participate in complex dialogs and conversations as interdependent messages are received and sent to other components.

  • Service Logic Host. The provider must be able to perform whatever complex logic is required to process the message and provide the necessary response, as well as possibly coordinate the input of several other services. This may require common application server tasks, such as pooling resources, activation, and scaling out logic processing.

The various new features in SQL Server 2005 provide support for these functions, besides plenty of other infrastructure to support data management. For example, a service provider must securely participate in an SOA system and be able to authenticate clients and in turn provide credentials to authenticate itself to others, provide durability, participate in conversations and transactions, and other application-level features.

SQL Server 2005 builds on the features of the SQL Server 2000 relational database engine as well as interim releases of new technologies since its original release, such as SQLXML 3.0, Notification Services, and other tools, to fully realize the Service-Oriented Database Architecture.

SQL Server 2005 SODA Features

SQL Server 2005 includes the features to implement SODA so that a single SQL Server process can function as a full-service provider in a distributed, loosely coupled application. Microsoft expects that similar features will ultimately be as common in enterprise database systems as network communications, thread pooling, and stored procedures or their equivalents already are. These new features of SQL Server 2005 are covered in this section:

  • Native Web Service Access, to allow message-based communication based on SOAP and other protocols that takes advantage of the Windows Server 2003 HTTP kernel-mode driver, Http.sys.

  • Service Broker, a new class of transactional middleware that is service-centric, rather than message-centric, to support scalable services.

  • Query Notifications that allow data-dependent caches to receive a notification that data requires refreshing because the underlying database has changed. The notification is generated based on the original query that was used to create and populate the cache.

  • SQLCLR that deeply integrates sophisticated logic processing into the database to reduce latencies due to remote data access.

Native Web Service Access: HTTP and Other Endpoints

For years, the only real way to communicate from a client to a server running SQL Server was by using the proprietary Tabular Data Stream (TDS) protocol. TDS is still the fastest and most efficient data access method, but to communicate with the server the client must have the proper libraries installed. For SQL Server 2005 to be a full SOA service provider, it must support standards-based protocols to provide endpoints for accepting and processing service requests for any kind of consumer. The SQLXML extensions to SQL Server 2000 laid the foundation for this feature by including an ISAPI filter to use with Internet Information Services (IIS) to allow HTTP Web service-based communication with SQL Server.

SQL Server 2005 supports a formal endpoint abstraction that you can use to support a variety of endpoint types, including TDS, database mirroring, Web services, and Service Broker. The HTTP endpoint type allows the SQL Server instance to serve as a service provider for any kind of application on any kind of device that has support for Web services over HTTP and the WS-Security protocols in an SOA solution.

Full native Web service access requires that SQL Server 2005 be installed on Windows Server 2003 to take advantage of the Windows Server kernel-mode HTTP listener. SQL Server can register with the HTTP listener to reserve portions of the URL namespace. Figure 2 shows the tight integration and direct connection between the driver and database. When the HTTP listener receives an HTTP request over port 80 (by default), it routes the request directly to the endpoint that you define within SQL Server. SQL Server then does whatever processing is required, and then returns a response through Windows.

Cc966516.soda_dk02(en-us,TechNet.10).gif

Figure 2:  SQL Server 2005 integration with the Windows Server 2003 Http.sys driver

Because the installation doesn’t require IIS and because requests are sent directly from the kernel-mode Http.sys driver to SQL Server, such requests are efficient and simple to administer. Http.sys provides kernel-based process isolation between the various applications that might own different portions of the URL namespace, so different endpoints in the same and other instances of SQL Server cannot interfere with each other.

After the HTTP listener in Windows is configured to send requests to SQL Server, a database administrator (DBA) can define endpoints and bind stored procedures and scalar-valued functions to an endpoint as Web methods. The CREATE ENDPOINT statement requires that you specify a unique URL that will be used to listen for incoming HTTP requests. Normally these will be of the form “http://myServer.com/myEndpoint” so that the DNS system can direct the request to the proper server. The server in turn routes the requests to the proper defined endpoint, and then to the SOAP processing layer within SQL Server. Each SQL Server instance can have multiple endpoints, each of which in turn can have multiple Web methods bound to it.

A Web method that is bound to an HTTP endpoint can be a stored procedure or scalar-valued user-defined function. The name of the method within SQL Server does not have to be the same as the publicly defined Web method name; SQL Server will execute the correct code as defined for the Web method. The implementation of the Web method doesn’t need to have any code to parse the request or to format the result for return to the service consumer because SQL Server 2005 manages those functions for you. You can allow the execution of batches of SQL statements against an endpoint; however, this is probably not useful in an SOA application and is unwise for security reasons other than in highly controlled environments.

An endpoint can be configured to automatically generate and provide Web Service Definition Language (WSDL) data that specifies the interface of the provider’s Web methods. You can also configure the server to provide custom WSDL if you need to hand-tune the information provided, or opt to not provide WSDL at all.

Endpoints support Basic, Digest, Integrated (NTLM or Kerberos), and SQL Server Authentication, but do not support anonymous requests. This selection of authentication methods supports almost any client in a mixed-platform SOA application; you can use Integrated authentication with Windows consumers and SQL Server Authentication for all other consumers, for example. SQL Server supports the WS-Security specification, so you can pass credentials by using the Username token headers for SQL Server Authentication. You can further restrict or allow requests based on the consumer’s IP address.

HTTP endpoints are off by default, so that a newly installed instance of SQL Server is secure by default. This means that attacks aren’t possible unless HTTP endpoints are explicitly enabled by an administrator. Only members of the sysadmin fixed server role or the endpoint creator can initially connect to the endpoint until they explicitly grant permission, by using the GRANT CONNECT statement, to allow other users to connect. As with any HTTP connection, you can secure the channel by using SSL to protect any clear text credentials that some of the authentication methods transmit.

Reliable Messaging: Service Broker

One of the primary requirements for an SOA service program is that it be able to communicate with other components in the application. A robust messaging infrastructure must be secure, asynchronous, reliable, and scaleable. Because SOA is built on messaging, SQL Server 2005 must support messaging if it is going to be a service provider.

SQL Server 2005 Service Broker is a messaging feature that receives, processes, and sends messages. It is tightly integrated with the database engine. Service Broker facilitates the exchange of messages among SOA service programs. Service Broker provides a variety of features for applications that rely on guaranteed delivery of ordered messages:

  • Asynchronous. By queuing messages, service programs can handle workload efficiently without severe blocking during peak loads.

  • Ordered. In a conversation between a service consumer and provider, the order of messages often matters. A system must respect the order of sent messages and guarantee that processing will occur in the correct order.

  • Reliable. With its close integration with the database engine, Service Broker takes full advantage of message and dialog transactions to ensure that critical messages are received exactly once.

  • Durable. Whether a message and its processing succeeds or fails, the message or results must survive a system failure. If processing fails, the message should be placed back on the receive queue.

Service Broker Objects and Processing

Service Broker introduces several new database objects, such as message types, service contracts, queues, services, dialogs, conversation groups, and service programs. Building a service provider involves multiple steps to create and configure some or all of these objects, using various new forms of the Transact-SQL CREATE statement. Once these objects are in place, you can send XML-formatted messages by using the Transact-SQL SEND statement and a service program can pull them off a receive queue with the RECEIVE statement.

Messages are sent to a service that you define, which places the messages into a queue to await processing by a service program, as shown in Figure 3. Most often a service program is a stored procedure that acts much like the Windows message pump, churning through messages, processing them, and sending replies. Service Broker uses a model that is something between the pull and event models, activating the service program that is bound to a queue as needed to process messages that have arrived. Service Broker can activate additional service programs up to a predefined limit as needed to avoid bottlenecks and efficiently handle peak loads.

Figure 3:  Service Broker structure and single Dialog

Figure 3:  Service Broker structure and single Dialog

The Service Broker queue is the primary means of supporting reliable, asynchronous message processing. Because a queue is a database table, queued messages receive the same benefits as other relational data, including recovery in the event of a database failure and guaranteed delivery to the queue using transactions with the same ACID benefits (atomicity, consistency, isolation, durability) as other data.

Service Broker does more than just manage queues, however. It provides a complete framework to support applications that require reliable messaging. Besides saving developers from creating the complex messaging infrastructure—a daunting task in the best of situations—Service Broker manages messages so that they are delivered in the proper order. It also manages conversations, contracts, routing, and remote service bindings.

When a traditional message-oriented application goes beyond a simple request/response messaging pattern, it must handle correlation and concurrency control issues between messages destined for the queue. This can be a problem when there are multiple service programs that are pulling messages from a queue. Service Broker uses conversation group locking to handle concurrency issues. A conversation group is the collection of all dialogs used for a particular task. A dialog is the exchange of messages between service programs, each alternating as a service consumer and provider.

Conversation Groups

Figure 4 shows a typical conversation group in response to the receipt of a Process Order message in a simple order entry system. When the Process Order message is received, the Order Processing Service sends messages to the Credit and Inventory Check Services. At that point, while waiting for responses in the ongoing conversation, the pending state can be committed to the database. Then the responses from the two services can arrive, individually or virtually simultaneously, which activates the conversation group.

Cc966516.soda_dk04(en-us,TechNet.10).gif

Figure 4:  Conversation Group that consists of multiple dialogs

It is conversation group activation—rather than the arrival of a message in a queue—that prompts service programs to process messages. An active conversation group is processed within a transaction that may only be processed by a single service program. This has a number of implications for different scenarios:

  • The Credit Check response is received while a service program is processing the Inventory Check response. In this case, the Credit Check response is not processed until the conversation group has committed and been reactivated by another service program.

  • Both responses arrive before any service program consumes the active conversation group. The service program is presented with both messages, batched together. In addition, the active service program could check for additional messages in the queue and process them before completing its work.

In the second case, batch processing can lead to scaleable solutions as work associated with a conversation group can accumulate in response to the processing latency of service programs. Conversation groups can also optimize state retrieval when state information is saved and associated with the GUID that identifies the group. Any service program that processes messages that are associated with the conversation group is able to retrieve the conversation group state once rather than for each message.

The entire conversation group needs to have its work committed in total or not at all. If all goes well, the conversation group state can be durably committed to the database, effectively processing the order, which in turn causes more messages to be sent, such as to a shipping service. If anything goes wrong, the original message is placed back on the original Order Processing Service queue and the state of the database is restored to what it was prior to receipt of the message.

When a service program grabs a message from a queue that is part of a conversation group, the group is locked so that the conversation’s messages are processed in the order specified by the service consumer. Service Broker uses this mechanism to ensure that messages are processed in the correct order.

Integration with the Database

Service Broker is built into the SQL Server 2005 database engine; it does not use a separate transactional middleware or message queuing infrastructure such as Microsoft Message Queue. This provides a number of benefits (a few of which are fully realized only when the service consumer and provider are in the same database):

  • Service Broker applications are developed by using Transact-SQL and SQLCLR. You build a Service Broker application using stored procedures, user-defined functions, and .NET Framework code, all supported by Service Broker extensions to Transact-SQL. You can leverage your existing skills when building a service provider with SQL Server.

  • Conversation group locking supports multiple queue readers. Service Broker uses a special database lock that allows multiple service programs to process messages in a single queue.

  • Administration is simpler. Since both messages and data located in the same database, any recovery activity restores both messages and data. If they are separate, they can easily get out of synchronization and it can be extremely difficult to figure out which messages have been processed. If the database uses mirroring, clustering, or other data protection features of SQL Server, the messages have the same protection as other data. In addition, you need only set security options in one place, even though you have the flexibility to set permissions on Service Broker and data objects as required.

  • Processing is more efficient. While Service Broker supports exchanging messages with other service broker services, if the consumer and provider service are in the same database a message can be sent directly to the receive queue, thereby bypassing the send queue and saving significant processing time. And because Service Broker has a global view of all of the queues in a database instance, it can efficiently manage messages, queues, and the infrastructure.

  • Common tools and languages are supported. You use the same tools and languages—such as Transact-SQL, Microsoft Visual Basic® .NET, Microsoft Visual C#®—both to manipulate data in the database and to manage Service Broker. Developers and administrators can use the tools they are familiar with to develop and manage messaging applications.

Service Broker differs from other transactional middleware in another important way: it is service-centric rather than message-centric. This lets SQL Server handle complex concurrency control of messages received from multiple concurrent service programs and allows transaction processing optimizations.

Query Notifications

One of the most useful ways to improve the performance of widely distributed, loosely coupled applications is to cache data. By caching rarely changing and easily refreshed data at a service provider or consumer that is remote from the source database, messages can be lighter weight and use less bandwidth.

Such a system needs a way to refresh data when data changes at the source. There are plenty of ways to do this. Probably the easiest method from a programming perspective is to just set a cache to expire periodically, anywhere from a few minutes to a few weeks, depending on the type of data. But such schemes are approximations, and tend to either refresh too often so that data is unnecessarily sent over the network or not often enough so that the data becomes stale. In many situations, the most efficient method is to simply notify the keeper of each cache that the data has changed, and either push the data out from the source database or pull it from the service program. A service program can use its own logic and choose to ignore the notification, for example, if it determines that the data doesn’t need to be refreshed or if the refresh can be deferred to off-peak hours.

Following is the general workflow for a query notification:

  1. A service application submits a SQL query to the database server and includes a special annotation that the service requests notification when the data in the query changes.

  2. SQL Server generates a subscription request and registers it for the query. It then creates an optimized execution plan and executes it.

  3. The query results are returned to the client, typically destined for a cache that is managed by the client.

  4. The database monitors for any change that would change the data in the query or its structure. When it detects such a change, it generates a change notification and sends the notification to a queue.

  5. The notification is processed and sent to the client service application.

    Cc966516.soda_dk05(en-us,TechNet.10).gif

    Figure 5:  Workflow for query notification

No matter how many changes are made to the underlying data, the client application receives a single query notification. To receive another notification for changed data, the client application must query the database again, once again including the notification request annotation. This is typically done when executing the query that refreshes the data cache.

Service Logic Host: SQLCLR

All of the features discussed so far provide critical infrastructure for SQL Server 2005 to function as a viable participant in an SOA application. The remaining critical ingredient is logic—the ability to run high-level code to control all aspects of the system, process inputs, and respond to changing conditions in the environment. SQL Server has long included Transact-SQL, an extension to the set-based Structured Query Language, as well as extensions, such as extended stored procedures and the ability to invoke external applications such as COM components using the sp_OA system stored procedures.

But to be a viable SOA service provider, it must be possible to run integrated, high-level, well performing code capable of materializing highly complex business logic abstractions. Transact-SQL is great for manipulating set-based, relational data but it is not up to the task of writing business logic. The Microsoft .NET Framework and .NET-compatible languages provide exactly the programming infrastructure and syntax that is needed. The .NET Framework version 2.0 has a CLR Hosting API that lets developers execute common language runtime (CLR) applications within a host process such as SQL Server 2005. You can use this SQLCLR feature of SQL Server to write .NET Framework code that runs within the SQL Server process, is close to the data and the other SODA features, and is integrated with database security. Code assemblies are stored and loaded directly from the database instead of the file system. This improves efficiency and reduces administrative complexity.

You can use the SQLCLR to write functions as replacements for extended stored procedures, and to write user-defined functions. It has a variety of other uses within the database for more traditional applications (although it does not replace all Transact-SQL applications). In an application that is based on SODA, hosting the CLR provides a code execution environment that supports multiple programming languages, garbage collection memory management, resource pooling, system resource management, and code access security.

Figure 6 shows how the SQLCLR integrates with the existing system and extended stored procedures in a common operating system process. The primary SQL Server process works with the CLR to control global process resources such as memory, concurrency primitives, and threads.

Figure 6:  SQLCLR Hosting Layer

Figure 6:  SQLCLR Hosting Layer

The integration of SQLCLR radically changes and expands the programming paradigm within SQL Server. It allows the development of custom types, custom aggregates, triggers, user-defined functions, logic-intense stored procedures, and other code objects that overcome the limitations of a SQL-based language and execution engine. This sophistication is required by a service program within a service-oriented architecture. You can place any or all of the logic within the database rather than develop and deploy a separate application component external to the database for logic processing. For more traditional 3- or n-tier applications, the line between hosting data processing code in the database and business logic in a middle tier is blurred. But it is now relatively easy to develop one code base that you can deploy either to the database or to a middle-tier application server as demands placed on the system change.

The SQLCLR feature set can be used to extend the SQL function and aggregate library available to the SQL language. Interfaces are available to implement user-defined functions, table-valued functions, and user-defined aggregates. Once installed, these functions are available for use within any user query. Since the CLR functions are compiled into intermediate language (IL) and ultimately into native machine instructions, the functions can run much faster than equivalent functions written in Transact-SQL, which are interpreted at run time rather than compiled beforehand. In many real-world scenarios, application speed has increased by 10 times or more when Transact-SQL functions or table-valued functions are rewritten in SQLCLR.

SQLCLR also provides a SQLClient data access library, which can run inside the relational engine. When SQLCLR data access code is executed within the SQL Server process as shown in Figure 6, latencies and the transaction costs of connecting to a remote database and acquiring data are significantly reduced. Data that is required to process a service request from an external server does not have to travel across a portion of the network or even from an adjacent machine on the server rack. On the other hand, SQLCLR code competes with other general database functions for server processing resources. Careful design is required to determine the best architecture for any particular application.

SODA Computing Topologies

Building SOA on a database presents some interesting topologies since SQL Server scales from a single laptop to enterprise-class servers, with logic hosted within the SQL Server process or on other network resources. For example, SQL Server 2005 supports occasionally connected scenarios where data is cached on a laptop for use during the day when it is disconnected from the network and then refreshed when reconnected. Best of all, scaling an SOA application doesn’t require changing the service contract; a loosely coupled SOA application doesn’t care how and where a specific component implements its logic.

For example, Figure 7 shows a service program that is completely contained within a SQL Server process, exposing a native Web service access to receive and send messages. This scenario reduces the latency between application logic and data, but consumes server resources that would otherwise be available for database work.

Figure 7:  Typical SODA service process

Figure 7:  Typical SODA service process

There is no requirement that the service program be hosted within the SQL Server process. Instead, it could execute in another process on the same machine or to an entirely different machine, shown in Figure 8. This supports scaling out service logic while the database engine handles processing messages to handle service requests and responses and regular database engine functions.

Figure 8:  Scaling out the service process

Figure 8:  Scaling out the service process

When applied to highly scaled applications, the service broker architecture enables implementation of sophisticated service routers that take care of routing dialogs across a tier of scaled services. This could take advantage of the routing capabilities of an SOA application and is shown in Figure 9.

Figure 9:  Scaled-out services using a Service Router

Figure 9:  Scaled-out services using a Service Router

SOA supports very flexible topologies, and SODA builds on those features to allow appropriate scaling and topologies even within a single service program hosted within SQL Server 2005.

Conclusion

Service-Oriented Database Architecture allows SQL Server to play a significant role in the development of applications based on loosely coupled, service-oriented architecture. SQL Server 2005 includes the Web service access, database change notification, transactional middleware, and integrated SQLCLR features because such features are an efficient way to implement a service program in an SOA application. Other database engines integrate with such features. However, including them directly in the database engine takes advantage of the SQL Server native ability to manage data and support a variety of methods of scaling up and out, as well as a variety of computing topologies. All of the features described in this paper can be used independently. However, when used together in a service program, they form a synergistic platform that can be used to create independent and autonomous service components.

SODA provides an alternative to traditional application servers and enables a new class of service-centric applications.

References

Service-Oriented Database Architecture

Service Oriented Architecture

SQL Server 2005

For more information:

https://www.microsoft.com/technet/prodtechnol/sql/default.mspx

Download

Top Of Page HowSQLEnablesSODA.doc
209 KB
Microsoft Word file

Get Office File Viewers