Microsoft Cloud Authors: Jim Kaskade, Lori MacVittie, Andreas Grabner, Janakiram MSV, Pat Romanski

Related Topics: Microsoft Cloud

Microsoft Cloud: Article

SQL Server Web Services

Create and host XML Web services inside SQL Server 2005

These days, applications built around a service-oriented architecture (SOA) are all the rage. Complex systems can be written as a collection of services that communicate with each other through standard protocols. When implemented properly, these systems can become immensely flexible, scalable, and easy to maintain. The most common SOA designs utilize Web services as the communications medium, because they can be created and consumed by disparate systems and platforms. This highly useful capability has made Web services a very important part of today's information systems.

Up until now, the classic Microsoft implementation of a Web service involved a back-end database server running SQL Server 2000 and a front-end Web server running a .NET Web service application on IIS. Now that SQL Server 2005 is here, we have an additional option: we can create and host Web services using SQL Server alone. In this scenario, no Web server is needed. When running on Windows Server 2003 or Windows XP with Service Pack 2, IIS is not needed either. These operating systems provide the HTTP API, also known as HTTP.SYS, which SQL Server uses to provide native Web service functionality.

In this article I will show how to create a Web service inside SQL Server. I will show how to define the service and how to access it from a .NET client application. Along the way, we will see how incredibly flexible SQL Server's Web service support is. Hopefully, we will also gain an appreciation of how capable a tool SQL Server 2005 has become. Let's get started.

SQL Server 2005 provides Web services through endpoints. Endpoints define the properties of a connection, which can use either the HTTP or the TCP protocol. They can provide support for SOAP, Service Broker, and Database Mirroring. As you might guess, the syntax for creating a new endpoint is quite complex. Because of this, I am going to focus solely on the syntax required to create a Web service. Listing 1 shows an example of this. We'll walk through each line in detail.

Endpoints are created via the CREATE ENDPOINT statement. This statement consists of four main parts: endpoint name, the initial state, the AS clause, and the FOR clause.

Name and Initial State
The first two parts are easy: they provide a name for the endpoint and specify the state of the endpoint after it is created. The state can be STARTED, STOPPED, or DISABLED. STARTED indicates that the endpoint is actively listening for connections and will respond to connection attempts. STOPPED indicates that the endpoint will listen for connections and will respond to connection attempts, but will refuse the connection and return an error to the client. The last option, DISABLED, indicates that the endpoint will not listen for connections, nor will it respond to connection attempts. STOPPED is the default, but this can be changed either through the CREATE ENDPOINT statement or via an ALTER ENDPOINT statement at a later time.

AS Clause
The AS clause specifies the communications protocol that the endpoint will use. Here we have specified the HTTP protocol, which is the protocol required for a Web service. (TCP is an option for other types of services, and would have different settings than those described here.) The exact implementation details are controlled by a list of specific options included inside parentheses. Those used in our example are described here:

  • PATH specifies the URL of the endpoint. This setting, along with the SITE setting described later, fully specifies the URL that will be used to invoke the Web service. (For example, I've used '/Employee,' so the URL would be HTTP://ServerName/Employee.)
  • AUTHENTICATION specifies the type(s) of authentication that will be used. This is a comma-separated list of authentication methods, including those that we should all be familiar with: BASIC, DIGEST, NTLM, KERBEROS, and INTEGRATED. These work as expected, although additional in-depth information can be found in SQL Server Books Online. My example used Windows Integrated authentication only, so I've specified INTEGRATED.
  • PORTS specifies the type of port to use: CLEAR and/or SSL. CLEAR indicates a non-encrypted port, which requires clients to use the HTTP protocol. SSL indicates an encrypted port, requiring clients to use the HTTPS protocol. If required, both settings can be used simultaneously. The example uses unencrypted HTTP communications, so the appropriate setting is CLEAR.
  • CLEAR_PORT specifies the actual port that will be used by unencrypted communications. If not specified, port 80 is assumed. In this example, I've used port 8080 because my computer has IIS installed, which means that port 80 is already in use. There is also an SSL_PORT setting that we can use if needed, and it defaults to port 443. My example doesn't use SSL, so I haven't included this option.
  • SITE determines the ServerName portion of the Web service URL. If we use an asterisk ("*"), it instructs the endpoint to use all possible host names that aren't explicitly reserved. I used this setting in my example so that both HTTP://localhost/Employee and HTTP://jdixon/Employee would work. If we use a plus sign ("+"), then the endpoint will use all possible host names, even if they are reserved. Finally, we could explicitly specify the host name, which would ensure that only that name would be used.
  • COMPRESSION specifies whether client requests for compressed communications will be honored. If enabled, GZIP encoding will be used when client requests include the appropriate HTTP encoding header. Disabled is the default, and specifies that compression will not be used even if the client properly requests it. I've chosen to enable compression in the example, although I don't specifically need it.
Our example AS clause is now complete. There are other settings that can be used, especially when different authentication methods are chosen, but those are beyond the scope of this article. Instead, we need to move on and set up our service options.

FOR Clause
The FOR clause specifies the service that the endpoint provides. This can be SOAP, SERVICE_BROKER, or DATABASE_MIRRORING. We want to create a Web service, so we must choose SOAP. (Once again, the possible settings vary greatly depending upon which option we choose, so I'm going to restrict my explanation to SOAP-specific settings. Consult Books Online for more details.) The FOR clause consists of one or more WEBMETHOD clauses followed by some service-wide settings.

The WEBMETHOD clause does exactly what you would expect; it defines a single Web method. If a Web service needs multiple methods, then multiple WEBMETHOD clauses will be needed. In our example, there are two methods. The two methods are quite similar; therefore, I'll describe just the first one.

The first part of the WEBMETHOD clause defines the name, or alias, of the method. The alias can also include a namespace, but this is optional. If the namespace is not included, then the namespace from the endpoint is used instead. (The endpoint's namespace will be described in a moment.) After the alias, additional settings are used to specify exactly how this method will function. Note that these settings are included inside parentheses, and that the parentheses are required.

  • NAME specifies the fully-qualified name of the stored procedure or user-defined function that will be invoked by the method. The name must be in the standard database.owner.name format. My example uses a stored procedure named uspGetEmployeeManagers that is found in the AdventureWorks database, within the dbo schema.
  • SCHEMA determines whether the SOAP message for this method will include an inline XSD schema. There are three options; NONE, STANDARD, and DEFAULT. NONE specifies that no schema will be used, while STANDARD specifies that one will. DEFAULT specifies that the setting from the endpoint itself should be used. If no value is specified, DEFAULT will be assumed.
  • FORMAT determines the exact content of the method response. The options are ALL_RESULTS, ROWSETS_ONLY, and NONE. ALL_RESULTS instructs the method to return not only the output from the stored procedure or user-defined function, but also the row count and any error messages or warnings that may exist. ROWSETS_ONLY omits everything but the actual results. NONE suppresses the results too, making this option useful only in very specific circumstances (see SQL Books Online for details). The example uses ROWSETS_ONLY.
In the example, the second WEB-METHOD clause is almost identical to the first, so we'll move on to the service-wide settings.

The final four settings apply to the whole Web service, and not to a specific method:

  • BATCHES specifies whether ad hoc T-SQL statements can be executed via this service. DISABLED, the default, prevents this. ENABLED allows it. When enabled, a special method called SQLBATCH will be created that supports ad hoc queries.
  • WSDL determines the type of WSDL (Web Services Definition Language) document that the Web service will generate. A value of NONE prevents the WSDL from being generated. DEFAULT, on the other hand, directs that the WSDL will be generated. In those rare cases where a special WSDL document is needed, you can specify the name of a stored procedure that returns the special document.
  • DATABASE identifies the name of the database context against which the Web methods will be executed. Our service uses the AdventureWorks database, so that name is included here. If DEFAULT is specified instead, then the default database for the login will be used.
  • NAMESPACE specifies the namespace for the endpoint. If DEFAULT is used, or if no namespace is specified, then the namespace will be HTTP://tempuri.org. If a namespace is included at the WEBMETHOD level, then that namespace will override the one provided here.

More Stories By Jerry Dixon

Jerry Dixon is a senior developer and architect for ACH Food Companies in Memphis, Tennessee. Over the past 16 years he has led development projects for a number of enterprise, mid-level, and small business organizations. While he has fulfilled multiple roles as an infrastructure designer, database administrator, and software developer, he specializes in XML, SQL and ASP.NET. He is a co-leader and frequent presenter at the Memphis .NET User Group. Jerry holds the following Microsoft certifications: MCSD (VB 6.0 and .NET), MCDBA (SQL 2000), MCSA (Windows 2000 and 2003), MCSE (Windows 2000), MCAD (.NET), MCT. He resides in Olive Branch, MS with his wife and son.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

@ThingsExpo Stories
A completely new computing platform is on the horizon. They’re called Microservers by some, ARM Servers by others, and sometimes even ARM-based Servers. No matter what you call them, Microservers will have a huge impact on the data center and on server computing in general. Although few people are familiar with Microservers today, their impact will be felt very soon. This is a new category of computing platform that is available today and is predicted to have triple-digit growth rates for some ...
November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Penta Security is a leading vendor for data security solutions, including its encryption solution, D’Amo. By using FPE technology, D’Amo allows for the implementation of encryption technology to sensitive data fields without modification to schema in the database environment. With businesses having their data become increasingly more complicated in their mission-critical applications (such as ERP, CRM, HRM), continued ...
SYS-CON Events announced today that Cloudbric, a leading website security provider, will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Cloudbric is an elite full service website protection solution specifically designed for IT novices, entrepreneurs, and small and medium businesses. First launched in 2015, Cloudbric is based on the enterprise level Web Application Firewall by Penta Security Sys...
SYS-CON Events announced today that SoftNet Solutions will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. SoftNet Solutions specializes in Enterprise Solutions for Hadoop and Big Data. It offers customers the most open, robust, and value-conscious portfolio of solutions, services, and tools for the shortest route to success with Big Data. The unique differentiator is the ability to architect and ...
Most people haven’t heard the word, “gamification,” even though they probably, and perhaps unwittingly, participate in it every day. Gamification is “the process of adding games or game-like elements to something (as a task) so as to encourage participation.” Further, gamification is about bringing game mechanics – rules, constructs, processes, and methods – into the real world in an effort to engage people. In his session at @ThingsExpo, Robert Endo, owner and engagement manager of Intrepid D...
WebRTC adoption has generated a wave of creative uses of communications and collaboration through websites, sales apps, customer care and business applications. As WebRTC has become more mainstream it has evolved to use cases beyond the original peer-to-peer case, which has led to a repeating requirement for interoperability with existing infrastructures. In his session at @ThingsExpo, Graham Holt, Executive Vice President of Daitan Group, will cover implementation examples that have enabled ea...
SYS-CON Events announced today that Enzu will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their online busine...
In the next forty months – just over three years – businesses will undergo extraordinary changes. The exponential growth of digitization and machine learning will see a step function change in how businesses create value, satisfy customers, and outperform their competition. In the next forty months companies will take the actions that will see them get to the next level of the game called Capitalism. Or they won’t – game over. The winners of today and tomorrow think differently, follow different...
SYS-CON Events announced today that Roundee / LinearHub will exhibit at the WebRTC Summit at @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. LinearHub provides Roundee Service, a smart platform for enterprise video conferencing with enhanced features such as automatic recording and transcription service. Slack users can integrate Roundee to their team via Slack’s App Directory, and '/roundee' command lets your video conference ...
Enterprise IT has been in the era of Hybrid Cloud for some time now. But it seems most conversations about Hybrid are focused on integrating AWS, Microsoft Azure, or Google ECM into existing on-premises systems. Where is all the Private Cloud? What do technology providers need to do to make their offerings more compelling? How should enterprise IT executives and buyers define their focus, needs, and roadmap, and communicate that clearly to the providers?
SYS-CON Events announced today that Sheng Liang to Keynote at SYS-CON's 19th Cloud Expo, which will take place on November 1-3, 2016 at the Santa Clara Convention Center in Santa Clara, California.
More and more brands have jumped on the IoT bandwagon. We have an excess of wearables – activity trackers, smartwatches, smart glasses and sneakers, and more that track seemingly endless datapoints. However, most consumers have no idea what “IoT” means. Creating more wearables that track data shouldn't be the aim of brands; delivering meaningful, tangible relevance to their users should be. We're in a period in which the IoT pendulum is still swinging. Initially, it swung toward "smart for smar...
DevOps is being widely accepted (if not fully adopted) as essential in enterprise IT. But as Enterprise DevOps gains maturity, expands scope, and increases velocity, the need for data-driven decisions across teams becomes more acute. DevOps teams in any modern business must wrangle the ‘digital exhaust’ from the delivery toolchain, "pervasive" and "cognitive" computing, APIs and services, mobile devices and applications, the Internet of Things, and now even blockchain. In this power panel at @...
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
In past @ThingsExpo presentations, Joseph di Paolantonio has explored how various Internet of Things (IoT) and data management and analytics (DMA) solution spaces will come together as sensor analytics ecosystems. This year, in his session at @ThingsExpo, Joseph di Paolantonio from DataArchon, will be adding the numerous Transportation areas, from autonomous vehicles to “Uber for containers.” While IoT data in any one area of Transportation will have a huge impact in that area, combining sensor...
Established in 1998, Calsoft is a leading software product engineering Services Company specializing in Storage, Networking, Virtualization and Cloud business verticals. Calsoft provides End-to-End Product Development, Quality Assurance Sustenance, Solution Engineering and Professional Services expertise to assist customers in achieving their product development and business goals. The company's deep domain knowledge of Storage, Virtualization, Networking and Cloud verticals helps in delivering ...
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
In his general session at 18th Cloud Expo, Lee Atchison, Principal Cloud Architect and Advocate at New Relic, discussed cloud as a ‘better data center’ and how it adds new capacity (faster) and improves application availability (redundancy). The cloud is a ‘Dynamic Tool for Dynamic Apps’ and resource allocation is an integral part of your application architecture, so use only the resources you need and allocate /de-allocate resources on the fly.
In the next five to ten years, millions, if not billions of things will become smarter. This smartness goes beyond connected things in our homes like the fridge, thermostat and fancy lighting, and into heavily regulated industries including aerospace, pharmaceutical/medical devices and energy. “Smartness” will embed itself within individual products that are part of our daily lives. We will engage with smart products - learning from them, informing them, and communicating with them. Smart produc...
In his keynote at 19th Cloud Expo, Sheng Liang, co-founder and CEO of Rancher Labs, will discuss the technological advances and new business opportunities created by the rapid adoption of containers. With the success of Amazon Web Services (AWS) and various open source technologies used to build private clouds, cloud computing has become an essential component of IT strategy. However, users continue to face challenges in implementing clouds, as older technologies evolve and newer ones like Docke...